Is there any LTRIM function for MS ACCESS 2007?
i am trying to ltrim 3 or 4 zeros from a column in Access 2007 but not getting any result where the data-type is text(from a csv data).
sql ms-access trim
asked Jan 1 at 8:46
SKPSKP
43
add a comment |
i am trying to ltrim 3 or 4 zeros from a column in Access 2007 but not getting any result where the data-type is text(from a csv data).
sql ms-access trim
asked Jan 1 at 8:46
SKPSKP
43
There is, and has always been, an LTrim in VBA. But, as RTrim and Trim, it removes spaces, not zeroes.
– Gustav
Jan 1 at 9:50
add a comment |
i am trying to ltrim 3 or 4 zeros from a column in Access 2007 but not getting any result where the data-type is text(from a csv data).
sql ms-access trim
asked Jan 1 at 8:46
SKPSKP
43
i am trying to ltrim 3 or 4 zeros from a column in Access 2007 but not getting any result where the data-type is text(from a csv data).
sql ms-access trim
sql ms-access trim
asked Jan 1 at 8:46
SKPSKP
43
asked Jan 1 at 8:46
SKPSKP
43
asked Jan 1 at 8:46
SKPSKP
43
asked Jan 1 at 8:46
SKPSKP
43
asked Jan 1 at 8:46
SKPSKP
43
43
There is, and has always been, an LTrim in VBA. But, as RTrim and Trim, it removes spaces, not zeroes.
– Gustav
Jan 1 at 9:50
add a comment |
There is, and has always been, an LTrim in VBA. But, as RTrim and Trim, it removes spaces, not zeroes.
– Gustav
Jan 1 at 9:50
There is, and has always been, an LTrim in VBA. But, as RTrim and Trim, it removes spaces, not zeroes.
– Gustav
Jan 1 at 9:50
There is, and has always been, an LTrim in VBA. But, as RTrim and Trim, it removes spaces, not zeroes.
– Gustav
Jan 1 at 9:50
add a comment |
4 Answers
4
active
oldest
votes
From what I read, MS Access in fact should support LTRIM. Here is a workaround in case you need it:
SELECT
IIF(string LIKE "0000*",
MID(string, 5),
IIF(string LIKE "000*", MID(string, 4), string)) output
FROM yourTable;
answered Jan 1 at 8:56
Tim BiegeleisenTim Biegeleisen
227k1394147
add a comment |
Here are a couple of quickly written LTrim & RTrim functions to operate with characters other than spaces:
Function LTrimChr(strStr As String, strChr As String) As String
If strStr Like strChr & "*" Then
LTrimChr = LTrimChr(Mid(strStr, 2), strChr)
Else
LTrimChr = strStr
End If
End Function
Function RTrimChr(strStr As String, strChr As String) As String
If strStr Like "*" & strChr Then
RTrimChr = RTrimChr(Left(strStr, Len(strStr) - 1), strChr)
Else
RTrimChr = strStr
End If
End Function
Expects the strChr to be a single character, e.g.:
?LTrimChr("000123456789000","0")
123456789000
?RTrimChr("000123456789000","0")
000123456789
answered Jan 1 at 12:10
Lee MacLee Mac
4,48431541
add a comment |
In case of digits only, you can use Val to strip leading zeroes:
LTrimmedValue = CStr(Val(Value))
' "000123456789000" -> "123456789000"
answered Jan 1 at 12:33
GustavGustav
30.1k51936
add a comment |
MS Access supports ltrim(), but only for spaces. So, assuming that your string has no spaces, you can use replace() and ltrim():
select replace(ltrim(replace(col, '0', ' ')), ' ', '0')
If you do have spaces, there is often a character you don't have, so you can replace the spaces first:
select replace(replace(ltrim(replace(replace(col, ' ', '~'
), '0', ' '
)
), ' ', '0'
), '~', ' '
)
answered Jan 1 at 12:52
Gordon LinoffGordon Linoff
777k35306409
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53994131%2fis-there-any-ltrim-function-for-ms-access-2007%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
From what I read, MS Access in fact should support LTRIM. Here is a workaround in case you need it:
SELECT
IIF(string LIKE "0000*",
MID(string, 5),
IIF(string LIKE "000*", MID(string, 4), string)) output
FROM yourTable;
answered Jan 1 at 8:56
Tim BiegeleisenTim Biegeleisen
227k1394147
add a comment |
From what I read, MS Access in fact should support LTRIM. Here is a workaround in case you need it:
SELECT
IIF(string LIKE "0000*",
MID(string, 5),
IIF(string LIKE "000*", MID(string, 4), string)) output
FROM yourTable;
answered Jan 1 at 8:56
Tim BiegeleisenTim Biegeleisen
227k1394147
add a comment |
From what I read, MS Access in fact should support LTRIM. Here is a workaround in case you need it:
SELECT
IIF(string LIKE "0000*",
MID(string, 5),
IIF(string LIKE "000*", MID(string, 4), string)) output
FROM yourTable;
answered Jan 1 at 8:56
Tim BiegeleisenTim Biegeleisen
227k1394147
From what I read, MS Access in fact should support LTRIM. Here is a workaround in case you need it:
SELECT
IIF(string LIKE "0000*",
MID(string, 5),
IIF(string LIKE "000*", MID(string, 4), string)) output
FROM yourTable;
answered Jan 1 at 8:56
Tim BiegeleisenTim Biegeleisen
227k1394147
edited Jan 1 at 11:28
answered Jan 1 at 8:56
Tim BiegeleisenTim Biegeleisen
227k1394147
answered Jan 1 at 8:56
Tim BiegeleisenTim Biegeleisen
227k1394147
answered Jan 1 at 8:56
Tim BiegeleisenTim Biegeleisen
227k1394147
227k1394147
add a comment |
add a comment |
Here are a couple of quickly written LTrim & RTrim functions to operate with characters other than spaces:
Function LTrimChr(strStr As String, strChr As String) As String
If strStr Like strChr & "*" Then
LTrimChr = LTrimChr(Mid(strStr, 2), strChr)
Else
LTrimChr = strStr
End If
End Function
Function RTrimChr(strStr As String, strChr As String) As String
If strStr Like "*" & strChr Then
RTrimChr = RTrimChr(Left(strStr, Len(strStr) - 1), strChr)
Else
RTrimChr = strStr
End If
End Function
Expects the strChr to be a single character, e.g.:
?LTrimChr("000123456789000","0")
123456789000
?RTrimChr("000123456789000","0")
000123456789
answered Jan 1 at 12:10
Lee MacLee Mac
4,48431541
add a comment |
Here are a couple of quickly written LTrim & RTrim functions to operate with characters other than spaces:
Function LTrimChr(strStr As String, strChr As String) As String
If strStr Like strChr & "*" Then
LTrimChr = LTrimChr(Mid(strStr, 2), strChr)
Else
LTrimChr = strStr
End If
End Function
Function RTrimChr(strStr As String, strChr As String) As String
If strStr Like "*" & strChr Then
RTrimChr = RTrimChr(Left(strStr, Len(strStr) - 1), strChr)
Else
RTrimChr = strStr
End If
End Function
Expects the strChr to be a single character, e.g.:
?LTrimChr("000123456789000","0")
123456789000
?RTrimChr("000123456789000","0")
000123456789
answered Jan 1 at 12:10
Lee MacLee Mac
4,48431541
add a comment |
Here are a couple of quickly written LTrim & RTrim functions to operate with characters other than spaces:
Function LTrimChr(strStr As String, strChr As String) As String
If strStr Like strChr & "*" Then
LTrimChr = LTrimChr(Mid(strStr, 2), strChr)
Else
LTrimChr = strStr
End If
End Function
Function RTrimChr(strStr As String, strChr As String) As String
If strStr Like "*" & strChr Then
RTrimChr = RTrimChr(Left(strStr, Len(strStr) - 1), strChr)
Else
RTrimChr = strStr
End If
End Function
Expects the strChr to be a single character, e.g.:
?LTrimChr("000123456789000","0")
123456789000
?RTrimChr("000123456789000","0")
000123456789
answered Jan 1 at 12:10
Lee MacLee Mac
4,48431541
Here are a couple of quickly written LTrim & RTrim functions to operate with characters other than spaces:
Function LTrimChr(strStr As String, strChr As String) As String
If strStr Like strChr & "*" Then
LTrimChr = LTrimChr(Mid(strStr, 2), strChr)
Else
LTrimChr = strStr
End If
End Function
Function RTrimChr(strStr As String, strChr As String) As String
If strStr Like "*" & strChr Then
RTrimChr = RTrimChr(Left(strStr, Len(strStr) - 1), strChr)
Else
RTrimChr = strStr
End If
End Function
Expects the strChr to be a single character, e.g.:
?LTrimChr("000123456789000","0")
123456789000
?RTrimChr("000123456789000","0")
000123456789
answered Jan 1 at 12:10
Lee MacLee Mac
4,48431541
answered Jan 1 at 12:10
Lee MacLee Mac
4,48431541
answered Jan 1 at 12:10
Lee MacLee Mac
4,48431541
answered Jan 1 at 12:10
Lee MacLee Mac
4,48431541
4,48431541
add a comment |
add a comment |
In case of digits only, you can use Val to strip leading zeroes:
LTrimmedValue = CStr(Val(Value))
' "000123456789000" -> "123456789000"
answered Jan 1 at 12:33
GustavGustav
30.1k51936
add a comment |
In case of digits only, you can use Val to strip leading zeroes:
LTrimmedValue = CStr(Val(Value))
' "000123456789000" -> "123456789000"
answered Jan 1 at 12:33
GustavGustav
30.1k51936
add a comment |
In case of digits only, you can use Val to strip leading zeroes:
LTrimmedValue = CStr(Val(Value))
' "000123456789000" -> "123456789000"
answered Jan 1 at 12:33
GustavGustav
30.1k51936
In case of digits only, you can use Val to strip leading zeroes:
LTrimmedValue = CStr(Val(Value))
' "000123456789000" -> "123456789000"
answered Jan 1 at 12:33
GustavGustav
30.1k51936
answered Jan 1 at 12:33
GustavGustav
30.1k51936
answered Jan 1 at 12:33
GustavGustav
30.1k51936
answered Jan 1 at 12:33
GustavGustav
30.1k51936
30.1k51936
add a comment |
add a comment |
MS Access supports ltrim(), but only for spaces. So, assuming that your string has no spaces, you can use replace() and ltrim():
select replace(ltrim(replace(col, '0', ' ')), ' ', '0')
If you do have spaces, there is often a character you don't have, so you can replace the spaces first:
select replace(replace(ltrim(replace(replace(col, ' ', '~'
), '0', ' '
)
), ' ', '0'
), '~', ' '
)
answered Jan 1 at 12:52
Gordon LinoffGordon Linoff
777k35306409
add a comment |
MS Access supports ltrim(), but only for spaces. So, assuming that your string has no spaces, you can use replace() and ltrim():
select replace(ltrim(replace(col, '0', ' ')), ' ', '0')
If you do have spaces, there is often a character you don't have, so you can replace the spaces first:
select replace(replace(ltrim(replace(replace(col, ' ', '~'
), '0', ' '
)
), ' ', '0'
), '~', ' '
)
answered Jan 1 at 12:52
Gordon LinoffGordon Linoff
777k35306409
add a comment |
MS Access supports ltrim(), but only for spaces. So, assuming that your string has no spaces, you can use replace() and ltrim():
select replace(ltrim(replace(col, '0', ' ')), ' ', '0')
If you do have spaces, there is often a character you don't have, so you can replace the spaces first:
select replace(replace(ltrim(replace(replace(col, ' ', '~'
), '0', ' '
)
), ' ', '0'
), '~', ' '
)
answered Jan 1 at 12:52
Gordon LinoffGordon Linoff
777k35306409
MS Access supports ltrim(), but only for spaces. So, assuming that your string has no spaces, you can use replace() and ltrim():
select replace(ltrim(replace(col, '0', ' ')), ' ', '0')
If you do have spaces, there is often a character you don't have, so you can replace the spaces first:
select replace(replace(ltrim(replace(replace(col, ' ', '~'
), '0', ' '
)
), ' ', '0'
), '~', ' '
)
answered Jan 1 at 12:52
Gordon LinoffGordon Linoff
777k35306409
answered Jan 1 at 12:52
Gordon LinoffGordon Linoff
777k35306409
answered Jan 1 at 12:52
Gordon LinoffGordon Linoff
777k35306409
answered Jan 1 at 12:52
Gordon LinoffGordon Linoff
777k35306409
777k35306409
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53994131%2fis-there-any-ltrim-function-for-ms-access-2007%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
There is, and has always been, an LTrim in VBA. But, as RTrim and Trim, it removes spaces, not zeroes.
– Gustav
Jan 1 at 9:50