Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-09-2018, 06:56 PM   #1
helmerr
Newly Registered User
 
Join Date: Nov 2013
Posts: 140
Thanks: 33
Thanked 0 Times in 0 Posts
helmerr is on a distinguished road
Build report with form controls and VBA

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") & "#"

helmerr is offline   Reply With Quote
Old 02-09-2018, 10:28 PM   #2
boerbende
Ben
 
Join Date: Feb 2013
Location: Norway
Posts: 327
Thanks: 38
Thanked 57 Times in 52 Posts
boerbende is on a distinguished road
Re: Build report with form controls and VBA

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]?
boerbende is offline   Reply With Quote
Old 02-10-2018, 08:30 AM   #3
helmerr
Newly Registered User
 
Join Date: Nov 2013
Posts: 140
Thanks: 33
Thanked 0 Times in 0 Posts
helmerr is on a distinguished road
Re: Build report with form controls and VBA

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

helmerr is offline   Reply With Quote
Old 02-10-2018, 08:39 AM   #4
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,283
Thanks: 96
Thanked 2,031 Times in 1,978 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Build report with form controls and VBA

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
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 02-10-2018, 08:46 AM   #5
helmerr
Newly Registered User
 
Join Date: Nov 2013
Posts: 140
Thanks: 33
Thanked 0 Times in 0 Posts
helmerr is on a distinguished road
Re: Build report with form controls and VBA

Hm.. Data Type mis-match. I should note, VehicleNum is short text.
helmerr is offline   Reply With Quote
Old 02-10-2018, 08:52 AM   #6
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,283
Thanks: 96
Thanked 2,031 Times in 1,978 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Build report with form controls and VBA

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 & "'"
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 02-10-2018, 08:59 AM   #7
helmerr
Newly Registered User
 
Join Date: Nov 2013
Posts: 140
Thanks: 33
Thanked 0 Times in 0 Posts
helmerr is on a distinguished road
Re: Build report with form controls and VBA

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?

helmerr is offline   Reply With Quote
Old 02-10-2018, 10:05 AM   #8
boerbende
Ben
 
Join Date: Feb 2013
Location: Norway
Posts: 327
Thanks: 38
Thanked 57 Times in 52 Posts
boerbende is on a distinguished road
Re: Build report with form controls and VBA

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 & "'" ,"")
boerbende is offline   Reply With Quote
Old 02-10-2018, 01:08 PM   #9
helmerr
Newly Registered User
 
Join Date: Nov 2013
Posts: 140
Thanks: 33
Thanked 0 Times in 0 Posts
helmerr is on a distinguished road
Re: Build report with form controls and VBA

I get a syntax error on this one :-(
helmerr is offline   Reply With Quote
Old 02-10-2018, 01:21 PM   #10
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,283
Thanks: 96
Thanked 2,031 Times in 1,978 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Build report with form controls and VBA

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 & "*'"
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 02-10-2018, 01:26 PM   #11
helmerr
Newly Registered User
 
Join Date: Nov 2013
Posts: 140
Thanks: 33
Thanked 0 Times in 0 Posts
helmerr is on a distinguished road
Re: Build report with form controls and VBA

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 '*".
helmerr is offline   Reply With Quote
Old 02-10-2018, 01:49 PM   #12
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,283
Thanks: 96
Thanked 2,031 Times in 1,978 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Build report with form controls and VBA

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 & "*'"
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
The Following User Says Thank You to jdraw For This Useful Post:
helmerr (02-10-2018)
Old 02-10-2018, 01:58 PM   #13
helmerr
Newly Registered User
 
Join Date: Nov 2013
Posts: 140
Thanks: 33
Thanked 0 Times in 0 Posts
helmerr is on a distinguished road
Re: Build report with form controls and VBA

never mind; I guess I missed copying part of your code. It works now. Thanks so much!
helmerr is offline   Reply With Quote
Old 02-10-2018, 02:00 PM   #14
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,283
Thanks: 96
Thanked 2,031 Times in 1,978 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Build report with form controls and VBA

10-4
Happy to help.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Reply

Tags
combobox , error , report , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Check value of three text box controls on a form then build text string with results kpaull Forms 3 01-10-2016 04:48 PM
Parameter Form to build report - Requery if Null Nz MadAtMicrosoft Forms 7 10-08-2010 01:34 PM
Build reference name(s) for multiple controls/buttons vdc1 Forms 5 07-06-2008 12:53 PM
Using a form to build a complicated query and pass to a report pagewrite Reports 1 06-26-2008 10:33 AM
Copying Controls from FORM to a REPORT? NewfieSarah General 10 01-12-2006 12:37 PM




All times are GMT -8. The time now is 10:44 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World