Great! I'm making some great progress here.
First, SaleDate is in the table, but I was asking the User
to Enter the date to choose the 90 day period. (The LP
person isn't always in the office, and may need to run the
report on one day, for many prior days. I'll work on that
efficiency later - first I just wanted to get the data).
So that's why it seemed to be both a parameter and in the
table.
I was using 2 columns, and your code here pointed me to
put the "Between" in the same way I'd put plain old [Enter
Date]. This worked - BUT
I put in -26 and -27 since I only have data from 1/3/04.
With a parameter of 2/1/04, I got 2 days selected 1/6 and
1/7, not one date as you expected. HOWEVER, this is good!
I actually want ALL the records (can have one per cashier
per store per day), where the cashier was off + or - $5.00
or more. So, I changed it to -30 and just [Enter Date] in
the code and I get all the records where the cashier is
either +4.99 or -4.99 or more. Once all this works, I'll
load the earlier data so I can do -90 as LP needs.
That is the first step, and the end of the good news.
Now, I put in "Count(*) >1" under the Cashier column. And
I only get one date (1/27/04) and only one record per
cashier, when each of the 3 displayed have at least 2 days
with the problem I'm looking to display. And I'm not
getting the other cashiers who have more than 1 day of
having +/- $5 in the time period.
Here is the code:
SELECT [T: Cashier OverShort].SALEDATE, [Static Store
Info].StoreName, [T: Cashier OverShort].Cashier, [T:
Cashier OverShort].LastName, [T: Cashier
OverShort].FirstName, [T: Cashier OverShort].OverShort
FROM [T: Cashier OverShort] INNER JOIN [Static Store Info]
ON [T: Cashier OverShort].STNum = [Static Store Info].StNum
GROUP BY [T: Cashier OverShort].SALEDATE, [Static Store
Info].StoreName, [T: Cashier OverShort].Cashier, [T:
Cashier OverShort].LastName, [T: Cashier
OverShort].FirstName, [T: Cashier OverShort].OverShort,
[T: Cashier OverShort].Cashier, [T: Cashier
OverShort].STNum, [T: Cashier OverShort].OverShort
HAVING ((([T: Cashier OverShort].SALEDATE) Between DateAdd
("d",-30,[EnterDate]) And [EnterDate]) AND (([T: Cashier
OverShort].OverShort)>4.99 Or ([T: Cashier
OverShort].OverShort)<-4.99) AND (Count(*)>1))
ORDER BY [T: Cashier OverShort].SALEDATE, [T: Cashier
OverShort].LastName, [T: Cashier OverShort].STNum;
Sample Data: (Just Date, Store, Cashier, OverShort)
1/15 Lynn 5703 (10.00)
1/20 Lynn 5703 10.63
1/17 Swansea 11207 (6.11)
1/4 Swansea 11207 (9.93)
1/27 Worcester 12513 (10.00)
1/27 Lynn 15318 (9.90)
1/27 Norwood 16228 (64.46)
1/30 Worcester 12513 (10.29)
1/3 Cranston 2334 (27.92)
1/3 Cranston 2482 18.44
1/3 Somerville 2287 9.91
With the code above, I only get the 3 1/27 records, and
not the 1/15 and 1/20 for 5703, or 1/17 and 1/4 for 11207.
ALSO, 15318 and 16228 have only 1 error in the time
period, so I don't know why they were selected.
I am stumped, and, again, ask for (and so greatly
appreciate) your help.
Sara
-----Original Message-----
You have me totaly confused now.
You wrote that [SaleDate] is your parameter, and then you write that
[SaleDate] is a field in your table.
I'm confused and I'm sure Access is also.
Is [SaleDate] a valid Date Datatype? Or is it a Text Datatype field?
And I still don't know if the [SaleDate] field includes a time value
or not.
Try this:
Change the query criteria for the [SaleDate] field to:
Between DateAdd("d",-90,[Enter a Date]) and DateAdd("d",- 89,
[Enter a Date])
You will be prompted just once to enter one date.
Enter the date in US date format
Month/day/year.
Do you get a record exactly 90 days old from that date (assuming there
is at least one)?
If you still have difficulty, post the entire query SQL.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
