Query with Date/Time Calculated Field

[kenman]

I have a calculated field that is being created in a query by extracting the Date and Time from a Text field called DateTimeTextField (Because there is stuff other than text in this field sometimes). I am using IIF statements to filter out the text, but will not include that here, as it will just make it more complicated. What I have is

Select DateValue(DateTimeTextField) & " " & TimeValue(DateTimeTextField) as Datetimefield
from T1
where DateValue(DateTimeTextField) & " " & TimeValue(DateTimeTextField) >= [forms]![frmSel]![StartDT]

When I run this query, and enter in the variable, I get inconsistent results. I am 99% sure that the problem is that the field that I am creating on the fly is not of type DateTime, but is of type text which means 1/1/2001 is different than 01/01/2001. Is there a way to make my calc field of type Datetime?

Thanks,
Kenman

 

[Quehay]

Since entry isn't limited to Date by datatype you ought to test your value first with IsDate function, e.g. (are you in a module or is this pure SQL--not sure if the function's available in the latter):

Dim varTextValue

varTextValue = txtMyText

If IsDate(varTextValue) Then . . .

 

[MichaelRed]

Also, look up the CDate function in 'Help'. It can force the type to Date.

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over