By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,985 Members | 2,884 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,985 IT Pros & Developers. It's quick & easy.

update groups of fields with sets of conditions

P: 9
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:

Expand|Select|Wrap|Line Numbers
  1. UPDATE TBLE_2 INNER JOIN TBLE_1
  2.     ON TBLE_2.part_no = TBLE_1.PART_NO
  3. SET    TBLE_2.prime_vendor_change = "Y"
  4.      , TBLE_2.prime_vendor_change_date = Date()-1
  5.      , TBLE_2.mod_code_change = "Y"
  6.      , TBLE_2.mod_code_change_date = Date()-1
  7.      , TBLE_2.release_date_change = "Y"
  8.      , TBLE_2.release_date_change_date = Date()-1
  9. WHERE ((TBLE_2.prime_vendor_change<>[TBLE_1].[PRM_SUPL_VNDR_ID])
  10.   AND  (TBLE_2.prime_vendor_change_date<>[TBLE_1].[PRM_SUPL_VNDR_ID])
  11.   AND  (TBLE_2.mod_code_change<>[TBLE_1].[PART_MOD_CD])
  12.   AND  (TBLE_2.mod_code_change_date<>[TBLE_1].[PART_MOD_CD])
  13.   AND  (TBLE_2.release_date_change<>[TBLE_1].[PART_REL_DATE])
  14.   AND  (TBLE_2.release_date_change_date<>[TBLE_1].[PART_REL_DATE]));
Nov 23 '11 #1
Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,492
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.
Nov 23 '11 #2

100+
P: 255
If you are running the query in Access, simply correct Date()-1 into DateDiff("d",1,Date).
Nov 23 '11 #3

NeoPa
Expert Mod 15k+
P: 31,492
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).
Nov 23 '11 #4

P: 9
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.
Nov 23 '11 #5

NeoPa
Expert Mod 15k+
P: 31,492
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 :
Expand|Select|Wrap|Line Numbers
  1. UPDATE [Table1]
  2. SET    [Surname] = IIf([Married],[SpouseName],[Surname])
Nov 24 '11 #6

P: 9
I applied IIF to my update query which works perfectly!
Thanks a bunch!
Nov 24 '11 #7

NeoPa
Expert Mod 15k+
P: 31,492
Good to hear it. Not everybody gets that - so well done.
Nov 24 '11 #8

Post your reply

Sign in to post your reply or Sign up for a free account.