-
stuck on simple date query
I have a table full of receipt data, and I want to be able to run a query to search through it by an approximate date, ie to bring up receipt issued +/- 7 days of the entered date, which is entered via a parameter. I tried
"> ([Enter Estimated Date]-7) and < ([Enter Estimated Date] +7)"
But when I run the query and enter say 01/01/01 it tells me the expression is typed incorrectly or is too complex.
So I guess my questions is
a) What am I doing wrong?
b) How do I enter the date numerically?
c) Will this do what I want?
I tried reading the help file (access XP) but it doesn't seem very... well... helpful: p
-
"> ([Enter Estimated Date]-7) and < ([Enter Estimated Date] +7)"
Try
PARAMETER [Enter Estimated Date] Date
SELECT yada, yada
FROM yada
WHERE ([FieldName] > Cdate [Enter Estimated Date]-7) and ([FieldName] < [Enter Estimated Date] +7)"
-
Ta for your help, I tried what you said, actully found something usefull in the help filr (heaven forbid!) i got the query to run using:
"PARAMETER [Enter Estimated Date] Date
SELECT [Recpit Date]
FROM [Accounting]
WHERE [Recpit Date]">"cdate[Enter Estimated Date] -7" And [Recpit Date]<[Enter Estimated Date]+7
but when i enter a date it say "expression is typed incorectly or is too complex to be evaluated"
doesnt bring up an errors if just use a number, but it doesnt bring up any records either
thanks for your help, Im really new at this.
-
Try DateAdd
Interesting. Well I used to always use DateAdd until I saw another poster use [Date] + X, X being a number of days. Looks like there's a good reason to stick with the more formal DateAdd function.
Try
PARAMETERS [Enter Estimated Date] DateTime;
SELECT Accounting.[Recpit Date]
FROM Accounting
WHERE (((Accounting.[Recpit Date])>DateAdd('d',-7,[Enter Estimated Date]) And (Accounting.[Recpit Date])<DateAdd('d',7,[Enter Estimated Date])));
HTH,
Peter
-
WOOOHOOO!
it goes with:
PARAMETERS [Enter Estimated Date] DateTime;
SELECT [Recpit Date]
FROM [Acc date]
WHERE [Recpit Date]>DateAdd('d',-7,[Enter Estimated Date]) And [Recpit Date]<DateAdd('d',7,[Enter Estimated Date])
I was usignthe accounting table instead of the accdate field in the query, so it gave me a bit of trouble, but it goes now,
Thanks for all your help
-
DOH
no it doesnt, it just brings up all the records.. I just had too few (ie : 1) test records..
edit:
well i made the query afresh, and just used
>DateAdd("d",-7,[Enter Estimated Date]) And <DateAdd('d',+7,[Enter Estimated Date])
and it works sweet as
thanks
Last edited by Wibber; 03-18-2003 at 05:48 PM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|