Build report with form controls and VBA

helmerr · 02-09-2018, 06:56 PM

Hey guys -

In my existing, I take a beginning and end date obtained from two text box controls, then use this information to build a report. The code is seen below.

I'm trying to incorporate a combobox named cboVehicle so the user can also select either a single vehicle, or all vehicles as it currently does, to build this report. The combobox values are based off the same table "tblOdometer".

However, when I run this code, I receive the error:
Run-time error '2465'
database name can't find the field '|1' referred to in your expression.

Any ideas?
Furthermore, how would I make the combobox optional, or somehow default the option for all vehicles?

Code:

DoCmd.OpenReport ReportName:="rptMileageHistory", View:=acViewPreview, _
WhereCondition:="[Odate] Between #" & Format(Me.txtBegin, "mm\/dd\/yyyy") & _
"# And #" & Format(Me.txtEnd, "mm\/dd\/yyyy") & "#"

boerbende · 02-09-2018, 10:28 PM

I am not sure if this is the reason, but I never use "view:=" and "wherecondition:=" in the report call

Try to simplify first
DoCmd.OpenReport ReportName:="rptMileageHistory", acViewPreview, "[Odate] Between #" & Format(Me.txtBegin, "mm\/dd\/yyyy") & "# And #" & Format(Me.txtEnd, "mm\/dd\/yyyy") & "#"

Or even without "between"
DoCmd.OpenReport ReportName:="rptMileageHistory", acViewPreview, "[Odate] >= #" & Format(Me.txtBegin, "mm\/dd\/yyyy") & "# And [Odate] <= #" & Format(Me.txtEnd, "mm\/dd\/yyyy") & "#"

Or to just debug
DoCmd.OpenReport ReportName:="rptMileageHistory", acViewPreview, "[Odate] >= #" & Format(Me.txtBegin, "mm\/dd\/yyyy") & "#"
etc
to see when you get the message.
Sure that [Odate] is the correct name? Not [0date]?

helmerr · 02-10-2018, 08:30 AM

Hi boerbende, I receive a compile error on all the suggested options that highlights "acViewPreview" that says Expected: named parameter.

I also forgot to include my combobox in my code. Please check this version:

Code:

DoCmd.OpenReport ReportName:="rptMileageHistory", View:=acViewPreview, _
WhereCondition:="[Odate] Between #" & Format(Me.txtBegin, "mm\/dd\/yyyy") & _
"# And #" & Format(Me.txtEnd, "mm\/dd\/yyyy") & "#" And [VehicleNum] = Me.cboVehicle

jdraw · 02-10-2018, 08:39 AM

Try this (move the double quote in WhereCondition)

Code:

DoCmd.OpenReport ReportName:="rptMileageHistory", View:=acViewPreview, _
WhereCondition:="[Odate] Between #" & Format(Me.txtBegin, "mm\/dd\/yyyy") & _
"# And #" & Format(Me.txtEnd, "mm\/dd\/yyyy") & "# And [VehicleNum] =" & Me.cboVehicle

helmerr · 02-10-2018, 08:46 AM

Hm.. Data Type mis-match. I should note, VehicleNum is short text.

jdraw · 02-10-2018, 08:52 AM

For VehicleNum is short text (you need to encapsulate with quote)
Try this one.

Code:

DoCmd.OpenReport ReportName:="rptMileageHistory", View:=acViewPreview, _
WhereCondition:="[Odate] Between #" & Format(Me.txtBegin, "mm\/dd\/yyyy") & _
"# And #" & Format(Me.txtEnd, "mm\/dd\/yyyy") & "# And [VehicleNum] ='" & Me.cboVehicle & "'"

helmerr · 02-10-2018, 08:59 AM

That works! May I ask, is there a better way to run this code if a VehicleNum is not selected from the combobox, without an IF statement? So, if a VehicleNum is not selected from the combobox, the report would include all vehicles?

boerbende · 02-10-2018, 10:05 AM

Try this

DoCmd.OpenReport ReportName:="rptMileageHistory", View:=acViewPreview, _
WhereCondition:="[Odate] Between #" & Format(Me.txtBegin, "mm\/dd\/yyyy") & _
"# And #" & Format(Me.txtEnd, "mm\/dd\/yyyy") & "# “ & iif(not(isnull(Me.cboVehicle)), "And [VehicleNum] ='" & Me.cboVehicle & "'" ,"")

helmerr · 02-10-2018, 01:08 PM

I get a syntax error on this one :-(

jdraw · 02-10-2018, 01:21 PM

Try this (untested)

Code:

DoCmd.OpenReport ReportName:="rptMileageHistory", View:=acViewPreview, _
WhereCondition:="[Odate] Between #" & Format(Me.txtBegin, "mm\/dd\/yyyy") & _
"# And #" & Format(Me.txtEnd, "mm\/dd\/yyyy") & "# And [VehicleNum] Like '" & Me.cboVehicle & "*'"

helmerr · 02-10-2018, 01:26 PM

Thanks for both of your ongoing support here..

Syntax error in string in query expression '[Odate] Between #02/10/2018# And #02/10/2018# And [VehicleNum] Like '*".

jdraw · 02-10-2018, 01:49 PM

Please post your vba.

Do you have the "*'" at the end?

Code:

DoCmd.OpenReport ReportName:="rptMileageHistory", View:=acViewPreview, _
WhereCondition:="[Odate] Between #" & Format(Me.txtBegin, "mm\/dd\/yyyy") & _
"# And #" & Format(Me.txtEnd, "mm\/dd\/yyyy") & "# And [VehicleNum] Like '" & Me.cboVehicle & "*'"

helmerr · 02-10-2018, 01:58 PM

never mind; I guess I missed copying part of your code. It works now. Thanks so much!

jdraw · 02-10-2018, 02:00 PM

10-4
Happy to help.