Passing a query parameter via code

[Jean-Marie]

I have in my access application a form to build report criterion. Among other
criterion is the reporting period which is set thru a txtStartDate and
txtEndDate text boxes. I want my report to return all the records if no date
is entered and to filter records only for the period if start Date and end
date are entered.
I am thinking of passing a parameter like "Between
[Forms]![MyForm]![txtStartDate] AND [Forms]![MyForm]![txtEndDate]" or no
parameter at all if no date was entered. Is there any way to do so via code?
I would use the button that previews the report.
I am running Access 2000 and WinXP.
Many thanks for your help

 

[Sharkbyte]

If I understand you correctly, you have a couple of options.

Probably the easiest would be to create 2 queries. 1 which expects the data
parameters, and one that doesn't. Then do an IF statement similar to:

IF isnull(txtStartDate) or txtStartDate = "" THEN
docmd.openquery "query1"
ELSE
docmd.openquery "query2"
END IF

You could also getting a little more complex and add functionality for
dealing with the possibility of someone only putting in one date; Start or
End.

Another option would be to do IIF statements, where if no Start Date exists
you submit 01/01/1900 and if no End Date exists you submit Date().

HTH

Sharkbyte

 

[Wolfgang Kais]

Hello Jean-Marie.

I have in my access application a form to build report criterion.
Among other criterion is the reporting period which is set thru
a txtStartDate and txtEndDate text boxes. I want my report to
return all the records if no date is entered and to filter records
only for the period if start Date and end date are entered.
I am thinking of passing a parameter like
"Between [Forms]![MyForm]![txtStartDate] AND " & _
"[Forms]![MyForm]![txtEndDate]"
or no parameter at all if no date was entered. Is there any way
to do so via code? I would use the button that previews the report.

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "ReportName"
If IsDate(txtStartDate) And IsDate(txtEndDate) Then
stLinkCriteria = BuildCriteria("[DateField]", dbDate, _
"between " & txtStartDate & " and " & txtEndDate)
End If
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

 

[Jean-Marie]

Sharkbyte,
Looks like my post of yersterday did not go through. Sorry about that. It
was to thank you for your response. I gave it a try and it works fine.
I am using the second option because it requires fewer queries as I have
many reports to outpout via the same form.
Thanks again
--
Jean-Marie

If I understand you correctly, you have a couple of options.

Probably the easiest would be to create 2 queries. 1 which expects the data
parameters, and one that doesn't. Then do an IF statement similar to:

IF isnull(txtStartDate) or txtStartDate = "" THEN
docmd.openquery "query1"
ELSE
docmd.openquery "query2"
END IF

You could also getting a little more complex and add functionality for
dealing with the possibility of someone only putting in one date; Start or
End.

Another option would be to do IIF statements, where if no Start Date exists
you submit 01/01/1900 and if no End Date exists you submit Date().

HTH

Sharkbyte

I have in my access application a form to build report criterion. Among other
criterion is the reporting period which is set thru a txtStartDate and
txtEndDate text boxes. I want my report to return all the records if no date
is entered and to filter records only for the period if start Date and end
date are entered.
I am thinking of passing a parameter like "Between
[Forms]![MyForm]![txtStartDate] AND [Forms]![MyForm]![txtEndDate]" or no
parameter at all if no date was entered. Is there any way to do so via code?
I would use the button that previews the report.
I am running Access 2000 and WinXP.
Many thanks for your help

 

[Jean-Marie]

Thanks Wolfgang
It works great. And it deals well with empty Start date and End date boxes.
Thank you very much
--
Jean-Marie

Hello Jean-Marie.

I have in my access application a form to build report criterion.
Among other criterion is the reporting period which is set thru
a txtStartDate and txtEndDate text boxes. I want my report to
return all the records if no date is entered and to filter records
only for the period if start Date and end date are entered.
I am thinking of passing a parameter like
"Between [Forms]![MyForm]![txtStartDate] AND " & _
"[Forms]![MyForm]![txtEndDate]"
or no parameter at all if no date was entered. Is there any way
to do so via code? I would use the button that previews the report.

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "ReportName"
If IsDate(txtStartDate) And IsDate(txtEndDate) Then
stLinkCriteria = BuildCriteria("[DateField]", dbDate, _
"between " & txtStartDate & " and " & txtEndDate)
End If
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria