Date Expression

  • Thread starter Thread starter Darla
  • Start date Start date
D

Darla

Help! My brain has died and I'm sure I've just stared at
this too long. I have three fields (Date on Hold),
(Release Date), and (Days in Hold). I'm trying to
calculate the days in hold field.

This is what I tried in my query.

Days in Hold:IIf((IsNull[Release Date]), (Now()-[Date On
Hold]), ([Release Date]-[Date On Hold]))
Obviously, it didn't work because I have a syntax error.

I'm trying to write it using code and an if then else
statement.

Private Sub ifthenelse()
If IsNull(Me.Release_Date.Date) Then


I'm not sure what the best approach is going to be.
Thanks for any help you can give.
 
Use:

Days in Hold: IIf([Release Date],([Release Date]-[Date On Hold]),Now()-[Date
On Hold])

Rick B




Help! My brain has died and I'm sure I've just stared at
this too long. I have three fields (Date on Hold),
(Release Date), and (Days in Hold). I'm trying to
calculate the days in hold field.

This is what I tried in my query.

Days in Hold:IIf((IsNull[Release Date]), (Now()-[Date On
Hold]), ([Release Date]-[Date On Hold]))
Obviously, it didn't work because I have a syntax error.

I'm trying to write it using code and an if then else
statement.

Private Sub ifthenelse()
If IsNull(Me.Release_Date.Date) Then


I'm not sure what the best approach is going to be.
Thanks for any help you can give.
 
I tried this and it didn't work. It showed a dialog box
asking for a date.

I've tried writing this in vba..can someone debug it for
me please.

Private Sub ifthenelse()
If IsNull(Me.Release_Date.Date) Then
(Me.Days_in_Hold.Date) = Date()-
(Me.Date_On_Hold.Date) Else
(Me.Days_in_Hold.Date) = (Me.Release_Date.Date)-
(Me.Date_On_Hold.Date)
End If
End Sub
 
Help! My brain has died and I'm sure I've just stared at
this too long. I have three fields (Date on Hold),
(Release Date), and (Days in Hold). I'm trying to
calculate the days in hold field.

This is what I tried in my query.

Days in Hold:IIf((IsNull[Release Date]), (Now()-[Date On
Hold]), ([Release Date]-[Date On Hold]))
Obviously, it didn't work because I have a syntax error.

I'm trying to write it using code and an if then else
statement.

Private Sub ifthenelse()
If IsNull(Me.Release_Date.Date) Then


I'm not sure what the best approach is going to be.
Thanks for any help you can give.
Click to expand...

Which do you want? The query syntax or VBA?

In the query:

DaysInHold:IIf(IsNull([Release Date]), (Date()-[Date On
Hold], [Release Date]-[Date On Hold])

Note the position of the parenthesis, and use Date(), not Now(), where
indicated.

In VBA ...

I notice from your one line above...
If IsNull(Me.Release_Date.Date) Then <
Click to expand...
You use Me.Release_Date.Date.
If Release_date is the name of a control (as you posted above), what
is Date?

In VBA try:

If IsNull(Me![Release_date]) Then
[DaysInHold] = Date-[Date On Hold]
Else
[DaysInHold] = [Release Date]-[Date On Hold]
End If

The above VBA must be used in form or report code window, not in an
Access Module.
 
Back
Top