Query on a calculated field

[Guest]

Having read millions of time not to store results of calculated fields in
tables, I have the following in a field on a form that calculates a date 6
months in the future [target date] from an field [actual date]

=IIf([Actual Date] Is Not Null,DateSerial(Year([Actual Date]),Month([Actual
Date])+6,Day([Actual Date])))

However, I now need to produce a query and report on [target date] and I am
unsure how to put this formula into the query. I need to produce a query that
shows me all [target dates] within a given date range.

I can read some SQL, but usually use the Access query design view.

Thanks in advance!

 

[Jeff Boyce]

Open a query in design view.

In the "field" grid, add:

YourNewField: IIF(... your expression...)

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Guest]

Thanks Jeff, I have now got it calculating the [target date] as a new
expression.

But then when I put my criteria of:
Between [forms]![Report Dates]![first date] And [forms]![Report Dates]![last
date]

It gives me all 240 records, instead of only the 4 that should be between
the dates I give.

If I change the criteria to be: Between #01/04/2007# And #30/09/2007# I get
the correct 4 records.

Any ideas why my parameter query will not work?

Open a query in design view.

In the "field" grid, add:

YourNewField: IIF(... your expression...)

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Having read millions of time not to store results of calculated fields in
tables, I have the following in a field on a form that calculates a date 6
months in the future [target date] from an field [actual date]

=IIf([Actual Date] Is Not Null,DateSerial(Year([Actual Date]),Month([Actual
Date])+6,Day([Actual Date])))

However, I now need to produce a query and report on [target date] and I am
unsure how to put this formula into the query. I need to produce a query that
shows me all [target dates] within a given date range.

I can read some SQL, but usually use the Access query design view.

Thanks in advance!

 

[John Spencer]

Or you can use the IIF expression or you can use DateAdd function which will
automatically handle null values.

Field: TargetDate: DateAdd("m",6, [Actual Date])



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Open a query in design view.

In the "field" grid, add:

YourNewField: IIF(... your expression...)

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Having read millions of time not to store results of calculated fields in
tables, I have the following in a field on a form that calculates a date
6
months in the future [target date] from an field [actual date]

=IIf([Actual Date] Is Not Null,DateSerial(Year([Actual Date]),Month([Actual
Date])+6,Day([Actual Date])))

However, I now need to produce a query and report on [target date] and I am
unsure how to put this formula into the query. I need to produce a query that
shows me all [target dates] within a given date range.

I can read some SQL, but usually use the Access query design view.

Thanks in advance!

 

[John Spencer]

Pardon me for jumping in.

It could be that the criteria is not being understood properly.

You can try to fix it by declaring the parameters or by forcing the
parameter type in the where clause

Between CDate([forms]![Report Dates]![first date]) And CDate([forms]![Report
Dates]![last date])

Or
Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks Jeff, I have now got it calculating the [target date] as a new
expression.

But then when I put my criteria of:
Between [forms]![Report Dates]![first date] And [forms]![Report
Dates]![last
date]

It gives me all 240 records, instead of only the 4 that should be between
the dates I give.

If I change the criteria to be: Between #01/04/2007# And #30/09/2007# I
get
the correct 4 records.

Any ideas why my parameter query will not work?

Open a query in design view.

In the "field" grid, add:

YourNewField: IIF(... your expression...)

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

message

Having read millions of time not to store results of calculated fields
in
tables, I have the following in a field on a form that calculates a
date 6
months in the future [target date] from an field [actual date]

=IIf([Actual Date] Is Not Null,DateSerial(Year([Actual Date]),Month([Actual
Date])+6,Day([Actual Date])))

However, I now need to produce a query and report on [target date] and
I am
unsure how to put this formula into the query. I need to produce a
query that
shows me all [target dates] within a given date range.

I can read some SQL, but usually use the Access query design view.

Thanks in advance!

 

[Guest]

Fantastic! Thanks so much John...I set the parameter data type and used this
more simple formula.

Or you can use the IIF expression or you can use DateAdd function which will
automatically handle null values.

Field: TargetDate: DateAdd("m",6, [Actual Date])



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Open a query in design view.

In the "field" grid, add:

YourNewField: IIF(... your expression...)

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Having read millions of time not to store results of calculated fields in
tables, I have the following in a field on a form that calculates a date
6
months in the future [target date] from an field [actual date]

=IIf([Actual Date] Is Not Null,DateSerial(Year([Actual Date]),Month([Actual
Date])+6,Day([Actual Date])))

However, I now need to produce a query and report on [target date] and I am
unsure how to put this formula into the query. I need to produce a query that
shows me all [target dates] within a given date range.

I can read some SQL, but usually use the Access query design view.

Thanks in advance!