Beginning & Enddates not dis correctly

[Ian Stow]

Can anyone help

I have a field called JOBDATE (formatted as a shortdate, 00/00/0000). ! have records with the following dates 01/01/2001 01/02/2001 01/03/2001 and 05/04/2001. In the criteria field I have the following Between [Beginningmonth] And [Endingmonth] . When I run the Query with the following dates I get the following results.

JAN 2001 & FEB 2001 - 01/01/2001 01/02/2001
JAN 2001 & MAR 2001 - 01/01/2001 01/02/2001 01/03/2001
JAN 2001 & APR 2001 - 01/01/2001 01/02/2001 01/03/2001

Why does it not display 05/04/2001 . Is it because it isn't the 1st of the month. Is there a way around this(I know I can input 01/01/2001 & 30/04/2001 to get the correct results but it is more error proof if I enter Month's (I keep forgetting how many days there are in a month and when a leap year is a leap year).

In Anticipation

 

[antomack]

You should put your criteria under another version of your date which you should format as "mmm yyyy"

So your new field will contain the expression, you can set this invisible, i.e. untick 'Show'
Format([JobDate],"mmm yyyy")

and your criteria can be set to
Between Format([BeginningMonth],"mmm yyyy") and Format([EndingMonth],"mmm yyyy")

HTH

 

[Ian Stow]

HTH

I Have Tried this and I am certain I am Inputing it correctly
One thing that does happen when I put the Format[JobDate] Seq in the field of the Query is that it forces Expr1: prior to the string I input.

Apart from that when I say input

JAN 2001 & JUN 2001 it reports JAN/JUN/JUL dates but also years other than Specified.

APR 2001 & AUG 2001 it reports APR/AUG dates but also years other than specified

FEB 2001 & MAY 2001 it reports FEB/JAN/JUN/JUL/MAR/MAY dates but also years other than Specified

ie in other words it reports all years regardless of the year criteria I specify but also only reports the months inc and inbetween the alphabet letter I specify to begin and the alphabet letter I specify to end.

Any help would be much appreciated

 

[RV]

Try this:

SELECT JobDate.JobDate
FROM JobDate
WHERE JobDate BETWEEN
CDate("01-" &(DatePart("m",BeginningMonth)) &"-"&DatePart("yyyy",BeginningMonth))
AND
CDate("01-" &(DatePart("m",EndingMonth)+1) &"-"&DatePart("yyyy",EndingMonth));

An answer to a question in your first posting:

>Is it because it isn't the 1st of the month.<

Yes, that's right. The suggestion made by antomack will also only look at "the first day of the month".

The reason is the format used to address to your JobDate.
mmm-yyyy doesn't take into account the days of the month.

HTH,

RV

 

[Jon K]

RV's query is settings specific (Short day style d/M/yy or d/M/yyyy in the Regional Settings in Control Panel). Besides, it always includes the first day of the month following the EndingMonth entered. And when the EndingMonth entered is Dec 2001, the cdate() function will convert it to 13/1/2001.


antomack's query is independent of short date styles. It will work if all the "mmm yyyy" in the format() functions are changed to "yyyy mm"

because Jan 2001, Feb 2001, Mar 2001, etc are not in any order,
while 2001 01, 2001 02, 2001 03, etc are in ascending order so the operator "Between ... And ..." can be used properly.

 

[antomack]

The Format should really have put the year first for the query to work properly so it should read, I had forgotten the cardinal rule of always putting the year first to ensure the comparison works correctly.

Format([JobDate],"yyyymm")

and your criteria can be set to
Between Format([BeginningMonth],"yyyymm") and Format([EndingMonth],"yyyymm")

You can ignore the Expr1 in front of the Format, it is just the default name access puts on an expression if you don't specifically name it yourself.

Sorry about the mix up.

 

[RV]

Jon K,

>Besides, it always includes the first day of the month following the EndingMonth entered. And when the EndingMonth entered is Dec 2001, the cdate() function will convert it to 13/1/2001.<

You're absolutely right, I guess that's the risk due to staying up too long after a hard days work

RV

 

[Ian Stow]

Thanks very much for everyones help it is much appreciated to a beginner like me, perhaps I can avail on your experience and now ask the next question which is

How would I then format a field (using those dates input formatted as YYYYMM) in a report to show these results but in the format of say

Report showing JOBS INVOICED between MMM YYYY and MMM YYYY

 

[antomack]

So long as you build your report on the query then the following can be entered as the Control Source for a field on the Report to give the desired title.

="Reports for Jobs Invoiced between " & Format([BeginningMonth],"mmm yyyy") & "and " & Format([EndingMonth],"mmm yyyy")