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.