Find all records ignoring time (1 Viewer)

Malcolm17

Member
Local time
Today, 21:05
Joined
Jun 11, 2018
Messages
44
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

Registered User.
Local time
Today, 16:05
Joined
Apr 9, 2015
Messages
3,644
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, 21:05
Joined
Jun 11, 2018
Messages
44
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:05
Joined
Oct 29, 2018
Messages
13,320
Hi. You could try this.
Code:
>=Forms!FirstLoad.TheDate And < Forms!FirstLoad.TheDate+1
 

Malcolm17

Member
Local time
Today, 21:05
Joined
Jun 11, 2018
Messages
44
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: 16

arnelgp

error reading drive A:
Local time
Tomorrow, 05:05
Joined
May 7, 2009
Messages
10,883
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:05
Joined
Oct 29, 2018
Messages
13,320
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

error reading drive A:
Local time
Tomorrow, 05:05
Joined
May 7, 2009
Messages
10,883
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:05
Joined
Oct 29, 2018
Messages
13,320
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, 21:05
Joined
Jun 11, 2018
Messages
44
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, 21:05
Joined
Jun 11, 2018
Messages
44
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:05
Joined
Oct 29, 2018
Messages
13,320
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