Need assistance creating a specific query

[LiveByTheCoad]

Hi there,
I was hoping someone can help me create a specific query.
The data i'm trying to capture is basically from 5 combo boxes which have corresponding selections.
But what I need to do is even if options 2-5 aren't filled out I still need to capture the details.
I have the beginning of code but it only captures if all 5 fields are completed.
If anyone can assist that'd be great.

This is the code that I have so far:

SELECT tblFLSFeedback.*, tblProductOpportunity.ProductName, tblSalesOpportunityType.SalesOppType, tblProductOpportunity_1.ProductName, tblSalesOpportunityType_1.SalesOppType, tblProductOpportunity_2.ProductName, tblSalesOpportunityType_2.SalesOppType, tblProductOpportunity_3.ProductName, tblSalesOpportunityType_3.SalesOppType, tblProductOpportunity_4.ProductName, tblSalesOpportunityType_4.SalesOppType
FROM tblSalesOpportunityType AS tblSalesOpportunityType_4 INNER JOIN (tblProductOpportunity AS tblProductOpportunity_4 INNER JOIN (tblSalesOpportunityType AS tblSalesOpportunityType_3 INNER JOIN (tblProductOpportunity AS tblProductOpportunity_3 INNER JOIN (tblSalesOpportunityType AS tblSalesOpportunityType_2 INNER JOIN (tblProductOpportunity AS tblProductOpportunity_2 INNER JOIN (tblSalesOpportunityType AS tblSalesOpportunityType_1 INNER JOIN (tblProductOpportunity AS tblProductOpportunity_1 INNER JOIN (tblSalesOpportunityType INNER JOIN (tblProductOpportunity INNER JOIN tblFLSFeedback ON tblProductOpportunity.ProductID = tblFLSFeedback.ProdOpp1) ON tblSalesOpportunityType.SalesOppID = tblFLSFeedback.SaleOpp1) ON tblProductOpportunity_1.ProductID = tblFLSFeedback.ProdOpp2) ON tblSalesOpportunityType_1.SalesOppID = tblFLSFeedback.SaleOpp2) ON tblProductOpportunity_2.ProductID = tblFLSFeedback.ProdOpp3) ON tblSalesOpportunityType_2.SalesOppID = tblFLSFeedback.SaleOpp3) ON tblProductOpportunity_3.ProductID = tblFLSFeedback.ProdOpp4) ON tblSalesOpportunityType_3.SalesOppID = tblFLSFeedback.SaleOpp4) ON tblProductOpportunity_4.ProductID = tblFLSFeedback.ProdOpp5) ON tblSalesOpportunityType_4.SalesOppID = tblFLSFeedback.SaleOpp5;

 

[GinaWhipp]

Hmm, I got a little problem with the Tables. I see Fields like ProdOpp1. ProdOpp2, ProdOpp3, etc... which tells me your tables are more like a spreadsheet than a Relational Database. You should really rethink that as it does make querying the data a bit more challenging.

That said, you going to need to add...

Or Is Null

...to every one of those so that it's not dependant on all the Fields being filled in.

 

[LiveByTheCoad]

Thanks for your speedy reply.
Where would be the best place to the Or Is Null statement in the coding?

 

[GinaWhipp]

On every line that has Criteria so it should look like...

tblProductOpportunity_2.ProductID = tblFLSFeedback.ProdOpp3 Or Is Null

 

[LiveByTheCoad]

I wasn't able to have any luck with that one Gina,
I have placed in code that looks like this:

SELECT tblFLSFeedback.*, tblProductOpportunity.ProductName, tblSalesOpportunityType.SalesOppType, tblProductOpportunity_1.ProductName, tblSalesOpportunityType_1.SalesOppType, tblProductOpportunity_2.ProductName, tblSalesOpportunityType_2.SalesOppType, tblProductOpportunity_3.ProductName, tblSalesOpportunityType_3.SalesOppType, tblProductOpportunity_4.ProductName, tblSalesOpportunityType_4.SalesOppType
FROM tblSalesOpportunityType AS tblSalesOpportunityType_4 INNER JOIN (tblProductOpportunity AS tblProductOpportunity_4 INNER JOIN (tblSalesOpportunityType AS tblSalesOpportunityType_3 INNER JOIN (tblProductOpportunity AS tblProductOpportunity_3 INNER JOIN (tblSalesOpportunityType AS tblSalesOpportunityType_2 INNER JOIN (tblProductOpportunity AS tblProductOpportunity_2 INNER JOIN (tblSalesOpportunityType AS tblSalesOpportunityType_1 INNER JOIN (tblProductOpportunity AS tblProductOpportunity_1 INNER JOIN (tblSalesOpportunityType INNER JOIN (tblProductOpportunity INNER JOIN tblFLSFeedback ON tblProductOpportunity.ProductID = tblFLSFeedback.ProdOpp1 Or Is Null) ON tblSalesOpportunityType.SalesOppID = tblFLSFeedback.SaleOpp1 Or Is Null) ON tblProductOpportunity_1.ProductID = tblFLSFeedback.ProdOpp2 Or Is Null) ON tblSalesOpportunityType_1.SalesOppID = tblFLSFeedback.SaleOpp2 Or Is Null) ON tblProductOpportunity_2.ProductID = tblFLSFeedback.ProdOpp3 Or Is Null) ON tblSalesOpportunityType_2.SalesOppID = tblFLSFeedback.SaleOpp3 Or Is Null) ON tblProductOpportunity_3.ProductID = tblFLSFeedback.ProdOpp4 Or Is Null) ON tblSalesOpportunityType_3.SalesOppID = tblFLSFeedback.SaleOpp4 Or Is Null) ON tblProductOpportunity_4.ProductID = tblFLSFeedback.ProdOpp5 Or Is Null) ON tblSalesOpportunityType_4.SalesOppID = tblFLSFeedback.SaleOpp5 Or Is Null;

 

