Lpad in Access

[DonnaA]

I am having trouble constructing an Update query using Lpad. I keep getting
an "unidentified function 'Lpad'" error message. The same happens in Query.

 

[Douglas J. Steele]

What is Lpad supposed to be? I'm not aware of any function built into Access
with that name.

 

[DonnaA]

lpad ('string', n [, 'string_pad')
rpad ('string', n [, 'string_pad')

string is left padded to length n with string_pad. If string_pad is ommited,
a space will be used as default

 

[DonnaA]

So is there an equivalent for Access? I'd already found some references via
Google but they involved scripting functions, not queries.

Did a google on 'lPad'. It's an Oracle function.

Bob

lpad ('string', n [, 'string_pad')
rpad ('string', n [, 'string_pad')

string is left padded to length n with string_pad. If string_pad is ommited,
a space will be used as default

What is Lpad supposed to be? I'm not aware of any function built into Access
with that name.

I am having trouble constructing an Update query using Lpad. I keep getting
an "unidentified function 'Lpad'" error message. The same happens in
Query.

 

[Douglas J. Steele]

You have to write a VBA function, and then call that function from your
query.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

So is there an equivalent for Access? I'd already found some references
via
Google but they involved scripting functions, not queries.

Did a google on 'lPad'. It's an Oracle function.

Bob

lpad ('string', n [, 'string_pad')
rpad ('string', n [, 'string_pad')

string is left padded to length n with string_pad. If string_pad is
ommited,
a space will be used as default

What is Lpad supposed to be? I'm not aware of any function built into
Access
with that name.

I am having trouble constructing an Update query using Lpad. I keep
getting
an "unidentified function 'Lpad'" error message. The same happens in
Query.

 

[John Spencer]

Custom functions required in VBA. These might look like the following.

IF strIn is longer than N do you want the entire string returned or do
you want it truncated or do you want an error generated?

The following functions will truncate the string to N characters in
length if the string is longer than N.

Public Function rPad(strIn, n As Integer, _
Optional strPad As String = " ") As String

rPad = Left(strIn & String(n, strPad), n)

End Function

Public Function lPad(strIn, n As Integer, _
Optional strPad As String = " ") As String

lPad = Right(String(n, strPad) & strIn, n)

End Function

 

[DonnaA]

Thanks for the answer but unfortunately my skills are very minimal and
limited to learning sql on a need to know basis. No formal training in
anything remotely tech and not enough time available for learning much other
than what I can pick up in a hour here and an hour there. Looks like I'm
going to have to jerry-rig something for this report.

 

[John Spencer]

Good solution, but personally I prefer having the two functions - just as VBA
has paired functions such as left, right and Instr, InStrRev.

Well the original poster has at least two possible solutions.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Douglas White]

Here is a workaround that seems to work in Access SQL:

step 1) Create a field with your 'pad' characters
step 2) trim the 'pad' characters to your desired length minus the length of the field you want padded
step 3) concatenate the field you want padded to your pad characters

Since you have already 'trimmed' the pad characters using teh length of the desired field, the concatenation will end up the exact length you desire.

In the example posted here I wanted to pad a currency field out to 12 chars with '0' as the pad. in other words, I wanted $1.25 to appear as 000000000125

SELECT Left("000000000000",12-Len(Abs(Final_Amount*100))) & Abs(Final_Amount*100) AS formatted_amt
FROM Table1;

I used 'ABS' to prevent the negative sign from becoming an issue on the length, and had another field just to carry the sign using 'SGN', but I didn't think that was significant here. It it's text you are padding, then there is obviously no problem with the negative/positive issue. If you want the pad to be on the right side, you'd have to change the order of the fields and use 'right' instead of left to trim the pads, but hopefully the idea comes through.....

I am having trouble constructing an Update query using Lpad. I keep getting
an "unidentified function 'Lpad'" error message. The same happens in Query.

On Saturday, May 17, 2008 12:18 PM Douglas J. Steele wrote:
What is Lpad supposed to be? I am not aware of any function built into Access
with that name.

On Sunday, May 18, 2008 4:00 PM Donna wrote:
lpad ('string', n [, 'string_pad')
rpad ('string', n [, 'string_pad')

string is left padded to length n with string_pad. If string_pad is ommited,
a space will be used as default

"Douglas J. Steele" wrote: