Formatting in a Query

[Wet_Blanket]

I have an issue, I need to format a field from a table. the field is [acct_num], and the current format is this (bad I know):

### - ###### - ###

or

### - ##### - ###

I need to just have the middle 5 or 6 numbers for the query, drop off everything else. Plus, where the middle numbers are only 5 digits, I need leading zeroes.

This is what I have so far, which does half the job:

acct_nbr: Trim((Left(

![acct_num],Len(

![acct_num])-5)))

Any suggestions?

 

[Wet_Blanket]

Got further, but having issue with the leading zero.

acct_nbr: Right(Left(

![acct_num],Len(

![acct_num])-5),6)

 

[pbaldy]

Rather than the Left() function, I'd use the Mid() function, since you know the starting point. You can use the InStrRev() function to find the position of the last dash. A little math with that should provide the length needed by the Mid() function.

 

[pbaldy]

Oh, and you can use the Format() function to provide the leading zero:

Format(Whatever, "000000")

 

[Wet_Blanket]

Wow, can't believe I didn't know about that function - actually I can. Here is the formula that works:

acct_nbr: Format(Mid(

![acct_num],7,6),"000000")