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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

"DonnaA" <Don...@discussions.microsoft.com> wrote in message
news:175DD6BA-1082-401A...@microsoft.com...

[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

[raskew via AccessMonster.com]

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

Bob

DonnaA 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
>

>> 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.

--
Message posted via http://www.accessmonster.com

[DonnaA]

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

[Douglas J. Steele]

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

"DonnaA" <Don...@discussions.microsoft.com> wrote in message

news:B2F615CE-BB5D-4EA5...@microsoft.com...

[raskew via AccessMonster.com]

See if this might work as a workaround:

Function padem(ByVal pStr As String, pchar As String, plen As Integer, pdir
As Boolean) As String
'*******************************************
'Purpose: Pad a string with user selected
' character
'coded by: raskew
'Inputs: x = "leftpad"; y = "rightpad"
'1) ? padem(x, "0", 10, False)
'2) ? padem(y, "-", 10, True)
'Output:
'1) 000leftpad
'2) rightpad--

'NOTE: pdir: true = left justify; false = right justify

'*******************************************

Dim strSql As String
Dim reps As Integer
Dim i As Integer
strSql = ""
'determine number padded of characters required
reps = plen - Len(pStr)
For i = 1 To reps
strSql = strSql & pchar
Next i
'place the padded string to the left or right
'based on the value of pDir
strSql = IIf(pdir = True, pStr & strSql, strSql & pStr)

padem = strSql
End Function

HTH -
Bob

raskew wrote:
>Did a google on 'lPad'. It's an Oracle function.
>
>Bob
>

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

>[quoted text clipped - 8 lines]

>>> > an "unidentified function 'Lpad'" error message. The same happens in
>>> > Query.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200805/1

[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

[raskew via AccessMonster.com]

John -

Thanks for that. To carry your code a step further (put it all in one
function):

Public Function lrPad(strIn, n As Integer, _
pdir As Boolean, _

Optional strPad As String = " ") As String

'Source:
http://www.accessmonster.com/Uwe/ForumPost.aspx?article=access-queries:46970:eg1LdETuIHA.5500%40TK2MSFTNGP04.phx.gbl

Dim x As String

x = String(n, strPad)
lrPad = IIf(pdir = 0, Left(strIn & x, n), Right(x & strIn, n))

End Function

'Test:
'? lrpad("A B C", 10,true, "-")
'-----A B C
'? lrpad("A B C", 10,false, "-")
'A B C-----

Bob

--

[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.....

> On Saturday, May 17, 2008 11:45 AM Donna wrote:

> 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.
>>

>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no private e-mails, please)

>>> 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:

>>>> On Sunday, May 18, 2008 4:32 PM raskew via AccessMonster.com wrote:

>>>> Did a google on 'lPad'. it is an Oracle function.
>>>>
>>>> Bob

>>>>
>>>> DonnaA wrote:
>>>>
>>>> --
>>>> Message posted via http://www.accessmonster.com

>>>>> On Sunday, May 18, 2008 4:40 PM Donna wrote:

>>>>> So is there an equivalent for Access? I'd already found some references via
>>>>> Google but they involved scripting functions, not queries.
>>>>>
>>>>>
>>>>> "raskew via AccessMonster.com" wrote:

>>>>>> On Sunday, May 18, 2008 4:47 PM Douglas J. Steele wrote:

>>>>>> You have to write a VBA function, and then call that function from your
>>>>>> query.
>>>>>>
>>>>>> --
>>>>>> Doug Steele, Microsoft Access MVP
>>>>>> http://I.Am/DougSteele
>>>>>> (no private e-mails, please)

>>>>>>>> On Sunday, May 18, 2008 5:49 PM John Spencer wrote:

>>>>>>>> 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

>>>>>>>>> On Sunday, May 18, 2008 7:58 PM raskew via AccessMonster.com wrote:

>>>>>>>>> John -
>>>>>>>>>
>>>>>>>>> Thanks for that. To carry your code a step further (put it all in one
>>>>>>>>> function):
>>>>>>>>>
>>>>>>>>> Public Function lrPad(strIn, n As Integer, _
>>>>>>>>> pdir As Boolean, _
>>>>>>>>> Optional strPad As String = " ") As String
>>>>>>>>> 'Source:
>>>>>>>>> http://www.accessmonster.com/Uwe/ForumPost.aspx?article=access-queries:46970:eg1LdETuIHA.5500%40TK2MSFTNGP04.phx.gbl
>>>>>>>>>
>>>>>>>>> Dim x As String
>>>>>>>>>
>>>>>>>>> x = String(n, strPad)
>>>>>>>>> lrPad = IIf(pdir = 0, Left(strIn & x, n), Right(x & strIn, n))
>>>>>>>>>
>>>>>>>>> End Function
>>>>>>>>>
>>>>>>>>> 'Test:
>>>>>>>>> '? lrpad("A B C", 10,true, "-")
>>>>>>>>> '-----A B C
>>>>>>>>> '? lrpad("A B C", 10,false, "-")
>>>>>>>>> 'A B C-----
>>>>>>>>>
>>>>>>>>> Bob
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> John Spencer wrote:
>>>>>>>>>

>>>>>>>>> --
>>>>>>>>> Message posted via http://www.accessmonster.com

>>>>>>>>>> On Sunday, May 18, 2008 9:34 PM Donna wrote:

>>>>>>>>>> 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.
>>>>>>>>>>
>>>>>>>>>> "Douglas J. Steele" wrote:

>>>>>>>>>>> On Monday, May 19, 2008 7:32 AM John Spencer wrote:

>>>>>>>>>>> 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
>>>>>>>>>>>
>>>>>>>>>>> raskew via AccessMonster.com wrote:

>>>>>>>>>>> Submitted via EggHeadCafe
>>>>>>>>>>> Using DataList to List Category/Subcategory with expand - collapse facility via javascript
>>>>>>>>>>> http://www.eggheadcafe.com/tutorials/aspnet/6b6d6022-60f6-4d05-beaf-7737a1f26987/using-datalist-to-list-categorysubcategory-with-expand--collapse-facility-via-javascript.aspx