Query help

[rblivewire]

I have to make a query that goes: If ([English] AND [Spanish] AND
[French].........= Not NA,"Multilingual", English Only). My problem is
that I have about 20 columns of different languages. The criteria won't
work because it seem like ACCESS only takes the last 4 columns of
languages and will output either multilingual or english only. Any
suggestions of a better way to do this or how to fix this one?

 

[Phil]

Insufficient information.

You have a table with a fields called English, Spanish, French, etc.
etc. All Yes/No fields?

OK, now what exactly do you want to do? AND OR combinations can be
tricky if you are not used to Boolean logic. If it seems like it is
only taking certian groups, like (the last four) it is probably just a
screwed up AND/Or Combination.

"AND" requires everything. So if you have a statement that says
"English AND French AND Spanish AND German", ALL FOUR of those will have
to be Yes to be considered True. 3 out of four will not cut it.

Post your SQL, and tell us in English what you want to result in a true,
and a false. IF you you want English and ANY OTHER LANGUAGE to be
considered "multilingual, something like if English AND (Spanish OR
German or French...) would give a true for English and one or more other
languages...

Phil

 

[rblivewire]

[Tool Tracking List].[English/Malta], [Tool Tracking List].Spanish,
[Tool Tracking List].French, [Tool Tracking List].German, [Tool
Tracking List].Italian, [Tool Tracking List].Dutch, [Tool Tracking
List].[EU - Extended], [Tool Tracking List].Danish, [Tool Tracking
List].Swedish, [Tool Tracking List].Norwegian, [Tool Tracking
List].Finnish, [Tool Tracking List].Portugese, [Tool Tracking
List].[Greek/Cyprus], [Tool Tracking List].[EU - Central], [Tool
Tracking List].Czech, [Tool Tracking List].Hungarian, [Tool Tracking
List].Slovakian, [Tool Tracking List].Slovenian, [Tool Tracking
List].Polish, [Tool Tracking List].[EU - Baltic], [Tool Tracking
List].Latvian, [Tool Tracking List].Lithuanian, [Tool Tracking
List].Estonian, [Tool Tracking List].Other, [Tool Tracking
List].Russian, [Tool Tracking List].Turkish, [Tool Tracking
List].[Asia-Pacific], [Tool Tracking List].Chinese, [Tool Tracking
List].Japanese, [Tool Tracking List].Korean, IIf([Spanish] And [French]
And [German] And [Italian] And [Dutch] And [Danish] And [Swedish] And
[Norwegian] And [Finnish] And [Portugese] And [Greek/Cyprus] And
[Czech] And [Hungarian] And [Slovakian] And [Slovenian] And [Polish]
And [Latvian] And [Lithuanian] And [Estonian] And [Russian] And
[Turkish] And [Chinese] And [Japanese] And [Korean]="NA","English
Only","Multilingual") AS Expr1

The langauges are not yes no columns; If they have NA in them, then it
means that they are not needed. If they have anything else in them such
as A,P,S,L or other letters, it means that they are needed. basically I
am using all AND because if everything but ENGLISH is NA, it means it
is English only, else it is mulitlingual. So if ALL columns except
English are NA, then English Only, else multilingual.

 

[Phil]

OK. First of all, DANG. That is a lot of fields.
Second of all, I would not expect that to work the way you would think.
Anything between "AND" is going to be evaluated on it's own. So
instead of saying 'do all of these things equal "NA"' you are saying are
all of these things TRUE, (meaning each one has the value of 1), except
the last where Korean="NA". This would be the only one to evaluate
properly.

What this should say is

"IIf([Spanish]="NA" And [French]="NA" And [German]="NA"...,"English

Only","Multilingual") AS Expr1

The only problem you might run into is a limit in character count. IF
this is the case, an alternate way to do it would be to use

IIf([Spanish]&French]&[German]&...&[Korean]="NANANANANANANANANANANANANANANANA",,"English

Only","Multilingual") AS Expr1

Making sure you have one "NA" for each language. What this does is just
concatenate the string together into one string, which would look
"NANANANA...", then check to see oif it looks liek it woudl if it was
all "NA".

This has a lot less characters, (you are saving two quotes, two spaces
and an "AND" for each one. It also has the value of being searchable.
For instance, if you have a need to look for any "P"s, you could use
Instr() to find a P.

Let me know if this makes sense, and if you get it to work.

Good luck.

[Tool Tracking List].[English/Malta], [Tool Tracking List].Spanish,
[Tool Tracking List].French, [Tool Tracking List].German, [Tool
Tracking List].Italian, [Tool Tracking List].Dutch, [Tool Tracking
List].[EU - Extended], [Tool Tracking List].Danish, [Tool Tracking
List].Swedish, [Tool Tracking List].Norwegian, [Tool Tracking
List].Finnish, [Tool Tracking List].Portugese, [Tool Tracking
List].[Greek/Cyprus], [Tool Tracking List].[EU - Central], [Tool
Tracking List].Czech, [Tool Tracking List].Hungarian, [Tool Tracking
List].Slovakian, [Tool Tracking List].Slovenian, [Tool Tracking
List].Polish, [Tool Tracking List].[EU - Baltic], [Tool Tracking
List].Latvian, [Tool Tracking List].Lithuanian, [Tool Tracking
List].Estonian, [Tool Tracking List].Other, [Tool Tracking
List].Russian, [Tool Tracking List].Turkish, [Tool Tracking
List].[Asia-Pacific], [Tool Tracking List].Chinese, [Tool Tracking
List].Japanese, [Tool Tracking List].Korean, IIf([Spanish] And [French]
And [German] And [Italian] And [Dutch] And [Danish] And [Swedish] And
[Norwegian] And [Finnish] And [Portugese] And [Greek/Cyprus] And
[Czech] And [Hungarian] And [Slovakian] And [Slovenian] And [Polish]
And [Latvian] And [Lithuanian] And [Estonian] And [Russian] And
[Turkish] And [Chinese] And [Japanese] And [Korean]="NA","English
Only","Multilingual") AS Expr1

The langauges are not yes no columns; If they have NA in them, then it
means that they are not needed. If they have anything else in them such
as A,P,S,L or other letters, it means that they are needed. basically I
am using all AND because if everything but ENGLISH is NA, it means it
is English only, else it is mulitlingual. So if ALL columns except
English are NA, then English Only, else multilingual.

 

[rblivewire]

Thanks Phil... there was enough characters to fit the first one.
Appreciate the help!!