I have a database setup that uses ODBC connections into SQL for read
only views to compare information from multiple sql databases in our
company. Most of the tables use the Employee ID as the primary key.
The problem is, one database formats the EmpID with leading zeros so
all EmpID's are 5 characters long. (ie, 00123, 01523) Another
database formats the EmpID as just the number of characters as the
number is (ie, 123, 1523). When you try to create the relationship
between the two tables - you can't because of the inconsistency. (the
fields are formatted as text fields in the original dbs)

I want to create a query that will create a new field to add the
preceeding zeros to the EmpID. This is where I need assistance. WHat
is the format code for forcing those preceeding zeros into a field?

On Jun 4, 3:51*pm, LFM <cruzen...@gmail.comwrote:

I have a database setup that uses ODBC connections into SQL for read
only views to compare information from multiple sql databases in our
company. * Most of the tables use the Employee ID as the primary key.
The problem is, one database formats the EmpID with leading zeros so
all EmpID's are 5 characters long. *(ie, 00123, 01523) Another
database formats the EmpID as just the number of characters as the
number is (ie, 123, 1523). *When you try to create the relationship
between the two tables - you can't because of the inconsistency. *(the
fields are formatted as text fields in the original dbs)
>
I want to create a query that will create a new field to add the
preceeding zeros to the EmpID. *This is where I need assistance. *WHat
is the format code for forcing those preceeding zeros into a field?

If you want to force leading zeros to be displayed, then the format
function will do the job -

Format(EmpID, "00000")

If you want to output a string holding this, then wrap a CSTR function
around it all:
cstr(Format(EmpID, "00000"))

Does this help?

Regards
Andrew