Build report with form controls and VBA (1 Viewer)

foshizzle

Registered User.
Local time
Today, 11:22
Joined
Nov 27, 2013
Messages
277
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
Local time
Today, 16:22
Joined
Feb 10, 2013
Messages
339
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]?
 

foshizzle

Registered User.
Local time
Today, 11:22
Joined
Nov 27, 2013
Messages
277
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
Local time
Today, 11:22
Joined
Jan 23, 2006
Messages
15,364
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
 

foshizzle

Registered User.
Local time
Today, 11:22
Joined
Nov 27, 2013
Messages
277
Hm.. Data Type mis-match. I should note, VehicleNum is short text.
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:22
Joined
Jan 23, 2006
Messages
15,364
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]"
 

foshizzle

Registered User.
Local time
Today, 11:22
Joined
Nov 27, 2013
Messages
277
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
Local time
Today, 16:22
Joined
Feb 10, 2013
Messages
339
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
Local time
Today, 11:22
Joined
Jan 23, 2006
Messages
15,364
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]"
 

foshizzle

Registered User.
Local time
Today, 11:22
Joined
Nov 27, 2013
Messages
277
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
Local time
Today, 11:22
Joined
Jan 23, 2006
Messages
15,364
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 & "*'"
 

foshizzle

Registered User.
Local time
Today, 11:22
Joined
Nov 27, 2013
Messages
277
never mind; I guess I missed copying part of your code. It works now. Thanks so much!
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:22
Joined
Jan 23, 2006
Messages
15,364
10-4
Happy to help.
 

Users who are viewing this thread

Top Bottom