SQL - Programming a Query (combobox form, isnull,...)

[chemavecino]

Hi guys, I know this is not a SQL forum, but since you helped me before, I was hoping you can help me with this too.

First of all, I'll explain you what I'm trying to achieve here:
I have two tables. The fisrt one, fichero nuevo, has as first field (repetitive, not key) a six digit code, and then another 6 fields with info about that first code.
The second table is codigos alternativos, which breaks that 6 digit code into groups. For example:

Code: 111001. First "1" tells me that this code belongs to a "beverage", the second "1" that the beverage is cold;...

On this example, the fields in the codigos alternativos table are:


N1-----------Nº N1---N2-----Nº N2----N3---------Nº N3--- ... ---CODE
Beverage----1--------Cold---1---------Coffee-----1--------- ... ---111001


Now (sorry for taking so long), I,ve created a form with 4 comboboxes. I've managed to create de comboboxes in such way that once you've chosen the first digit group on the first combobox, you can only choose that group's sub-groups on the second combobox and so on.

The problem comes when creating the query that the form should return. It works great if I choose an option on every combobox, but I can't make it work for a "null" option, which should return every option.

Here is the SQL code:



SELECT [CODIGOS ALTERNATIVOS].N1, [CODIGOS ALTERNATIVOS].N2, [CODIGOS ALTERNATIVOS].N3, [CODIGOS ALTERNATIVOS].N5, Sum([FICHERO NUEVO].CANTIDAD) AS SumaDeCANTIDAD, Sum([FICHERO NUEVO].IMPORTE) AS SumaDeIMPORTE, Sum([FICHERO NUEVO].P) AS SumaDeP, Sum([FICHERO NUEVO].M) AS SumaDeM, Sum([FICHERO NUEVO].G) AS SumaDeG, Sum([FICHERO NUEVO].X) AS SumaDeX

FROM [FICHERO NUEVO] LEFT JOIN [CODIGOS ALTERNATIVOS] ON [FICHERO NUEVO].CODIGO = [CODIGOS ALTERNATIVOS].CODIGO
GROUP BY [CODIGOS ALTERNATIVOS].N1, [CODIGOS ALTERNATIVOS].N2, [CODIGOS ALTERNATIVOS].N3, [CODIGOS ALTERNATIVOS].N5, [CODIGOS ALTERNATIVOS].[nº N1]

HAVING ((([CODIGOS ALTERNATIVOS].N1)=[Formularios]![AV POR PRODUCTO]!
[LISTA_NIVEL1]) AND (([CODIGOS ALTERNATIVOS].N2)=IIf(IsNull([Formularios]![AV POR PRODUCTO]!
[LISTA_NIVEL2]),[N2] Like "*",[Formularios]![AV POR PRODUCTO]!
[LISTA_NIVEL2])) AND (([CODIGOS ALTERNATIVOS].N3)=IIf(IsNull([Formularios]![AV POR PRODUCTO]!
[LISTA_NIVEL2]),[N3] Like "*",IIf(IsNull([Formularios]![AV POR PRODUCTO]!
[LISTA_NIVEL3]),[N3] Like "*",[Formularios]![AV POR PRODUCTO]!
[LISTA_NIVEL3]))) AND (([CODIGOS ALTERNATIVOS].N5)=IIf(IsNull([Formularios]![AV POR PRODUCTO]!
[LISTA_NIVEL2]),[N5] Like "*",IIf(IsNull([Formularios]![AV POR PRODUCTO]!
[LISTA_NIVEL3]),[N5] Like "*",IIf(IsNull([Formularios]![AV POR PRODUCTO]!
[LISTA_NIVEL5]),[N5] Like "*",[Formularios]![AV POR PRODUCTO]!
[LISTA_NIVEL5])))))

ORDER BY [CODIGOS ALTERNATIVOS].[nº N1];


Sorry the code is not well arranged. Obviously I've been "programming" on the query menu.
Please ask me whatever you need, I probably explained myself awfully.

Thank you so much in advance!!!

PS: The form name is AV por product, and the name of the comboboxes are lista_nivel1, lista_nivel2, lista_mivel3, lista_nivel5.

[OBP]

Can you provide a simple copy of the database in Access 2007 format?
I am not very good with SQL, but have a lot of experience with Access Queries.
Perhaps you should not have a Null value ie if you do not actually select something in the combo the field defaults to something like N/A or even not identified

[chemavecino]

Can you provide a simple copy of the database in Access 2007 format?
I am not very good with SQL, but have a lot of experience with Access Queries.
Perhaps you should not have a Null value ie if you do not actually select something in the combo the field defaults to something like N/A or even not identified

Thanks OBP!

I will upload a version of it without the sensitive information as soon as I can.
As I said, I haven't actually done any SQL programming, I just thought you guys would understand the issue this way, but I realise that was not the way to explain it.

I hope that at least I've explained correctly what I'm trying to do with this query.

[OBP]

OK.

[chemavecino]

Here is the data base link:

https://www.dropbox.com/s/mmexgyfynaz4ws0/ANALISIS%20VENTAS.accdb?dl=0

If anyone can check it out and tell me why the query is not working when I leave comboboxes empty, wuld be much aprecciated.

Kind regards!!

[OBP]

I think this does basically what you want.

[chemavecino]

Works great!! Thank you so much!!

What I understood you are saying with that query's criteria ((([CODIGOS ALTERNATIVOS].N2) Like "*" & Forms![AV POR PRODUCTO]!LISTA_NIVEL2 & "*")), is that the criteria is Like "*", unless there's an option on that combobox, right?

That's what I wanted to "say", but in way easier sentence.

Thanks for the help OBP!

Edit: P.S.: Did you modify anything else besides field's criteria?

[OBP]

The Like "*" means the text being checked contains that combo text somewhere in it.
The ending "*" means that it does not have to be at the end of the text there could be something after it ie embedded in the text.
It is a quirk of SQL that it will accept nothing at all as well.

It is very useful for text searching as you only need to enter part of some text to find it.

[chemavecino]

I know how the * character works as a wildcard. What I meant was that when setting the criteria as the combobox text between *'s, what you do is setting the combobox text (embedded in a larger text) as your criteria, or have no criteria at all if the combobox is empty, because the criteria Like "*" & "" & "*" doesn't filter.

Anyhow, thanks again!!