update groups of fields with sets of conditions

[suet]

I have a query that I like to run in SQL format, but it's not getting corret results. This is to update multiple fields with multiple conditions. Should I be using VB to apply this type of update? My preference is to run the query using macro. Does anyone out there has any suggestions?

Here are sample codes:

UPDATE TBLE_2 INNER JOIN TBLE_1
    ON TBLE_2.part_no = TBLE_1.PART_NO
SET    TBLE_2.prime_vendor_change = "Y"
     , TBLE_2.prime_vendor_change_date = Date()-1
     , TBLE_2.mod_code_change = "Y"
     , TBLE_2.mod_code_change_date = Date()-1
     , TBLE_2.release_date_change = "Y"
     , TBLE_2.release_date_change_date = Date()-1
WHERE ((TBLE_2.prime_vendor_change<>[TBLE_1].[PRM_SUPL_VNDR_ID])
  AND  (TBLE_2.prime_vendor_change_date<>[TBLE_1].[PRM_SUPL_VNDR_ID])
  AND  (TBLE_2.mod_code_change<>[TBLE_1].[PART_MOD_CD])
  AND  (TBLE_2.mod_code_change_date<>[TBLE_1].[PART_MOD_CD])
  AND  (TBLE_2.release_date_change<>[TBLE_1].[PART_REL_DATE])
  AND  (TBLE_2.release_date_change_date<>[TBLE_1].[PART_REL_DATE]));

[NeoPa]

Please read [code] Tags Must be Used.

As far as the question goes, if you have a QueryDef then it doesn't matter greatly whether it's invoked via macro or VBA code. The same is also true for a predefined SQL string. If you need to build the string before executing it then VBA would make a lot more sense.

I'm never going to recommend use of macros as I believe that is a shortcut to a dead-end and any time learning how to use them is essentially wasted time, but that isn't the answer to this particular question.

[colintis]

If you are running the query in Access, simply correct Date()-1 into DateDiff("d",1,Date).

[NeoPa]

That change may be more aesthetically pleasing for a coder but would have no effect on the running of the query I'm afraid (Of course you'd need to fix the fix as it provides tomorrow's date instead of yesterday's, but that's a minor point).

[suet]

Hmm..I just realize the sql codes did not really explain what I am trying to do.
I need to figure out if I can update "vendor chanage" and "vendor change date" based on one condition and "mod code change" and "mod code change date" with another condition within the same table. All 4 fields are in the same table. With other programming language, I can easily use case statement and code the criteria to apply the update value. Can I do this without VBA code? The query will run with querydef, without building a string. Hope this is littel clear.

[NeoPa]

You can do that (and No - You didn't ask that remotely clearly in post #1). Even now you provide little in the way of detail, but I'll try to explain in more general terms for you.

IIf() can be used in this scenario, but not to update or not (It cannot control the process - it simply returns values). What you can do though is to update a field to its existing value if you don't want it updated, but a separate (new) value if you do.

Here's an example that should be self-explanatory :

UPDATE [Table1]
SET    [Surname] = IIf([Married],[SpouseName],[Surname])

[suet]

I applied IIF to my update query which works perfectly!
Thanks a bunch!

[NeoPa]

Good to hear it. Not everybody gets that - so well done.