How do I run an access query to pull only records with the current month from a date that is formatted as a short text?

Elijah Steed 0
Viewed 35

I have some tables that I cannot modify and a field "STAT_DATE" that is formatted as a short text like "yyyymmdd". I tried the Month(now()) functions and this did not work because the field is not in date format. I have also tried using the mid and monthname functions to try and match the current months name with the name of the digits that are meant to be the month in the string.

ms-access-2010 ms-access sql
1 Answers

Use Like to compare your date text to the current date formatted as 'yyyymm\*'

Here is an Immediate window session to demonstrate the technique:

' give STAT_DATE today's date ...
STAT_DATE = "20220913"
' display today's date as the yyyymm* pattern ...
? Format(Date(), "yyyymm\*")
202209*
? STAT_DATE Like Format(Date(), "yyyymm\*")
True

Then use that approach in a query:

SELECT y.*
FROM [Your Table] AS y
WHERE y.STAT_DATE Like Format(Date(), 'yyyymm\*');

If you're using ADO/OleDb to run your query, use % instead of * as the wildcard:

WHERE y.STAT_DATE Like Format(Date(), 'yyyymm\%');
HansUp0
Related