Leading Zeros 2

[markedback]

I have a table 'B' that is created (via a make table query) from another table 'A'.

A column (Text datatype) in table 'A' carries employee ID numbers. These numbers are supposed to be 5-digit numbers but some employees have 4-digit ID numbers. The ones with 4-digit ID numbers are recorded as 5-digit (with a leading zero) to satisfy the requirement of table 'A'.

Table 'B' is created to use ID numbers in actual form (4 or 5-digit) to run queries against another table 'C'.

If the actual number is 4-digit, the query returns an error when the 5-digit form is used as table 'C' doesn't recognize the 4-digit number with a leading zero.

Is there a way I can drop the leading zero in the make table query so that table 'B' has number in the actual form?

Thanks for your help.

Mark.

 

[Remou]

Val or Int should suit:

SELECT Int([ID]) AS nID
INTO B
FROM A

 

[SkipVought]

Hi,

"...as table 'C' doesn't recognize the 4-digit number with a leading zero."

What we have here, is a failure to communicate.

Here's the problem. You have a column that is an IDENTIFIER, containing a NUMBER. The NUMBER 1, even if it is FORMATTED to DISPLAY 00001 is vastly different than the TEXT 00001. BUT you don't have TEXT you have NUMBERS.

I'd wager that table C is TEXT.

You can join them either by converting the TEXT to a NUMBER, or by FORMATTING a STRING to conform to the 5-character format of table C.



Skip,
[sub]
Just traded in my old subtlety...
for a brand NUANCE![/sub]

 

[markedback]

Thanks Remou and Skip!It worked!