By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,131 Members | 1,560 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,131 IT Pros & Developers. It's quick & easy.

Update query help

P: n/a
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?

Nov 13 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
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?

Nov 13 '05 #2

P: n/a

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


Nov 13 '05 #3

P: n/a
"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?



Nov 13 '05 #4

P: n/a
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?
>
>
>



Nov 13 '05 #5

P: n/a
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?
> >
> >
> >
>
>



Nov 13 '05 #6

P: n/a
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?
> > >
> > >
> > >
> >
> >
>
>



Nov 13 '05 #7

P: n/a
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?
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>



Nov 13 '05 #8

P: n/a
"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
Nov 13 '05 #9

P: n/a
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!)

Nov 13 '05 #10

P: n/a
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
Nov 13 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.