Another Nested IIF Statement ? 1

[wvSusie]

Hoping someone can help with this wild and wooly IIF statement..


fields in table:
1aPN, 1aSN
1bPN, 1bSN
2aPN, 2aSN
2bPN, 2bSN

I was using this statement in my query when I was only capturing info for 2 parts...

Part Info: IIf([1aPN]="NONE","NONE",IIf([1aPN] Is Null,"",IIf([2aPN] Is Null,[1aPN] & "; P/N " & [1aPN] & "; S/N " & [1aSN],"1) " & [1aPN] & "; PN " & [1aPN] & "; SN " & [1aSN] & Chr(13) & Chr(10) & "2) " & [2aPN] & "; PN " & [2aPN] & "; SN " & [2aSN])))

...but now I've added fields for parts 1b and 2b...


There's never data in 1b unless 1a has data, and never data in 2b unless 2a has data.

In short, I want to return PN and SN for each group (1a,1b,2a,2b) IF there's data in the PN field. If all four groups have data, it would look like this...

1a) P/N: xxxx; S/N: xxxx
1b) P/N: xxxx; S/N: xxxx
2a) P/N: xxxx; S/N: xxxx
2b) P/N: xxxx; S/N: xxxx

It needs to always be in the order 1a,1b,2a,2b BUT nothing displayed if the field is Null, and "NONE" displayed if "None" is entered into 1aPN or 2aPN fields. For example:

1a) P/N: xxxx; S/N: xxxx
1b) P/N: xxxx; S/N: xxxx
2) P/N: xxxx; S/N: xxxx

or

NONE
2a) P/N: xxxx; S/N: xxxx
2b) P/N: xxxx; S/N: xxxx

Ultimately, I would like the "a" and "b" omitted if there is only one part for either group 1 or 2. For example:

1) P/N: xxxx; S/N: xxxx
2) P/N: xxxx; S/N: xxxx

or

1) NONE
2a) P/N: xxxx; S/N: xxxx
2b) P/N: xxxx; S/N: xxxx

or

1a) P/N: xxxx; S/N: xxxx
1b) P/N: xxxx; S/N: xxxx
2) P/N: xxxx; S/N: xxxx

or

1a) P/N: xxxx; S/N: xxxx
1b) P/N: xxxx; S/N: xxxx
2) NONE


Thank you for any assistance!!

 

[SkipVought]

hi,

Fields? Does thatn mean that you have 8 fields? Don't really understand???
[tt]
fields in table:
1aPN, 1aSN
1bPN, 1bSN
2aPN, 2aSN
2bPN, 2bSN
[/tt]
How are these fields related?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[wvSusie]

Yes, those are 8 of about 30 fields in the table.

No relation, other than its the part # and serial # for each of up to four parts - 2 for "issue" 1 (1a,1b), and 2 for "issue" 2 (2a,2b)

 

[dhookom]

Ultimately" have you considered normalizing your table structure so you wouldn't have to create "wild and wooly IIF statements"?

Duane
Hook'D on Access
MS Access MVP

 

[wvSusie]

hi duane,

yes. but, all the part data must be concatenated into one cell/field for reports and export to excel.

i considered your code for concatenating records from a one-to-many. but my VBA skills are "beginner" at best. i could not figure out how i'd get the additional formatting that is required- the "PN:" prefixes, carriage returns, etc.

if you would help with that, the table structure could be like..

tblExHist: ExHistID (pk), Discrepancy (ex: 1. window cracked 2. seat loose)

tblExHist_CompData: CompDataID (pk), ExHistID (fk), ExIssue (1, 2, 3), PartName, PartNbr, PartSerNbr

Discrepancy field is imported from a corporate db and cannot be "normalized". the analyst must enter part data for each "issue" and identify if it is issue 1, 2 ot 3 (we've never had more than 3 issues in one discrepancy; there's only one issue 99% of the time).

i dont think we'd want a 3rd table for the issues since 1) there's no way to extract the individual issues due to irregularities in the data entry, and 2) the analyst could easily enter a 1 or 2 to indicate which issue the part(s) data is for.

your thoughts?

 

[dhookom]

The concatenate function in a query might look something like:

SELECT *, Concatenate("SELECT ExIssue & ',  PName: ' & PartName & ', P/N:' & PartNbr & ', S/N:' & PartSerNbr FROM tblExHist_CompData WHERE exHistID = " & ExhistID, Chr(13) & Chr(10))
FROM tblExHist;

Duane
Hook'D on Access
MS Access MVP

 

[wvSusie]

Hi Duane,

I'm still trying to get this to work, but not having much joy. Am I on the right track?


