 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / February 2008Calculated dates
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 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.  SignatureJohn W. Vinson [MVP] 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 > >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 [quoted text clipped - 9 lines] > 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. 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. SignatureJohn W. Vinson [MVP] 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 > >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 [quoted text clipped - 4 lines] > relevant fields? I'd say that Grow Weeks *SHOULD* be a Number, long integer; > its format is completely irrelevant. Results were the same. The critiera is filtering as a number and not by date. > >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 [quoted text clipped - 9 lines] > This will return a date/time value and I suspect that your expression will > return a Double Number. 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? SignatureJohn Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County . > Results were the same. The critiera is filtering as a number and not by > date. [quoted text clipped - 15 lines] >> will >> return a Double Number. 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 > The problem could be that the query is misinterpreting the type of [1st > Date] and [2nd Date]. [quoted text clipped - 24 lines] > >> will > >> return a Double Number. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.