Trim format question?

  • Thread starter Thread starter a24t42
  • Start date Start date
A

a24t42

I have a simple format question.

I have a field called -
ID - custom counter, long number, needs to be 4 characters. I have set
format to 0000. I use DLookup to find this number.

I want to display the number with "SREG" in front of it. I have tried

Control Source: ="SREG" &
DLookUp("[NextAvailableCounter]","CounterTable")

when I do this the number gets trimmed. Say the next number is 0362. I
get SREG362. I want SREG0362. Is there a way to make sure that number
is not trimmed.

Hope that makes sense. Any hekp would be apprecieted.
 
Use the following expression:

="SREG" &
Format(Nz(DLookUp("[NextAvailableCounter]","CounterTable"),1),"0000")

Ken Sheridan
Stafford, England
 
Use the following expression:
Click to expand...
Thank you Ken. That worked perfectly.

Judy


="SREG" &
Format(Nz(DLookUp("[NextAvailableCounter]","CounterTable"),1),"0000")

Ken Sheridan
Stafford, England

[email protected] said:
I have a simple format question.
Click to expand...
I have a field called -
ID - custom counter, long number, needs to be 4 characters. I have set
format to 0000. I use DLookup to find this number.
Click to expand...
I want to display the number with "SREG" in front of it. I have tried
Click to expand...
Control Source: ="SREG" &
DLookUp("[NextAvailableCounter]","CounterTable")
Click to expand...
when I do this the number gets trimmed. Say the next number is 0362. I
get SREG362. I want SREG0362. Is there a way to make sure that number
is not trimmed.
Click to expand...
Hope that makes sense. Any hekp would be apprecieted.
Click to expand...
Click to expand...
 
I have a simple format question.

I have a field called -
ID - custom counter, long number, needs to be 4 characters. I have set
format to 0000. I use DLookup to find this number.

I want to display the number with "SREG" in front of it. I have tried

Control Source: ="SREG" &
DLookUp("[NextAvailableCounter]","CounterTable")

when I do this the number gets trimmed. Say the next number is 0362. I
get SREG362. I want SREG0362. Is there a way to make sure that number
is not trimmed.

Hope that makes sense. Any hekp would be apprecieted.
Click to expand...

Accedss does not store preceding zero's in a Number datatype field.
You can DISPLAY the number with preceding zeros by formatting the
control that displays them.
When you use the DLookUp, what you get is the actual data, not the
formatted data.

Just format the data again.

="SREG" & Format(DLookUp("[NextAvailableCounter]",
"CounterTable"),"0000")

will return "SREG0362" as a string.
 
Back
Top