Criteria in a calculated query field

[Rubem]

Hi,

I have the below formula in the query field and run fine.

Expiration: (([shelf Life (Months)]/12*365)+[Date])

But when I write the criteria below and not not get the data I want

Between [enter start date] And [enter end date]

Example: when I run just the formula I get for example dates in April 2010.
But when I use the criteria and ask for data between 04/01/10 and 04/30/10 I
get blank results.

What am I doing wrong?

Thank you for your help.

Rubem

 

[John W. Vinson]

Hi,

I have the below formula in the query field and run fine.

Expiration: (([shelf Life (Months)]/12*365)+[Date])

But when I write the criteria below and not not get the data I want

Between [enter start date] And [enter end date]

Example: when I run just the formula I get for example dates in April 2010.
But when I use the criteria and ask for data between 04/01/10 and 04/30/10 I
get blank results.

What am I doing wrong?

Thank you for your help.

Rubem

One thing is using Date as a fieldname. It's a reserved word for the builtin
Date() function, which returns today's date from the system clock. It may be
looking at Date() - 4/18/2010 - instead of the table field. Another is putting
parentheses and blanks in fieldnames; I'd be inclined to use ShelfLife as the
fieldname, and put explanatory information such as (Months) on a form or in
the Caption property of the fieldname.

You may also want to reconsider your algorithm for Expiration. Another option
would be to use the DateAdd() function:

Expiration: DateAdd("m", [shelf life (Months)], [Date])

 

[Ernie]

Hi,

I have the below formula in the query field and run fine.

Expiration: (([shelf Life (Months)]/12*365)+[Date])

But when I write the criteria below and not not get the data I want

Between [enter start date] And [enter end date]

Example: when I run just the formula I get for example dates in April
2010.
But when I use the criteria and ask for data between 04/01/10 and 04/30/10
I
get blank results.

What am I doing wrong?

Thank you for your help.

Rubem

 

[Rubem]

John,

Thank you for your suggestions. I changed them as you suggested.

I am using the formula you posted which seems to be easier. The problem is
that I still can use the criteria.

Using my formula if i use, for example "40229" and 05/01/10 I can get an
answer. But if I use what you recommended I get the following message "Data
type mismatch in query expression".

Rubem

Hi,

I have the below formula in the query field and run fine.

Expiration: (([shelf Life (Months)]/12*365)+[Date])

But when I write the criteria below and not not get the data I want

Between [enter start date] And [enter end date]

Example: when I run just the formula I get for example dates in April 2010.
But when I use the criteria and ask for data between 04/01/10 and 04/30/10 I
get blank results.

What am I doing wrong?

Thank you for your help.

Rubem

One thing is using Date as a fieldname. It's a reserved word for the builtin
Date() function, which returns today's date from the system clock. It may be
looking at Date() - 4/18/2010 - instead of the table field. Another is putting
parentheses and blanks in fieldnames; I'd be inclined to use ShelfLife as the
fieldname, and put explanatory information such as (Months) on a form or in
the Caption property of the fieldname.

You may also want to reconsider your algorithm for Expiration. Another option
would be to use the DateAdd() function:

Expiration: DateAdd("m", [shelf life (Months)], [Date])

 

[John W. Vinson]

John,

Thank you for your suggestions. I changed them as you suggested.

I am using the formula you posted which seems to be easier. The problem is
that I still can use the criteria.

Using my formula if i use, for example "40229" and 05/01/10 I can get an
answer. But if I use what you recommended I get the following message "Data
type mismatch in query expression".

Try a criterion of #05/01/10#, or a Parameter Query with the parameter defined
as a Date/Time.

 

[John W. Vinson]

Using my formula if i use, for example "40229" and 05/01/10 I can get an
answer. But if I use what you recommended I get the following message "Data
type mismatch in query expression".

Please post the complete actual SQL and indicate the datatype sof the fields.

 

[John Spencer]

You might try declaring the parameters or using

Between CDate([enter start date]) And CDate([enter end 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
Repeat for the second parameter.

IN SQL View you would see a line added to the beginning of the query.
Parameters [enter start date] DateTime, [enter end date] DateTime;
SELECT ...
FROM ...

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County