Replace Data 1

[Dummy99]

How do I replace data in a Table: Field1 by NewYr & Field2 if first 4 characters of Field1 = OldYr?

I have:
Dim dbs As Database
Dim strSQL, OldYr, NewYR as String
Set dbs = CurrentDb
OldYr = [Forms]![MM].[YR]
NewYr = OldYr + 1
strSQL = "UPDATE Customer SET Field1 = " & NewYr & "&" Customer SET Field2 & " " _
& "WHERE SET Left(Field1,4) = " & OldYr & " "
dbs.Execute strSQL

I've tried variations but nothing works.

Many thanks,
Dummy99

 

[bperry]

You want to have Field1 replaced by NewYr + Field2?

You have repeated the words CUSTOMER SET,
but they are only needed once.
Try this (if I got it right for you):

strSQL = "UPDATE Customer SET Field1 = " & NewYr & " + " & Field2 _
& " WHERE Left(Field1,4) = " & OldYr
-----------------------------------------------
By the way I find it easier to read if I separate that code like this: Do you:

strSQL = "UPDATE Customer SET "
strSQL = strSQL & "Field1= " & NewYear & "+" & Field2
strSQL = strSQL & " WHERE Left(Field1,4) = " & OldYr

maybe not, eh?

Anyway, that should clear up the syntax errors. But I'm still not sure that this will work for you. Are NewYr and Field2 both numeric datatypes that you intend to add together? Or are they char types that you want to string together.

Anyway, perhaps this will do it for you.

-----------------
bperry

 

[Dummy99]

Hello bperry,

You weren't sure if it would clear up the problem. Well, now you can be sure - it doesn't! Nonetheless, thanks for your help and I like the way you split the statement - it is easier to read and I'll use that format from now on.

By the way, what's with the "eh?"? are you Canadian? I am. What do tink about dat eh? I'm working in Vienna, Austria. It's very lonely out here with no one to talk to about these tech problems - unless of course you speak German. But thanks to the Internet & the forum I can get some help.

The NewYear and Field2 are strings to be put together. So I've changed the + sign to an & sign.

The result/problem I get is "Undefined Variable", i.e., it takes the Field2 syntax as a variable instead of the name of the field in my Table record. That's why I was showing "Customer Set" a second time - I was trying to define Field2 as the name of the Table field. My Table/records look like this:
Filed1 Field2
2000ABC ABC
2000WXYZ WXYZ
etc.

If you or anyone else have any more ideas, I'd appreciate it.

 

[bperry]

Okay, since they are strings, please try this.

strSQL = "UPDATE Customer SET "
strSQL = strSQL & "Field1= '" & NewYear & Field2 & "'"
strSQL = strSQL & " WHERE Left(Field1,4) = '" & OldYr & "'"

If that doesn't work, then do a print or display of the contents of strSQL, then copy&paste the result here so we can see what strSQL actually contains.

vperry

 

[Dummy99]

Hello vperry,

If I do exactly as you have it, I get a run-time error "Variable Not Defined". The so called variable is "Field2".

If I put Field2 in quotes, then it works but I get the wrong reults - I get "2001ABC" replaced by "2002Field2" instead of "2002" and the "ABC" which is in Field2. (By the way if it's possible to replace just the 4 left characters of Field1 with NewYear, that would be Ok. I've tried but I just can't get the correct SQL to do that.)

In addition to trying several variations, I changed the code to:
strSQL = "UPDATE Customer SET "
strSQL = strSQL & "Field1= '" & NewYear & "' & Tables!Customer.Field2"
strSQL = strSQL & " WHERE LEFT(Field1,4) = '" & OldYr & "'"

and I get
Run-Time Error '3061'
Too few parameters. Expected 1.


The SQL is:
UPDATE Customer SET Field1='2002' & Tables!Customer.Field2 WHERE Left(Field1,4)='2001'

Looking forward to hear from you,
Dummy99

 

[bperry]

please try this:
strSQL = "UPDATE Customer SET "
strSQL = strSQL & "Field1= '" & NewYear & "' + Field2"
strSQL = strSQL & " WHERE Left(Field1,4) = '" & OldYr & "'"

If that doesn't work, then do a print or display of the exact contents of strSQL (just before it is executed, that is), then copy&paste the result here so we can see what strSQL actually contains.

This usually isn't that tough. I must be having a mental block.

 

[Dummy99]

Hello bperry,

I posted this before but it seems it didn't get posted.

It works!

Many thanks & congratulations on your award!

Dummy99

 

[SQLBill]

Dummy99,

Since bperry's solution seemed to help you and you really appreciated it...why don't you give him a star? In your thread, go to the post that helped you and at the bottom left corner there is a link to mark the post as helpful or expert.

That's how bperry got the award...most stars for the week.

-SQLBill

(let posters know if they were helpful. Reward them with a star! - mark their post as helpful or expert)