Find records with 1st five characters (1 Viewer)

Zydeceltico

Registered User.
Local time
Today, 04:03
Joined
Dec 5, 2017
Messages
843
Hi all,

I'm trying to create a report for manufacturing inspections by job number.

Our job numbers look like this: A762502001.

The first five characters (e.g., A7625) are the actual job number. The next three (e.g., 020) is what we call the Task. The last two e.g., 01) are what we call the Resource.

The query returns them as one concatenated string (i.e., A762502001).

There could be, and usually permutations of Task and Resource and they are not, rarely, consecutive in nature.
For instance the entire job A7625 might have have these subs: A762502001, A762502002, A762503007, A762550001 or many other combinations.

I want to create a report where there is combo box in the header that the user can type in just the first five characters (e.g., A7625) and return all records for all of the permutations to include all Tasks and Resources.

I suspect this would a SUBSTRING call but I'm not sure how to implement it within my SELECT statement especially since I am concatenating in the SELECT statement.

Here is my current code:


SQL:
SELECT tblinspectionevent.inspectionevent_pk,
       tbljobs.job_id,
       [jobnumber] & [task] & [resource] AS Job,
       tblinspectmill.amsbundlenumber,
       tblfinalproducts.finalproducttype,
       tblparts.parttype,
       tblinspectionevent.notes,
       tblinspectmill.coveragemaintained,
       tblinspectmill.coveragewidth,
       tblinspectmill.interlockgap1,
       tblinspectmill.interlockgap2,
       tblinspectmill.camberfromzero,
       tblinspectmill.sweepfromzero,
       tblinspectmill.lengthactual,
       tblinspectmill.shapeconforms,
       tblcoils.coilnumber,
       tblcoils.matcert,
       tblinspectionevent.oilcanning,
       tblinspectionevent.coatingissues,
       tblinspectionevent.issuesother
FROM   (tblfinalproducts
        INNER JOIN tbljobs
                ON tblfinalproducts.finalproduct_id = tbljobs.finalproduct_fk)
       INNER JOIN (tblinspectionevent
                   INNER JOIN (tblparts
                               INNER JOIN (tblinspectmill
                                           INNER JOIN tblcoils
                                                   ON
                                           tblinspectmill.coilnumber_pk =
                                           tblcoils.coilnumber_pk)
                                       ON tblparts.part_id =
                              tblinspectmill.parttype_fk)
                           ON tblinspectionevent.inspectionevent_pk =
                              tblinspectmill.inspectionevent_fk)
               ON tbljobs.job_id = tblinspectionevent.job_fk
ORDER  BY tblinspectionevent.inspectionevent_pk DESC;

Thank you as always,

Tim
 

GPGeorge

Grover Park George
Local time
Today, 01:03
Joined
Nov 25, 2004
Messages
1,762
If the job number is always the first five characters, then Left([jobnumber]) will return them and you can add that to a WHERE clause to filter the entire list.

WHERE Left([jobnumber], 5) = Forms!frmYourformNameGoesHere.cboYourFilteringComboBoxNameGoesHere
 

plog

Banishment Pending
Local time
Today, 03:03
Joined
May 11, 2011
Messages
11,597
I would do this with a DoCmd.OpenReport:


You build a form with a text input or combo box where the user inputs/selects the Job Number they want. Then they click a button that builds a criteria string with the Job Number they want and opens the Report to just that data.

What you need to do to the query is bring JobNumber in the SELECT by itself. You do that, the report has access to it and can use it to filter by.
 

Eugene-LS

Registered User.
Local time
Today, 11:03
Joined
Dec 7, 2018
Messages
481
I want to create a report where there is combo box in the header that the user can type in just the first five characters (e.g., A7625) and return all records for all of the permutations to include all Tasks and Resources.
It can be done much easier
If I understood your "wish" correctly, here is solution:
 

Attachments

  • Example_V001.zip
    255.7 KB · Views: 214

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:03
Joined
May 7, 2009
Messages
19,094
from the Query string you posted, you already have the idea that there
is such JobNumber field in one of your tables.

you can set the RowSource of your combobox (cboJobNumber) to this field:

select JobNumber from your table Group by JobNumber;

to retrieve records with this JobNumber, you create a Query that will
filter the Query you posted earlier:

select * from [yourPostedQueryName] Where Job Like '" & Me!cboJobNumber & "*';"
 

Users who are viewing this thread

Top Bottom