Type Mismatch

[JeffMKlein]

I have a query that returns dates and times. in this query i have a field
that creates a time ("9:00:00 PM" AS EndTime).
This is a value that is needed in every returned record.
This query works fine until it is used in a union query. I am getting a
Type mismatch. I believe I need to set this "Endtime" to a date/time type.
If I try to set this in Parameters it prompts me at run time....not what I
want. Is there another way to set this "StartTime" to date/time?

 

[Tom Ellison]

Dear Jeff:

I take it then the the value in the column is currently a text value. You
may have some success in your query if you convert it to a Date/Time value.
Use:

CDate(YourColumnName)

to do this.

However, if any of the text entered is inconsistent with the ability of
CDate to recognize a Date/Time value, it will fail for those rows. I expect
it is a mistake to use something general like a text fileld to store data
that must adhere to a tightly controlled format in order to be recognized as
a numeric or date function.

Tom Ellison

 

[John Vinson]

I have a query that returns dates and times. in this query i have a field
that creates a time ("9:00:00 PM" AS EndTime).

Try using the date delimiter instead of the text delimiter:

#9:00:00 PM# AS EndTime

John W. Vinson[MVP]