Add or Ignore 0s at beginning of Query criteria for text field 1

[cdck]

In my table, a sales order number is stored with 6-digits, but the number is stored in a text field. Most of the numbers begin with 3-4 0s, as in 000097 or 000355. I cannot change the way this data is stored (in a SQL server), as it is input and processed through a separate software from Access.

The users are used to being able to input the numbers without the preceding 0s and find the item, anyway, in this other software. Unfortunately, the software does not contain all the reports that they need, and so we are creating missing reports in Access.

Is there a way that when they input the sales order number in the criteria pop-up, it automatically will fill in 0s at the beginning for any missing digits (up to the total of 6)? I learned how to do this with numeric fields, previously, but cannot figure out how to do it with a text field.



Cheryl dc Kern

 

[LarrySteele]

Cheryl,

You can wrap your variable in the CLng() function. This will coerce your string variable into a Long Integer (which does not have leading zeros). Do the same with the paramater to make sure you're comparing equivalent data types.

HTH,
Larry

 

[cdck]

The current query is:

[tt]
SELECT dbo_jodbom.fbompart, dbo_jodbom.fcbomudrev, dbo_jodbom.fbomdesc, dbo_jodbom.fbook, dbo_jodbom.fbommeas, dbo_jodbom.fjobno, dbo_jodbom.fparent, dbo_jodbom.fparentrev, dbo_jodbom.fbomsource, dbo_jomast.fpartno, dbo_joitem.fdesc, dbo_joitem.fdescmemo, dbo_inmast.f2labcost, dbo_inmast.f2matlcost, dbo_inmast.fonhand, dbo_jomast.fstatus, dbo_somast.fsono, dbo_somast.fcompany
FROM dbo_somast INNER JOIN (((dbo_inmast INNER JOIN dbo_jodbom ON dbo_inmast.fpartno = dbo_jodbom.fbompart) INNER JOIN dbo_jomast ON dbo_jodbom.fjobno = dbo_jomast.fjobno) INNER JOIN dbo_joitem ON dbo_jomast.fjobno = dbo_joitem.fjobno) ON dbo_somast.fsono = dbo_jomast.fsono
WHERE (((dbo_jodbom.fbook)>0) AND ((dbo_jomast.fstatus)='OPEN' Or (dbo_jomast.fstatus)='RELEASED') AND ((dbo_somast.fsono)=[Sales Order:]) AND ((dbo_inmast.fbulkissue)='N'));
[/tt]

Which works fine, but doesn't take care of the preceding 0 issue.

Attempting to follow your suggestion, I made it:
For a quicker skim, the only change is at the end of the statement

[tt]
SELECT dbo_jodbom.fbompart, dbo_jodbom.fcbomudrev, dbo_jodbom.fbomdesc, dbo_jodbom.fbook, dbo_jodbom.fbommeas, dbo_jodbom.fjobno, dbo_jodbom.fparent, dbo_jodbom.fparentrev, dbo_jodbom.fbomsource, dbo_jomast.fpartno, dbo_joitem.fdesc, dbo_joitem.fdescmemo, dbo_inmast.f2labcost, dbo_inmast.f2matlcost, dbo_inmast.fonhand, dbo_jomast.fstatus, dbo_somast.fsono, dbo_somast.fcompany
FROM dbo_somast INNER JOIN (((dbo_inmast INNER JOIN dbo_jodbom ON dbo_inmast.fpartno = dbo_jodbom.fbompart) INNER JOIN dbo_jomast ON dbo_jodbom.fjobno = dbo_jomast.fjobno) INNER JOIN dbo_joitem ON dbo_jomast.fjobno = dbo_joitem.fjobno) ON dbo_somast.fsono = dbo_jomast.fsono
WHERE (((dbo_jodbom.fbook)>0) AND ((dbo_jomast.fstatus)='OPEN' Or (dbo_jomast.fstatus)='RELEASED') AND ((dbo_inmast.fbulkissue)='N') AND ((CLng(dbo_somast.fsono)=CLng([Sales Order:])));
[/tt]

This now throws a syntax error in the Where statement, but it does not define the error, it just repeats the entire Where clause from (((dbo_jodbom.fbook)>0) on. Any suggestions? What did I mis-type?

Cheryl dc Kern

 

[LarrySteele]

Cheryl,

Sorry about that. I've been working with DB2 for too long and sometimes I forget that there are those subtle differences between DB2 and JET.

Long story short, here's a method that appeared to work:

dbo_somast.fsono = format([Sales Order:],"000000")

HTH,
Larry

 

[cdck]

Thank you, that worked perfectly!

Cheryl dc Kern

 

[cdck]

Larry:

Another issue popped up right away. What do I do is the number is broken by a hyphen, as in 00098-0000? I have to do the same thing with the job field, which is always 5 digits, hyphen, 4 digits.

Cheryl dc Kern

 

[LarrySteele]

Cheryl,

Glad to help & thanks for the star.

The hyphen one is a bit trickier. Your number looks a lot like the US postal code Zip+4. Is that what you're working with? If not, I'll need to know the formats the users use to enter in this field (I know the formats for Zip+4).

I may not be able to answer tonight as I'm getting ready to head home and have much I need to do. I'll pick this up tomorrow if someone else doesn't pitch in tonight.

- Larry

 

[cdck]

It's not a zip code, unfortunately. It's a 5-digit job number followed by a 4-digit sub-job, or task, number. The field is generated by the separate software automatically as a job and its sub-jobs are entered, and it is stored as text just like the sales order number.

Thank for all your help with this. When I can remember that you can use functions in the middle of a query, I still can't ever seem to find the right function for what I'm doing!

Cheryl dc Kern

 

[LarrySteele]

Cheryl,

How do your users enter the job number? Take for example the following two numbers:

[tt]00098-0000
00098-0001[/tt]

How do your users want to enter each of these numbers?

- Larry

 

[cdck]

Optimally, if they enter only a 98, I would like it to fill in as 00098-0000. If the numbers after the hyphen are not all 0, they would have to type all four of those digits, thus entering 98-0001 to get 00098-0001.

Cheryl dc Kern