Date Comparrisson

[paulmcdonnell]

Hi Guys,

I'm trying to cet a period when reports need to be printed. Each report is needed either on a monthly basis or quarterly basis.

I want to set the date of a report as the month it has been completed like 072002 then get access to know that the next report is required on the 082002.

I've been using format([field],"mmyyyy") but in some queries to find dates similar to this the query doesn't work.

I'm sure that this is because this method is superficial and formatting is not the bext method. Is it datepart.

How do I set a date in a field in the form of the month reference like 072002 and keep it this way. Then check periodically if the date() month and year is equal to this...?


HOPE YOU CAN HELP.

(How many mice does it take to screw in a lightbulb? - punchline if you can help....)

CHEERS
PAUL

 

[antomack]

Gernerally in cases where you are formatting a date in this way it is best to format it with the year first as it helps in comparisons especially if you intend doing betweens or less thans or greater thans.

Example:
082002 to 200208
If you try to find the one's between July and September then it would not work correctly in the format mmyyyy if you have results for more than one year.

Between 072002 and 092002 would also return 072003, 082000, 082001, etc. while Between 200207 and 200209 would only give 200207, 200208 and 200209.