Find all records ignoring time (1 Viewer)

[Malcolm17]

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:

>=[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]

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]

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]

Hi. You could try this.

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

 

[Malcolm17]

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

 

[arnelgp]

add another Calculated Column to your Query:

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

 

[theDBguy]

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]

try to minimize the formula:

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

can be translated to:

=Forms!FirstLoad.TheDate

 

[theDBguy]

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:

>=CDate(Forms!FirstLoad.TheDate) And < CDate(Forms!FirstLoad.TheDate)+1

 

[Malcolm17]

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]

Okay, I had to do this sometimes. Try it this way:

>=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]

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.