469,927 Members | 1,777 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,927 developers. It's quick & easy.

yes/no in Access

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,Evenin gPhone,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.
Jul 19 '05 #1
15 8055
"middletree" wrote ...
('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
Jul 19 '05 #2
No, use 1 instead of '1'. I suggest never using true/false.

Ray at home

"Rob Meade" <ro********@NO-SPAM.kingswoodweb.net> wrote in message
news:Vh*********************@news-text.cableinet.net...
"middletree" wrote ...
('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

Jul 19 '05 #3
On Sat, 8 Nov 2003 17:31:34 -0500, "Ray at <%=sLocation%>"
<myfirstname at lane 34 . komm> wrote:
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??).

"Rob Meade" <ro********@NO-SPAM.kingswoodweb.net> wrote in message
news:Vh*********************@news-text.cableinet.net...
"middletree" wrote ...
> ('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


Jul 19 '05 #4

"Dan Brussee" <db******@nc.rr.com> wrote in message
news:ge********************************@4ax.com...
On Sat, 8 Nov 2003 17:31:34 -0500, "Ray at <%=sLocation%>"
<myfirstname at lane 34 . komm> wrote:
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??).


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
Jul 19 '05 #5
or set a constant at the top of the page
const myTrue=1
const myFalse=0
"Ray at <%=sLocation%>" <myfirstname at lane 34 . komm> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

"Dan Brussee" <db******@nc.rr.com> wrote in message
news:ge********************************@4ax.com...
On Sat, 8 Nov 2003 17:31:34 -0500, "Ray at <%=sLocation%>"
<myfirstname at lane 34 . komm> wrote:
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??).


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

Jul 19 '05 #6
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.
"Ray at <%=sLocation%>" <myfirstname at lane 34 . komm> wrote in message
news:Ow**************@tk2msftngp13.phx.gbl...
No, use 1 instead of '1'. I suggest never using true/false.

Ray at home

"Rob Meade" <ro********@NO-SPAM.kingswoodweb.net> wrote in message
news:Vh*********************@news-text.cableinet.net...
"middletree" wrote ...
('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


Jul 19 '05 #7
Actually, I did post the exact error msg I was getting in the browser.
"Rob Meade" <ro********@NO-SPAM.kingswoodweb.net> wrote in message
news:Vh*********************@news-text.cableinet.net...
"middletree" wrote ...
('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

Jul 19 '05 #8
Tried it, with and without the quotes, same error
"Rob Meade" <ro********@NO-SPAM.kingswoodweb.net> wrote in message
news:Vh*********************@news-text.cableinet.net...
"middletree" wrote ...
('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

Jul 19 '05 #9

"middletree" <mi********@htomail.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
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



"Ray at <%=sLocation%>" <myfirstname at lane 34 . komm> wrote in message
news:Ow**************@tk2msftngp13.phx.gbl...
No, use 1 instead of '1'. I suggest never using true/false.

Ray at home

"Rob Meade" <ro********@NO-SPAM.kingswoodweb.net> wrote in message
news:Vh*********************@news-text.cableinet.net...
"middletree" wrote ...

> ('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



Jul 19 '05 #10
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.
"Ray at <%=sLocation%>" <myfirstname at lane 34 . komm> wrote in message
news:OI**************@tk2msftngp13.phx.gbl...

"middletree" <mi********@htomail.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
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



"Ray at <%=sLocation%>" <myfirstname at lane 34 . komm> wrote in message
news:Ow**************@tk2msftngp13.phx.gbl...
No, use 1 instead of '1'. I suggest never using true/false.

Ray at home

"Rob Meade" <ro********@NO-SPAM.kingswoodweb.net> wrote in message
news:Vh*********************@news-text.cableinet.net...
> "middletree" wrote ...
>
> > ('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
>
>



Jul 19 '05 #11

"middletree" <mi********@htomail.com> wrote in message
news:ey**************@tk2msftngp13.phx.gbl...
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
Jul 19 '05 #12
On Sat, 8 Nov 2003 22:37:18 -0600, "middletree"
<mi********@htomail.com> wrote:
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)
Jul 19 '05 #13
Thanks. This has been informative. As usual.
"Ray at <%=sLocation%>" <myfirstname at lane 34 . komm> wrote in message
news:OE**************@TK2MSFTNGP11.phx.gbl...

"middletree" <mi********@htomail.com> wrote in message
news:ey**************@tk2msftngp13.phx.gbl...
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

Jul 19 '05 #14
ljb
Only 0 is false. Non 0 is true and that could be -1 or 1 or 99.

"Dan Brussee" <db******@nc.rr.com> wrote in message
news:ge********************************@4ax.com...
On Sat, 8 Nov 2003 17:31:34 -0500, "Ray at <%=sLocation%>"
<myfirstname at lane 34 . komm> wrote:
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??).

"Rob Meade" <ro********@NO-SPAM.kingswoodweb.net> wrote in message
news:Vh*********************@news-text.cableinet.net...
"middletree" wrote ...

> ('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

Jul 19 '05 #15
Use true/false....

Keyur Shah
Verizon Communications
732-423-0745

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 19 '05 #16

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

63 posts views Thread by Jerome | last post: by
13 posts views Thread by Simon Bailey | last post: by
64 posts views Thread by John | last post: by
17 posts views Thread by Mell via AccessMonster.com | last post: by
37 posts views Thread by jasmith | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.