syntax problem - data type mismatch

[krc777]

I have this code in a module in Access:

CurrentDb.Execute "UPDATE Matchonyyyymm Set FirstDate = 'Y' WHERE MISSN = '" & fssn & "'"

The MISSN is a double data type in the table.
The fssn is a varient/double in the code. I get a data type mismatch on this line. Can anyone tell me what I'm doing wrong?
Thanks.

 

[KeithG]

Is firstDate a date format or text? I think you need to remove the " " from " & fssn & "'

 

[krc777]

When I took out the WHERE statement the line ran fine so it's in the WHERE. I've tried removing quotes still get the error. Thanks for your suggestions though.
k

 

[selenau837]

When I took out the WHERE statement the line ran fine so it's in the WHERE. I've tried removing quotes still get the error. Thanks for your suggestions though.
k

If the fssn field is a number field, don't you need to remove the single quotes and use ## instead.

I'm no genius, but that is just an idea.

 

[workmad3]

You dont need anything on the number field afaik.

See if

CurrentDb.Execute "UPDATE Matchonyyyymm Set FirstDate = 'Y' WHERE MISSN = " & fssn

works

 

[selenau837]

You dont need anything on the number field afaik.

See if

CurrentDb.Execute "UPDATE Matchonyyyymm Set FirstDate = 'Y' WHERE MISSN = " & fssn

works

Thank you,

so the ## are used for? Date fields? I'm still not an expert on that. I only know that text requires " or ' depending.

 

[krc777]

I tried removing the quotes and replacing with #. Error about the #'s.
If anyone could tell me or give me the URL of a site with all the various possibilities like formating a string, ' " & string & " ' or number ?? or date, that would be so helpful.
k

 

[selenau837]

I tried removing the quotes and replacing with #. Error about the #'s.
If anyone could tell me or give me the URL of a site with all the various possibilities like formating a string, ' " & string & " ' or number ?? or date, that would be so helpful.

k

sorry krc...wasn't trying to confuse you.

Try this link. It has some SQL tutorials in it.

 

[krc777]

I tried:
CurrentDb.Execute "UPDATE Matchonyyyymm Set FirstDate = 'Y' WHERE MISSN = 'fssn' "
no luck. Still data mismatch error.

 

[selenau837]

I tried:
CurrentDb.Execute "UPDATE Matchonyyyymm Set FirstDate = 'Y' WHERE MISSN = 'fssn' "
no luck. Still data mismatch error.

If fssn is a number field, remove the single quotes from around it.

 

[krc777]

If I remove all the quotes like MISSN = fssn then I get the too few parameters error. When the Quotes are in and I check it in the window it has the correct number. I just can't get find the right syntax to get the line to go.
Thanks.

 

[krc777]

I have no idea why but this worked.
CurrentDb.Execute "UPDATE Matchonyyyymm Set FirstDate = 'Y' WHERE MISSN = Cstr(' & fssn & ')"

The data type for this field is number but this worked.
k

 

[selenau837]

I have no idea why but this worked.
CurrentDb.Execute "UPDATE Matchonyyyymm Set FirstDate = 'Y' WHERE MISSN = Cstr(' & fssn & ')"

The data type for this field is number but this worked.
k

The reason it worked, is because that Cstr. convereted your number datatype to a string datatype, and the quotes around that field made it work.

 

[workmad3]

When you say you removed all the quotes, did you get rid of the double quotes as well?

What you really want, like I said, is

CurrentDb.Execute "UPDATE Matchonyyyymm Set FirstDate = 'Y' WHERE MISSN = " & fssn

 

[workmad3]

oh, and standard SQL just has ' for strings (or " if you are typing directly into an sql console) and the lack of them for numbers. # is an access (and possibly SQL server) identifier for date types.