yes, im filtering a query which collecting data from main table.
date is column , which some times no need to filter and sometimes to be filter in a range depending upon a checkbox selection.
i already using below
DoCmd.OpenReport "rpt_pend_issue", acViewPreview, , "[pending]=true and [trandate]= between '" & fromdate & "' and '" & todate & "'", acDialog
yes, im filtering a query which collecting data from main table.
date is column , which some times no need to filter and sometimes to be filter in a range depending upon a checkbox selection.
i already using below
DoCmd.OpenReport "rpt_pend_issue", acViewPreview, , "[pending]=true and [trandate]= between '" & fromdate & "' and '" & todate & "'", acDialog
You will need # enclosing your dates.?
I'd construct a string strWhere so you can debug.print it to check the syntax then use that as the Where parameter in your OpenReport command.
Absolutely, there are MANY ways to achieve that result. The problem is, TOO many ways. I'm going to walk through what I think you are asking for and explain as I go.
You appear to be opening a report. From your question, you are doing so from a form that has a checkbox on it. It is not 100% clear to me what that checkbox is named but if it is pending then you are using it incorrectly in the filter. I'll show you the simplest and most straight-forward way.
The simplest way is to choose to design your report without filtration. That is, you presume that your checkbox is TRUE when you build the report.
Then in your code that launches the report,
Code:
IF Me.pending Then
DoCmd.OpenReport "rpt_pend_issue", acViewPreview, , , acDialog
Else
DoCmd.OpenReport "rpt_pend_issue", acViewPreview, , "[trandate]= between '" & fromdate & "' and '" & todate & "'", acDialog
End If
That is, if the checkbox is true then don't apply the filter. If the checkbox is false, apply the filter. Note, however, that I am NOT addressing the date format in the sample code here. I'll explain that separately.
When dealing with the dates as VBA variables you can compare dates to dates easily enough. SQL can also directly compare dates that are fields in a recordset. However, if your range dates are controls on a form, then they are inherently in a text format and SQL will want them in a date format. Date STRINGS are usually bracketed by the # sign, which alerts SQL that the string bracketed that way needs conversion before use. For one possible example,
Code:
DoCmd.OpenReport "rpt_pend_issue", acViewPreview, , "[trandate]= between '#" & fromdate & "#' and '#" & todate & "#'", acDialog
Note that if you need more than this, you have to ask a bit more explicitly where you need this to go. I took a simplistic view.