QUERY NOT GIVING THE EXPECTING RECORDS

[Wyoming]

Hello,

I have a query named EstadosHistoricos_AUX_Nuevo with the following fields:
ID_LEAD - Numeric
FECHA DE ALTA - Date
FECHA HCO LEAD - Date
ID_HCO_LEAD - Numeric
ID_TIPO_ACCION - Numeric

I am trying to run a second query on this one that gives me the following:
For those records with a FECHA HCO LEAD less than a certain date from a form ([Forms]![frmNavegacion]![SubformularioDeNavegación].[Form]![txtFechaInformes].[Value]) I want to obtain the maximum of ID_HCO_LEAD.
For the rest of the cases (FECHA HCO LEAD is null or FECHA HCO LEAD is greater than the date from the Form, I want to obtain a text string.

I have created this query:

Code: Select all

SELECT EstadosHistoricos_AUX_Nuevo.ID_LEAD, IIf([EstadosHistoricos_AUX_Nuevo].[FECHA_HCO_LEAD]<[Forms]![frmNavegacion]![SubformularioDeNavegación].[Form]![txtFechaInformes].[Value],Max([EstadosHistoricos_AUX_Nuevo].[ID_HCO_LEAD]),"Se debe poner a 1") AS MáxDeID_HCO_LEAD, EstadosHistoricos_AUX_Nuevo.FECHA_HCO_LEAD
FROM EstadosHistoricos_AUX_Nuevo
GROUP BY EstadosHistoricos_AUX_Nuevo.ID_LEAD, EstadosHistoricos_AUX_Nuevo.FECHA_HCO_LEAD;

But the query I have created is showing only those records where FECHA HCO LEAD is less than the reference date, and the records where FECHA HCO LEAD is null, but it doesn't show the records where FECHA HCO LEAD is greater than the Form date.
What is wrong in my query?

Thanks

[HansV]

I can't reproduce the problem. Does it make a difference if you change

[Forms]![frmNavegacion]![SubformularioDeNavegación].[Form]![txtFechaInformes].[Value]

to

[Forms]![frmNavegacion]![SubformularioDeNavegación]![txtFechaInformes]

If not, could you attach a stripped down and zipped copy of the database (without sensitive information) to a reply?

[Wyoming]

Hello Hans,

I was building a sample DB with only the relevants tables and queries in order to send it to you, since the real one has too much stuff in it and it is a split DB, and for some reason when I run the queries in this sample DB they returned the results that I was expecting.
So I have re-copied the queries from the sample to the original one and, you know what? They work
Don't ask me how or why but they work even though they're supposed to be the same.

So, thank you for your help once more. This time I didn't make you work too much...