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.

re: yes/no in Access

"middletree" wrote ...
[color=blue]
> ('test','','','','','','','','','','','1')[/color]
[color=blue]
> I'm baffled. Can you help?[/color]

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

re: yes/no in Access

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

Ray at home

"Rob Meade" <robb.meade@NO-SPAM.kingswoodweb.net> wrote in message
news:Vhdrb.2257$uT1.16043221@news-text.cableinet.net...[color=blue]
> "middletree" wrote ...
>[color=green]
> > ('test','','','','','','','','','','','1')[/color]
>[color=green]
> > I'm baffled. Can you help?[/color]
>
> 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
>
>[/color]

re: yes/no in Access

On Sat, 8 Nov 2003 17:31:34 -0500, "Ray at <%=sLocation%>"
<myfirstname at lane 34 . komm> wrote:
[color=blue]
>No, use 1 instead of '1'. I suggest never using true/false.
>
>Ray at home[/color]

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??).
[color=blue]
>
>"Rob Meade" <robb.meade@NO-SPAM.kingswoodweb.net> wrote in message
>news:Vhdrb.2257$uT1.16043221@news-text.cableinet.net...[color=green]
>> "middletree" wrote ...
>>[color=darkred]
>> > ('test','','','','','','','','','','','1')[/color]
>>[color=darkred]
>> > I'm baffled. Can you help?[/color]
>>
>> 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
>>
>>[/color]
>[/color]

re: yes/no in Access

"Dan Brussee" <dbrussee@nc.rr.com> wrote in message
news:getqqvcnacep1vrqmh6lvrsed0etti8jfh@4ax.com...[color=blue]
> On Sat, 8 Nov 2003 17:31:34 -0500, "Ray at <%=sLocation%>"
> <myfirstname at lane 34 . komm> wrote:
>[color=green]
> >No, use 1 instead of '1'. I suggest never using true/false.
> >
> >Ray at home[/color]
>
> 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??).
>[/color]

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

re: yes/no in Access

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:%23mZqvHlpDHA.3256@tk2msftngp13.phx.gbl...[color=blue]
>
> "Dan Brussee" <dbrussee@nc.rr.com> wrote in message
> news:getqqvcnacep1vrqmh6lvrsed0etti8jfh@4ax.com...[color=green]
> > On Sat, 8 Nov 2003 17:31:34 -0500, "Ray at <%=sLocation%>"
> > <myfirstname at lane 34 . komm> wrote:
> >[color=darkred]
> > >No, use 1 instead of '1'. I suggest never using true/false.
> > >
> > >Ray at home[/color]
> >
> > 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??).
> >[/color]
>
> So that when/if he upgrades to SQL Server ever, he won't have to worry[/color]
about[color=blue]
> changing all the TRUE/FALSE's to 1/0's.
>
> Ray at home
>
>[/color]

re: yes/no in Access

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:OwbNvdkpDHA.2776@tk2msftngp13.phx.gbl...[color=blue]
> No, use 1 instead of '1'. I suggest never using true/false.
>
> Ray at home
>
> "Rob Meade" <robb.meade@NO-SPAM.kingswoodweb.net> wrote in message
> news:Vhdrb.2257$uT1.16043221@news-text.cableinet.net...[color=green]
> > "middletree" wrote ...
> >[color=darkred]
> > > ('test','','','','','','','','','','','1')[/color]
> >[color=darkred]
> > > I'm baffled. Can you help?[/color]
> >
> > 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[/color][/color]
etc[color=blue][color=green]
> > (use a response.write)
> >
> > Rob
> >
> >[/color]
>
>[/color]

re: yes/no in Access

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


"Rob Meade" <robb.meade@NO-SPAM.kingswoodweb.net> wrote in message
news:Vhdrb.2257$uT1.16043221@news-text.cableinet.net...[color=blue]
> "middletree" wrote ...
>[color=green]
> > ('test','','','','','','','','','','','1')[/color]
>[color=green]
> > I'm baffled. Can you help?[/color]
>
> 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
>
>[/color]

re: yes/no in Access

Tried it, with and without the quotes, same error
"Rob Meade" <robb.meade@NO-SPAM.kingswoodweb.net> wrote in message
news:Vhdrb.2257$uT1.16043221@news-text.cableinet.net...[color=blue]
> "middletree" wrote ...
>[color=green]
> > ('test','','','','','','','','','','','1')[/color]
>[color=green]
> > I'm baffled. Can you help?[/color]
>
> 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
>
>[/color]

re: yes/no in Access

"middletree" <middletree@htomail.com> wrote in message
news:%23r2xivmpDHA.1728@TK2MSFTNGP09.phx.gbl...[color=blue]
> So you're saying ('test','','','','','','','','','','',1)
> instead of ('test','','','','','','','','','','','1')?
>[/color]

Yes.

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

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

[color=blue]
>
> 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[/color]
dYes,[color=blue]
> and it went ahead and put a value of Yes (True, 1, whatever) in the field.[/color]

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

