Help opening a report to display a group of records (1 Viewer)

Punice

Registered User.
Local time
Today, 19:30
Joined
May 10, 2010
Messages
135
Access 2007: I have a form with a combobox that is configured as value list. I want to pick a trip number (ie., D_TN) from the list, like trips 1 thru 10, and open a report.

I, also, have a query that contains the leg numbers for each trip, like: 3.01, 3.02, . . . . 3.05., which are all under trip 3, where D_TN = 3.

I already have written afterupdate code that gets the max & min leg numbers after a trip number is selected, which for this example are maxT_TN = 3.05 & minT_TN = 3.01.

I need help with the code that uses those three parameters and opens a report displaying only the records for the selected trip number, 3 for this example.

I know how to do that for date ranges, from Allen Browne's paper, but couldn't get a variation of it to work.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 16:30
Joined
Oct 29, 2018
Messages
21,358
Hi. If the value from your list is say a "3", have you tried using Like? For example:
Code:
DoCmd.OpenReport "ReportName", acViewPreview, , "[FieldName] Like '" & Me.ComboboxName & "*'"
Hope it helps...
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:30
Joined
Feb 19, 2002
Messages
42,980
The problem seems to be caused because you have mushed Trip number and leg into a single, text field. The solution suggested by theDBGuy may work because it compares the trip to the first part of trip.leg. But it only works if you want all legs of a trip. If you want a subset of legs, then the best solution is to fix the data problem and store the tripID and the Leg in two separate fields. You can print them as trip.leg if you want but you don't want to keep them as a mushed text field if you need to pull out a range of values.

Here's an example of why what you are doing will eventually fail.
Code:
print iif("3.01" < "10.01", true, false)
False
In the immediate window, I compared two text fields which humans would always say should return true. However, computers are dumb as rocks and they would disagree when the fields are defined as text. The reason is that text fields are compared character by character, left to right. Therefore since "3" is NOT less than "1", "3.01" is NOT less than "10.01".
 

Punice

Registered User.
Local time
Today, 19:30
Joined
May 10, 2010
Messages
135
I used theDBGuy's code and it does what I wanted done for single reports. Thanks for you help.
 

Punice

Registered User.
Local time
Today, 19:30
Joined
May 10, 2010
Messages
135
Your code did what I wanted. Now, I need to know how to have it do it for a main report and a sub-report, with the code in a form's combobox where an integer that represents a trip number and the main report displays the trip leg records (date, mileage, city, etc.) and the sub-report displays the fueling records (date, gal, vendor, city & state) for the trip. The following works for individual reports, but not for both
In the main report: DoCmd.OpenReport strReport, acViewPreview, , "[T_TN] Like '" & Me.cboBoth_lbl & "*' "
Don't know how to include this: DoCmd.OpenReport strReport, acViewPreview, , "[F_SerNo] Like '"& Me.cboF_SerNo_lbl & " * '".
Currently, only the main report works right; the sub displays all records, because it's now in my sub-routine.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 16:30
Joined
Oct 29, 2018
Messages
21,358
Your code did what I wanted. Now, I need to know how to have it do it for a main report and a sub-report, with the code in a form's combobox where an integer that represents a trip number and the main report displays the trip leg records (date, mileage, city, etc.) and the sub-report displays the fueling records (date, gal, vendor, city & state) for the trip. The following works for individual reports, but not for both
In the main report: DoCmd.OpenReport strReport, acViewPreview, , "[T_TN] Like '" & Me.cboBoth_lbl & "*' "
Don't know how to include this: DoCmd.OpenReport strReport, acViewPreview, , "[F_SerNo] Like '"& Me.cboF_SerNo_lbl & " * '".
Currently, only the main report works right; the sub displays all records, because it's now in my sub-routine.
Hi. Can you post a screenshot of your report? If we're talking about a true subreport here, you cannot use the OpenReport method to open it inside a main report. You simply open the main report and make sure the subreport is properly linked to it.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:30
Joined
May 7, 2009
Messages
19,169
on the subreport you add Link Master Fields (PK of main report) and Link Child Fields (FK of subreport).
 

Users who are viewing this thread

Top Bottom