DoCmd Open report with multiple Where clauses

Topic

New Reply

I have a form that I would like users to make a selection on, which filters a report, but I can’t work out the VBA

The first box is a drop down called SchoolName which allows them to select the school

The next two boxes are the dates they want to filter the report for TxtStartdate and txtEnd date,

when the users has selected dates the report will filter the form to show all training courses attended by that school between the dates that have been selected.

I can get it to filter on school or between the dates but not both?

P.s I’m new to VBA

Help?

Thanks

Kelly

Reply | Quote

Replies

Kelly,

You want something like this:

Code:

   Dim zWhere as String

   zWhere = "[SchoolName] = " & Form!YourFormName!SchoolName & " And  [TxtStartDate] >= " & _
                     Form!YourFormName!TxtStartDate & " And [TxtEndDate] <= " & _
                     Form!YourFormName!TxtEndDate

   DoCmd.OpenReport "Your Report Name Here", acViewPreview, zWhere

Note: Items in [ ] are your db field names.
HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

RG’s zWhere may need to be changed, especially if SchoolName is not a number field, and depending on where you live you may have format the dates as well, viz:
zWhere = “[SchoolName] = ” & Chr(34) & Form!YourFormName!SchoolName & Chr(34) & ” And [TxtStartDate] >= ” & _
“#” & Format(Form!YourFormName!TxtStartDate,”mm/dd/yyyy”) & “# And [TxtEndDate] <= " & _
"#" & Format(Form!YourFormName!TxtEndDate,"mm/dd/yyyy") & "#"

I think RG has made a mistake in the date check, I think he meant to show:
" AND RecordDate Between #" & Format(Form!YourFormName!TxtStartDate,"mm/dd/yyyy") & "# And #" & Format(Form!YourFormName!TxtEndDate,"mm/dd/yyyy") & "#"
where recordDate is the name of the date field in the reports source code.

so zWhere should be:
zWhere = "[SchoolName] = " & Chr(34) & Form!YourFormName!SchoolName & Chr(34) & " AND RecordDate Between #" & Format(Form!YourFormName!TxtStartDate,"mm/dd/yyyy") & "# And #" & Format(Form!YourFormName!TxtEndDate,"mm/dd/yyyy") & "#"

Not treading on toes RG.

Reply | Quote

Patt,

Ouch! 35623-ROTFLOL

Not a problem. Thanks for correcting the code. :clapping:
:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi Guys

Thanks for your replies but neither code is working. I am based in the UK if that makes a difference. It tells me that it is an invalid outside procedure?

maybe i didn’t explain it correctly, i have a list of training courses that have been attended and there is a db field called ‘course date’ there is also a DB field called school.

on the form there is a field called schoolname to select a school and txtstartdate and txtenddate so they can pick any courses dates that have been attended during that period?

the form is called ‘selectSchoolforTrainingAttReport and the report is called ‘attended training courses’

thank you

kelly

Reply | Quote

I would suggest something like this on an open report button

This allows for ALL or NONE of the filter components to be entered

It is a bit more long winded, but more flexible

Code:

Dim strFilter as string, strThisPart as String
Dim conSPM = """"

'Build Filter Components from data on the form
'I could have done it in a shorter way, but this is easier to debug
'It is always worth considering an err trap as well

If Not IsNull(schoolname) Then
	strThisPart = " [School] = " & conSPM & schoolname & conSPM & " "
	'Build Filter either this is the first bit or we need to add
	If strFilter = "" Then
		strFilter = strThisPart
	Else
		strFilter = strFilter & " AND " & strThisPart
	End If
End If

If Not IsNull(txtstartdate) Then
	strThisPart = " [course date] >= #" & Format(txtstartdate,"mm/dd/yyyy") & "# "
	If strFilter = "" Then
		strFilter = strThisPart
	Else
		strFilter = strFilter & " AND " & strThisPart
	End If
End If

If Not IsNull(txtenddate) Then
	strThisPart = " [course date] <= #" & Format(txtenddate,"mm/dd/yyyy") & "# "
	If strFilter = "" Then
		strFilter = strThisPart
	Else
		strFilter = strFilter & " AND " & strThisPart
	End If
End If

'Now open the Report and pass it the filter
If strFilter = "" Then
       DoCmd.OpenReport ReportName:="attended training courses"
Else
       DoCmd.OpenReport ReportName:="attended training courses", WhereCondition:=strFilter 
End iF

This could have been done more quickly, but since you are new to VBA,
have a look at it and see if you can refine it.

Reply | Quote

By the way Kelly, would you post here what you used for a WHERE clause, and let us see what you tried.

Reply | Quote