Find all records ignoring time

Malcolm17

Member
Local time
Today, 12:26
Joined
Jun 11, 2018
Messages
114
Hi,

I have a form that displays the date of any records that I would like to see for a specific date in a query, however the timestamp that I am using has the time. How can I find all records in my query for the date that I am looking for?

I'm trying the following but it is not working:
Code:
>=[Forms]![FirstLoad]![TheDate 00:00:01] And <=[Forms]![FirstLoad]![TheDate 23:59:99]

I have tried various things, but I thought that the code above would work for me but it doesn't. The field that I am using on the form to input the date is TheDate.

Many thanks,

Malcolm
 
Make a base query that converts the field to pure date:
DteFld Format(field,"mm/dd/yyyy")

THEN do your date range in that.
 
Hi Ranman256,

I don't really understand what you mean by that, please can you tell me a bit more.

Did you mean this,
NewDate: Format([CreatedDate],"dd/mm/yyyy") - (I don't think you do)

I did try this and it didn't work for me.

Malcolm
 
Hi. You could try this.
Code:
>=Forms!FirstLoad.TheDate And < Forms!FirstLoad.TheDate+1
 
Hey theDBguy,

I think that is probably exactly what I was trying to get to, however when I try to run the query I get an error. I have attached this part of my project, please can you have a look to see if you can see where I am going wrong with it?

Thank you,

Malcolm
 

Attachments

add another Calculated Column to your Query:

Field:.........Dte: DateValue([theDateFieldName])
Table:
Sort:
Show: (no tick)
Criteria: DateValue([Forms]![FirstLoad]![TheDate])
 
Hey theDBguy,

I think that is probably exactly what I was trying to get to, however when I try to run the query I get an error. I have attached this part of my project, please can you have a look to see if you can see where I am going wrong with it?

Thank you,

Malcolm
Hi Malcolm. The file you sent does not have the form you're referencing in the query.
 
try to minimize the formula:

>=Forms!FirstLoad.TheDate And < Forms!FirstLoad.TheDate+1

can be translated to:

=Forms!FirstLoad.TheDate
 
Hey theDBguy,

I think that is probably exactly what I was trying to get to, however when I try to run the query I get an error. I have attached this part of my project, please can you have a look to see if you can see where I am going wrong with it?

Thank you,

Malcolm
Okay, I had to do this sometimes. Try it this way:
Code:
>=CDate(Forms!FirstLoad.TheDate) And < CDate(Forms!FirstLoad.TheDate)+1
 
Hey aenelgp,

Thank you for that, that was the trick that I needed, I'll be able to use that in other areas as well now. :)

Malcolm
 
Okay, I had to do this sometimes. Try it this way:
Code:
>=CDate(Forms!FirstLoad.TheDate) And < CDate(Forms!FirstLoad.TheDate)+1
Hi, thank you theDBguy, that worked for me too, I'll keep hold of that for the future.
Malcolm
 
Hi, thank you theDBguy, that worked for me too, I'll keep hold of that for the future.
Malcolm
Hi. Glad to hear you got it to work. Good luck with your project.
 

Users who are viewing this thread

Back
Top Bottom