[color=blue]
>
>
> "Ray at <%=sLocation%>" <myfirstname at lane 34 . komm> wrote in message
> news:OwbNvdkpDHA.2776@tk2msftngp13.phx.gbl...[color=green]
> > No, use 1 instead of '1'. I suggest never using true/false.
> >
> > Ray at home
> >
> > "Rob Meade" <robb.meade@NO-SPAM.kingswoodweb.net> wrote in message
> > news:Vhdrb.2257$uT1.16043221@news-text.cableinet.net...[color=darkred]
> > > "middletree" wrote ...
> > >
> > > > ('test','','','','','','','','','','','1')
> > >
> > > > I'm baffled. Can you help?
> > >
> > > Try 'true' instead of the 1, failing that can you please post the[/color][/color][/color]
exact[color=blue][color=green][color=darkred]
> > > error message you are getting...and the code you are using with values[/color][/color]
> etc[color=green][color=darkred]
> > > (use a response.write)
> > >
> > > Rob
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

re: yes/no in Access

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:OIbqZBnpDHA.3616@tk2msftngp13.phx.gbl...[color=blue]
>
> "middletree" <middletree@htomail.com> wrote in message
> news:%23r2xivmpDHA.1728@TK2MSFTNGP09.phx.gbl...[color=green]
> > So you're saying ('test','','','','','','','','','','',1)
> > instead of ('test','','','','','','','','','','','1')?
> >[/color]
>
> Yes.
>
>[color=green]
> > I thought all values entered in any insert or update statement had to be[/color]
> in[color=green]
> > single quotes. Oh well, I learn things ne all the time. Let's give it a
> > shot.[/color]
>
> Numeric values (or boolean) are't entered with ' delimiters. And in[/color]
Access,[color=blue]
> date columns are delimited with #.
>
>[color=green]
> >
> > Nope. Same error. Interestingly, I got the same error in the browser.[/color][/color]
But[color=blue][color=green]
> > when I ran it in Access, it gave me the same msg box as before, then[/color][/color]
asked[color=blue][color=green]
> > if I wanted to go ahead and run the query anyway. This time, I clicke[/color]
> dYes,[color=green]
> > and it went ahead and put a value of Yes (True, 1, whatever) in the[/color][/color]
field.[color=blue]
>
> 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,[/color]
but[color=blue]
> 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[/color]
case,[color=blue]
> you' have to insert with ## delimiters.
>
> Ray at home
>
>[color=green]
> >
> >
> > "Ray at <%=sLocation%>" <myfirstname at lane 34 . komm> wrote in message
> > news:OwbNvdkpDHA.2776@tk2msftngp13.phx.gbl...[color=darkred]
> > > No, use 1 instead of '1'. I suggest never using true/false.
> > >
> > > Ray at home
> > >
> > > "Rob Meade" <robb.meade@NO-SPAM.kingswoodweb.net> wrote in message
> > > news:Vhdrb.2257$uT1.16043221@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[/color][/color]
> exact[color=green][color=darkred]
> > > > error message you are getting...and the code you are using with[/color][/color][/color]
values[color=blue][color=green]
> > etc[color=darkred]
> > > > (use a response.write)
> > > >
> > > > Rob
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

re: yes/no in Access

"middletree" <middletree@htomail.com> wrote in message
news:ey83innpDHA.1656@tk2msftngp13.phx.gbl...[color=blue]
> 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[/color]
very[color=blue]
> helpful msg box.[/color]

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

re: yes/no in Access

On Sat, 8 Nov 2003 22:37:18 -0600, "middletree"
<middletree@htomail.com> wrote:
[color=blue]
>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.[/color]

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)

re: yes/no in Access

Thanks. This has been informative. As usual.


"Ray at <%=sLocation%>" <myfirstname at lane 34 . komm> wrote in message
news:OExcfXopDHA.1096@TK2MSFTNGP11.phx.gbl...[color=blue]
>
> "middletree" <middletree@htomail.com> wrote in message
> news:ey83innpDHA.1656@tk2msftngp13.phx.gbl...[color=green]
> > You are correct. Actually, the field Firstbase is the Date/Time field.[/color][/color]
It[color=blue][color=green]
> > 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[/color]
> very[color=green]
> > helpful msg box.[/color]
>
> You'll have to insert a date into it, or leave it null by not including[/color]
the[color=blue]
> 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[/color]
include[color=blue]
> it, you have to give it a value, like #1/1/1900# or something.
>
> Ray at home
>
>[/color]

re: yes/no in Access

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

"Dan Brussee" <dbrussee@nc.rr.com> wrote in message
news:getqqvcnacep1vrqmh6lvrsed0etti8jfh@4ax.com...[color=blue]
> On Sat, 8 Nov 2003 17:31:34 -0500, "Ray at <%=sLocation%>"
> <myfirstname at lane 34 . komm> wrote:
>[color=green]
> >No, use 1 instead of '1'. I suggest never using true/false.
> >
> >Ray at home[/color]
>
> 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??).
>[color=green]
> >
> >"Rob Meade" <robb.meade@NO-SPAM.kingswoodweb.net> wrote in message
> >news:Vhdrb.2257$uT1.16043221@news-text.cableinet.net...[color=darkred]
> >> "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[/color][/color][/color]
etc[color=blue][color=green][color=darkred]
> >> (use a response.write)
> >>
> >> Rob
> >>
> >>[/color]
> >[/color]
>[/color]

re: yes/no in Access

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!