Using date from a text box in a where condition (1 Viewer)

[bonzitre]

Hello,
So, I have a form with buttons and a text box for different reports. One "report" is a form that opens in datasheet mode to show activity logs for the day (phone calls, staff activities, etc). I want it to where they can select a date in txtDate on the form and click a button to open the datasheet and filter it based on the date in txtDate.

Private Sub cmdActivityLogDaily_Click()
Dim strCaption As String
strCaption = "Today's Activity Log"

DoCmd.OpenForm "ActivityLogQReport", acFormDS, , "CallDate= date()", , , strCaption
End Sub

This code works for filtering it to todays date. What I can't figure out is how to change date() to txtDate's value. txtDate is unbound and formatted as short date.

Bonus points if you could also tell me how to filter it based on two text boxes for start and end dates.


Also, I have a checkbox, patientdeceased, that I would like to add to the where statement possibly where patientdeceased = false. Multiple where conditions always jack me up.

 

[Gasman]

Try

DoCmd.OpenForm "ActivityLogQReport", acFormDS, , "CallDate=" & Format(Me.YourDateControl,"\#mm/dd/yyyy\#"), , , strCaption
{/code]

For the Bonus, use BETWEEN and the two controls, formatted as above.
Between DateFrom and DateTo

 

[bonzitre]

Try

DoCmd.OpenForm "ActivityLogQReport", acFormDS, , "CallDate=" & Format(Me.YourDateControl,"\#mm/dd/yyyy\#"), , , strCaption
{/code]

For the Bonus, use BETWEEN and the two controls, formatted as above.
Between DateFrom and DateTo

It isnt liking something. Says syntax error


EDIT: I'm an idiot and missed a quote. It works. Thank you.

 

[Gasman]

FWIW I have this in a module then just use that as the format string.
I used to use the first, but then changed to the latter after mentions of it here on this site.

'Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.
Public Const strcJetDate = "\#yyyy-mm-dd\#"  'Needed for dates in queries as Access expects USA but will accept ISO format.

 

[bonzitre]

Try

DoCmd.OpenForm "ActivityLogQReport", acFormDS, , "CallDate=" & Format(Me.YourDateControl,"\#mm/dd/yyyy\#"), , , strCaption
{/code]

For the Bonus, use BETWEEN and the two controls, formatted as above.
Between DateFrom and DateTo

For the between,

Private Sub cmdActivityLogDaily_Click()
Dim strCaption As String
strCaption = "Today's Activity Log"

DoCmd.OpenForm "ActivityLogQReport", acFormDS, , "CallDate=" & BETWEEN Format(Me.txtDate, "\#mm/dd/yyyy\#") and Format(Me.txtDate, "\#mm/dd/yyyy\#"), , , strCaption
End Sub

That isn't working. Am I placing it in the wrong spot?

 

[Gasman]

You have to separate the control values from the text.

DoCmd.OpenForm "ActivityLogQReport", acFormDS, , "CallDate BETWEEN " &  Format(Me.txtDate, "\#mm/dd/yyyy\#")  & " and " & Format(Me.txtDate, "\#mm/dd/yyyy\#"), , , strCaption

Tip. Put your criteria into a string variable. Then you can debug.print it to see if is correct. When it is correct, just use that in the command/function.
Always check what you actually have, not what you *think* you have.

 

[Pat Hartman]

The #'s delimit the string and should not be part of it. You want:

#02/13/2024#
NOT
"#02/13/2024#"