Main form based on a query does not response to the txtStart- and

[upandaway]

Main form based on a query does not response to the txtStart- and txtEndDate
of the calendar (from Allen Browne’s tip for Access users).

I need help again:

I have a Form “frmMainOffice†with two included Sub Forms “sbfOffice_A and
sbfOf-fice _Bâ€
By entering a start date and an end date, the form including the sub forms
should show the Days between Start of Process and End of Process in a given
date frame.
This works perfect, also on the sub forms, thanks to a tip from Ken Snell.

To make the date range entry more sophisticated I designed a form based on
an ex-ample of a popup calendar out of Allen Browne’s tip for Access users
(frmWhatDate).
Whereby the given date range between the start and end date of a process
should be established via the WHERE clause (strWhere) in the [Event
Procedure] on the “On Click†of the calendar’s OK-Button, activating the
StartProcess field in the qry-MainOffice, like it does, in case I enter a
WHERE clause Between [StartDate] And [EndDate] in the criteria of the
StartProcess field in the qryMainOffice.
However, this does not work; I get a Run-time error 3075 and when debugging
the yellow bar in the Private Sub cmdOK_Click() is on:
DoCmd.OpenForm strForm, acNormal, , strWhere
The Immediate window tells me strWhere has been according to the calendar’s
entry.
Here are the SQL-Statements of the Queries and the code Pirvate Sub
cmdOK_Click() of the frmWhatDate

Queries:
qryMainOffice:
SELECT [tblService].[StartProcess], [tblService].EndProcess,
(Date-Diff("d",[StartProcess],[EndProcess])+1) AS DaysEndProcess,
(Date-Diff("d",[EndProcess],Date())) AS DaysEndProcessTillToday,
(Date-Diff("d",[StartProcess],Date())+1) AS DaysStartProcessTillToday,
[DaysEndProcess-TillToday]+[DaysEndProcess] AS Substract
FROM [tblService]

qryOffice_A:
SELECT [tblService].[StartProcess], [tblService].EndProcess,
[tblService].Office, (DateDiff("d",[StartProcess],[EndProcess])+1) AS
DaysEndProcess, (Date-Diff("d",[EndProcess],Date())) AS
DaysEndProcessTillToday, (Date-Diff("d",[StartProcess],Date())+1) AS
DaysStartProcessTillToday, [DaysEndProcess-TillToday]+[DaysEndProcess]
AS Substract
FROM [tblService]
WHERE ((([tblService].[StartProcess]) Between
[Forms]![frmMainOffice]![StartDate] And [Forms]![frmMainOffice]![EndDate])
And [tblService].Office) Like "BBG"));

qryOffice_B:
SELECT [tblService].[StartProcess], [tblService].EndProcess,
[tblService].Office, (DateDiff("d",[StartProcess],[EndProcess])+1) AS
DaysEndProcess, (Date-Diff("d",[EndProcess],Date())) AS
DaysEndProcessTillToday, (Date-Diff("d",[StartProcess],Date())+1) AS
DaysStartProcessTillToday, [DaysEndProcess-TillToday]+[DaysEndProcess]
AS Substract
FROM [tblService]
WHERE ((([tblService].[StartProcess]) Between
[Forms]![frmMainOffice]![StartDate] And [Forms]![frmMainOffice]![EndDate])
And [tblService].Office) Like "SFT"));


Forms: frmMainOffice (from qryMainOffice)

Subforms in frmMainOffice:
sbfOffice_B (from qryOffice_A)
sbfOffice_B (from qryOffice_B)


Here is Allen’s code for the Ok button's Click procedure (thank you Allen).

1. Dim strForm As String 'Name of form to open.
2. Dim strField As String 'Name of your date field.
3. Dim strWhere As String 'Where condition for OpenForm.
4. Const conDateFormat = "\#mm\/dd\/yyyy\#"
5.
6. strForm = "rptMainOffice"
7. strField = "StartProcess"
8.
9. If IsNull(Me.txtStartDate) Then
10. If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
11. strWhere = strField & " < " & Format(Me.txtEndDate,
conDateFormat)
12. End If
13. Else
14. If IsNull(Me.txtEndDate) Then 'Start date, but no End.
15. strWhere = strField & " > " & Format(Me.txtStartDate,
conDateFormat)
16. Else 'Both start and end dates.
17. strWhere = strField & " Between " & Format(Me.txtStartDate,
conDateFormat) _
18. & " And " & Format(Me.txtEndDate, conDateFormat)
19. End If
20. End If
21.
22. ' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenForm strForm, acNormal, , strWhere

Any help is greatly appreciated.
Upandaway.