Choosing form input on query

[Guest]

Is there a way to choose which form to pick as the input on a query.
When I am running a query that requires a date range, I build a form with
the dates and reference the form in the criteria. If there were 2 forms that
may provide input to a query, I thought it would look like

Between [Forms]![frmSelectRevenueDates]![txtBeginningDate] And
[Forms]![frmSelectRevenueDates]![txtEndingDate] Or Between
[Forms]![frmSelectRevenueDatesforSummaryRpt]![txtBeginningDate] And
[Forms]![frmSelectRevenueDatesforSummaryRpt]![txtEndingDate]

It brings up a dialog box asking for input from the form not open.

Hope that makes sense.

 

[Guest]

That will not work, because the entire statment is evaluated before
execution. A way around that would be to write a function to determine which
form is open, evaluate the dates, and return a true of false value. Then,
change the criteria in your query to use the function. The function will
have to be a Public Function in a standard module. It might go something
like this:

Public Function IsGoodRevenueDate(dtmSomeDate) As Boolean
Dim frm As Form

If Currentproject.AllForms("frmSelectRevenueDates").IsLoaded Then
Set frm = "frmSelectRevenueDates"
ElseIf Currentproject.AllForms( _
"frmSelectRevenueDatesforSummaryRpt").IsLoaded Then
Set frm = "frmSelectRevenueDatesforSummaryRpt"
End If

With frm
If dtmSomeDate >= .txtBeginningDate And _
dtmSomeDate <= .txtEndingDate Then
IsGoodRevenueDate = True
Else
IsGoodRevenueDate = False
End If
End With

Set frm = Nothing

End Function

Then Change your query criteria to:

IsGoodRevenueDate ([DateFieldName])

DateFieldName is the name of the field you want to evaluate.

 

[Guest]

Thanks Klatuu

I'll need some time to absorb that.
--
Thanks for any assistance

That will not work, because the entire statment is evaluated before
execution. A way around that would be to write a function to determine which
form is open, evaluate the dates, and return a true of false value. Then,
change the criteria in your query to use the function. The function will
have to be a Public Function in a standard module. It might go something
like this:

Public Function IsGoodRevenueDate(dtmSomeDate) As Boolean
Dim frm As Form

If Currentproject.AllForms("frmSelectRevenueDates").IsLoaded Then
Set frm = "frmSelectRevenueDates"
ElseIf Currentproject.AllForms( _
"frmSelectRevenueDatesforSummaryRpt").IsLoaded Then
Set frm = "frmSelectRevenueDatesforSummaryRpt"
End If

With frm
If dtmSomeDate >= .txtBeginningDate And _
dtmSomeDate <= .txtEndingDate Then
IsGoodRevenueDate = True
Else
IsGoodRevenueDate = False
End If
End With

Set frm = Nothing

End Function

Then Change your query criteria to:

IsGoodRevenueDate ([DateFieldName])

DateFieldName is the name of the field you want to evaluate.

Is there a way to choose which form to pick as the input on a query.
When I am running a query that requires a date range, I build a form with
the dates and reference the form in the criteria. If there were 2 forms that
may provide input to a query, I thought it would look like

Between [Forms]![frmSelectRevenueDates]![txtBeginningDate] And
[Forms]![frmSelectRevenueDates]![txtEndingDate] Or Between
[Forms]![frmSelectRevenueDatesforSummaryRpt]![txtBeginningDate] And
[Forms]![frmSelectRevenueDatesforSummaryRpt]![txtEndingDate]

It brings up a dialog box asking for input from the form not open.

Hope that makes sense.