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?
10  3274 
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?
"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?
"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?
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?
>
>
>
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?
> >
> >
> >
>
>
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?
> > >
> > >
> > >
> >
> >
>
>
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?
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
"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
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!) 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 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
|
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...
|
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,...
|
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
|
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
| | |
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?
|
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
|
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.
|
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...
|
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...
|
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,...
| | |
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...
|
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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 presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
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...
| |
