Query VBA & Combo Box

[Ace2014]

Hello everyone,

I'm having a little problem.

Objective
Query where Dataset1 must = the criteria from data that is entered into a combo box (or if it's null)


Then it produces Dataset2

Here is the code I've come up with so far, but I'm getting a syntax error

SELECT tbl3_HierarchySeg.L1 
WHERE isNull[Form]![SelectScope]![ComboL1] 
OR [Form]![SelectScope]![ComboL1] = [Table][tbl3_HierarchySeg][L1], 
tbl3_HierarchySeg.L2
FROM tbl3_HierarchySeg;

Thanks in advance for your help

[ranman256]

WHERE [field] = isNull([Form]![SelectScope]![ComboL1] )

Plug your sql into the QBE designer and your syntax will always be right.

[lfpm062010]

ISNULL is a function. It uses like IIF(ISNULL(VARIABLE)

IS NULL is mostly use for comparision. It uses like TABLEFIELD is NULL.

There are other problem with your WHERE clause. Not sure what are you trying to do there (in red)

SELECT tbl3_HierarchySeg.L1
WHERE isNull[Form]![SelectScope]![ComboL1]
OR [Form]![SelectScope]![ComboL1] = [Table][tbl3_HierarchySeg][L1],
tbl3_HierarchySeg.L2
FROM tbl3_HierarchySeg;

[Ace2014]

WHERE [field] = isNull([Form]![SelectScope]![ComboL1] )

Plug your sql into the QBE designer and your syntax will always be right.

Sorry, what's the QBE designer and how do I use it?
I didn't see an option for anything other than build a query from the combo box under properties: data: row source

Here's my round 2 try:

SELECT tbl3_HierarchySeg.L1
Where [L1]= isNull[Form]![SelectScope]![ComboL1] 
OR [Form]![SelectScope]![ComboL1];
tbl3_HierarchySeg.L2;
FROM tbl3_HierarchySeg;

Iam getting clearer about how to phrase what I want in sql!!! sorry if this is frustrating for you guys.

where [field] = if(isNull(combo box);return all values;return values [field]=[combo box]

*I don't know how to command it to return all values

[Ace2014]

ISNULL is a function. It uses like IIF(ISNULL(VARIABLE)

IS NULL is mostly use for comparision. It uses like TABLEFIELD is NULL.

There are other problem with your WHERE clause. Not sure what are you trying to do there (in red)

SELECT tbl3_HierarchySeg.L1
WHERE isNull[Form]![SelectScope]![ComboL1]
OR [Form]![SelectScope]![ComboL1] = [Table][tbl3_HierarchySeg][L1],
tbl3_HierarchySeg.L2
FROM tbl3_HierarchySeg;

Ok, so I'm thinking this is what I want the query to say
where [field] = if(isNull(combo box);return all values;return values [field]=[combo box]

But I don't know how to say in query language return all values

This is just the criteria level,
I want the data from the another field, but I want it limited based off the combo box selection for another field

[lfpm062010]

It is all good.

If you are trying to return all values, you will need to use wild card (*). The wild card are use along with key word "LIKE".

SELECT tbl3_HierarchySeg.L1
Where [L1] LIKE IIF(isNull([Form]![SelectScope]![ComboL1]), "*", [Form]![SelectScope]![ComboL1])
FROM tbl3_HierarchySeg;

[ItsMe]

Quick observation
Where [L1] LIKE IIF(isNull([Forms]![SelectScope]![ComboL1]), "*", [Forms]![SelectScope]![ComboL1])

[Ace2014]

Great, thanks for being understanding.

I tried that and still got the syntax error.
As for LIKE, with the second part of the equation: [Form]![SelectScope]![ComboL1], I want those to be precise.

I decided to try building the query first outside of the report in the query design view.
I also decided that for this particular selection, I want the user to have to select an option from the combo box.
So I've added a message box

This is what I entered: where [L1]= if(IsNull(MsgBox("Please Select Level 1";"Ok");[Forms]![SelectScope]![ComboL1])
and it still didnt work
I even followed the hints they gave me

That looked pretty good to me

[Ace2014]

Too bad there's no "like" button haha

but unfortunately, that wasn't the answer

[lfpm062010]

Great catch. ItsMe. I was just copy and paste what Ace had without looking the actual syntax.

MsgBox is use in VB. I have never try to use that in a query.

To allow user enter a value, try this
where [L1]= [Please Select Level 1] ' That will give you a pop up window to allow you enter something.

I still not sure what to make of this.
where [L1]= if(IsNull(MsgBox("Please Select Level 1";"Ok");[Forms]![SelectScope]![ComboL1])

[Ace2014]

I got it!!! although without the textbox...I'm guessing I'd have to use VBA for that...voilà code!!

SELECT tbl3_HierarchySeg.L1, tbl3_HierarchySeg.L2FROM tbl3_HierarchySeg
WHERE (((tbl3_HierarchySeg.L1)=IIf(IsNull([Forms]![SelectScope]![ComboL1]),"*",[Forms]![SelectScope]![ComboL1])));