Criteria in a calculated query field

  • Thread starter Thread starter Rubem
  • Start date Start date
R

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
 
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
Click to expand...

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])
 
Rubem said:
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
Click to expand...
 
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

John W. Vinson said:
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
Click to expand...

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])
Click to expand...
 
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".
Click to expand...

Try a criterion of #05/01/10#, or a Parameter Query with the parameter defined
as a Date/Time.
 
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".
Click to expand...

Please post the complete actual SQL and indicate the datatype sof the fields.
 
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
 
Back
Top