Find all records ignoring time (1 Viewer)

Malcolm17

Member
Local time
Today, 20:47
Joined
Jun 11, 2018
Messages
107
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
 

Ranman256

Well-known member
Local time
Today, 16:47
Joined
Apr 9, 2015
Messages
4,339
Make a base query that converts the field to pure date:
DteFld Format(field,"mm/dd/yyyy")

THEN do your date range in that.
 

Malcolm17

Member
Local time
Today, 20:47
Joined
Jun 11, 2018
Messages
107
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:47
Joined
Oct 29, 2018
Messages
21,358
Hi. You could try this.
Code:
>=Forms!FirstLoad.TheDate And < Forms!FirstLoad.TheDate+1
 

Malcolm17

Member
Local time
Today, 20:47
Joined
Jun 11, 2018
Messages
107
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

  • Example.accdb
    440 KB · Views: 286

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:47
Joined
May 7, 2009
Messages
19,169
add another Calculated Column to your Query:

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

theDBguy

I’m here to help
Staff member
Local time
Today, 13:47
Joined
Oct 29, 2018
Messages
21,358
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.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:47
Joined
May 7, 2009
Messages
19,169
try to minimize the formula:

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

can be translated to:

=Forms!FirstLoad.TheDate
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:47
Joined
Oct 29, 2018
Messages
21,358
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
 

Malcolm17

Member
Local time
Today, 20:47
Joined
Jun 11, 2018
Messages
107
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
 

Malcolm17

Member
Local time
Today, 20:47
Joined
Jun 11, 2018
Messages
107
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:47
Joined
Oct 29, 2018
Messages
21,358
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

Top Bottom