473,721 Members | 2,216 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Update query help

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.PART NUM = Trim([partnum]);

Would someone please tell me how to do an update query that will trim the
spaces?

Nov 13 '05 #1
10 3272
Randy,

Create a new field in your query and enter this expression:
MyUpdateField:T rim([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******@pcdata sheet.com
www.pcdatasheet.com
"Randy Harris" <ra***@SpamFree .com> wrote in message
news:aU******** *******@newssvr 31.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.PART NUM = Trim([partnum]);

Would someone please tell me how to do an update query that will trim the
spaces?

Nov 13 '05 #2

"PC Datasheet" <no****@nospam. spam> wrote in message
news:b0******** *********@newsr ead3.news.atl.e arthlink.net...
Randy,

Create a new field in your query and enter this expression:
MyUpdateField:T rim([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******@pcdata sheet.com
www.pcdatasheet.com
"Randy Harris" <ra***@SpamFree .com> wrote in message
news:aU******** *******@newssvr 31.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.PART NUM = Trim([partnum]);

Would someone please tell me how to do an update query that will trim the spaces?


Nov 13 '05 #3
"Randy Harris" <ra***@SpamFree .com> wrote in message
news:rs******** **************@ newssvr28.news. prodigy.com...

"PC Datasheet" <no****@nospam. spam> wrote in message
news:b0******** *********@newsr ead3.news.atl.e arthlink.net...
Randy,

Create a new field in your query and enter this expression:
MyUpdateField:T rim([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:T rim([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******@pcdata sheet.com
www.pcdatasheet.com
"Randy Harris" <ra***@SpamFree .com> wrote in message
news:aU******** *******@newssvr 31.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.PART NUM = Trim([partnum]);

Would someone please tell me how to do an update query that will trim the spaces?



Nov 13 '05 #4
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. prodigy.com...
"Randy Harris" <ra***@SpamFree .com> wrote in message
news:rs******** **************@ newssvr28.news. prodigy.com...

"PC Datasheet" <no****@nospam. spam> wrote in message
news:b0******** *********@newsr ead3.news.atl.e arthlink.net...
Randy,

Create a new field in your query and enter this expression:
MyUpdateField:T rim([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:T rim([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******@pcdata sheet.com
www.pcdatasheet.com
"Randy Harris" <ra***@SpamFree .com> wrote in message
news:aU******** *******@newssvr 31.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.PART NUM = Trim([partnum]);
>
> Would someone please tell me how to do an update query that will

trim the
> spaces?
>
>
>



Nov 13 '05 #5
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******@veriz on.net> wrote in message
news:Dt******** ***********@nwr ddc02.gnilink.n et...
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. prodigy.com...
"Randy Harris" <ra***@SpamFree .com> wrote in message
news:rs******** **************@ newssvr28.news. prodigy.com...

"PC Datasheet" <no****@nospam. spam> wrote in message
news:b0******** *********@newsr ead3.news.atl.e arthlink.net...
> Randy,
>
> Create a new field in your query and enter this expression:
> MyUpdateField:T rim([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:T rim([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******@pcdata sheet.com
> www.pcdatasheet.com
>
>
> "Randy Harris" <ra***@SpamFree .com> wrote in message
> news:aU******** *******@newssvr 31.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.PART NUM = Trim([partnum]);
> >
> > Would someone please tell me how to do an update query that will trim the
> > spaces?
> >
> >
> >
>
>



Nov 13 '05 #6
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******** ******@newssvr3 1.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******@veriz on.net> wrote in message
news:Dt******** ***********@nwr ddc02.gnilink.n et...
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. prodigy.com...
"Randy Harris" <ra***@SpamFree .com> wrote in message
news:rs******** **************@ newssvr28.news. prodigy.com...
>
> "PC Datasheet" <no****@nospam. spam> wrote in message
> news:b0******** *********@newsr ead3.news.atl.e arthlink.net...
> > Randy,
> >
> > Create a new field in your query and enter this expression:
> > MyUpdateField:T rim([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:T rim([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******@pcdata sheet.com
> > www.pcdatasheet.com
> >
> >
> > "Randy Harris" <ra***@SpamFree .com> wrote in message
> > news:aU******** *******@newssvr 31.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.PART NUM = Trim([partnum]);
> > >
> > > Would someone please tell me how to do an update query that will

trim
> the
> > > spaces?
> > >
> > >
> > >
> >
> >
>
>



Nov 13 '05 #7
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("PartNu m", "tblManuals ", "ManualID=1 89")
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_djsteel e@NOSPAM_canada .com> wrote in message
news:4t******** ******@news04.b loor.is.net.cab le.rogers.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******** ******@newssvr3 1.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******@veriz on.net> wrote in message
news:Dt******** ***********@nwr ddc02.gnilink.n et...
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. prodigy.com...
> "Randy Harris" <ra***@SpamFree .com> wrote in message
> news:rs******** **************@ newssvr28.news. prodigy.com...
> >
> > "PC Datasheet" <no****@nospam. spam> wrote in message
> > news:b0******** *********@newsr ead3.news.atl.e arthlink.net...
> > > Randy,
> > >
> > > Create a new field in your query and enter this expression:
> > > MyUpdateField:T rim([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:T rim([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******@pcdata sheet.com
> > > www.pcdatasheet.com
> > >
> > >
> > > "Randy Harris" <ra***@SpamFree .com> wrote in message
> > > news:aU******** *******@newssvr 31.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.PART NUM = Trim([partnum]);
> > > >
> > > > Would someone please tell me how to do an update query that

will trim
> > the
> > > > spaces?
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>



Nov 13 '05 #8
"Randy Harris" <ra***@SpamFree .com> wrote in message news:<o0******* **********@news svr19.news.prod igy.com>...
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("PartNu m", "tblManuals ", "ManualID=1 89")
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
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 fBuildAsciiTabl e()
Dim MyDB As DAO.Database
Dim MyRst As DAO.Recordset

Set MyDB = CurrentDb
Set MyRst = MyDB.OpenRecord set("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******** *********@newss vr19.news.prodi gy.com...
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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
10235
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 View Query: Select Sq from ( Select substr(to_date(End_Date,"DD-MON-YYYY"),4), End_Date, Rank() Over (Partition by substr(to_date(End_Date,"DD-MON-YYYY"),4) Order by End_Date) As Sq
2
8536
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 example of what I'm trying to do is below: update (tbl_ind_mananas LEFT JOIN (select count(*) as count, (dubicacion || zona || manzana) as cod_manzana from tbl_censo_poblacion_1993 group by dubicacion, zona, manzana) tbl1 on relacion = cod_manzana) as...
1
5184
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 product sold in that transaction. I have a subtransaction table that maintains record of each subtransaction, with data such as: ProductID, Quantity, TransactionID etc. The Products table has fields: ProductID, UnitsSold, UnitsOrdered,...
8
3722
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: "Date","P1","P2","P3","P4","P5","P6","P7","P8","P9","P10","P11","P12","P13","P14","P15","P16","P17","P18","P19","P20","P21" 1/1/2005,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21 1/2/2005,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22
7
3529
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" Transactions = name of the table I want to update balance = name of the field i want to update daily balance= name of the query result that I want to move to the table
3
3448
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 the necessary records in it when testing it. I get the error "No value given for one or more required parameters." when I try to update the database. Can you tell me what am I doing wrong?
8
1901
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 other users in the database. I keep getting the message 'Operation must use an updateable query.' Process seems to work on some PC's, not others. Is there a difference in .Net framework versions? Or some other
3
2680
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 row of data, etc. then I thought it would be nice to do a select, and perhaps an update (the title of the image) on the same page.
16
3507
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 renaming the duplicate records? My thinking was to take the results of the duplicate query, and somehow have it number each line where there is a duplicate (tried a groups query, but "count" won't work), then do an update query to change the duplicate to...
0
8840
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
8730
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9367
Oralloy
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9215
jinu1996
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9064
tracyyun
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5981
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4753
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3189
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2130
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.