"Between Dates" from form to query (1 Viewer)

[jmsjazz]

Hi - I have a form which accepts a start and end date in 2 unbound textboxes. A 3rd textbox allows the user to select the status of te incident. On clicking a command button, I can open a subform which displays each matching record:
Dim strWhere As String
Dim ststatus As String
ststatus = Me.txtStatus
If ststatus = "*" Then
strWhere = "[Incident date] Between " & Format(Me.startdate, "\#mm\/dd\/yyyy\#") & " And " & Format(Me.enddate, "\#mm\/dd\/yyyy\#")
Else
strWhere = "[Incident date] Between " & Format(Me.startdate, "\#mm\/dd\/yyyy\#") & " And " & Format(Me.enddate, "\#mm\/dd\/yyyy\#") & " And " & "[Status] = '" & txtStatus & "'"
End If

DoCmd.OpenForm "qryIncident Subform", acViewNormal, , strWhere


I want to be able to include a command button on the subform to allow the user to print a report of all matching records. The query is called QryIncidentdates, and has the following criteria in the [INCIDENTDATE] field:

Between [forms]![frmPREVIEWDATES]![startdate] And [forms]![frmPREVIEWDATES]![enddate]

When I run the query, I get no matching records - could anyone help?

Thanks - John

 

[veraloopy]

In your query, where does the field INCIDENTDATE come into it as you are searching on the startdate and enddate fields?

 

[Brianwarnock]

I notice that incident date has a space in the VBA code but not when you talk about the query, are they different fields?

Brian

 

[weeblebiker]

my limited understanding is any query associated with a form or sub forms runs before the form opens.
so you open the form, no criteria is available, and the query return zero results.

so you need to run the query again after the criteria is entered or there is a prebuilt macro called "requery"
the easiest way I do this is:
select "properties" for your last criteria field [enddate], click on the event tab on which ever event you choose click the little dot dot dot box, select macro builder and select "requery" in the action column
I use the "lost focus" event.

or not, I'm not very good at this stuff

btw this works even when putting the unbound query criteria fields on the form generated by the query, like dates, and the dates stay on the form after the query is run