Question formatting a date

xirokx

Registered User.
Local time
Today, 23:15
Joined
Jun 28, 2009
Messages
47
Hi there,

I have setup a report in access, the field already has a date within it, I would like that date to go red 7 days before the due date. So when I run the report I can easily view which dates are due to expire 7days from today.

How can I set this up? Please clarify which formulas or expressions best to use?

Please advise

thanks in advance for your support
 
In versions since 2000, the simplest solution is Format/Conditional Formatting (in the ribbon in 2007).
 
And a hint as to the formula - there is a function named DateDiff you will want to look into.
 
thanks guys...

I knew the first part i.e. auto format...

However need more advice on the "date diff" command please?

Do I setup a query and run "date diff" from there or do I setup an expression?

Please help

Cheers
 
Okay, here goes -

Select the text box in design view and go to the Conditional Formatting. Select EXPRESSION IS and then use:

DateDiff("d", [YourDateField], Date()) <= 7
 
Oh and select the formatting of your choice that will show up when that formula evaluates to true.
 
am gonna test that right now...

cheers
 
I get a box appear asking me to input todays date...

I was hoping not to get the box and for it auto calculate using the date I run the report.

How can I do this?

Please confirm

Thanks
 
What exactly did you put in as your expression? The parameter prompt indicates something is misspelled.
 
this is exactly what I put in the expression box...

DateDiff("d", [YourDateField], Date()) <= 7

but I am now prompted with a box that asks me to enter the date, once I do that it returns the expired dates in red on my report

I was hoping for the system to automatically use todays date....

What am I doing wrong?

Please advise

Cheers
 
You would need to replace "YourDateField" with the actual name of the date field in your table. Date() should be getting today's date.
 
so to clarify should it read this instead:-

DateDiff("d", Date() ,Date()) <= 7

please confirm
 
Last edited:
No. "YourDateField" in SOS's original post must be replaced by the name of the field in your data that contains the date you want compared to today's date. Can you post your db?
 
cant post the DB sorry...

lets assume the field name is "insuranceexpirydate"

please advise the exact expression I should use to obtain what I require?

thanks
 
DateDiff("d", [insuranceexpirydate], Date()) <= 7
 
Oh boy, here we go! 14 posts and we're still going 'duh-huh'.

Maybe, Paul, you may need to provide a Google map to the help file.

Best wishes -- Bob
 
thanks...

can i ask, if i have two fields with diff field names both with expiry dates then what would be the expression??

cheers really appreciate ya help
 
That expression and another just like it with the other field name, separated by " OR ":

ThisExpression OR ThatExpression
 
hmmm but now the box appears as a prompt box with the label "insurance expiry date"

I was hoping when I clicked a button, the report would automatically show me the insurance and MOTs due to expire within the next 7 days...

Theres something wrong? But what?

The format is correct because when I run the report the dates due to expire show up in Bold Red...

But I am still having to input todays date - why?

please clarify

ta
 
Again, what is the exact expression you're using. You should not need to input today's date.
 

Users who are viewing this thread

Back
Top Bottom