2 functions 1 field in an append query

  • Thread starter Thread starter Heeeeelppp
  • Start date Start date
H

Heeeeelppp

I am trying to do a split of one field into two and put the last two digits
of an 11 digit number in field number two. I used the statement below. I
also want to use a format statement in the same field so the leading zero
doesn't get taken off when exporting the data. How can this be done. The
statement is as follows.

PCP SUF Id: Mid([PCP ID],10,2)

I want to add this format statement.
PCP SUF Id: Format([PCP SUF ID],"00")

Is it possible?
 
Heeeeelppp said:
I am trying to do a split of one field into two and put the last two digits
of an 11 digit number in field number two. I used the statement below. I
also want to use a format statement in the same field so the leading zero
doesn't get taken off when exporting the data. How can this be done. The
statement is as follows.

PCP SUF Id: Mid([PCP ID],10,2)

I want to add this format statement.
PCP SUF Id: Format([PCP SUF ID],"00")
Click to expand...


If [PCP ID] is a Text field, then you should not lose a
leading zero.

If [PCP ID] is a number type, then you may need to use:
PCP SUF Id: Format([PCP SUF ID] Mod 100,"00")

If neither of those keep a leading zero, then check you
export spec to make sure the export is not converting the
field back to a number.

If you only need to see the zero after it was exported, then
try to format the receiving location to 00 instead of foolin
around in your query.
 
Back
Top