Report based on date relative to today

[Reynastus]

Hi All

I know this has probably been asked a heap of times before but I cannot find the solution.

What I would like to do is pretty simple.

I want to create a report that refers to a "ReviewDue" cell in a table and if the date has passed then add the expired documents to the report. If today > ReviewDue date then have those results filtered.

However I have not worked with dates in Access before and have no-idea where to start.

I was thinking of having a hidden control on a form that would have today's date in it (through calling the system date) and then using baldy's filter example however I am really new to access and have no-idea how to link this to the table syntax wise

this is what I thought would work but again I dont know about dates and it probably won't (am about to try the following but if it does not work then I have no-idea where to go, hence the pre-emptive post)

[FONT=Courier New]DoCmd.OpenReport "expired",acViewPreview , , "ReviewDue < [COLOR=#ff0000]#[/COLOR]" & Me.Today[COLOR=#ff0000] & "#"[/COLOR][/FONT]
[FONT=Courier New][COLOR=#ff0000]

[/COLOR][/FONT]

Any help is appreciated

Rey

 

[boblarson]

You are close:

DoCmd.OpenReport "expired",acViewPreview , , "ReviewDue < #" & Date & "#"

Date is the function which returns today's date.

Now, if your field only stores dates that is good but if you store times as well (i.e. set to General instead of ShortDate in the table's field) then you need a slightly different approach.

 

[boblarson]

Oh, and one more thing - if you are not in the U.S. you may need to disambiguate the dates by using this:

DoCmd.OpenReport "expired",acViewPreview , , "ReviewDue < Format(Date, "\#mm\/dd\/yyyy\#")

 

[Reynastus]

Thanks for the speedy reply Bob

You are correct in thinking I am not from the states and I am thinking that (due to being in Aus) that the following would be right for dd/mm/yyyy formatted dates

"ReviewDue < [B][COLOR=red]Format(Date, "\#dd\/mm\/yyyy\#")[/COLOR][/B] Today 03:53 PM

and the bit I was looking for was the

"ReviewDue < #" & [B][COLOR=red]Date[/COLOR][/B] & "#"

part ... in particular the Date function as I was not sure what the answer was ... I knew there would be a more elegant way to call todays date than using an unbound text box with today's date in it. I was sure I read somewhere that the beauty of VBA was that it could call the windows api for such things.

Thanks for the hints.

My solution did work as well (once I got the <> around the right way)

Rey

 

[boblarson]

If you use the

"ReviewDue < Format(Date, "\#dd\/mm\/yyyy\#")

You need to use it as I had it \#mm\/dd\/yyyy\#)

And you can see the reasons why here (from a fellow countryman of yours and longtime Access MVP)

 

[Reynastus]

Coolio

Tried it exactly as you mentioned and its giving me a compile error: Expected Expression and Highlighting the first #

as in at the red #

"ReviewDue < Format(Date, "\#mm\/dd\/yyyy\#")

any ideas as to why?

 

[boblarson]

Coolio

Tried it exactly as you mentioned and its giving me a compile error: Expected Expression and Highlighting the first #

as in at the red #

"ReviewDue < Format(Date, "\#mm\/dd\/yyyy\#")

any ideas as to why?

Yeah, you're missing a quote and ampersand (sorry about that, I did that when I first posted the fixed code):

"ReviewDue < " & Format(Date, "\#mm\/dd\/yyyy\#")

 

[Reynastus]

That makes alot more sence once seeing it.

And after running the report with the just the Date option I can see that it will require the formatting of the date as well.

However when I run the application I get a runtime error and access closes - without the date formatting the app works fine (just adds records to the expired list that are not expired)

Any idea why? I'm running access 2003 if that helps

Rey

 

[Reynastus]

actually nevermind I got it sorted.

Problem was I had an excess of #'s in my code

so what should have been

DoCmd.OpenReport "expired", acViewPrieview,,"ReviewDue <" & Format(Date, "\#mm\/dd\/yyyy\#")

I had

DoCmd.OpenReport "expired", acViewPrieview, , "ReviewDue < #" & Format(Date, "\#mm\/dd\/yyyy\#") & "#"

This came about with me playing around and not getting a clear error report. I changed pc's here and managed a clearer error report (for some reason my machine just gave me a run time error not one with syntax details - probably to do with running access 2003 runtime environment and doing all db design in access 2000 - which for some reason is not printing reports for me)

So thanks for the assistance Bob and its all working great now.