X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Very Long If Statement ? If Or ? If And ?, Any Version    
post Jul 30 2019, 09:24 AM

Posts: 5,573
Joined: 2-November 04
From: Downey, CA

This could also be done in Access, but I posted here in Excel forum

My company has about 20 offices... what's so crazy is that the job numbers include the office number as part of the job number, and 2 offices can have the same job number! (Only 2 max)

How do you know exactly what office the job belongs to? If the 5-digit sequential job number starts with a 6 or a zero! The office number is in the 4th position of the 10-digit job number, but in the majority of cases, the job number is not unique... So, for example, San Francisco and Los Angeles are both office#4, but if the last 5-digits of the job number starts with a zero, the job was in Los Angeles and if it
(the last five digits of the job number) starts with a SIX then the job was started by the Los Angeles office

My question is this: Is the only way I can write a query/make-table query to have a massive, gargantuan IF statement to determine what office the job originated in?

Another example:
Anaheim office is office #4 and the Chicago office is ALSO #4 - but the 3rd section of the Anaheim sequential job #'s start with zero, like
Go to the top of the page
post Jul 30 2019, 09:50 AM

Posts: 2,679
Joined: 4-February 07
From: USA, Florida, Delray Beach

If you could actually Post some Sample Data along with expected results, that would be very helpful. Try to cover as many contingencies as possible.
Go to the top of the page
post Jul 30 2019, 10:11 AM

Posts: 5,573
Joined: 2-November 04
From: Downey, CA


You can kind of see what I did here - luckily, this is a one-and-done, but inquiring minds want to know

Thanks for chiming in !!


EDIT: the screen shot is from another tab, where I "forced" a unique value for the office, like Dallas and Los Angeles are both office #2, but the Job numbers for Dallas start with "6" in that 3rd section of the job #, with the format XXX-XX-XXXXX So Los Angeles is office number "2," and Dallas is 2X, just for the purpose of populating the office names
This post has been edited by bakersburg9: Jul 30 2019, 10:15 AM
Attached File(s)
Attached File  IFchallenge.png ( 40.23K )Number of downloads: 3
Attached File  IFchallengeExcel.zip ( 10.43K )Number of downloads: 2
Go to the top of the page
post Jul 30 2019, 12:25 PM

Posts: 1,003
Joined: 12-November 03
From: Iowa Lot

In general, if I have numerous "ifs" I will build a table. In Access, I use a join query. In Excel, I use vlookup. Either way, I compare to a table with

if Value then result

This can help clean up complex if statements, especially for simple sub-constructs. You may need to add stuff to the if statement for exceptions, but this approach works for me.
Go to the top of the page
post Jul 30 2019, 01:39 PM

Posts: 5,573
Joined: 2-November 04
From: Downey, CA

Thanks for your reply - that's very helpful - I was just curious on suggestions on how to handle this in a more 'organic,' ongoing situation - fortunately for me and the task at hand, this is just a huge block of static data, and just basically a "one-and-done" scenario - sometimes, the cost/benefit factor doesn't warrant taking the time to construct a ginormous IF/AND statement or whatever, I was ok with the way I accomplished this task, because it went pretty quick, but I appreciate your input - I need to get better at working with tables in Excel

Thanks again! cool.gif

Go to the top of the page
post Jul 30 2019, 04:44 PM

UtterAccess VIP
Posts: 3,620
Joined: 19-August 03
From: Auckland, Little Australia

My rule of thumb, and I am sure many others, is if it can be managed/maintained in data, as opposed to code or formula, then that is the way to go.

Having a table with data is preferable than having values in a formula in my opinion. Unless those values are NEVER going to change, and performance is paramount, then a formula might be OK, but for me, I prefer maintainability over a slight performance gain.

Beer, natures brain defragging tool.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    20th October 2019 - 09:34 PM