yes/no in Access

[Rob Meade]

...

('test','','','','','','','','','','','1')

I'm baffled. Can you help?

Try 'true' instead of the 1, failing that can you please post the exact
error message you are getting...and the code you are using with values etc
(use a response.write)

Rob

 

[middletree]

A couple of days ago, I posted a qusstion as to why my simple form isn't
working. The error I get in the browser is:
Data type mismatch in criteria expression.
/grace/shapethankyou.asp, line 197

The error I get when I try to run it in Access 2000 is that it's a "Type
conversion failure"

The SQL Statement is:
INSERT INTO
Personal(FName,LName,PreferredName,DayPhone,EveningPhone,ContactTime,Email,F
irstBase,Gift1,Gift2,Gift3,babies)VALUES
('test','','','','','','','','','','','1')

All the fields you see there, with the exception of the last one, are text
fields. Users can manually type whatever they want in the web form. In the
database, they are mostly defined as text, and one or two are datetime.

The last one is a checkbox (I have more, but commented out for now until I
get this one working). I had the checkbox set to a value of "Yes", and I
got these same errors, so I posted a question at this forum. It was
explained to me that Access needs to have a 1 or 0 passed to fields defined
as yes/no. I tried this, as you can from the above SQL insert statement,
and I'm getting the same error. I even tried "ON", and it didn't work.

All fields in this statement are defined as not required, and the statement
works fine when that last field isn't included.

I'm baffled. Can you help?

Disclaimer: I'm used to SQL Server, but this church site project has to be
done in Access.

 

[Ray at]

No, use 1 instead of '1'. I suggest never using true/false.

Ray at home

 

[Dan Brussee]

No, use 1 instead of '1'. I suggest never using true/false.

Ray at home

Why not? In Access, this is the value that is being used, plus a
boolean is easier to work with than a numeric value of 1 or 0 (or is
that -1 and 0??).

 

[Ray at]

Why not? In Access, this is the value that is being used, plus a
boolean is easier to work with than a numeric value of 1 or 0 (or is
that -1 and 0??).

So that when/if he upgrades to SQL Server ever, he won't have to worry about
changing all the TRUE/FALSE's to 1/0's.

Ray at home

 

[Tom B]

or set a constant at the top of the page
const myTrue=1
const myFalse=0

 

[middletree]

So you're saying ('test','','','','','','','','','','',1)
instead of ('test','','','','','','','','','','','1')?

I thought all values entered in any insert or update statement had to be in
single quotes. Oh well, I learn things ne all the time. Let's give it a
shot.

Nope. Same error. Interestingly, I got the same error in the browser. But
when I ran it in Access, it gave me the same msg box as before, then asked
if I wanted to go ahead and run the query anyway. This time, I clicke dYes,
and it went ahead and put a value of Yes (True, 1, whatever) in the field.

 

[middletree]

Tried it, with and without the quotes, same error

...



Try 'true' instead of the 1, failing that can you please post the exact
error message you are getting...and the code you are using with values etc
(use a response.write)

Rob

 

[middletree]

Actually, I did post the exact error msg I was getting in the browser.

 

[Ray at]

So you're saying ('test','','','','','','','','','','',1)
instead of ('test','','','','','','','','','','','1')?

Yes.


I thought all values entered in any insert or update statement had to be in
single quotes. Oh well, I learn things ne all the time. Let's give it a
shot.

Numeric values (or boolean) are't entered with ' delimiters. And in Access,
date columns are delimited with #.

Nope. Same error. Interestingly, I got the same error in the browser. But
when I ran it in Access, it gave me the same msg box as before, then asked
if I wanted to go ahead and run the query anyway. This time, I clicke dYes,
and it went ahead and put a value of Yes (True, 1, whatever) in the field.

Are ~all~ of the other columns a text-type of column either text or memo?
What happens if you try:

INSERT INTO Personal(FName,babies)VALUES ('test',1)

If any of your columns don't allow nulls, this will also cause an error, but
if not, try it. I'm going to guess that your conversion error has to do
with your ContactTime column, which expects a date. And if that's the case,
you' have to insert with ## delimiters.

Ray at home

 

[middletree]

You are correct. Actually, the field Firstbase is the Date/Time field. It
was the culprit. I took it out, worked fine.
Short answer is I could make it a field where people typ the date in
manually, won't hurt anything in this case.

But just for giggles, how is it supposed to work? I tried this:
INSERT INTO Personal(FName,FirstBase,Gift2,Gift3,babies)
VALUES ('test',#,'','',1)

and this:

INSERT INTO Personal(FName,FirstBase,Gift2,Gift3,babies)
VALUES ('test',##,'','',1)

in Access itself, and the msg box said there was a syntax error. Not a very
helpful msg box.

 

[Dan Brussee]

You are correct. Actually, the field Firstbase is the Date/Time field. It
was the culprit. I took it out, worked fine.
Short answer is I could make it a field where people typ the date in
manually, won't hurt anything in this case.

But just for giggles, how is it supposed to work? I tried this:
INSERT INTO Personal(FName,FirstBase,Gift2,Gift3,babies)
VALUES ('test',#,'','',1)

and this:

INSERT INTO Personal(FName,FirstBase,Gift2,Gift3,babies)
VALUES ('test',##,'','',1)

in Access itself, and the msg box said there was a syntax error. Not a very
helpful msg box.

I would set the date field to allow nulls and put a null there when
you dont want a date.

INSERT INTO Persona(FName, FirstBase, Gift2,
Gift3, babies) VALUES ('test', null, '', '', 1)

 

[Ray at]

You are correct. Actually, the field Firstbase is the Date/Time field. It
was the culprit. I took it out, worked fine.
Short answer is I could make it a field where people typ the date in
manually, won't hurt anything in this case.

But just for giggles, how is it supposed to work? I tried this:
INSERT INTO Personal(FName,FirstBase,Gift2,Gift3,babies)
VALUES ('test',#,'','',1)

and this:

INSERT INTO Personal(FName,FirstBase,Gift2,Gift3,babies)
VALUES ('test',##,'','',1)

in Access itself, and the msg box said there was a syntax error. Not a very
helpful msg box.

You'll have to insert a date into it, or leave it null by not including the
column at all, if your DB allows it. Or, you can set a default value for
that column, and if you don't define a value for it when a new record is
inserted, the default value will be put in. If you aren't going to insert
anything into that column, don't include it in your INSERT. If you include
it, you have to give it a value, like #1/1/1900# or something.

Ray at home

 

[middletree]

Thanks. This has been informative. As usual.

You'll have to insert a date into it, or leave it null by not including the
column at all, if your DB allows it. Or, you can set a default value for
that column, and if you don't define a value for it when a new record is
inserted, the default value will be put in. If you aren't going to insert
anything into that column, don't include it in your INSERT. If you include
it, you have to give it a value, like #1/1/1900# or something.

Ray at home

 

[ljb]

Only 0 is false. Non 0 is true and that could be -1 or 1 or 99.

 

[keyur shah]

Use true/false....

Keyur Shah
Verizon Communications
732-423-0745