Need quick help! Convert Numeric to Text and keep leading zeros

  • Thread starter Thread starter Gilbert Noetzel
  • Start date Start date
G

Gilbert Noetzel

I need a quick fix to this problem.

Apparently we are convert to one system to another. Oringnal system
uses numeric fields while the new system use text.

We are trying to setup the chart of account list such as

01-001-0001-0001 layout

However when I use Access, it comes out with the wrong numeric placment
for an example:

1-100-3000-0

There are 4 fields concat together:

Acct1 + Acct2 + Acct3 + Acct4

Field = Spaces
Acct1 = 2
Acct2 = 3
Acct3 = 4
Acct4 = 4

I tried the IIF statment and & functions but they are not achieving the
desire results.

Can anyone help?

Thanks in advance...

Gil
 
Use Format function:

Format(Acct1, "00") & "-" & Format(Acct2, "000") & "-" & Format(Acct3,
"0000") & "-" & Format(Acct4, "0000")
 
Ken -

I am able to do that with no problem. However I am trying to link 2
tables together. One is Text and the other is numeric.

How am I able to link the Numeric to Text fields?

For Example:

Table1 Table2

Acct (Text) Acct(Numeric)
ASub (Text) ASub(Numeric)

Thanks in advance.

Gil
 
I need help from the following:

I've created a Query of a file:

QRY: ARSYSCTRL

File: SYSCTRL
File: ARCTRL

Query both file above with NO Links, therefore every record in SYSCTRL
file is listed with all Records in ARCTRL file.

Now I created another Query of a file:

QRY: CHRTACCT

File: GLACCT
File: CHRT

Query both file above with NO Links, therefore every records in GLACCT
and CHRT are listed

HERES the problem:

I create a Query on a query program:

QRY: CHARTACCT

Query: ARSYSCTRL
Query: CHRTACCT

They are link by their common fields (Acct1, Acct2, Acct3, Acct4)

Acct2, Acct3, Acct4 are link between the two Query (both are Text)

However, I am unable to see the value in Acct1 which comes from
ARSYSCTRL Query. If I run the Query on just ARSYSCTRL, it list the
Acct1 data, but the CHARTACCT does not.

What is causing this to happen?

Gil
 
Use what's called a non-equi-join query. This query can only be built in the
SQL view in ACCESS query designer. For example, this query would "convert"
the numeric field in Table1 to a two-character "string" that is then matched
to the text field in Table2:

SELECT Table1.Acct, Table2.Acct
FROM Table1 INNER JOIN Table2
ON Format(Table1.Acct, "00") = Table2.Acct;

Variations on the above can be done to give you the proper comparisons
between tables. Note that this type of query will be nonupdatable.
 
Back
Top