Trimming data and updating table info 1

[balllian]

I have the following data which populates certain fields within my database.

this appears as follows.'%20GBWTB0052E'

i need the data to appear as 'GBWTB0052E'

what is the best way to change what records appear with %20 to be chnaged so that they then appear within the database in the correct format.

Im currently using the find and replace function which is very labour intensive and wish to find an automated way of changing these records.

Thanks in advance

 

[Golom]

UPDATE myTable SET myField = Replace(myField, "%20", "" )

 

[balllian]

i have entered the following

UPDATE tTable_test SET [Name] = Replace([Name], "%20", "" );

but im getting this error message

Undefined function 'replace' in expression.

 

[PHV]

Write your own replace function in a standard code module, like this (ac2k or above):
Public Function myReplace(myString, myFind As String, myRepl As String)
If Trim(myString & "") <> "" Then
myReplace = Replace(myString, myFind, myRepl)
End If
End Function

And then, your SQL:
UPDATE tTable_test SET [Name] = myReplace([Name], '%20', '')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[balllian]

for the sql part u sent me im getting the following error message.

ambigious name, in query expression ,myreplace([Name], '%20',")

sorry i not too good at sql so nothing obvious is standing out to me which is saying its incorrect.

thanks again

 

[PHV]

BTW, did you already had a myReplace function defined somewhere ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[balllian]

yes i have another module which contains this. This is for another field i'm manipulating. Will this cause a problem?

 

[PHV]

Will this cause a problem?
Yes, an ambiguous name.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[balllian]

ok ive deleted the secondary module.

Ive got the following in SQL Statement but this doesnt seem to like something in the code.

UPDATE tTable_test SET Name = myReplace([Name], '%20',")

it comes back with wrong number of arguements used with function in query expression 'myreplace([Name],'%20',")

I have changing the end part to ,"") but this also doesnt work.

 

[PHV]

balllian, seems you've deleted the wrong function ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[balllian]

this is the first one ive got

Public Function myReplace(ByVal v_varIn As Variant) As Variant
If IsNull(v_varIn) Then
myReplace = Null
Else
myReplace = Replace(Left$(v_varIn, 9), "-", "/")
End If
End Function

and this is the second one

Public Function myReplace(myString, myFind As String, myRepl As String)
If Trim(myString & "") <> "" Then
myReplace = Replace(myString, myFind, myRepl)
End If
End Function

Can these then be amalgamated into module?

 

[PHV]

Public Function myReplace(ByVal v_varIn As Variant) As Variant
If IsNull(v_varIn) Then
myReplace = Null
Else
myReplace = Replace(Left$(v_varIn, 9), "-", "/")
End If
End Function

Public Function myReplace2(myString, myFind As String, myRepl As String)
If Trim(myString & "") <> "" Then
myReplace2 = Replace(myString, myFind, myRepl)
End If
End Function

And the query:
UPDATE tTable_test SET [Name] = myReplace2([Name], '%20', '')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

Probably better to just give them unique names

Public Function myReplace[COLOR=red]2[/color](myString, myFind As String, myRepl As String)[COLOR=red] As String[/color]
If Trim(myString & "") <> "" Then
  myReplace[COLOR=red]2[/color] = Replace(myString, myFind, myRepl)
End If
End Function

And Then

UPDATE tTable_test SET Name = myReplace[COLOR=red]2[/color]([Name], '%20',")

 

[PHV]

Golom, for me an UDF used in SQL should return Variant to properly handle null values.

 

[balllian]

This is now working without any issues.

Thanks again

I have two further changes which are required to the data.
i have raised this on another message but i cant get to work.

i need to chnage this format to the following

05-apr-05:14:00:00

05/apr/05

is this possible to do in one update query?

 

[PHV]

This is the purpose of your 1st myreplace function ...

 

[balllian]

UPDATE tTable_test SET tTable_test = myreplace([Nunber Table]="/")
WHERE [tTable_test].[Nunber Table] Like '*-*:*';

With the above code this changes the date to a '0' for some reason????

 

[PHV]

UPDATE tTable_test SET [Nunber Table] = myreplace([Nunber Table])
WHERE [Nunber Table] Like '*-*:*';

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[balllian]

yes that works . what can i say brilliant!!!!

 

[olivier1978]

I have a table in which I would like to add a field and the value it contains from another table. I tried creating a new field and then using the UPDATE command but was unsuccessful to import data. When using the INSERT command, new data were copied following the last entry in the table.