IIf queries

[SteveNapper]

Thanks for the help yesterday guys and girls. I have a few more questions though.

This question about the WHERE clause

I am using the following code with four combo boxes and it works brilliantly, now you told me what most of it means! I can use any combination of the 1st three fine. The fourth one works if only it is used, i.e. not null. If I use another combo box with it, i.e. look up records that satisfy two requirements, it shows everything, as if all boxes are null. Can you tell me why?

Also, brackets? Why do some start with two, and end with three or four? Can you explain the rule to me?

SELECT [Complexity].[ComplexityName], [Materials].[MaterialName], [Object].[ObjectName], [Process].[ProcessName], [Job].[Time], [Job].[Quantity]

FROM Process INNER JOIN ([Object] INNER JOIN (Materials INNER JOIN (Complexity INNER JOIN Job ON [Complexity].[ComplexityID]=[Job].[ComplexityID]) ON [Materials].[MaterialsID]=[Job].[MaterialsID]) ON [Object].[ObjectID]=[Job].[ObjectID]) ON [Process].[ProcessID]=[Job].[ProcessID]

WHERE ((([Complexity].[ComplexityName])=(IIf(IsNull([Forms]![JobQueryLookUp]![Complexity]),[ComplexityName],[Forms]![JobQueryLookUp]![Complexity]))) And (([Materials].[MaterialName])=(IIf(IsNull([Forms]![JobQueryLookUp]![Material]),[MaterialName],[Forms]![JobQueryLookUp]![Material]))) And (([Object].[ObjectName])=(IIf(IsNull([Forms]![JobQueryLookUp]![Object]),[ObjectName],[Forms]![JobQueryLookUp]![Object]))) And (([Process].[ProcessName])=(IIf(IsNull([Forms]![JobQueryLookUp]![Process]),[ProcessName],[Forms]![JobQueryLookUp]![Process]))));

This question about the FROM clause:


FROM Process INNER JOIN ([Object] INNER JOIN (Materials INNER JOIN (Complexity INNER JOIN Job ON [Complexity].[ComplexityID]=[Job].[ComplexityID]) ON [Materials].[MaterialsID]=[Job].[MaterialsID]) ON [Object].[ObjectID]=[Job].[ObjectID]) ON [Process].[ProcessID]=[Job].[ProcessID]

Are the ( before [Object] INNER JOIN AND Materials INNER JOIN, a bit like , in normal text list? I take it then that ON [Complexity]. etc describes the join. Just why is the ON the opposite order to the 1st part of the list? Is this standard SQL or Access specific, or doesn't it matter?

I ask, because I need to make this thing web based in the near future and am looking at using XML or Java to create forms to suit.

Thanks again and sorry it's so long.

Steve

NB
I am posting this in the correct forum aren't I? I've posted it here because it's do with the forms element of access.

 

[Petemush]

Can you provide a link to the thread you refer to from yesterday. I searched for threads with SteveNapper as the handle but I couldn't find any apart from this.

Just insert the thread number!

Cheers,

Pete

 

[SteveNapper]

thread702-347875

Try the above. I logged in as Snapper Visitor yesterday before I registered.

 

[Petemush]

Right,

I understand you're using this on a form but what controls are on this form? Is it just the four comboboxes or are there more?

The reason I ask is because I'm trying to work out what [ProcessName], [ObjectName], [MaterialsName] and [ComplexityName] all refer to in your WHERE clause.

From the error you describe, it would seem like you've told the query to use the wrong thing if [Forms]![JobQueryLookUp]![Process] is null. Sooooo, what do those four refer to?


In answer to your brackets question, every opening bracket must have a corresponding closing bracket. So if your code is correctly bracketed and you went through the entire thing and counted all the opening brackets, you should have the same number of closing brackets. Where they're positioned helps clarity when reading the query and also tells the query what you would like evaluated first, so:

(([test] = 1) AND ([test2] = 2)) OR ([test3] = 3)

For this to be true

test = 1 AND test2 = 2

OR

test3 = 3

however for

(([test] = 1) AND (([test2] = 2) OR ([test3] = 3)))

to be true

test = 1 AND test2 = 2

OR

test = 1 AND test3 = 3


Sorry if that's confusing! I'll try again if you want!

Cheers,

Pete

 

[SteveNapper]

I understand you're using this on a form but what controls are on this form? Is it just the four comboboxes or are there more?*1

*1Just the four combo boxes, and a button with a macro to run the query

The reason I ask is because I'm trying to work out what [ProcessName], [ObjectName], [MaterialsName] and [ComplexityName]*2 all refer to in your WHERE clause.

*2All refer to the field name in the table's and the query. I know I haven't been very Pro about that side. I have refered to the 'English' name, note the ID no which I should have. Once I get to the bottom of this I intend to re code a little better!

From the error you describe, it would seem like you've told the query to use the wrong thing if [Forms]![JobQueryLookUp]![Process]*3 is null. Sooooo, what do those four refer to?
*3Combo Box Name

From what you say about the brackets, have I not bracketed properly at the start, i.e. included the Complexity side (How ironic. This is so complex my brain is oozing out of my ears! Would you believe this is all part of a long distance PhD. I am an Engineer looking at Costing, Planning and Efficiency and want to use this to validate a model and prove increases in efficiency, etc. and I barely knew how to turn a PC on a few years ago!)