Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-11-2019, 05:08 AM   #1
VicNagib
Newly Registered User
 
Join Date: Jul 2019
Location: Sao Paulo, Brazil
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
VicNagib is on a distinguished road
Query not working when one field is not filled

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 by VicNagib; 07-11-2019 at 05:09 AM. Reason: Forgot to mention: DATAIN and DATAFIM are the date fields, they are fine.
VicNagib is offline   Reply With Quote
Old 07-11-2019, 05:26 AM   #2
mike60smart
Newly Registered User
 
mike60smart's Avatar
 
Join Date: Aug 2017
Location: Dunbar, Scotland
Posts: 439
Thanks: 21
Thanked 90 Times in 89 Posts
mike60smart will become famous soon enough
Re: Query not working when one field is not filled

Hi Can you Post a screenshot of your Relationships in your Query?
mike60smart is offline   Reply With Quote
Old 07-11-2019, 05:32 AM   #3
VicNagib
Newly Registered User
 
Join Date: Jul 2019
Location: Sao Paulo, Brazil
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
VicNagib is on a distinguished road
Re: Query not working when one field is not filled

@mike60smart, the image is attached. Is that what you mean?
Attached Images
File Type: png query.PNG (46.6 KB, 16 views)

VicNagib is offline   Reply With Quote
Old 07-11-2019, 06:08 AM   #4
mike60smart
Newly Registered User
 
mike60smart's Avatar
 
Join Date: Aug 2017
Location: Dunbar, Scotland
Posts: 439
Thanks: 21
Thanked 90 Times in 89 Posts
mike60smart will become famous soon enough
Re: Query not working when one field is not filled

Hi

That is the SQL View of the query can you switch to Design View and then post the relationships
mike60smart is offline   Reply With Quote
Old 07-11-2019, 08:13 AM   #5
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,037
Thanks: 20
Thanked 381 Times in 374 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Query not working when one field is not filled

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.

Mark_ is offline   Reply With Quote
Reply

Tags
access , form , sql

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Query Pulling Information From a Filled Field dr_destructo Queries 3 06-16-2012 10:48 PM
Enable a field if the previous field is filled talibnawaz Forms 3 07-10-2011 05:49 AM
Field not being filled DanF Forms 2 04-30-2009 04:49 AM
Capturing that field is ’filled to overflowing’ lorveney Reports 3 11-19-2007 02:47 AM
field must be filled in first maxmangion Forms 6 08-18-2004 05:38 AM




All times are GMT -8. The time now is 05:09 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World