yes/no in Access

[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.a sp, 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,Preferred Name,DayPhone,E veningPhone,Con tactTime,Email, F
irstBase,Gift1, Gift2,Gift3,bab ies)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.

[Rob Meade]

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

[Ray at]

Re: yes/no in Access

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

Ray at home

"Rob Meade" <robb.meade@N O-SPAM.kingswoodw eb.net> wrote in message
news:Vhdrb.2257 $uT1.16043221@n ews-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]

[Dan Brussee]

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@N O-SPAM.kingswoodw eb.net> wrote in message
>news:Vhdrb.225 7$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]

[Ray at]

Re: yes/no in Access


"Dan Brussee" <dbrussee@nc.rr .com> wrote in message
news:getqqvcnac ep1vrqmh6lvrsed 0etti8jfh@4ax.c om...[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

[Tom B]

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:%23mZqvHlp DHA.3256@tk2msf tngp13.phx.gbl. ..[color=blue]
>
> "Dan Brussee" <dbrussee@nc.rr .com> wrote in message
> news:getqqvcnac ep1vrqmh6lvrsed 0etti8jfh@4ax.c om...[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]

[middletree]

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:OwbNvdkpDH A.2776@tk2msftn gp13.phx.gbl...[color=blue]
> No, use 1 instead of '1'. I suggest never using true/false.
>
> Ray at home
>
> "Rob Meade" <robb.meade@N O-SPAM.kingswoodw eb.net> wrote in message
> news:Vhdrb.2257 $uT1.16043221@n ews-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]

[middletree]

Re: yes/no in Access

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


"Rob Meade" <robb.meade@N O-SPAM.kingswoodw eb.net> wrote in message
news:Vhdrb.2257 $uT1.16043221@n ews-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]

[middletree]

Re: yes/no in Access

Tried it, with and without the quotes, same error
"Rob Meade" <robb.meade@N O-SPAM.kingswoodw eb.net> wrote in message
news:Vhdrb.2257 $uT1.16043221@n ews-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]

[Ray at]

Re: yes/no in Access


"middletree " <middletree@hto mail.com> wrote in message
news:%23r2xivmp DHA.1728@TK2MSF TNGP09.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:OwbNvdkpDH A.2776@tk2msftn gp13.phx.gbl...[color=green]
> > No, use 1 instead of '1'. I suggest never using true/false.
> >
> > Ray at home
> >
> > "Rob Meade" <robb.meade@N O-SPAM.kingswoodw eb.net> wrote in message
> > news:Vhdrb.2257 $uT1.16043221@n ews-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]

[middletree]

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:OIbqZBnpDH A.3616@tk2msftn gp13.phx.gbl...[color=blue]
>
> "middletree " <middletree@hto mail.com> wrote in message
> news:%23r2xivmp DHA.1728@TK2MSF TNGP09.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:OwbNvdkpDH A.2776@tk2msftn gp13.phx.gbl...[color=darkred]
> > > No, use 1 instead of '1'. I suggest never using true/false.
> > >
> > > Ray at home
> > >
> > > "Rob Meade" <robb.meade@N O-SPAM.kingswoodw eb.net> wrote in message
> > > news:Vhdrb.2257 $uT1.16043221@n ews-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]

[Ray at]

Re: yes/no in Access


"middletree " <middletree@hto mail.com> wrote in message
news:ey83innpDH A.1656@tk2msftn gp13.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

[Dan Brussee]

Re: yes/no in Access

On Sat, 8 Nov 2003 22:37:18 -0600, "middletree "
<middletree@hto mail.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)

[middletree]

Re: yes/no in Access

Thanks. This has been informative. As usual.


"Ray at <%=sLocation% >" <myfirstname at lane 34 . komm> wrote in message
news:OExcfXopDH A.1096@TK2MSFTN GP11.phx.gbl...[color=blue]
>
> "middletree " <middletree@hto mail.com> wrote in message
> news:ey83innpDH A.1656@tk2msftn gp13.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]

[ljb]

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:getqqvcnac ep1vrqmh6lvrsed 0etti8jfh@4ax.c om...[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@N O-SPAM.kingswoodw eb.net> wrote in message
> >news:Vhdrb.225 7$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]