Query date criteria problems

[Jia]

Hello there!
I've created a query that allows users to input the criteria. In this query, there's this field called Transaction Date. A criteria is also set under the Transaction Date field to be:
[Enter Specific Transaction Date]

However when I run the query, e.g. I entered "25/02/2012", all the records with Transaction Dates from i.e. 24/02/2012 10:50 PM, etc till 24/02/2012 11:59 PM. Records after 24/02/2012 wasn't shown but I do have a lot of records with 25/02/2012. Does anybody know why is this happening? :/

 

[NickHa]

Can you post your query, please? This will help us to understand the context better. You are entering dates in UK format dd/mm/yyyy, but in the query they should be American format mm/dd/yyyy. I don't think this explains your problem from the data samples you have given.

 

[Jia]

The Transaction Date field is in the dd/mm/yyyy hh:nn:ss AM/PM format.
jiajian.sg/IMG_001.png
jiajian.sg/IMG_002.png
jiajian.sg/IMG_003.png
But now it doesn't show any records...

 

[NickHa]

Did you mean to attach some images? I don't see them?
I assume you are taking screen shots of your query design - if so, it would be easier to show the SQL - with the query open in design view, right-click and select SQL view. Copy the entire SQL string from that screen and post it for each of your queries (please).

 

[RainLover]

Jia

Did you test your attachments?

 

[NickHa]

I forgot to ask what are the field types you are using? I assume Date/Time, but the string for date and time as you show it would be the result of formatting the underlying data, unless it's a string field, in which case it's a literal. I hope it's the former!

 

[Jia]

Date/Time. Using format "dd/mm/yyyy hh:nn:ss AM/PM"

 

[jdraw]

Post the sql for your query.

 

[Jia]

SELECT Transaction.[Transaction ID], Transaction.[Transaction Date], Transaction.[Member ID], Transaction.Quantity, Transaction.[Product ID], Products.[Item Description], Products.[Selling Price], [Quantity]*[Selling Price] AS [Amount (S$)], [Amount (S$)]*0.07 AS [GST@7%]
FROM [Transaction] INNER JOIN Products ON Transaction.[Product ID] = Products.[Product ID]
WHERE (((Transaction.[Transaction Date]) Like [Enter Transaction Date] & "*"));

 

[jdraw]

SELECT Transaction.[Transaction ID], Transaction.[Transaction Date], Transaction.[Member ID], Transaction.Quantity, Transaction.[Product ID], Products.[Item Description], Products.[Selling Price], [Quantity]*[Selling Price] AS [Amount (S$)], [Amount (S$)]*0.07 AS [GST@7%]
FROM [Transaction] INNER JOIN Products ON Transaction.[Product ID] = Products.[Product ID]
WHERE (((Transaction.[Transaction Date]) Like [Enter Transaction Date] & "*"));

I would recommend you adopt a naming convention that does not include embedded spaces and special characters.
I'm not sure what exactly you expected, and you haven't said.

You might try a query such below to see if it gives what you want/need.

I have used 2 dates and a bETWEEN... AND ... CONSTRUCT

PARAMETERS pTransactionStartDate as DateTime
,pTransactionEndDate as DateTime;
SELECT Transaction.[Transaction ID]
, Transaction.[Transaction Date]
, Transaction.[Member ID]
, Transaction.Quantity
, Transaction.[Product ID]
, Products.[Item Description]
, Products.[Selling Price]
, [Quantity]*[Selling Price] AS [Amount_S]
, [Amount (S$)]*0.07 AS [GST_07]
FROM [Transaction] INNER JOIN Products ON
Transaction.[Product ID] = Products.[Product ID]
WHERE 
(((Transaction.[Transaction Date]) BETWEEN pTransactionStartDate AND pTransactionEndDate;

Note I did change your Amount and GST variable names.

 

[Jia]

Ahh thanks for your help! Will use that code.

 

[Jia]

Worked like a charm! But are there any ways to do like:
Like Between [Enter Starting Date] & "*" And [Enter Ending Date] & "*"?
Because the field's date time format is "dd/mm/yyyy hh:nn:ss AM/PM"
Let's say if I enter 25/02/2012 and ends at 26/02/2012, it won't show records with Transaction Date on 26/02/2012.

 

[jdraw]

The default as I understand it is Date only and the associated time is 00:00:00, and if you include Time with the date, all times will be later than 00:00:00 and will in fact be "later "than just Date.

You could (I think)
a) keep the Date with Time as you have it, and simply add 1 day to the EndDate (in the Between ... And EndDateparm + 23 hrs 59 min 59 seconds), or
b) adjust the default of the Date in the Table to only be Date (no time)

If you don't need the Time on your transactions, then b would seem easiest.
If the time is important, then a)

How do you put the Date and Time on your transactions?

You might adjust the query as follows: (last line)

PARAMETERS pTransactionStartDate as DateTime
,pTransactionEndDate as DateTime;p
SELECT Transaction.[Transaction ID]
, Transaction.[Transaction Date]
, Transaction.[Member ID]
, Transaction.Quantity
, Transaction.[Product ID]
, Products.[Item Description]
, Products.[Selling Price]
, [Quantity]*[Selling Price] AS [Amount_S]
, [Amount (S$)]*0.07 AS [GST_07]
FROM [Transaction] INNER JOIN Products ON
Transaction.[Product ID] = Products.[Product ID]
WHERE 
(Transaction.[Transaction Date]) BETWEEN pTransactionStartDate AND 
DateAdd("d",1,pTransactionEndDate);