Formula to convert text/string to date format

[sunnyzwei]

Hello!

I'm trying to add a formula that will compare whatever the current date is to a date field. However the date field is just a string/text value.

the date field is stored like this 20230914 and it's just recognized as text so I need it to be a date like this 9-14-2023

I've tried format([date field],"mm-dd-yyyy") >date() but I get an overflow error

I know the answer is simple but just can't figure out what it should be.

 

[sunnyzwei]

and i'm trying to do this in the design view of the query for criteria

 

[Joe4]

The FORMAT function converts date/numeric fields to text values, not the other way around.
You first need to convert the Text value to a Date field. Then you can do the comparison.

You can use the DATESERIAL function to do this, i.e.

DATESERIAL(LEFT([BBNDTEF],4),MID([BBNDTEF],5,2),RIGHT([BBNDTEF],2))

I would do that in a Calculated field, and then apply the criteria to that calculated field of:

<DATE()

 

[james_lankford]

I'd just take the date() function and format it as yyyymmdd and leave the database field as is

BBNDTEF < Format(Date, "yyyymmdd")

 

[Joe4]

I'd just take the date() function and format it as yyyymmdd and leave the database field as is

BBNDTEF < Format(Date, "yyyymmdd")

While that should work in this case, just note that this method won't always work.

In this instance, you are comparing a string to a string. It should work if the format is "yyyymmdd", but most other date formats will NOT work (i.e. if the order is not YEAR -> MONTH -> DATE or if either the month or date does not use leading zeroes).

So it would work for formats like:
yyyymmdd
yyyy-mm-dd
yyyy/mm/dd
yyyy.mm.dd
but it would not work for most other formats.

 

[james_lankford]

I don't know about MS Access, but I think i've read that in MS SQL Server if you have an index on field and then do a formula on that field the index isn't used

so in this case if BBNDTEF was indexed and you used format or dateserial on something on it then the index is ignored
but if you leave it as is and instead put the formula on the date() then the index is used

 

[sunnyzwei]

James this worked! thanks so much!