SELECT Concatenate([tblCompAnal].[Issue#] & ", PN: " & [tblCompAnal].[PartName] & "', P/N:" & [tblCompAnal].[PartNbr] & ", S/N:" & [tblCompAnal].[PartSerNbr],Chr(13) & Chr(10)) AS Part Data, *
FROM tblExHist LEFT JOIN tblCompAnal ON tblExHist.ExHistID = tblCompAnal.ExHistID;

Running this gives me error '3078': ... cannot find the input table or query ', PN: ', P/N:, S/N:'. Make sure it exists and that its name is spelled correctly.

 

[dhookom]

The concatenate function has a full SQL statement as the first argument. Yours has some strings and field names with no criteria.

Assuming ExHistID is numeric:

SELECT Concatenate("SELECT [Issue#] & ',  PN: ' & [PartName] & ', P/N:' & [PartNbr] & ', S/N:' & [PartSerNbr] FROM tblCompAnal WHERE ExHistID = " & ExHistID ,Chr(13) & Chr(10)) AS Part Data, *
FROM tblExHist ;

Duane
Hook'D on Access
MS Access MVP

 

[wvSusie]

Thanks for the quick response!

ExHistID is text. Using..

SELECT Concatenate("SELECT [Issue#] & ', PN: ' & [PartName] & ', P/N:' & [PartNbr] & ', S/N:' & [PartSerNbr] FROM tblCompAnal WHERE ExHistID = """ & ExHistID & """" ,Chr(13) & Chr(10)) AS PartData, *
FROM tblExHist ;

the query returns all records in tblExHist and includes all fields in the table. It also includes PartData field but with no data.

 

[dhookom]

Try open the debug window (press Ctrl+G) and enter:

? Concatenate("SELECT [Issue#] & ',  PN: ' & [PartName] & ', P/N:' & [PartNbr] & ', S/N:' & [PartSerNbr] FROM tblCompAnal WHERE ExHistID = 'known value here'" ,Chr(13) & Chr(10))

Make sure you substitute in a good ExHistID value for known value here.

What do you get returned?
How about if you set a break point and step through the code?

Duane
Hook'D on Access
MS Access MVP

 

[wvSusie]

I currently have the SQL statement in a query (SQL view). I now see that it is actually returning the correct values in PartData, however the query results also include every record in tblExHist along with every field in that table. I assumed it wasn't returning anything in PartData because I currently only have 1 test record in tblCompAnal and it was several thousand records down in the query results.

I entered your code into the immediate window.. when I click run, it opens a box for me to select a macro.. uggh! i tried both with and without the single quotation marks. again, i'm a total newb with VBA.

 

[dhookom]

In the debug window you just enter the expression and press enter.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

the query results also include every record in tblExHist
What about this ?

SELECT DISTINCT ExHistID, Concatenate("SELECT [Issue#] & ',  PN: ' & PartName & ', P/N:' & PartNbr & ', S/N:' & PartSerNbr FROM tblCompAnal WHERE ExHistID='" & ExHistID & "'", Chr(13) & Chr(10)) AS PartData
FROM tblExHist

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[wvSusie]

Duane - In the debug window, I get...

1A, PN: FAN, RACK COOLING, P/N:xxxxxx, S/N:xxx
1B, PN: FAN THERMAL SWITCH, P/N:xxxx, S/N:xxx
1C, PN: EXHAUST FLOW CIRCUIT BREAKER, P/N:xxx-xxx-xx, S/N:xxxx
1D, PN: FLOW SWITCH ASSY, P/N:xxxx-xx, S/N:xxxx

...which looks good. The issue now is the query returns every field in the tblExHist table (along with the new PartData field).

Also, how would I add additional fields from tblCompAnal to the query.. Can I add just that table to the query design view and select the fields I want? There is a one-to-many relationship between the 2 tables - do I leave that intact in the query design?


PHV - I get an error message "The syntax of the subquery in this expression is incorrect."

 

[dhookom]

wvSusie,
The "*" in the SELECT clause causes every field from tblExHist to display. You can change this as desired.

If you want to add more fields from tblCompAnal, you probably need to insert them inside the Concatenate() function.

Duane
Hook'D on Access
MS Access MVP

 

[wvSusie]

Thank you Duane!

 

[wvSusie]

Install Info: Concatenate("SELECT [Issue#] & ') ' & [InstallDate] & '; TOW:' & [PartTOW] FROM tblCompAnal WHERE ExHistID = """ & [ExHistID] & """",Chr(13) & Chr(10))

returns...

1A) 10/11/03; TOW:2681
1B) ; TOW:
1C) ; TOW:EI COMPONENT - NOT TRACKED
1D) 05/01/11; TOW:247

I would like it to return..

1A) INSTL'D 10/11/03; TOW:2681
1C) TOW:EI COMPONENT - NOT TRACKED
1D) INSTL'D 05/01/11; TOW:247

or

1A) INSTL'D 10/11/03; TOW:2681
1B) NONE
1C) TOW:EI COMPONENT - NOT TRACKED
1D) INSTL'D 05/01/11; TOW:247


1) why do i need 3 sets of quotes around "& [ExHistID] &"?

2) how can i prefix the [InstallDate] with "INSTL'D" (minus the quotes)? the apostrophe is giving me grief.

3) how can i have it display "NONE" if the [InstallDate] and [PartTOW] are null for one or more "issues"?

 

[dhookom]

You need three sets because ExHistID is text. If it was numeric, you would remove two of them. You could also use a single apostrophe if ExHistID never contains an apostrophe.
Modify your query to filter out records with no TOW

Install Info: Concatenate("SELECT [Issue#] & ')  INSTL''D:' + [InstallDate] & '; TOW:' + [PartTOW] FROM tblCompAnal WHERE ExHistID = '" & [ExHistID] & "' AND [Issue#] is not Null",Chr(13) & Chr(10))

You could also check the length of the returned string and use IIf() to return "NONE".

Duane
Hook'D on Access
MS Access MVP