User Input Query using Data/Time

[Locopete99]

Hi,

I have a table that records user queries for other departments. This has a Date/Time field in for "Date Raised".

We changed it from a simple date to Date / Time so we could see what hours of the day were more productive.....We did not envisage being in a lock down with users working from home.

I now have a team leader that wants to see how many records his users are creating daily.

How can I do this using a select query and having a user input as part of the criteria?

I have tried the below, so the user would enter a date, I.e 25/03/20 and then it would add 09:00:00 to it.

Or is there a simpler way to query Date / Times using just the date?

>[Please enter date:] & #09:00:00#

 

[MajP]

Or is there a simpler way to query Date / Times using just the date?

It is not clear what you want to query. If you want all values for that date then convert the field in a query to just the date part

DateOnly: Int(Nz([dateTimeField]))

now you can just enter a date on that calculated field. Int removes the time portion which is a fraction.

 

[vba_php]

Locopete, you say this:

I now have a team leader that wants to see how many records his users are creating daily.

How can I do this using a select query and having a user input as part of the criteria?

the obvious answer to that question is:

When a user creates a record, you must have a date/time field that records the time of record creation. For instance, a control source in a textbox like this:

=NOW()

We assume you already have one?

You also say this:
I have tried the below, so the user would enter a date, I.e 25/03/20 and then it would add 09:00:00 to it.

Or is there a simpler way to query Date / Times using just the date?

>[Please enter date:] & #09:00:00#

if you already have a date field that has values in it like this:

03/24/2020 09:00:00AM

and you want to query the records out by having a user enter a DATE ONLY, just give it a try. Did you? If I remember right, that should return what you want. But regardless, the statements of yours that I've quoted above still kind of contradict one another. Furthermore, date/time fields in access are not sophisticated in nature outside of how they are formatted (eg. - LONG DATE SHORT DATE, SHORT TIME, etc....). Not like mysql or sql server.

 

[MajP]

@vba_php
I may be wrong but I assume the OP originally had a date only field and they provided an input date to query. It therefore did something like
workDate = #1/1/2020#
Now that the field accepts time it returns no records since I doubt anyone enters a date time as 1/1/2020 00:00:00 am
So you need to get rid of the time portion in the query.
Int(Nz([WorkDate])) = #1/1/2020#
will return all records for that date even though they have a time component

 

[CJ_London]

Or is there a simpler way to query Date / Times using just the date?

use the datevalue function which returns just the date part of a datetime field

datevalue(workdate)=#01/01/2020#

 

[vba_php]

Majp,

I'm just offering something different. They will know what to do regardless of what they read

 

[MajP]

and you want to query the records out by having a user enter a DATE ONLY, just give it a try. Did you?

@vba_php
It may be different, but it is also wrong. I thought I explained why if you query a field with date and time parts but provide only a date part, no records are returned. Unless I misunderstood and you are asking them to give it a try to prove it fails which is strange advice.

 

[vba_php]

you are asking them to give it a try to prove it fails which is strange advice.

yes, that's what I was telling them to do. that's the best thing one can do before posting a question to a forum like this. fruthermore, you are correct in your advice. nice work Mr. Wheelhouse. =)