Just query the Date portion of a Date/Time field

[Zydeceltico]

Hi all -

I record both Date and Time in the same field like this: 5/3/2019 2:31:27 PM.

I want to build a query that will let me look at all of the records from some other day - a single day.

I tried this code that clearly isn't correct as it returns nothing even though I have data on this date:

SELECT lutblinspectiontypes.inspectiontype, 
       tblinspectionevent.datetime, 
       [jobnumber] & [task] & [resource] AS Job 
FROM   tbljobs 
       INNER JOIN (lutblinspectiontypes 
                   INNER JOIN tblinspectionevent 
                           ON lutblinspectiontypes.inspectiontype_id = 
                              tblinspectionevent.inspectiontype_fk) 
               ON tbljobs.job_id = tblinspectionevent.job_fk 
WHERE  (( ( tblinspectionevent.datetime ) = [enter date:] ));

What do I need to do to parse out just the date?

Thanks!

Tim

 

[June7]

Try:

CDate(Int([datetime]))

Recommend not using popup input prompts in query - can't validate user input.

 

[theDBguy]

Hi Tim. You could also use the DateValue() function but your query won’t be sargable. Meaning, the database engine won’t be able to take advantage of any index against that column. Instead, I’d recommend using a complete criteria for the whole day, including the time. For example, Between #5/3/2019 00:00 AM# And #5/3/2019 23:59 PM#

 

[June7]

Ah, forgot about DateValue.

However, requiring users to input a full date/time is rather demanding. They will mess up the date part often enough without throwing in the time as well. Again, don't use popup prompts, reference controls on form.

 

[theDBguy]

Ah, forgot about DateValue.

However, requiring users to input a full date/time is rather demanding. They will mess up the date part often enough without throwing in the time as well. Again, don't use popup prompts, reference controls on form.

Hi. The user only needs to enter the date once or select it from a calendar control. You add the times yourself in the code.

 

[June7]

Right, so in query, tested and this works:

WHERE datetime Between [enter date:] & " 00:00:00" And [enter date:] & " 23:59:59"

Again, still advise not to use popup input prompts in query.

 

[Zydeceltico]

For example, Between #5/3/2019 00:00 AM# And #5/3/2019 23:59 PM#

Operationally what does that look like given that I am assuming that I'll need to use a popup form for the user to enter criteria? Would I have 2 selection boxes? One for Beginning Sate and for Ending Date? Come to think of it - IF that can accomodate a single day as a range it would probably be the best way to kill many birds with one stone.

 

[Zydeceltico]

Again, don't use popup prompts, reference controls on form.

Forgive my inexperience - - - what does that mean operationally? How do I do that - by example?

Thanks!

 

[Zydeceltico]

Hi. The user only needs to enter the date once or select it from a calendar control. You add the times yourself in the code.

So I could add/concatenate 0:00:00 as the time to both dates to return midnight to midnight?

 

[Zydeceltico]

Right, so in query, tested and this works:

WHERE datetime Between [enter date:] & " 00:00:00" And [enter date:] & " 23:59:59"

Again, still advise not to use popup input prompts in query.

LOL - I should read ALL of both of your posts

 

[Zydeceltico]

Right, so in query, tested and this works:

WHERE datetime Between [enter date:] & " 00:00:00" And [enter date:] & " 23:59:59"

Again, still advise not to use popup input prompts in query.

So - again - how do I avoid popups?

Thanks!

 

[June7]

User enters criteria in a form control. Query references form control.

WHERE datetime Between Forms!yourformname!yourcontrolname & " 00:00:00" And Forms!yourformname!yourcontrolname & " 23:59:59"

I don't use dynamic parameterized queries. I prefer VBA to build and apply filter criteria. Review http://allenbrowne.com/ser-62.html

 

[Zydeceltico]

Review http://allenbrowne.com/ser-62.html

Thank you for that link. Very helpful info on searching/querying in general.

