I imported records into a table, later found out that many of them had
trailing spaces in one of the fields. If I'd caught it sooner, I could have
trimmed the spaces before the import.
This wouldn't work (nothing changed):
UPDATE tblManuals SET tblManuals.PARTNUM = Trim([partnum]);
Would someone please tell me how to do an update query that will trim the
spaces?
10  3077 
Randy,
Create a new field in your query and enter this expression:
MyUpdateField:Trim([partnum])
Then change the query to an Update Query and enter this expression where it says
Update To in the PartNum field:
MyUpDateField
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications re******@pcdatasheet.com www.pcdatasheet.com
"Randy Harris" <ra***@SpamFree.com> wrote in message
news:aU***************@newssvr31.news.prodigy.com. .. I imported records into a table, later found out that many of them had
trailing spaces in one of the fields. If I'd caught it sooner, I could have
trimmed the spaces before the import.
This wouldn't work (nothing changed):
UPDATE tblManuals SET tblManuals.PARTNUM = Trim([partnum]);
Would someone please tell me how to do an update query that will trim the
spaces?
"PC Datasheet" <no****@nospam.spam> wrote in message
news:b0*****************@newsread3.news.atl.earthl ink.net... Randy,
Create a new field in your query and enter this expression:
MyUpdateField:Trim([partnum])
Then change the query to an Update Query and enter this expression where
it says Update To in the PartNum field:
MyUpDateField
Perfect. Should have thought of that myself. Thanks!
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Randy Harris" <ra***@SpamFree.com> wrote in message
news:aU***************@newssvr31.news.prodigy.com. .. I imported records into a table, later found out that many of them had
trailing spaces in one of the fields. If I'd caught it sooner, I could
have trimmed the spaces before the import.
This wouldn't work (nothing changed):
UPDATE tblManuals SET tblManuals.PARTNUM = Trim([partnum]);
Would someone please tell me how to do an update query that will trim
the spaces?
"Randy Harris" <ra***@SpamFree.com> wrote in message
news:rs**********************@newssvr28.news.prodi gy.com...
"PC Datasheet" <no****@nospam.spam> wrote in message
news:b0*****************@newsread3.news.atl.earthl ink.net... Randy,
Create a new field in your query and enter this expression:
MyUpdateField:Trim([partnum])
Then change the query to an Update Query and enter this expression where it says Update To in the PartNum field:
MyUpDateField
Perfect. Should have thought of that myself. Thanks!
Well, I spoke a bit too soon. I still have a problem. It doesn't look as
though the Trim functions work in the A2K query grid. No error, they just
don't trim the spaces. I tried the MyUpdateField:Trim([partnum]) as an
Update query, a MakeTable query and as a Select query. Trim just plain
doesn't work. Surprising.
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Randy Harris" <ra***@SpamFree.com> wrote in message
news:aU***************@newssvr31.news.prodigy.com. .. I imported records into a table, later found out that many of them had
trailing spaces in one of the fields. If I'd caught it sooner, I
could have trimmed the spaces before the import.
This wouldn't work (nothing changed):
UPDATE tblManuals SET tblManuals.PARTNUM = Trim([partnum]);
Would someone please tell me how to do an update query that will trim the spaces?
The following worked for me:
In an update query based solely on the table in question put
TRIM:(TableName.FieldName) in the update to field.
For tblTest(ID, name), in SQL view, this yields
UPDATE tblTest SET tblTest.name = Trim([tblTest].[Name]);
--
ROGER NEYMAN
"Randy Harris" <ra***@SpamFree.com> wrote in message
news:e2**********************@newssvr28.news.prodi gy.com... "Randy Harris" <ra***@SpamFree.com> wrote in message
news:rs**********************@newssvr28.news.prodi gy.com...
"PC Datasheet" <no****@nospam.spam> wrote in message
news:b0*****************@newsread3.news.atl.earthl ink.net... Randy,
Create a new field in your query and enter this expression:
MyUpdateField:Trim([partnum])
Then change the query to an Update Query and enter this expression
where it says Update To in the PartNum field:
MyUpDateField
Perfect. Should have thought of that myself. Thanks!
Well, I spoke a bit too soon. I still have a problem. It doesn't look as
though the Trim functions work in the A2K query grid. No error, they just
don't trim the spaces. I tried the MyUpdateField:Trim([partnum]) as an
Update query, a MakeTable query and as a Select query. Trim just plain
doesn't work. Surprising.
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Randy Harris" <ra***@SpamFree.com> wrote in message
news:aU***************@newssvr31.news.prodigy.com. ..
> I imported records into a table, later found out that many of them
had > trailing spaces in one of the fields. If I'd caught it sooner, I
could have > trimmed the spaces before the import.
>
> This wouldn't work (nothing changed):
> UPDATE tblManuals SET tblManuals.PARTNUM = Trim([partnum]);
>
> Would someone please tell me how to do an update query that will
trim the > spaces?
>
>
>
I am quite possibly losing my mind. Trim appears to be working perfectly.
Except for on one record. Naturally, the record I was checking to see if
Trim worked or not. It won't Trim that one record, it must have some weird
character in it or something.
Thanks to you both for your help.
Randy
"rpnman" <re******@verizon.net> wrote in message
news:Dt*******************@nwrddc02.gnilink.net... The following worked for me:
In an update query based solely on the table in question put
TRIM:(TableName.FieldName) in the update to field.
For tblTest(ID, name), in SQL view, this yields
UPDATE tblTest SET tblTest.name = Trim([tblTest].[Name]);
--
ROGER NEYMAN
"Randy Harris" <ra***@SpamFree.com> wrote in message
news:e2**********************@newssvr28.news.prodi gy.com... "Randy Harris" <ra***@SpamFree.com> wrote in message
news:rs**********************@newssvr28.news.prodi gy.com...
"PC Datasheet" <no****@nospam.spam> wrote in message
news:b0*****************@newsread3.news.atl.earthl ink.net...
> Randy,
>
> Create a new field in your query and enter this expression:
> MyUpdateField:Trim([partnum])
>
> Then change the query to an Update Query and enter this expression where it says
> Update To in the PartNum field:
> MyUpDateField
Perfect. Should have thought of that myself. Thanks!
Well, I spoke a bit too soon. I still have a problem. It doesn't look
as though the Trim functions work in the A2K query grid. No error, they
just don't trim the spaces. I tried the MyUpdateField:Trim([partnum]) as an
Update query, a MakeTable query and as a Select query. Trim just plain
doesn't work. Surprising.
> PC Datasheet
> Your Resource For Help With Access, Excel And Word Applications
> re******@pcdatasheet.com
> www.pcdatasheet.com
>
>
> "Randy Harris" <ra***@SpamFree.com> wrote in message
> news:aU***************@newssvr31.news.prodigy.com. ..
> > I imported records into a table, later found out that many of them
had > > trailing spaces in one of the fields. If I'd caught it sooner, I
could have
> > trimmed the spaces before the import.
> >
> > This wouldn't work (nothing changed):
> > UPDATE tblManuals SET tblManuals.PARTNUM = Trim([partnum]);
> >
> > Would someone please tell me how to do an update query that will trim the
> > spaces?
> >
> >
> >
>
>
Check for the presence of a Null (Chr(0)).
--
Doug Steele, Microsoft Access MVP http://I.Am/DougSteele
(no e-mails, please!)
"Randy Harris" <ra***@SpamFree.com> wrote in message
news:7o**************@newssvr31.news.prodigy.com.. . I am quite possibly losing my mind. Trim appears to be working perfectly.
Except for on one record. Naturally, the record I was checking to see if
Trim worked or not. It won't Trim that one record, it must have some
weird character in it or something.
Thanks to you both for your help.
Randy
"rpnman" <re******@verizon.net> wrote in message
news:Dt*******************@nwrddc02.gnilink.net... The following worked for me:
In an update query based solely on the table in question put
TRIM:(TableName.FieldName) in the update to field.
For tblTest(ID, name), in SQL view, this yields
UPDATE tblTest SET tblTest.name = Trim([tblTest].[Name]);
--
ROGER NEYMAN
"Randy Harris" <ra***@SpamFree.com> wrote in message
news:e2**********************@newssvr28.news.prodi gy.com... "Randy Harris" <ra***@SpamFree.com> wrote in message
news:rs**********************@newssvr28.news.prodi gy.com...
>
> "PC Datasheet" <no****@nospam.spam> wrote in message
> news:b0*****************@newsread3.news.atl.earthl ink.net...
> > Randy,
> >
> > Create a new field in your query and enter this expression:
> > MyUpdateField:Trim([partnum])
> >
> > Then change the query to an Update Query and enter this expression where > it says
> > Update To in the PartNum field:
> > MyUpDateField
>
> Perfect. Should have thought of that myself. Thanks!
Well, I spoke a bit too soon. I still have a problem. It doesn't
look as though the Trim functions work in the A2K query grid. No error, they just don't trim the spaces. I tried the MyUpdateField:Trim([partnum]) as
an Update query, a MakeTable query and as a Select query. Trim just
plain doesn't work. Surprising.
> > PC Datasheet
> > Your Resource For Help With Access, Excel And Word Applications
> > re******@pcdatasheet.com
> > www.pcdatasheet.com
> >
> >
> > "Randy Harris" <ra***@SpamFree.com> wrote in message
> > news:aU***************@newssvr31.news.prodigy.com. ..
> > > I imported records into a table, later found out that many of
them had > > > trailing spaces in one of the fields. If I'd caught it sooner,
I could
> have
> > > trimmed the spaces before the import.
> > >
> > > This wouldn't work (nothing changed):
> > > UPDATE tblManuals SET tblManuals.PARTNUM = Trim([partnum]);
> > >
> > > Would someone please tell me how to do an update query that will
trim > the
> > > spaces?
> > >
> > >
> > >
> >
> >
>
>
Good guess, but in this case it was a 160. Anyone have a clue what a
Chr(160) is, or even better, how she got it in there in the first place?
(Data was entered in Excel, then imported, and yes, I went back to the
original spreadsheet and confirmed it was that way in there!)
Hmmm... It just occurred to me that a 160 is a space char (32) with the
highest order bit turned on. There must be some sort of a connection there.
But, I'm bewildered.
Private Sub Randy()
Dim I As Integer, S As String
S = DLookup("PartNum", "tblManuals", "ManualID=189")
Debug.Print S
For I = 1 To Len(S)
Debug.Print I; Asc(Mid(S, I, 1))
Next I
End Sub
93-027192 - NX2
1 57
2 51
3 45
4 48
5 50
6 55
7 49
8 57
9 50
10 32
11 45
12 32
13 78
14 88
15 50
16 32
17 32
18 160
19 32
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:4t**************@news04.bloor.is.net.cable.ro gers.com... Check for the presence of a Null (Chr(0)).
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Randy Harris" <ra***@SpamFree.com> wrote in message
news:7o**************@newssvr31.news.prodigy.com.. . I am quite possibly losing my mind. Trim appears to be working
perfectly. Except for on one record. Naturally, the record I was checking to see
if Trim worked or not. It won't Trim that one record, it must have some weird character in it or something.
Thanks to you both for your help.
Randy
"rpnman" <re******@verizon.net> wrote in message
news:Dt*******************@nwrddc02.gnilink.net... The following worked for me:
In an update query based solely on the table in question put
TRIM:(TableName.FieldName) in the update to field.
For tblTest(ID, name), in SQL view, this yields
UPDATE tblTest SET tblTest.name = Trim([tblTest].[Name]);
--
ROGER NEYMAN
"Randy Harris" <ra***@SpamFree.com> wrote in message
news:e2**********************@newssvr28.news.prodi gy.com...
> "Randy Harris" <ra***@SpamFree.com> wrote in message
> news:rs**********************@newssvr28.news.prodi gy.com...
> >
> > "PC Datasheet" <no****@nospam.spam> wrote in message
> > news:b0*****************@newsread3.news.atl.earthl ink.net...
> > > Randy,
> > >
> > > Create a new field in your query and enter this expression:
> > > MyUpdateField:Trim([partnum])
> > >
> > > Then change the query to an Update Query and enter this
expression where
> > it says
> > > Update To in the PartNum field:
> > > MyUpDateField
> >
> > Perfect. Should have thought of that myself. Thanks!
>
> Well, I spoke a bit too soon. I still have a problem. It doesn't look as > though the Trim functions work in the A2K query grid. No error,
they just > don't trim the spaces. I tried the MyUpdateField:Trim([partnum]) as
an > Update query, a MakeTable query and as a Select query. Trim just plain > doesn't work. Surprising.
>
>
> > > PC Datasheet
> > > Your Resource For Help With Access, Excel And Word Applications
> > > re******@pcdatasheet.com
> > > www.pcdatasheet.com
> > >
> > >
> > > "Randy Harris" <ra***@SpamFree.com> wrote in message
> > > news:aU***************@newssvr31.news.prodigy.com. ..
> > > > I imported records into a table, later found out that many of them had
> > > > trailing spaces in one of the fields. If I'd caught it
sooner, I > could
> > have
> > > > trimmed the spaces before the import.
> > > >
> > > > This wouldn't work (nothing changed):
> > > > UPDATE tblManuals SET tblManuals.PARTNUM = Trim([partnum]);
> > > >
> > > > Would someone please tell me how to do an update query that
will trim
> > the
> > > > spaces?
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
"Randy Harris" <ra***@SpamFree.com> wrote in message news:<o0*****************@newssvr19.news.prodigy.c om>... Good guess, but in this case it was a 160. Anyone have a clue what a
Chr(160) is, or even better, how she got it in there in the first place?
(Data was entered in Excel, then imported, and yes, I went back to the
original spreadsheet and confirmed it was that way in there!)
Hmmm... It just occurred to me that a 160 is a space char (32) with the
highest order bit turned on. There must be some sort of a connection there.
But, I'm bewildered.
Private Sub Randy()
Dim I As Integer, S As String
S = DLookup("PartNum", "tblManuals", "ManualID=189")
Debug.Print S
For I = 1 To Len(S)
Debug.Print I; Asc(Mid(S, I, 1))
Next I
End Sub
93-027192 - NX2
1 57
2 51
3 45
4 48
5 50
6 55
7 49
8 57
9 50
10 32
11 45
12 32
13 78
14 88
15 50
16 32
17 32
18 160
19 32
From http://www.unicode.org/charts/PDF/U0000.pdf for Basic Latin, it
appears that 160 is a NBSP (No break space). It makes sense that Trim
would not delete this character. You can create a public function
TrimNBSP() that will get rid of them and then put the function in an
update query. Air code follows:
Public Function TrimNBSP(varIn As Variant) As Variant
Dim strTemp As String
Dim strChar As String
Dim lngI As Long
Dim lngLen As Long
TrimNBSP = varIn
If IsNull(varIn) Then Exit Function
If varIn = "" Then Exit Function
lngLen = Len(varIn)
strTemp = ""
For lngI = 1 To lngLen
strChar = Mid(varIn, lngI, 1)
If Asc(strChar) <> 160 Then
strTemp = strTemp & strChar
End If
Next lngI
TrimNBSP = strTemp
End Function
Then something like:
UPDATE tblManuals SET PartNum = TrimNBSP([PartNum]) WHERE
ManualID=189;
This will get you by until some better answers come along. This
technique should work for all versions of Access.
James A. Fortune
Hi Randy,
It looks like you have your questions answered already, but if you wanted to
build a quick-reference table for Ascii codes, it's quite simple to create
one.
Public Function fBuildAsciiTable()
Dim MyDB As DAO.Database
Dim MyRst As DAO.Recordset
Set MyDB = CurrentDb
Set MyRst = MyDB.OpenRecordset("tblChr2Asc", dbOpenDynaset)
Dim i As Integer
With MyRst
For i = 0 To 255
.AddNew
!MyChr = i
!MyAsc = Chr$(i)
.Update
Next i
.Close
End With
Set MyRst = Nothing
Set MyDB = Nothing
End Function
It is also possible to check / convert ascii codes (one at a time) in the
Debug window... i.e.
?Chr$(160)
Don
"Randy Harris" <ra***@SpamFree.com> wrote in message
news:o0*****************@newssvr19.news.prodigy.co m... Good guess, but in this case it was a 160. Anyone have a clue what a
Chr(160) is, or even better, how she got it in there in the first place?
(Data was entered in Excel, then imported, and yes, I went back to the
original spreadsheet and confirmed it was that way in there!)
On Sun, 11 Jul 2004 19:31:19 GMT, "Don Leverton"
<le****************@telusplanet.net> wrote: Hi Randy,
It looks like you have your questions answered already, but if you wanted to
build a quick-reference table for Ascii codes, it's quite simple to create
one.
Public Function fBuildAsciiTable()
Dim MyDB As DAO.Database
Dim MyRst As DAO.Recordset
Set MyDB = CurrentDb
Set MyRst = MyDB.OpenRecordset("tblChr2Asc", dbOpenDynaset)
Dim i As Integer
With MyRst
For i = 0 To 255
.AddNew
!MyChr = i
!MyAsc = Chr$(i)
.Update
Next i
.Close
End With
Set MyRst = Nothing
Set MyDB = Nothing
End Function
It is also possible to check / convert ascii codes (one at a time) in the
Debug window... i.e.
?Chr$(160)
You can do it all at once in the debug window, too. Try:
for i = 0 to 127: print i,chr$(i),i+128,chr$(i+128):next i
Note that the formatting will be a bit messed up on the lines that
show chr$(9), chr$(10), and chr$(13) which are the tab, line feed and
carraige return characters.
mike This discussion thread is closed Replies have been disabled for this discussion. Similar topics
4 posts
views
Thread by Surendra |
last post: by
|
2 posts
views
Thread by Mike Leahy |
last post: by
|
1 post
views
Thread by Kunal |
last post: by
|
8 posts
views
Thread by Maxi |
last post: by
|
7 posts
views
Thread by Mark Carlyle via AccessMonster.com |
last post: by
|
3 posts
views
Thread by Shapper |
last post: by
|
8 posts
views
Thread by rriness |
last post: by
|
3 posts
views
Thread by eholz1 |
last post: by
|
16 posts
views
Thread by ARC |
last post: by
| | | | | | | | | | | | | | | | | | | |
