Query not working when one field is not filled (1 Viewer)

VicNagib

New member
Local time
Today, 04:50
Joined
Jul 11, 2019
Messages
4
Hey there!

I'm working on a form so the user can perform an SQL query without realizing he's actually doing it. So, I have a form with 2 date fields and 4 comboboxes, linked in pairs so the second will display a list depending on what the user selected on the first.

The thing is, if the user does not choose an option in one of the comboboxes, the query does not work. This is the query I was originally using (showing here only the "where" bit):

Code:
WHERE (((dbo_temp_aof_reativos_ferramenta.area)=[Forms]![Indicadores]![Area2]) AND ((dbo_temp_aof_reativos_ferramenta.coordenacao)=[Forms]![Indicadores]![coordenacao2]) AND ((dbo_temp_aof_reativos_ferramenta.data_abertura) Between [Forms]![Indicadores]![DATAIN] And [Forms]![Indicadores]![DATAFIM]) AND ((dbo_temp_aof_reativos_ferramenta.jornada)=[Forms]![Indicadores]![jornada2]) AND ((dbo_temp_aof_reativos_ferramenta.subjornada)=[Forms]![Indicadores]![subjornada2]));
Here, dbo_temp_aof_reativos_ferramenta is my SQL DB connected to Access;
Indicadores is my form;
Area2, coordenacao2, jornada2 and subjornada2 are the comboboxes.

Searching online, I found a similar query using IFF for when the user does not want to select a value in one or more comboboxes, shown below:

Code:
WHERE (dbo_temp_aof_reativos_ferramenta.data_abertura Between Forms!Indicadores!DATAIN And Forms!Indicadores!DATAFIM) And IIf(Not IsNull(Forms!Indicadores!Area2),dbo_temp_aof_reativos_ferramenta.area=Forms!Indicadores!Area2) And IIf(Not IsNull(Forms!Indicadores!coordenacao2),dbo_temp_aof_reativos_ferramenta.coordenacao=Forms!Indicadores!coordenacao2) And IIf(Not IsNull(Forms!Indicadores!jornada2),dbo_temp_aof_reativos_ferramenta.jornada=Forms!Indicadores!jornada2) And IIf(Not IsNull(Forms!Indicadores!subjornada2),dbo_temp_aof_reativos_ferramenta.subjornada=Forms!Indicadores!subjornada2);
But this also does not work. Is there anything else I could do?

Thanks in advance!! =D
 
Last edited:

mike60smart

Registered User.
Local time
Today, 07:50
Joined
Aug 6, 2017
Messages
1,899
Hi Can you Post a screenshot of your Relationships in your Query?
 

VicNagib

New member
Local time
Today, 04:50
Joined
Jul 11, 2019
Messages
4
@mike60smart, the image is attached. Is that what you mean?
 

Attachments

  • query.PNG
    query.PNG
    46.6 KB · Views: 341

mike60smart

Registered User.
Local time
Today, 07:50
Joined
Aug 6, 2017
Messages
1,899
Hi

That is the SQL View of the query can you switch to Design View and then post the relationships
 

Mark_

Longboard on the internet
Local time
Today, 00:50
Joined
Sep 12, 2017
Messages
2,111
You do not provide the "False" value when you use IIF. You are giving a condition and a "TRUE" value.

Were I to do this, I would have a SUB build up your where clause ONLY using those pieces that are needed.
 

Users who are viewing this thread

Top Bottom