Question: Looking at his code and being inexperienced AND using Access 2016: would I need to modify this line at all or does Access still utilize Jet?

    Const conJetDate = "\#mm\/dd\/yyyy\#"

Also - because I'm a nerd - I love that he has the custom expression show at the bottom of the form - - - how does he make that happen? It is really instructive to see that phrasing as the query operates. I opened his form in design view and I see this line:

IIf([Form].[FilterOn].[Form].[Filter], Null)

but that doesn't seem like it would deliver whatever search criteria query phrase someone might build on the fly by making choices in the form - - - or am I wrong?

 

[theDBguy]

Thank you for that link. Very helpful info on searching/querying in general.

Question: Looking at his code and being inexperienced AND using Access 2016: would I need to modify this line at all or does Access still utilize Jet?

    Const conJetDate = "\#mm\/dd\/yyyy\#"

Hi. Don’t get mixed up. conJetDate is just a name for the “constant” being created. You can actually call it anything you want. But to answer your question, Access doesn’t use JET anymore. It uses ACE now.

 

[Zydeceltico]

Hi. Don’t get mixed up. conJetDate is just a name for the “constant” being created. You can actually call it anything you want. But to answer your question, Access doesn’t use JET anymore. It uses ACE now.

That's what I thought - - Hey - - I edited/added to the post you responded to w/ another sidebar question that I'm really interested in - -- if you get a chance.

Thanks!

Tim

 

[theDBguy]

That's what I thought - - Hey - - I edited/added to the post you responded to w/ another sidebar question that I'm really interested in - -- if you get a chance.

Thanks!

Tim

Hi Tim. If you're referring to the EAV thread, I've been following it, but you're already in good hands, so I don't want to muddy the waters with additional comments. If you're referring to something else, can you please post a link? Thanks.

 

[Zydeceltico]

Hi Tim. If you're referring to the EAV thread, I've been following it, but you're already in good hands, so I don't want to muddy the waters with additional comments. If you're referring to something else, can you please post a link? Thanks.

No -still referring to the same. I've started to design the tables and I think it will work well. I need to wait about two weeks for our manufacturing dept. to finalize drawings that are an integral portion of this branch. Afterwards, I'll post on this thread again for review and feedback.

Thanks

 

[theDBguy]

No -still referring to the same. I've started to design the tables and I think it will work well. I need to wait about two weeks for our manufacturing dept. to finalize drawings that are an integral portion of this branch. Afterwards, I'll post on this thread again for review and feedback.

Thanks

Okay, sounds good. Good luck!

 

[Zydeceltico]

Okay, sounds good. Good luck!

I just remembered what I was asking you about specifically: In a general way do you have any idea what this code means/does? It's in the footer of Allen Browne's multi-input search form.

IIf([Form].[FilterOn].[Form].[Filter], Null)

When the search form is used the search string that the user has created in the combo boxes and text fields is displayed in the footer - - but how does the code work exactly?

 

[theDBguy]

I just remembered what I was asking you about specifically: In a general way do you have any idea what this code means/does? It's in the footer of Allen Browne's multi-input search form.

IIf([Form].[FilterOn].[Form].[Filter], Null)

When the search form is used the search string that the user has created in the combo boxes and text fields is displayed in the footer - - but how does the code work exactly?

Hi Tim. Looking at the code you posted, it didn't make sense to me, so I had to download a copy of Allen's demo, and here's what the code actually looks like:

=IIf([Form].[FilterOn],[Form].[Filter],Null)

This makes better sense now, so let's try to break it down.

The IIf() function takes three (3) arguments: condition, true, and false. So, in the above case, the condition argument was [Form].[FilterOn], The True argument was [Form].[Filter], and the False argument was Null.


The condition argument must return a True or False, which will dictate which of the other two the IIf() function will return to the calling routine.


So, in the above case, we're basically saying, if a filter is applied to the form, then return the filter used. Otherwise, return Null.

Hope it makes sense...