Date Function

[profxavier27809]

Hello all,

Thanks first of all for all of the help I always receive in here. My question is I have to fields on a report simply reading from a query. They are Effective Date and Warning Delivered. These are formatted short date with an input mask of 07/12/05. I wondered if you knew of a function that, for example, if the effective date was 09/01/05 and the warning delivered was 09/03/05 that it would return a value that says 2 days. Thanks again in advance for your help or direction.

Sincerely,

Raymond

 

[raskew]

Hi Raymond -

Read-up on the DateDiff() function. Here's an example
from the debug window:

EffecDate = #9/1/05#
WarnDate = #9/3/05#

? datediff("d", effecDate, warnDate)
2


HTH - Bob

 

[profxavier27809]

Thanks for that. I went and looked around at a few spots but all they were showing is how to enter like a date, and it returns the number of days you have left until your next birthday, until Christmas, etc...This one was the closest: Function DiffADate(theDate)
DiffADate = "Days from today: " & DateDiff("d", Now, theDate)
End Function

But I cannot figure out how to use functions in code. I know how to to AfterUpdate PrivateSubs kinda like the one you showed me, but I do not want to place values in there for it to evaluate. I would like to put it either as an expression on a field in the report or running in the background to calculate the days elapsed. I'll see what I can do.

 

[profxavier27809]

Ok I figured it out. Now I want it to say "Day" or Days". I tried this on the reports OnOpen

If Me.Text7 > 1 Then
Me.Text6 = "Day2"
Else: Me.Text6 = "Day"
End If
End Sub

It tells me "You entered an expression that has no value" Although it clearly has a value. Is there something I can do in the Control area where I have it like this: datediff("d", Start_Date, End_Date)

 

[raskew]

Raymond -

Go to the Access Help File. Look up the DateDiff() function and study it.
If you want your response to be:

'2 days'

...it would look like:

EffecDate = #9/1/05#
WarnDate = #9/3/05#

? datediff("d", effecDate, warnDate) & " days"
2 days

Bob