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  3237 
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 thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Surendra |
last post by:
I have this query that I need to use in an Update statement to
populate a field in the table by the value of Sq
----------------------------------------------------------------------------
Inline...
|
by: Mike Leahy |
last post by:
Hello all,
This question is related to updating tables - is there any way to calculate or
update the values in a column in a table to the values in a field produced by
a query result? An...
|
by: Kunal |
last post by:
Hi,
I need some help on writing an update query to update "UnitsSold" field in
Products Table whenever I save a transaction. The transaction may contain
several "Subtransactions", one for each...
|
by: Maxi |
last post by:
There is a lotto system which picks 21 numbers every day out of 80
numbers.
I have a table (name:Lotto) with 22 fields (name:Date,P1,P2....P21)
Here is the structure and sample data:
...
|
by: Mark Carlyle via AccessMonster.com |
last post by:
I have this update query that I am trying to run. I know the syntax is
messed up but do not know how to correct it.
Select 'UPDATE', Transactions,'Set = where =
' From "Get Daily Balances"
...
|
by: Shapper |
last post by:
Hello,
I have created 3 functions to insert, update and delete an Access
database record. The Insert and the Delete code are working fine.
The update is not. I checked and my database has all...
|
by: rriness |
last post by:
I'm getting an inconsistent failure when trying to save
data in ADO.Net.
I'm using an Access database with a simple query - SELECT
StudentID, FirstName, LastName FROM Students - and have
no...
|
by: eholz1 |
last post by:
Hello PHP programmers.
I had a brilliant idea on one of my pages that selects some data from
my mysql database.
I first set the page up to display some info and an image, just one
item, with a...
|
by: ARC |
last post by:
Hello all,
So I'm knee deep in this import utility program, and am coming up with all
sorts of "gotcha's!".
1st off. On a "Find Duplicates Query", does anyone have a good solution for...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |
