number of digits

  • Thread starter Thread starter anonymous
  • Start date Start date
A

anonymous

I'm trying to format the number of digits in a field, but
run into a problem when the digit is zero.
it returns '100' instead of '00'

Can someone help me with the right Statement?

here is an example of what I have:
SELECT (((Format(tblOccRep.occYy,"00")) & (Format
(tblOccRep.reportNo,"000")) & (tblOccRep.reportCodes))) AS
reportNo
FROM tblOccRep;
 
It's not clear from your post just what your query's expression will
produce. Can you post some examples of the data and how your query converts
them? From which data are you getting the "100" result?
 
I'm trying to format the number of digits in a field, but
run into a problem when the digit is zero.
it returns '100' instead of '00'

Can someone help me with the right Statement?

here is an example of what I have:
SELECT (((Format(tblOccRep.occYy,"00")) & (Format
(tblOccRep.reportNo,"000")) & (tblOccRep.reportCodes))) AS
reportNo
FROM tblOccRep;
Click to expand...

What are the datatypes of occYy and reportNo? What is the value of
reportCodes? Could you specify the values of all three fields and show
what undesired result you get? it's not clear to me which value is
returning 100.
 
The data comes from 3 fields. 2 number fields and 1 text
field. The first
number field is the year (which I want to show up as 2
digits...00, 01, 02....), the second number field is an
autonumber field (which I want to show up as 3
digits.....000, 001, 002...)
and the third is a code. Together they make up a report
number to sort by.

here is an example:

Year | autoNo.| code | DeriredResult | ActualResult
------|--------|----- | --------------|-------------
00 | 1 | A | 00001A | 100001A
01 | 2 | B | 01002B | 01002B
02 | 3 | C | 02003C | 02003C


On the report I would like it to look something similar to
the desired Result.

The problem is with the year 2000, it shows up as 100001A.
instead of 00001A.

It adds acts as if 00 is 100.
 
I just figured out what I was doing wrong. I was pulling
the id, instead
of the text field. Thanks for your help.
 
Back
Top