"Data type mismatch in criteria expression" Error When Trying To Pass A Variable

[lhooker]

Can someone tell me why I'm getting the below error message.

"Data type mismatch in criteria expression"

I'm trying to pass a variable (via VBA) from a form to another form that retrieves a associated records to the form called. The form criteria is to match a variable that has the value of the Now() statement. All date fields and variables are defined as a date format. Attached is a sample version of the forms and tables. Thanks ! ! !

 

[Pat Hartman]

I don't have time to look at the database but Now() is a double precision number and with a gazillion decimal places, it is hard to get an exact match.

 

[The_Doc_Man]

You are attempting to pass a date/time variable through string syntax, putting quotes (") around something that isn't inherently a string. That ought to earn you a type mismatch error right there. To pass time in quotes you have to format it into a string first, and you haven't done that.

Pat's comment is significant as well. Passing a date/time field filled from Now() is a sure-fire way to get non-equal comparisons. You have, in essence, 86,400 different times of day plus quite a range of possible dates you could use for the timestamp (not counting what you ARE using.)

What is the theory behind this passage? Is the timestamp supposed to be a unique value so that you can use it as a search key?

 

[lhooker]

Thanks for responding ! ! ! . . . Is a there a command to convert Now() to a character string ?

 

[Gasman]

Thanks for responding ! ! ! . . . Is a there a command to convert Now() to a character string ?

Use a format

'Public Const strcJetDate = "\#mm\/dd\/yyyy\#" 'Needed for dates in queries as Access expects USA format.
Public Const strcJetDate = "\#yyyy-mm-dd\#" 'Needed for dates in queries as Access expects USA but will accept ISO format.

 

[lhooker]

Thanks ! ! ! . . . How can I convert Now() to a string character in MS Access VBA ?

 

[Gasman]

Well my format function only works on dates without tìmevalue. Do you actually need Now() and not Date() ???

 

[lhooker]

I'm trying to combine the date and time together into a variable and pass the variable to a MS Access form.

 

[Gasman]

Just pass the value as is.
You only need to format if using in a sql string, or anywhere it is needed as a string.
Leave it as it is, in date format as much/long as you can.
Use a TempVar to pass the value? or just pass Now() as an OpenArgs

 

[lhooker]

Thank you ! ! !