By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,844 Members | 1,918 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,844 IT Pros & Developers. It's quick & easy.

Filter dates in VB

100+
P: 147
I have a report based on a query called qryMonthlyReport. I have a form that I use to launch the report, on the form I have two date fields I used to filter the query. I want to use the query w/o the filter with other reports so I would like to add the date filter in VB when I click my Run Reports button. I have created the following but the date filter will not work. Any ideas? Thanks in advance.
Dan


Private Sub btnRun_Click()
On Error GoTo Err_btnRun_Click

Dim stDocName As String

stDocName = "rptMonthly"
DoCmd.OpenReport stDocName, acPreview,,qryMonthlyReport!DateOpened = Between [Forms]![frmMonthlyReport]![Date1] And [Forms]![frmMonthlyReport]![Date2]

Exit_btnRun_Click:
Exit Sub

Err_btnRun_Click:
MsgBox Err.Description
Resume Exit_btnRun_Click

End Sub
Dec 1 '07 #1
Share this Question
Share on Google+
6 Replies


puppydogbuddy
Expert 100+
P: 1,923
I have a report based on a query called qryMonthlyReport. I have a form that I use to launch the report, on the form I have two date fields I used to filter the query. I want to use the query w/o the filter with other reports so I would like to add the date filter in VB when I click my Run Reports button. I have created the following but the date filter will not work. Any ideas? Thanks in advance.
Dan


Private Sub btnRun_Click()
On Error GoTo Err_btnRun_Click

Dim stDocName As String

stDocName = "rptMonthly"
DoCmd.OpenReport stDocName, acPreview,,qryMonthlyReport!DateOpened = Between [Forms]![frmMonthlyReport]![Date1] And [Forms]![frmMonthlyReport]![Date2]

Exit_btnRun_Click:
Exit Sub

Err_btnRun_Click:
MsgBox Err.Description
Resume Exit_btnRun_Click

End Sub
try this:

DoCmd.OpenReport stDocName, acPreview, , "qryMonthlyReport!DateOpened Between " & DateValue([Forms]![frmMonthlyReport]![Date1]) And DateValue([Forms]![frmMonthlyReport]![Date2])
Dec 2 '07 #2

100+
P: 147
try this:

DoCmd.OpenReport stDocName, acPreview, , "qryMonthlyReport!DateOpened Between " & DateValue([Forms]![frmMonthlyReport]![Date1]) And DateValue([Forms]![frmMonthlyReport]![Date2])

No Luck, I get the following error:

Compiled error: Expected: end of statement
Dec 2 '07 #3

100+
P: 147
Disregard my last I had a typo. But now when I try to run the report by clicking the above button I get the error:

Type mismatch
Dec 2 '07 #4

100+
P: 147
I forgot to add that my query the report is based on is based on another query. It it this second query that I filter the date on.

Query that the report is based on is:

SELECT tblOffenses.Offenses, Count(qryMonthlyIntakeSUB.Offenses) AS CountOfOffenses
FROM tblOffenses LEFT JOIN qryMonthlyIntakeSUB ON tblOffenses.Offenses = qryMonthlyIntakeSUB.Offenses
GROUP BY tblOffenses.Offenses;

The query that the above query is based on and that I want to filter with the date is:

SELECT tblOffenses.Offenses, tblCaseManagement.DateOpened, tblCaseManagement.ClearanceCode
FROM tblOffenses LEFT JOIN tblCaseManagement ON tblOffenses.Offenses = tblCaseManagement.Offense;
Dec 2 '07 #5

100+
P: 147
I forgot to add that my query the report is based on is based on another query. It it this second query that I filter the date on.

Query that the report is based on is:

SELECT tblOffenses.Offenses, Count(qryMonthlyIntakeSUB.Offenses) AS CountOfOffenses
FROM tblOffenses LEFT JOIN qryMonthlyIntakeSUB ON tblOffenses.Offenses = qryMonthlyIntakeSUB.Offenses
GROUP BY tblOffenses.Offenses;

The query that the above query is based on and that I want to filter with the date is:

SELECT tblOffenses.Offenses, tblCaseManagement.DateOpened, tblCaseManagement.ClearanceCode
FROM tblOffenses LEFT JOIN tblCaseManagement ON tblOffenses.Offenses = tblCaseManagement.Offense;

Opps I made a typo in my example the two quries are:

SELECT tblOffenses.Offenses, Count(qryMonthlyIntakeSUB.Offenses) AS CountOfOffenses
FROM tblOffenses LEFT JOIN qryMonthlyIntakeSUB ON tblOffenses.Offenses = qryMonthlyIntakeSUB.Offenses
GROUP BY tblOffenses.Offenses;

The query that the above query is based on and that I want to filter with the date is:

SELECT tblOffenses.Offenses, tblCaseManagement.DateOpened
FROM tblOffenses LEFT JOIN tblCaseManagement ON tblOffenses.Offenses = tblCaseManagement.Offense;
Dec 2 '07 #6

puppydogbuddy
Expert 100+
P: 1,923
I think I had a syntax error in the expression I gave you before: Also, if your date fields are formatted as dates you don't need to use the DateValue function.

try it this way:
DoCmd.OpenReport stDocName, acPreview, , "qryMonthlyReport!DateOpened Between " & [Forms]![frmMonthlyReport]![Date1] & " And " & [Forms]![frmMonthlyReport]![Date2]
Dec 2 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.