Error message -Data type mismatch

[ifeyinwa]

Can anyone pls tell me what I have done wrong here. This is the Error message that I get and below it is the actual asp script that the error references. thanks as usual

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

/itp/legconfirm.asp, line 42


SQLStmt = SQlstmt & "Where Test =" & Test
Conn.Execute(SQLStmt)

 

[logicalunatic]

ifey,

Sorry, this is assuming that your reqDate column in the database isn't just a string and is actually set to a date/time data type.

LL

 

[onpnt]

___________________________________________________________________
[sub]

http://www.onpnt.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page faq333-3811[/sub]

 

[Veep]

 

[ifeyinwa]

Onpnt and Veep your comments did not show just your names

 

[ifeyinwa]

My updDate column in the access database has some empty records is this where the problem is from?? In the design view of the upDate field do I tell it to take zero length field and what will the option say??

 

[Veep]

Open your table in design view. Put your cursor in the column "upDate" (by the way, in the output of your SQL statement upDate was spelled updDate. You might want to look at that.) In the properties for that field (at the bottom) will be a property "Allow Zero Length". Change it to "Yes". That still may solve your problem though.

 

[ifeyinwa]

the properties for the updDate field does not have the "Allow zero length" option because I choose the data type Date/time.It is only when you choose data type text etc.. that it has that property. These are all the properties it has listed for Date/time Data type which updDate is ;
format
input mask
Caption
default value
validation rule
Validation text
Required
Indexed
which of these do I use then??

 

[Veep]

OK. Then you need to update that field with a valid date or don't update it at all. Also, logicallunatic was correct about using "#" as your date delimiter. MSAccess is going to want to see your date variables surrounded by "#" like so:

...updDate='#" & YourDateVariable & "#', etc=

 

[Double02]

logicalunatic, although Access uses # natively for dates, ADO uses standard SQL, so you use single quotes (and, similarly, SQL wildcards)

 

[Double02]

Surely the best option is to provide the current Date - isn't the upDate the date it was updated?

 

[ifeyinwa]

Double02 the fiedl is not to be updated by the current date It is not to be updated or touched by the users .
well guys thank you for all your input. I just decided to remove that script from my sql statement since it is not a field that the users should change. they can only view the field.THe administrator is the one that updates it from the backend.(decided to take the easy way since this error message has refused to go away)
thanks everyone for your contribution.thanks for your time

 

[DreXor]

dont know if you're still on notification for this thread but one thing i noticed in this is if there was a reserved word issue... try putting all the field names in []'s for "just in case" instances

and regardless of the ADO connection MS Access SQL statement need to be written in MSA SQL so use the '#" & updDate & "#' as mentioned