Calculated dates

  • Thread starter Thread starter JV
  • Start date Start date
J

JV

I am running a query that subtracts a number in a table against a date in the
same table. Plant Date: (([Sales Date])-([Grow Weeks]*7)) The answer is
correct but when I run a critiera of Between[1st date]and[2nd date] I don't
get the filter information desired.

JV
 
I am running a query that subtracts a number in a table against a date in the
same table. Plant Date: (([Sales Date])-([Grow Weeks]*7)) The answer is
correct but when I run a critiera of Between[1st date]and[2nd date] I don't
get the filter information desired.

JV
Click to expand...

I'd suggest using the DateAdd() function instead:

PlantDate: DateAdd("ww", -[Grow Weeks], [Sales Date])

This will return a date/time value and I suspect that your expression will
return a Double Number.
 
John thanks for your input but the result is the same. I am getting numbers
formated as dates but the critiera is seaching as a number. Perhaps i am
missing something at the table level where grow weeks should not be a number
Feild Size "Long interger", and format I have left empty.

JV

John W. Vinson said:
I am running a query that subtracts a number in a table against a date in the
same table. Plant Date: (([Sales Date])-([Grow Weeks]*7)) The answer is
correct but when I run a critiera of Between[1st date]and[2nd date] I don't
get the filter information desired.

JV
Click to expand...

I'd suggest using the DateAdd() function instead:

PlantDate: DateAdd("ww", -[Grow Weeks], [Sales Date])

This will return a date/time value and I suspect that your expression will
return a Double Number.
Click to expand...
 
Results were the same. The critiera is filtering as a number and not by date.

John W. Vinson said:
I am running a query that subtracts a number in a table against a date in the
same table. Plant Date: (([Sales Date])-([Grow Weeks]*7)) The answer is
correct but when I run a critiera of Between[1st date]and[2nd date] I don't
get the filter information desired.

JV
Click to expand...

I'd suggest using the DateAdd() function instead:

PlantDate: DateAdd("ww", -[Grow Weeks], [Sales Date])

This will return a date/time value and I suspect that your expression will
return a Double Number.
Click to expand...
 
The problem could be that the query is misinterpreting the type of [1st
Date] and [2nd Date].

You can declare the parameters for the query or you can force the type using
Bewteen CDate([1st Date]) and CDate([2nd Date])

Also, what format are you using to input the values? You should be using
m/d/yy or yyyy-mm-dd?

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

JV said:
Results were the same. The critiera is filtering as a number and not by
date.

John W. Vinson said:
I am running a query that subtracts a number in a table against a date
in the
same table. Plant Date: (([Sales Date])-([Grow Weeks]*7)) The answer is
correct but when I run a critiera of Between[1st date]and[2nd date] I
don't
get the filter information desired.

JV
Click to expand...

I'd suggest using the DateAdd() function instead:

PlantDate: DateAdd("ww", -[Grow Weeks], [Sales Date])

This will return a date/time value and I suspect that your expression
will
return a Double Number.
Click to expand...
Click to expand...
 
John thanks for your input but the result is the same. I am getting numbers
formated as dates but the critiera is seaching as a number. Perhaps i am
missing something at the table level where grow weeks should not be a number
Feild Size "Long interger", and format I have left empty.
Click to expand...

what are in fact the names and datatypes, and perhaps sample data, of the
relevant fields? I'd say that Grow Weeks *SHOULD* be a Number, long integer;
its format is completely irrelevant.
 
Sales Date - Short Date
Grow Weeks Number Long interger.
These are the only two fields that are of concern.
It is so simple and I can get the correct result in the query, it is only an
issue when I set a limit.


Sales Date Item Quantity Grow weeks Plant Date
01/01/08 Cyclamen 500 16 09/11/07
02/01/08 Cyclamen 350 14 10/26/07
02/15/08 Cyclamen 175 14 11/09/07
03/01/08 Cyclamen 500 12 12/08/07
04/01/08 Cyclamen 300 12 01/08/08

JV
 
Got it.

Set Query parameter 1st Date to Date/time and 2nd date to Date/time.

I just had never had that happen with date limits before

Thanks

JV

John Spencer said:
The problem could be that the query is misinterpreting the type of [1st
Date] and [2nd Date].

You can declare the parameters for the query or you can force the type using
Bewteen CDate([1st Date]) and CDate([2nd Date])

Also, what format are you using to input the values? You should be using
m/d/yy or yyyy-mm-dd?

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

JV said:
Results were the same. The critiera is filtering as a number and not by
date.

John W. Vinson said:
I am running a query that subtracts a number in a table against a date
in the
same table. Plant Date: (([Sales Date])-([Grow Weeks]*7)) The answer is
correct but when I run a critiera of Between[1st date]and[2nd date] I
don't
get the filter information desired.

JV

I'd suggest using the DateAdd() function instead:

PlantDate: DateAdd("ww", -[Grow Weeks], [Sales Date])

This will return a date/time value and I suspect that your expression
will
return a Double Number.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top