Build report with form controls and VBA (1 Viewer)

helmerr

Registered User
Joined
Nov 27, 2013
Messages
140
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

Ben
Joined
Feb 10, 2013
Messages
329
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

Registered User
Joined
Nov 27, 2013
Messages
140
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

Super Moderator
Staff member
Joined
Jan 23, 2006
Messages
12,426
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

Registered User
Joined
Nov 27, 2013
Messages
140
Hm.. Data Type mis-match. I should note, VehicleNum is short text.
 

jdraw

Super Moderator
Staff member
Joined
Jan 23, 2006
Messages
12,426
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] =[COLOR="Red"]'[/COLOR]" & Me.cboVehicle & "[COLOR="Red"]'[/COLOR]"
 

helmerr

Registered User
Joined
Nov 27, 2013
Messages
140
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

Ben
Joined
Feb 10, 2013
Messages
329
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 & "'" ,"")
 

jdraw

Super Moderator
Staff member
Joined
Jan 23, 2006
Messages
12,426
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 [COLOR="DarkOrange"]'[/COLOR]" & Me.cboVehicle & "[COLOR="DarkOrange"]*'[/COLOR]"
 

helmerr

Registered User
Joined
Nov 27, 2013
Messages
140
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

Super Moderator
Staff member
Joined
Jan 23, 2006
Messages
12,426
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

Registered User
Joined
Nov 27, 2013
Messages
140
never mind; I guess I missed copying part of your code. It works now. Thanks so much!
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top