?How to flag row based on date value?

[MsAxes]

Hello



I have a field (Clock in - Data Type = Short Text) where that tells me the date and time an employee clocked in, ex. 03/20/2022 8:16:34 AM. I created a field to extract the date

DateClockedIn: Format(left([Clock in],10),"General Date"

which returns 03/20/2022 * I have also tried mm/dd/yyyy where I have "General Date".

I then create another field

Flag: IIF([DateClockedIn] < #03/20/2022#,"True","")

. This field only returns "True" on fields where the month is March, it doesn't flag all other dates that were before March 20, 2022. I am convinced it's on how I extracted the date (I've always had trouble properly formatting dates when I extract them to read as an actual date).

Any ideas?

[June7]

Issue is because [DateClockedIn] is not really a date/time value, it is just a string. Format function returns string. Have to convert to date/time value.

IIf(CDate([DateClockedIn]) < #03/20/2022#,"True","")

Why is the raw data in a text field instead of date/time type?

Advise not to use spaces in naming convention.

[MsAxes]

Thank you - I never had to use that expression; it worked. So CDate should be used when we have a date that is a string and want to have set a criteria?

[June7]

If you want a value to be treated as a date/time for chronological sorting/filtering, yes, convert to date/time.

Must understand that a true date/time value is really a double number. The MM/DD/YYYY hh:nn:ss structure that is displayed is just a format.

[Welshgasman]

Could also try DateValue?

? DateValue(Now())
26/03/2022

[June7]

Dang, forgot about DateValue again.

DateClockedIn: DateValue([Clock In])

Flag: IIf(DateClockedIn < #3/20/2022#, "True, "")

or just

Flag: IIf(DateValue([Clock In]) < #3/20/2022#, "True", "")

[MsAxes]

Thank you both - this helped a lot.