Solved Problem with date range defined by a textbox (1 Viewer)

[Hecronis]

I have a query that has one column that groups names together, a second column that sums up the quantity of how many total units someone has received, and then a third column that has purchase dates in it that is set to "where". I am trying to set a criteria in the date column to show results between two dates that will be set on a form. For whatever reason when I put my conditions in the query it returns a blank query.

I'm using this as my criteria:
>=[Forms]![Form1]![Date1] AND <=[Forms]![Form1]![Date2]

I know this should be simple and I have gotten this same concept to work in another database. I just can't understand why I keep getting a blank query as my result.

 

[Uncle Gizmo]

How were the dates generated in the table? Were they generated with Date() or Now() Date() just provides the date Now() provides the date and time, hence, just searching for a date won't work on Now() generated dates.

 

[Gasman]

Is the form open?
Is there anything in those controls?

 

[Gasman]

How were the dates generated in the table? Were they generated with Date() or Now() Date() just provides the date Now() provides the date and time, hence, just searching for a date won't work on Now() generated dates.

Tony, it might not be accurate, but it would still produce data when a date entered is greater than a previous date, even if time is included in that previous date, and vice versa?

 

[Uncle Gizmo]

More info on Date/Time on my website here:-

Time Gentlemen Please - Nifty Access

Time Gentlemen Please Using Date and Time in MS Access can be a bit of a minefield, so I suggest you do plenty of research on it. I've done a few video's on Date/Time over the last few years and I've brought them together in this post More Links in the DATE MENU Below! Add

www.niftyaccess.com

 

[Uncle Gizmo]

Tony, it might not be accurate

Haven't tested, it but I believe you need to extract the date part as I demonstrate in video number two in the the webpage linked to above.

 

[Gasman]

I have a query that has one column that groups names together, a second column that sums up the quantity of how many total units someone has received, and then a third column that has purchase dates in it that is set to "where". I am trying to set a criteria in the date column to show results between two dates that will be set on a form. For whatever reason when I put my conditions in the query it returns a blank query.

I'm using this as my criteria:
>=[Forms]![Form1]![Date1] AND <=[Forms]![Form1]![Date2]

I know this should be simple and I have gotten this same concept to work in another database. I just can't understand why I keep getting a blank query as my result.

I use the GUI to create this.?

SELECT TestTransactions.*, TestTransactions.TransactionDate
FROM TestTransactions
WHERE (((TestTransactions.TransactionDate)>=[Forms]![frmDates]![Text0] And (TestTransactions.TransactionDate)<=[Forms]![frmDates]![Text2]));

so if that is 'literally' what you have written, then you have falling into the AND/OR trap.?
You need to specify what you are comparing against each time or Use BETWEEN

 

[Hecronis]

How were the dates generated in the table? Were they generated with Date() or Now() Date() just provides the date Now() provides the date and time, hence, just searching for a date won't work on Now() generated dates.

Now()

 

[Hecronis]

I use the GUI to create this.?

SELECT TestTransactions.*, TestTransactions.TransactionDate
FROM TestTransactions
WHERE (((TestTransactions.TransactionDate)>=[Forms]![frmDates]![Text0] And (TestTransactions.TransactionDate)<=[Forms]![frmDates]![Text2]));

so if that is 'literally' what you have written, then you have falling into the AND/OR trap.?
You need to specify what you are comparing against each time or Use BETWEEN

Yes I used the gui to create this. Give me a sec and I'll upload the SQL.

 

[Hecronis]

Now()

I tried replacing it with date() and removed the time portions from the table and I'm still getting nothing.

 

[Hecronis]

Here's the SQL for the query

SELECT Tbl_DataSizes.Size, Sum(Tbl_PickUp.Quantity) AS SumOfQuantity
FROM (Tbl_Interaction LEFT JOIN Tbl_PickUp ON Tbl_Interaction.ID_Interaction = Tbl_PickUp.ID_Interaction) LEFT JOIN Tbl_DataSizes ON Tbl_PickUp.[ID_Size] = Tbl_DataSizes.ID_Sizes
WHERE (((Tbl_Interaction.Interaction_DateStamp)>=[Forms]![SizeReport]![Date1] And (Tbl_Interaction.Interaction_DateStamp)<=[Forms]![SizeReport]![Date2]))
GROUP BY Tbl_DataSizes.Size;

 

[Gasman]

Upload the dB.? With enough to test.
I must admit I always use the table I am getting the data from first? You are using the table with the dates, but the data is not from that table?

 

[Hecronis]

Upload the dB.? With enough to test.
I must admit I always use the table I am getting the data from first? You are using the table with the dates, but the data is not from that table?

How do I upload the DB?

 

[Hecronis]

Like I said, I'm really confused because I've gotten this to work in other databases with no problems. I followed the same steps but it's not working.

 

[Gasman]

How do I upload the DB?

Zip it and attach as a file. Just enough to see the issue.

 

[Hecronis]

I got it figured out. I was missing something on the form. It's working now.

 

[Gasman]

I got it figured out. I was missing something on the form. It's working now.

And that was?

Remember, these forums are here to help others, and someone else might make the same mistake?
So please post what was wrong and what you did to correct it.

Also mark the thread as Solved?

 

[Hecronis]

I misnamed the label to what I wanted the text box to be named. To be honest I feel embarrassed that I wasted your time.

 

[Gasman]

Not wasted, might help someone else, if only to check they have named everything correctly.
Thank you.

 

[Babycat]

What if you let Date1 or Date2 blank in your form? I think it will introduce an error.
I want to design a query that if Date2 textbox is blank, the query will select all record from Date1 to today.
Is there any way to accomplish it?

Feel free to advise.