Show date even if there is no date

[jw5107]

I am working with a query that takes all the dates shown and formats the date like: 1/1/04 by using - DateSerial(DatePart("yyyy",[Date]),DatePart("m",[Date]),1).
If the any date is something like 12/13/03 or 2/4/04, it re-formats the date to show 12/1/03 or 2/1/04.
The problem is there are some months skipped. Can I get the query to show 9/1/04 even if 9/14/04 isn't in the field?

Any examples as to how I can do this??

thanks in advance.
jw

 

[Trendsetter]

Try this:

iif(isNull([TheDate]),"9/1/04",[TheDate])

This will work if the Date field is null(no entry)

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[jw5107]

Trendsetter,

Thanks for the fast response!! However - this isn't quite what I was looking for (or perhaps I am using it wrong...)

I took your example and applied -
MonthDate: IIf(IsNull([Date]),DateSerial(DatePart("yyyy",[Date]),DatePart("m",[Date]),1),[Date])

Like I was saying I have 2 years of dates in a date field.
Some months might be skipped (meaning an aircraft didn't take a delay that month), but I still need to show 0 for that month even though nothing occured during the month.

Hope this helps some!!! Any other fixes???
Thanks again!
jw

 

[Trendsetter]

Right!
Just a point of interest - you should not use DATE as a field name as it is an Access constant and potentially could cause you big problems later on in your application.

What you need to do is count the records by month and where there are 0 records against a month force an entry of zero.


Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[lespaul]

or have a table of the names of each month and left/right join into that table so that the null months still appear.

Leslie