[GinaWhipp]

Hmm, let's try something... as those fields may not be NULL but just empty. Copy/paste the below into a new query and see if it works...

[FONT=Verdana][COLOR=black][COLOR=black][FONT=Verdana]SELECT tblFLSFeedback.*, tblProductOpportunity.ProductName, tblSalesOpportunityType.SalesOppType, tblProductOpportunity_1.ProductName, tblSalesOpportunityType_1.SalesOppType, tblProductOpportunity_2.ProductName, tblSalesOpportunityType_2.SalesOppType, tblProductOpportunity_3.ProductName, tblSalesOpportunityType_3.SalesOppType, tblProductOpportunity_4.ProductName, tblSalesOpportunityType_4.SalesOppType[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]FROM tblSalesOpportunityType AS tblSalesOpportunityType_4 INNER JOIN (tblProductOpportunity AS tblProductOpportunity_4 INNER JOIN (tblSalesOpportunityType AS tblSalesOpportunityType_3 INNER JOIN (tblProductOpportunity AS tblProductOpportunity_3 INNER JOIN (tblSalesOpportunityType AS tblSalesOpportunityType_2 INNER JOIN (tblProductOpportunity AS tblProductOpportunity_2 INNER JOIN (tblSalesOpportunityType AS tblSalesOpportunityType_1 INNER JOIN (tblProductOpportunity AS tblProductOpportunity_1 INNER JOIN (tblSalesOpportunityType INNER JOIN (tblProductOpportunity INNER JOIN tblFLSFeedback ON tblProductOpportunity.ProductID = tblFLSFeedback.ProdOpp1 Or = "") ON tblSalesOpportunityType.SalesOppID = tblFLSFeedback.SaleOpp1 Or = "") ON tblProductOpportunity_1.ProductID = tblFLSFeedback.ProdOpp2 Or = "") ON tblSalesOpportunityType_1.SalesOppID = tblFLSFeedback.SaleOpp2 Or = "") ON tblProductOpportunity_2.ProductID = tblFLSFeedback.ProdOpp3 Or = "") ON tblSalesOpportunityType_2.SalesOppID = tblFLSFeedback.SaleOpp3 Or = "") ON tblProductOpportunity_3.ProductID = tblFLSFeedback.ProdOpp4 Or = "") ON tblSalesOpportunityType_3.SalesOppID = tblFLSFeedback.SaleOpp4 Or = "") ON tblProductOpportunity_4.ProductID = tblFLSFeedback.ProdOpp5 Or = "") ON tblSalesOpportunityType_4.SalesOppID = tblFLSFeedback.SaleOpp5 Or = ""; [/COLOR][/FONT]
[/COLOR][/FONT]

 

[LiveByTheCoad]

Didn't have any luck with that one.
I believe they are cascading combo boxes if that helps at all?

 

[LiveByTheCoad]

And the error message I'm receiving is:
Syntax error (missing operator) in query expression 'tblProductOpportunity.ProductID = tblFLSFeedback.ProdOpp 1 Or = "",

 

[GinaWhipp]

Yeah, that is why I said post into a new query because I thought that might happen. Cascading Combos... do they have deafults set or are you requerying after the selection of one or more?

 

[vbaInet]

Was just going to point out that the correct syntax is:

WHERE [[COLOR="Blue"]TableName[/COLOR]].[[COLOR="blue"]FieldName[/COLOR]] = [Forms]![[COLOR="blue"]FormName[/COLOR]]![[COLOR="blue"]ComboboxName[/COLOR]] Or [Forms]![[COLOR="blue"]FormName[/COLOR]]![[COLOR="blue"]ComboboxName[/COLOR]] Is Null

Amend the bits in blue. And also check that the value the combo box is returning is correct. Lastly, you're better off doing this in code.

 

[torz]

the 2nd combo box re-queries after the first box selection has been made

 

[GinaWhipp]

vbaInet,

Not sure because it was working but only for one Combo Box at a time, see first post.

I have the beginning of code but it only captures if all 5 fields are completed

Maybe the better question... LiveByTheCoad, where are you using this code?

 

[vbaInet]

The first post doesn't have a WHERE clause and it shouldn't work anyway. It would throw a syntax error for "Or Is Null".

torz doesn't seem to be the OP. Unless s/he has a 2nd account.

 

[GinaWhipp]

Hmm, I didn't realize that... just read reply not name. Will wait to hear from OP...

 

[vbaInet]

I only noticed it because it was such a random comment

 

[torz]

different people same database...

the combo boxs work fine all 5 working as they should on the form

issue is the data that is stored in the main table are numbers instead of names

the query was to try and show the names in the main table

the query itself will work if you have the 5 options filled out but say you only have 2 of the 5 options filled out the query will bring nothing back

 

[vbaInet]

Oh alright. Did you see my post #10?

 

[GinaWhipp]

Okay, instead of having it do straight across for each one go down a level on the Criteria line...

Field 1 Feild 2
Criteria Blank
Blank Criteria

Understand?

 

[torz]

nope lost me sorry

I think I have found a different cascading combo box example that will store the names instead of the numbers so will give that a try

 

[GinaWhipp]

If your example does not work post back and I'll a screen shot of what I mean.