By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,081 Members | 2,118 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,081 IT Pros & Developers. It's quick & easy.

SQL Format leading zeros

P: n/a
HELP! SOS! I have a field that I'm trying to format within a query. The
results from my field "Appointment.EMP_FILE_NB" give various numbers:
(e.g.:

213
105523
125400
1245
834

When what I want is:

000213
105523
125400
001245
000834

SELECT Employment.PREFERRED_NAME, Employment.LAST_NAME,
Appointment.JOB_TITLE, Appointment.BU_NAME, Appointment.CO_C,
Appointment.EMP_FILE_NB

FROM Employment INNER JOIN Appointment ON (Employment.EMPL_NO =
Appointment.EMPL_NO) AND (Employment.PERSON_ID = Appointment.PERSON_ID)

WHERE (((Appointment.APPT_START_DATE)<=Date()) AND
((Appointment.APPT_END_DATE) Is Null Or
(Appointment.APPT_END_DATE)>=Date()) AND ((Employment.EMPL_TYPE_CODE)
Like "FTR" Or (Employment.EMPL_TYPE_CODE) Like "PTR") AND
((Appointment.PRIMARY_APPT)=-1))

ORDER BY Employment.LAST_NAME;
_________________________________________________
I tried this string:

SELECT Employment.PREFERRED_NAME, Employment.LAST_NAME,
Appointment.JOB_TITLE, Appointment.BU_NAME, Appointment.CO_C,
Format([Appointment.EMP_FILE_NB],"000000")

But this gives me no records, whereas my original SQL gave all the
records expected.

Any clues? HELP!

Jan 9 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Br
Brian Campbell wrote:
HELP! SOS! I have a field that I'm trying to format within a query.
The results from my field "Appointment.EMP_FILE_NB" give various
numbers: (e.g.:

213
105523
125400
1245
834

When what I want is:

000213
105523
125400
001245
000834

SELECT Employment.PREFERRED_NAME, Employment.LAST_NAME,
Appointment.JOB_TITLE, Appointment.BU_NAME, Appointment.CO_C,
Appointment.EMP_FILE_NB

FROM Employment INNER JOIN Appointment ON (Employment.EMPL_NO =
Appointment.EMPL_NO) AND (Employment.PERSON_ID =
Appointment.PERSON_ID)

WHERE (((Appointment.APPT_START_DATE)<=Date()) AND
((Appointment.APPT_END_DATE) Is Null Or
(Appointment.APPT_END_DATE)>=Date()) AND ((Employment.EMPL_TYPE_CODE)
Like "FTR" Or (Employment.EMPL_TYPE_CODE) Like "PTR") AND
((Appointment.PRIMARY_APPT)=-1))

ORDER BY Employment.LAST_NAME;
_________________________________________________
I tried this string:

SELECT Employment.PREFERRED_NAME, Employment.LAST_NAME,
Appointment.JOB_TITLE, Appointment.BU_NAME, Appointment.CO_C,
Format([Appointment.EMP_FILE_NB],"000000")

But this gives me no records, whereas my original SQL gave all the
records expected.

Any clues? HELP!

Maybe put a criteria on the Format statement in case the value is NULL?

eg.
SELECT Employment.PREFERRED_NAME, Employment.LAST_NAME,
Appointment.JOB_TITLE, Appointment.BU_NAME, Appointment.CO_C,
Iif(Not IsNull([Appointment.EMP_FILE_NB]),
Format([Appointment.EMP_FILE_NB])) AS MyField

--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response
Jan 9 '06 #2

P: n/a
How would I phrase the "000000" which is critical to this being done
per my user's needs. All the numbers must have 6 digits.

Thanks!
Brian

Jan 9 '06 #3

P: n/a
Br
Brian Campbell wrote:
How would I phrase the "000000" which is critical to this being done
per my user's needs. All the numbers must have 6 digits.

Thanks!
Brian


I just forgot to fill out the format statement fully....

SELECT Employment.PREFERRED_NAME, Employment.LAST_NAME,
Appointment.JOB_TITLE, Appointment.BU_NAME, Appointment.CO_C,
Iif(Not IsNull([Appointment.EMP_FILE_NB]),

Format([Appointment.EMP_FILE_NB],"000000") ) AS MyField

regards,

Br@dley

Jan 10 '06 #4

P: n/a
You have
Format([Appointment.EMP_FILE_NB],"000000")

It should be
Format([Appointment].[EMP_FILE_NB],"000000")

or
Format(Appointment.EMP_FILE_NB,"000000")

You have to watch out for the QBE doing stupid things like adding sputious
[] and "".
--

Terry Kreft
"Brian Campbell" <ca***************@yahoo.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...
HELP! SOS! I have a field that I'm trying to format within a query. The
results from my field "Appointment.EMP_FILE_NB" give various numbers:
(e.g.:

213
105523
125400
1245
834

When what I want is:

000213
105523
125400
001245
000834

SELECT Employment.PREFERRED_NAME, Employment.LAST_NAME,
Appointment.JOB_TITLE, Appointment.BU_NAME, Appointment.CO_C,
Appointment.EMP_FILE_NB

FROM Employment INNER JOIN Appointment ON (Employment.EMPL_NO =
Appointment.EMPL_NO) AND (Employment.PERSON_ID = Appointment.PERSON_ID)

WHERE (((Appointment.APPT_START_DATE)<=Date()) AND
((Appointment.APPT_END_DATE) Is Null Or
(Appointment.APPT_END_DATE)>=Date()) AND ((Employment.EMPL_TYPE_CODE)
Like "FTR" Or (Employment.EMPL_TYPE_CODE) Like "PTR") AND
((Appointment.PRIMARY_APPT)=-1))

ORDER BY Employment.LAST_NAME;
_________________________________________________
I tried this string:

SELECT Employment.PREFERRED_NAME, Employment.LAST_NAME,
Appointment.JOB_TITLE, Appointment.BU_NAME, Appointment.CO_C,
Format([Appointment.EMP_FILE_NB],"000000")

But this gives me no records, whereas my original SQL gave all the
records expected.

Any clues? HELP!

Jan 10 '06 #5

P: n/a
I'm having a similar problem with needing to keep leading 0's. I'm
kinda
new to Access and learning on my own, so please forgive me
for anything that doesn't sound right. See details below....
I have created a table that tracks dogs by number. Some of the dog
numbers start with leading 0's (example 001). I set the dog number
field type to be numerical so when I print a report by dog number, it
will sort them correctly. When I enter 001 it automatically converts

this to 1. I tried changing the field type to text....this keeps the
dog number as 001, but does not allow me to print a report by numerical

order. Is there a way to be able to key in a dog number like 001 and

still be able to print a report by numerical order?
Thanks in advance for any help!!

Jan 10 '06 #6

P: n/a
TC:
Keep your table with the dog number defined as text. In the underlying
query for your report, add another field using the Val function.
DogNumber: Val(DogText)
(replace DogText with the name of your dog number field that has the
text in it with the leading zeros)

You can then sort your report by the DogNumber, but show the DogText
field on the report to get them in the order you want.

HTH,
Jana

Jan 10 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.