time sensitive query 1

[ksbrace]

I have a query that was built with help from this forum using Between date. I need it to be a bit more sensitive, I need yesterday's sales, but I need it to stay within 9:30am to 12:30am (following day), it's a liquor store.

I would also love to be able to get a total value of the SumOfSales column.

Thanks in advance for any help/guidance.

tables:

Product Code
Description
Transaction Number
Quantity
Sales Value
Cost
Transaction Date
Transaction Time
Department
Type Code
Cashier
Computer Name
Customer Code

Query:

SELECT [product history].[transaction date], [product history].[Product Code], [product history].[description one] AS Brand, Sum([product history].Quantity) AS SumOfQuantity, Sum([product history].[Sales Value]) AS [SumOfSales Value]
FROM [product history]
WHERE ((([product history].[Transaction Date]) Between Date()-1 And Date()) AND (([product history].[department])='01')) and [transaction time] >= '9:30:00 AM' and [transaction time] <='12:30:00 AM'
GROUP BY [product history].[transaction date], [product history].[Product Code], [product history].[description one];

 

[PWise]

And [product history].[transaction date] Between date()-1 and date()

 

[ksbrace]

pwise,
I already have that in there, I want to specifically keep it between 9:30am today and 1:00am tomorrow. Thanks in advance.

 

[ksbrace]

so....I have it working from 9:30 AM yesterday to 11:59:59 PM tonight, but I need to go one more hour into the next day.

SELECT  [product history].[transaction date], [product history].[Product Code], [product history].[description one] AS Brand, Sum([product history].Quantity) AS SumOfQuantity, 

Sum([product history].[Sales Value]) AS [SumOfSales Value]

FROM [product history]

WHERE ( ( ( [product history].[Transaction Date] ) Between Date()-1 And Date() ) AND ( ( [product history].department)='01')  and ([transaction time] >=#9:30:00 AM# ) and ([transaction time] <=#23:59:59 PM#) )

GROUP BY [product history].[transaction date], [product history].[Product Code], [product history].[description one];

 

[jaxtell]

SELECT  [product history].[transaction date], [product history].[Product Code], [product history].[description one] AS Brand, Sum([product history].Quantity) AS SumOfQuantity, 

Sum([product history].[Sales Value]) AS [SumOfSales Value]

FROM [product history]

WHERE ( ( ( [product history].[Transaction Date] ) = Date()-1 AND ([transaction time] >=#9:30:00 AM#) OR ( ( [product history].[Transaction Date] ) = Date() AND ([transaction time] <=#12:30:00 AM#)) ) AND ( ( [product history].department)='01')   )
 

GROUP BY [product history].[transaction date], [product history].[Product Code], [product history].[description one];

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[lespaul]

the reason this is such an issue is because you have split the transaction date and time into two fields.
A "date" is float represented by the number of days since 12/30/1899 as the integer portion and the time of day represented by the decimal portion.
To see this in action, open excel and put:[tt]
A B
1 9/3/2008 1:34 pm =A1[/tt]

In cell B2, format the cell as a number with 8 decimals; you will see that the number is 39694.56527778.

Next time you need to capture a specific time an event occurs, just use a regular date field and capture the date and time.
HTH

Leslie

Have you met Hardy Heron?

 

[ksbrace]

jaxtell,
Thanks your solution worked.

lespaul,
Trust me if I created the db/tables, I would have just used one field. Unfortnately, I'm just helping out and it's a canned application where I don't have access to the source code.

 

[PHV]

What about this ?
WHERE ([Transaction Date]+TimeValue([transaction time])) Between (Date()-1+#9:30:00 AM#) AND (Date()+#12:30:00 AM#)
AND [product history].department='01'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886