Nested IIF issue

Spidre23

Registered User.
Local time
Today, 23:41
Joined
Oct 10, 2012
Messages
13
Howdy guys,

hope this is the right place to post this.

I can't get this nested IIF to work; not sure what is wrong....

name: IIf(IsNull([TempVendorScopes].[vendor_standard]);IIf(IsNull([TempVendorScopes].[vendor_name_we]);[TempVendorScopes].[vendor_name];[TempVendorScopes].[vendor_name_we]);[TempVendorScopes].[vendor_standard])

I have a table TempVendorScopes with three columns:
- vendor_name
- vendor_name_we
- vendor_standards

vendor_name contains local (untranslated) vendor names

vendor_name_we is filled only when vendor_name was in a different language and vendor_name_we contains a translation of vendor_name.

vendor_standard contains a standardized name if different vendor_names and/or vendor_name_we address the same vendor.

For instance:

vendor_name...........vendor_name_we..................vendor_standard
1 kaas BV..................Cheese Ltd.........................
2 Cheese Inc......................................................Cheese Group
3 Fromage Paris..........Cheese Paris........................Cheese Group

I want the formula to return (when filled) vendor_standard, else (when filled) vendor_name_we and else vendor_name.
Thanks for your help!
Best,

S
 
So If I am right.. You wish to display the name in the following way..
High priority to display the Standardized name, (if available)
Next priority TranslatedName (if available)
If none of the two is available you wish to display the Vendor Name..
You are complicating the IIF statement, use a simplified If for checking Null values.. It is called the Nz function.. It works basically like this..
Code:
Nz( the_Value_You_Wish_To_Display , If_Not_Available_what_do_you_want )
So based on that logic.. you should have it as..
Code:
Nz([TempVendorScopes].[vendor_standard],Nz([TempVendorScopes].[vendor_name_we],Nz([TempVendorScopes].[vendor_name],"N/A")))
 
pr2-eugin is right. For what you're trying to do Nz is 1/3 simpler.

But the reason yours didn't work is because you're separating the arguments with semicolons. They should be commas.
 
Thanks guys! Well, the whole comma / semicolon issue depends on your system settings; for me it's semicolons, that I am sure of. I'll try out the suggestion!

S
 
Thanks guys! Well, the whole comma / semicolon issue depends on your system settings; for me it's semicolons, that I am sure of.

That's a new one on me! I was assuming this was a calculated field in query designer because of

name: ...

(Which, btw, is not a good 'name' for a field because it is a very common property of ... well... everything)

In SQL a semicolon means the end of a line.

If it's VBA then the whole [TempVendorScopes].[vendor_standard] thing needs changing to Me![vendor_standard] or DLookups or some other way to get the values of the fields.

But in either case, I was not aware that you could change the syntax of SQL or VBA at such a fundamental level as to swap the meanings of semicolons and commas. If indeed you can and have please explain how.
 
@VilaRestal

You are right, it's a calculated field in query designer, not VBA.

You need to change something (not sure what) when you go to the region and language settings of your OS. I suppose it has to do with the list seperator, but am not sure.

S
 
The List Separator setting in Regional settings doesn't affect SQL or VBA syntax. And it would be a really bad idea if it did: it would mean all code written on another computer would need to be changed before it would work on yours.

I assure you commas separate function arguments in both languages (in all programming languages that I can think of) and semicolons terminate lines in SQL no matter your List Separator setting is set to.
 
Well, you seem to know this stuff way better than I do. But I can assure you that I use semicolons; even access' 'on-the-fly' suggestions for when you start typing a formula (e.g. 'Iif(...') shows the semicolons. I can't explain it, but it's the truth.

I wanted to upload a screenshot for ultimate convincing but I can only link to websites...

S
 
Spidre.. I am not trying to put my feet on this.. but I have never seen semicolos used in an IIF statement.. Also..
Well, you seem to know this stuff way better than I do.
Yes he does know a bit more than I do.. so if some advice is thrown my way I would explore why that is.. so I am NOT saying you should just listen to everything others say.. Just investigate about it..
I wanted to upload a screenshot for ultimate convincing but I can only link to websites...
About uploading pics.. look at this thread..
 
OK I believe you. It sounds horrible. Every other database, every sample code, every help file needs translating to work with your system.

If I were you I'd be trying to find out where that setting is and put it back to normal.
 
:D Seems like a fine mess! I'll definitely explore this a bit more and discuss it with my boss. Anyway, here's the screenshot!

attachment.php


S
 

Attachments

  • Untitled.jpg
    Untitled.jpg
    19.4 KB · Views: 161
I don't like the null test as it can leave you hanging if you have a zero len string and it makes it easier to read if I turn the test around so the real true part is always first:

Code:
iif([TempVendorScopes].[vendor_standard] & "" <> "", [TempVendorScopes].[vendor_standard], iif([TempVendorScopes].[vendor_name_we] & "" <>"",[TempVendorScopes].[vendor_name_we],[TempVendorScopes].[vendor_name]))
 
#10 No.

For country settings where "," is the decimal separator, ";" is used as separator in eg IIF. If you then attempt to run your db in Anglo-Saxon country settings, it runs just fine, so no reason to panic.
 
OK but code copied would need to be changed?
Does , become the line terminator in SQL?
I still think as a feature it sucks big time.
People with that country setting are forced to use different syntax to everyone else. Does this only happen in Access SQL or are there are other languages that respond to country settings in this way?
Or is it just in the query designer?
When switched to SQL view does it actually do it in normal syntax?
When executing SQL from VBA can you/do you have to use this syntax?
 
Yes, I have had to change , into ; several times when copying code from the net, but only when working in query designer or those control sources for labels and such. There's only 3 of us worldwide who use this application and we are all located in the Netherlands. So for us it isn't really a problem.

When switching from query designer to SQL, it transforms all semicolons to commas and vice versa.

When I write VBA, I use comma's just like you normal people. ;)

Anyway, my issue has been solved using KenHiggs solution because it seemed the easiest one for now. I am a beginner so I'm taking baby steps.

S
 
Thanks for explaining and sorry for doubting you.
I suppose I always assumed everyone adopted dots as radix points since all computer languages adopted it. It must cause confusion like it did here.

I don't entirely agree with Ken. Null and zero length strings are not the same and you don't always want to test for them both at once as such.

And if it's not a text field then Nz is always the way to do such a thing (replace Null with a default).

And for future reference, in your original code the way to test for null in SQL is

IIf(X IS NULL,,)

not the VBA way of

IIf(IsNull(X),,)
 
Ok, thanks Vila! :) Aaaaaand I think we are done here. Until next time!

S
 
...I don't entirely agree with Ken. Null and zero length strings are not the same and you don't always want to test for them both at once as such.

What? I didn't say that did I?
 
Sorry Ken. I took from you saying I don't like the null test as advice not to use it.
I wanted to make clear that only in those cases where you want to treat null and zero length string as the same would you do as you said.
 
I see - :)

I think the point for using a test like I posted would be to catch any instances where there may be a zero len string or a null. A zero len string would fail a null test. And actually, you could trim() it before testing to catch a spaces only entry.
 

Users who are viewing this thread

Back
Top Bottom