Subform by date; datetime issue

[Chillendw]

Good afternoon everyone,

So I'm having an issue with a subform populating records. I want it to populate all the orders for whichever date is selected on the main form.
However, it doesn't pull up all of the records. I've been able to deduce that it's the format of the date. I have the date stored on the query it's pulling from as DateTime. However, on the combobox, I'm obviously not going to have datetime as a dropdown. So I used:

SELECT DISTINCT DateValue([qryDispensedOrders].[OrderDate]) AS OrderDate
FROM qryDispensedOrders;

So this does display the dates that orders were placed in the combobox.
However, in the subform, records only appear if it's in mm/dd/yyyy format. If it includes the time, the records are not displayed.

What do I need to do to include those other records?

I've attached some pictures to better display what I'm saying.

Thanks in advance for any help.

 

[pbaldy]

The DateValue() function should work to exclude the time portion. You can also use Between and add a day to the selected day.

 

[Uncle Gizmo]

Storing Date & Time in MS Access_32
https://youtu.be/pnG1XQFz-mY

 

[Chillendw]

The DateValue() function should work to exclude the time portion. You can also use Between and add a day to the selected day.

The DateValue() function does work in regards to pulling the dates on the main form. My issue is that for any records in the table that have the time stamp attached (all will, any that don't was just me testing), the subform will not pull up any records. The form comes up blank despite there being actual orders for that date.

 

[pbaldy]

I meant to use the DateValue() function when you filter the subform. You haven't said how you do that, so can't be more specific.

 

[Chillendw]

I meant to use the DateValue() function when you filter the subform. You haven't said how you do that, so can't be more specific.

It's actually just a text box with the Control source as a field in the query...


So I didn't change anything and it somehow started working. I did close and reopen it, not sure if that did it.

But I came across another problem. The dropdown on the main form gives me dates when orders were placed, but I don't see that going well a year from now when there will be many dates. So I tried typing a date in to see what it would return on a date with no orders. e.g., I chose 04/19/2017 from the drop down; the subform had 3 records. I typed in 04/03/2017 (shouldn't have any records), and the first record from 04/19/2017 was changed to 04/03/2017. Not what I want to happen.

a) How do I prevent this from happening?
- Is it possible for the subform to say "No data for this date"?
- How do I prevent the date from being changed?
b) What would be a better approach?

Thanks in advance.

 

[pbaldy]

It sounds like your combo is bound to a field. It should not be.