VB/SQL Update Query with Substr 3

[hext2003]

Hi All,

I am writing an update query to update a text field.

The field looks like 000XXXXXXXXX.

Not all fields have a 000 in the front. I want to delete only the 000 and leave the rest of the data.

Here is what I have.


Dim SQL1 As String

SQL1 = "UPDATE [customers] SET [acct] = Replace (acct, '000', '') " _
& "WHERE SUBSTR(acct, 1, 3) = '000' "

DoCmd.RunSQL SQL1


This compiles but then I get an error message

Undefined Function 'SUBSTR' in expression. I have tried both SUBSTR and SUBSTRING. What am I doing wrong. Some research said you can't use substr with a text field that it can only be used with VarChar is this correct? if So how do I convert this to VarChar?

 

[jrbarnett]

Substr and substring are not valid expression in Access databases.
The equivalent in Access/Jet is Mid, for example:

SQL1 = "UPDATE [customers] SET [acct] = Replace (acct, '000', '') " _
& "WHERE mid (acct, 3, 1) = '000' "

Or a more optimised version:

SQL1 = "UPDATE [customers] SET [acct] = Replace (acct, '000', '') " _
& "WHERE acct LIKE '000*' "

This is faster because:
i) It is making one less VBA function call from within the SQL engine.
ii) Specifying the first 3 characters rather than using Like means that the query optimiser component can use any indexes present to search for relevant rows, rather than retrieve every row within the Customers table (called a Table scan).

John

 

[JBinQLD]

No drama mate, replace SUBSTR with Instr, it's the VB equiv

You could use a left with an iif in your situation too, check the help file if you want

JB

 

[JBinQLD]

Gnagh! Ops, sorry, yep, meant mid, not instr!

 

[hext2003]

THANK YOU! WORKS GREAT!

OK, can we complitcate it just a bit.

The string looks like 000XXXXXXXX

but some look like XXXXX000.

So I want to get rid of the 000 only if it's the 1st 3 char or the last 3 chars. There are cases where 000 is in the middle and I want to leave that alone. other wise a flat Replace('000', '' ) would work.

The Like '000*' worked great can I add an OR statement there and get the last 3 char. The lengths of the fields vary.

 

[lespaul]

Like 000* finds all records that START with 000

if you do *000 it will find all records that END with 000

(and for future reference doing this: *000* will find it ANYWHERE in the record: beginning, end or middle!)

HTH


Leslie

Have you met Hardy Heron?

 

[JBinQLD]

The Right function can be used to get the last x amount of chars, so iif(right(str,3)="000",left(str,len(str)-3),str) would work - the iif function is an immediate if, it's equivilant to

if right(str,3)="000" then
   return left(str,len(str)-3)
else
   return str
endif

hope this makes sense,

JB

 

[hext2003]

Getting Closer...

SQL1 = "UPDATE [customers] SET [acct] = Replace (acct, '000', '') " _
& "WHERE number LIKE '000*' or '*000' "


If I have an acct 000XXXXX000XXXX the like 000* is true and then the Replace(acct, '000', ' ') hits and it take out both the front 000 and the middle 000

leaving just XXXXXXX when I want XXXX000XXXXX

any thoughts?

JB how do I put that into an update query? or would this require additional coding to step through all of the records of the db?

 

[jrbarnett]

What are the x values? Are they other numbers, letters, punctuation marks?
Could there be a single zero amongst them legitimately?

John

 

[PHV]

SQL1 = "UPDATE [customers] SET [acct] = Mid(acct, 4) " _
& "WHERE acct LIKE '000*'"


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hext2003]

John, the X values are mainly numbers. Yes there could be a 0 or a series of 3 zeros in the Middle.

0001010001000
or
0002020002000

i want 1010001 and 2020002 to come back right now I get

1011 and 2022

 

[PHV]

Did you try my suggestion ?

 

[hext2003]

PHV :

Yes, your suggestion worked great for the 1st 3 zeros. What about in cases that have 3 zeros at the end?

How do you write a

SQL1 = "UPDATE [temp_trans] SET [number] = Mid(number, String - 3 chars ) " _
& "WHERE number LIKE '*000'"

and can this be combine into one SQL statement or should it be 2?

 

[hext2003]

i think I have it with

SQL1 = "UPDATE [cust] SET [acct] = left(acct, len(acct)-3) " _
& "WHERE acct LIKE '*000'"

DoCmd.RunSQL SQL1

SQL1 = "UPDATE [cust] SET [acct] = Mid(acct, 4) " _
& "WHERE acct LIKE '000*'"

DoCmd.RunSQL SQL1

can this be combine into one SQL??

 

[lespaul]

SQL1 = "UPDATE [cust] SET [acct] = left(acct, len(acct)-3) " _
& "WHERE acct LIKE '*000' OR acct LIKE '000*'"

Leslie

Have you met Hardy Heron?

 

[hext2003]

Leslie that's not quite it

Where acct Like '000*' I need SET [acct] = Mid(acct, 4)

and where acct Like '*000' I need SET [acct] = left(acct, len(acct)-3)