help with correct syntax

[lynn atkinson]

I have a calculated date on a query which I want to use as a parameter. I
have looked at previous posts and have found that access is not good at
recognising date formats in calculated fields and is you use cvdate with the
formula it will recognise date format.

However, I am not sure how to use this function with the IIf statement used
to do the calculation in my query. Or is there another way?

Can anyone help

The calculation is as follows:

expected completion: IIf([award]="CARE 2",DateAdd("m",12,[start
date]),IIf([award]="care 3" Or [award]="management 3",DateAdd("m",18,[start
date]),IIf([award]="care 4" Or [award]="management 4",DateAdd("m",24,[start
date]),"")))

regards

 

[Gary Walter]

I have a calculated date on a query which I want to use as a parameter. I
have looked at previous posts and have found that access is not good at
recognising date formats in calculated fields and is you use cvdate with
the
formula it will recognise date format.

However, I am not sure how to use this function with the IIf statement
used
to do the calculation in my query. Or is there another way?

Can anyone help

The calculation is as follows:

expected completion: IIf([award]="CARE 2",DateAdd("m",12,[start
date]),IIf([award]="care 3" Or [award]="management
3",DateAdd("m",18,[start
date]),IIf([award]="care 4" Or [award]="management
4",DateAdd("m",24,[start
date]),"")))

Hi Lynn,

I may not understand, but it appears to me....

You have a stored query that uses [start date]
from a field in a table to calculate an "expected completion" date.

You want to open a report (or change the recordsource of a form)
based on this stored query, but filter for specific completion date(s).

If you are opening a report, you probably have a command button
on a form to do this. If so, put a textbox on the form (say "txtFilterDate")
and change code for click event of that command button.

Private Sub cmdPreviewReport_Click()
On Error GoTo Err_cmdPreviewReport_Click

Dim stDocName As String
Dim strWhere As String

stDocName = "rptNameOfYourReport"

If IsDate(me!txtFilterDate) = True then
strWhere = "[expected completion] = #" & Me!txtFilterDate & "#"
DoCmd.OpenReport stDocName, acPreview, , strWhere
Else
'filter date was Null or not date,
'so open report w/o filter
DoCmd.OpenReport stDocName, acPreview
End If


Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewReport_Click

End Sub


the equals sign in the code line
strWhere = "[expected completion] = #" & Me!txtFilterDate & "#"
could be changed to

= for completions on or after txtFilterDate

< for completions before txtFilterDate

Again, I may not have understood correctly....

good luck,

gary

 

[John Spencer (MVP)]

Since you've told the query to return an empty string, all your values are being
converted to strings. Try

expected completion:
IIf([award]="CARE 2",DateAdd("m",12,[start date]),
IIf([award] IN("care 3","management 3"),DateAdd("m",18,[start date]),
IIf([award] IN ("care 4","management 4"),DateAdd("m",24,[start date]),NULL)))

Or try wrapping the entire IIF in CVDate (But you still have to get rid of the
empty string).

expected completion: CVDate(IIf([award]="CARE 2",DateAdd("m",12,[start
date]),IIf([award]="care 3" Or [award]="management 3",DateAdd("m",18,[start
date]),IIf([award]="care 4" Or [award]="management 4",DateAdd("m",24,[start
date]),NULL))))

 

[Gary Walter]

Thank you John....I missed the empty string.

One more alternative so don't need to
involve CVDate(), but always assured
that calculation is type Date, would be to
use a final nested IIF in place of the empty
string:

IIF(-1,Null,#1/1/1900#)

Thank you again for catch John,

gary

Since you've told the query to return an empty string, all your values are
being
converted to strings. Try

expected completion:
IIf([award]="CARE 2",DateAdd("m",12,[start date]),
IIf([award] IN("care 3","management 3"),DateAdd("m",18,[start date]),
IIf([award] IN ("care 4","management 4"),DateAdd("m",24,[start
date]),NULL)))

Or try wrapping the entire IIF in CVDate (But you still have to get rid of
the
empty string).

expected completion: CVDate(IIf([award]="CARE 2",DateAdd("m",12,[start
date]),IIf([award]="care 3" Or [award]="management
3",DateAdd("m",18,[start
date]),IIf([award]="care 4" Or [award]="management
4",DateAdd("m",24,[start
date]),NULL))))

I have a calculated date on a query which I want to use as a parameter. I
have looked at previous posts and have found that access is not good at
recognising date formats in calculated fields and is you use cvdate with
the
formula it will recognise date format.

However, I am not sure how to use this function with the IIf statement
used
to do the calculation in my query. Or is there another way?

Can anyone help

The calculation is as follows:

expected completion: IIf([award]="CARE 2",DateAdd("m",12,[start
date]),IIf([award]="care 3" Or [award]="management
3",DateAdd("m",18,[start
date]),IIf([award]="care 4" Or [award]="management
4",DateAdd("m",24,[start
date]),"")))

regards