D
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.
an "unidentified function 'Lpad'" error message. The same happens in Query.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
Douglas J. Steele
What is Lpad supposed to be? I'm not aware of any function built into Access
with that name.
with that name.
D
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
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
D
DonnaA
So is there an equivalent for Access? I'd already found some references via
Google but they involved scripting functions, not queries.
Google but they involved scripting functions, not queries.
raskew via AccessMonster.com said: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.
D
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)
query.
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
DonnaA said: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 said: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.
J
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
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
D
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.
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.
J
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
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
D
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.....
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:
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.