Between date and time

[Tigerweld]

I'm having trouble creating a 'between date and time' query. The times will always be the same, but the dates will vary so I will need to be able to input the start and end date, preferably with a date picker.




Example:

Start Jan 12, 2016 5:00:01PM
End Jan 15, 2016 10:00:00 AM

[June7]

To use date picker will have to build a form with controls for the date inputs. Then query can concatenate:

SELECT * FROM table WHERE [datefield] BETWEEN Forms!formname!tbxStart & " 5:00:01 PM" AND Forms!formname!tbxEnd & " 10:00:00 AM";

[Tigerweld]

I get This expression is typed incorrectly, or it is too complex to be evaluated.

WHERE (((Remosit.Date) Between [Forms]![Holidays]![txtStartDate] & "5:00:01 PM" And [Forms]![Holidays]![txtEndDate] & "10:00:00 AM"))

[Tigerweld]

oh wait, now I know why that is wrong, there isn't a time stamp in that field in the table, but in another field called "Time Processed" How would I add that to the query?

[June7]

Look at my example again. Concatenation of time needs a space after quote mark in front of the time part.

So date and time parts are in separate fields? Try:

WHERE [Date] + [Time Processed] BETWEEN [Forms]![Holidays]![txtStartDate] & " 5:00:01 PM" And [Forms]![Holidays]![txtEndDate] & " 10:00:00 AM"


Date is a reserved word. Should not use reserved words as names. Also, advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[Tigerweld]

I changed the name of the date field, but it still isn't working. When I run the query it's pulling in times before 5:00pm on the start date.

WHERE ((([TransDate]+[Time Processed]) Between [Forms]![Holidays]![txtStartDate] & " 5:00:01 PM" And [Forms]![Holidays]![txtEndDate] & " 10:00:00 AM"))

[June7]

Don't know why.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Tigerweld]

The query in question is the qryTime_Holidays

[June7]

Still shows Date as field name in table.

Should first label on Holidays form say "First Day Worked"?

The query is not recognizing the expressions as a date/time value.

Expr1: CDate([Date]+[Time Processed])

Between CDate([Forms]![Holidays]![txtStartDate] & " 5:00:01 PM") And CDate([Forms]![Holidays]![txtEndDate] & " 10:00:00 AM")

[Tigerweld]

That worked like a charm. I understand the query now. thanks!