date query

[varean]

Have a date field with data type as "text", the data is in MM/YYYY format. having trouble querying by date range. Any sugestions?. I'm not allowed to change the date type in table. Help is appreciated.

 

[jebry]

Hi!

You should be able to do what you need with the following:

Where Datefield < &quot;11/2003&quot; And DateField > &quot;10/2001&quot;

Just make sure you pick the dates and comparison operators correctly. If you want to use today's date you can say:

Where Datefield < Format(Date(), &quot;mm/yyyy&quot

If you can't use the Format function in the where clause of a query, you can finagle it by making it a subquery.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[varean]

did that, I'm still getting the the year portion wrong. seems like it is only looking at the month portion of the data.................. thanks for your response.

 

[PoopCheese]

Try formatting the date field you are searching for and the date criteria in the where clause. I also have the same annoying problem at one of my jobs (dates in text format). One thing I've found very helpful is to format the dates with the year first, then month, then day. This makes sorting the data chronologically possible.

Try pasting this into the SQL view of a query and tweak it to meet your needs...

SELECT Format([DateField],&quot;yyyy/mm&quot AS Expr1
WHERE (((Format([DateField],&quot;yyyy/mm&quot)>Format([Enter Start Date],&quot;yyyy/mm&quot And (Format([DateField],&quot;yyyy/mm&quot)<Format([Enter End Date],&quot;yyyy/mm&quot);

Good Luck,
Poop