How do i use an IF condition in a SELECTION query?

[Sweeda]

Hi everybody,

i'm trying to write a selection query that filters a list of records which the "date" field value is BETWEEN a minimum and a maximum values. These values must be taken from a form IF they are not empty. If they are empty the query must return the whole unfilterd list.
I don't know how to insert an IF condition in a SELECT....WHERE function.

Many thanks for your attention

[ADezii]

I'm not sure if you can accomplish this in an SQL Statement, and quite frankly, I'm not the one to ask. What you can do is to dynamically create a Query, conditionally building the SQL Source Statement based on whether or not a Form Field named txtDOB is Null or not. The following Code will dynamically create a Query named 'New Query' based on an Employee Table named tblEmployees. If a Date of Birth Field ([txtDOB]) Is Null, then all Records are returned from tblEmployees. If the Date of Birth Field is Not Null, then all Employee Records whose whose date of Birth falls between 1/1/1970 and 12/31/1980 are returned.

On Error Resume Next
Dim strSQL As String
Dim qdfNew As DAO.QueryDef

'DELETE the pre-existing Query (may/may not exist)
CurrentDb.QueryDefs.Delete "New Query"

If IsNull(Me![txtDOB]) Then
  strSQL = "SELECT * FROM tblEmployees;"
Else
  strSQL = "SELECT * FROM tblEmployees WHERE [DOB] BETWEEN #1/1/1970# AND #12/31/1980#;"
End If

Set qdfNew = CurrentDb.CreateQueryDef("New Query", strSQL)

[Mihail]

Take a look at this thread, especially to post #6

http://bytes.com/topic/access/answers/933592-query-data-type-mismatch-criteria-expression

I think that can manage your problem.

[Sweeda]

MMMh.. i tried this as you suggested but the new query is always empty, is it a problem with a too long string or what else?

On Error Resume Next
Dim codiceSQL As String
Dim qdfNew As DAO.QueryDef
 
'DELETE the pre-existing Query (may/may not exist)
CurrentDb.QueryDefs.Delete "Estrazione"
 
If (IsNull(Me![Forms]![Estrazione]![Text9]) And IsNull(Me![Forms]![Estrazione]![Text11])) Then
  codiceSQL = "SELECT * FROM Records WHERE (((Records.Persona_coinvolta_1) Like " * " & [Forms]![Estrazione]![Combo0]) AND ((Records.[Fatto_SI/NO]) Like " * " & [Forms]![Estrazione]![Check20])AND ((Records.ID_categoria1) Like " * " & [Forms]![Estrazione]![Combo5]) AND ((Records.ID_categoria2) Like   " * " & [Forms]![Estrazione]![Combo7]));"
Else
    If (IsNull(Me![Forms]![Estrazione]![Text9]) And Not IsNull(Me![Forms]![Estrazione]![Text11])) Then

    codiceSQL = "SELECT * FROM Records WHERE (((Records.Persona_coinvolta_1) Like " * " & [Forms]![Estrazione]![Combo0]) AND ((Records.[Fatto_SI/NO]) Like " * " & [Forms]![Estrazione]![Check20]) AND ((Records.ID_categoria1) Like " * " & [Forms]![Estrazione]![Combo5]) AND  ((Records.ID_categoria2) Like " * " & [Forms]![Estrazione]![Combo7]) AND ((Records.Scadenza)<[Forms]![Estrazione]![text11]));"
    Else
        If (Not IsNull(Me![Forms]![Estrazione]![Text9]) And IsNull(Me![Forms]![Estrazione]![Text11])) Then

        codiceSQL = "SELECT * FROM Records WHERE (((Records.Persona_coinvolta_1) Like " * " & [Forms]![Estrazione]![Combo0]) AND ((Records.[Fatto_SI/NO]) Like " * " & [Forms]![Estrazione]![Check20]) AND ((Records.ID_categoria1) Like " * " & [Forms]![Estrazione]![Combo5]) AND  ((Records.ID_categoria2) Like " * " & [Forms]![Estrazione]![Combo7]) AND ((Records.Scadenza)>[Forms]![Estrazione]![text9]));"
        
        Else
            
        codiceSQL = "SELECT * FROM Records WHERE (((Records.Persona_coinvolta_1) Like " * " & [Forms]![Estrazione]![Combo0]) AND ((Records.[Fatto_SI/NO]) Like " * " & [Forms]![Estrazione]![Check20]) AND ((Records.ID_categoria1) Like " * " & [Forms]![Estrazione]![Combo5]) AND  ((Records.ID_categoria2) Like " * " &[Forms]![Estrazione]![Combo7]) AND ((Records.Scadenza) BETWEEN [Forms]![Estrazione]![text9] AND [Forms]![Estrazione]![text11]));"
    
        End If
    End If

End If
 
Set qdfNew = CurrentDb.CreateQueryDef("Estrazione", codiceSQL)

[Sweeda]

Mihail, thank you but i don't think i can understand that post.. i'm quite a noob. And I hope to find something easier :)

[Mihail]

Line #9 : Else... What ?

Try this code instead yours:

    If IsNull(Me![Forms]![Estrazione]![Text9]) Then
        If IsNull(Me![Forms]![Estrazione]![Text11]) Then
            codiceSQL = "SELECT * FROM Records WHERE (((Records.Persona_coinvolta_1) Like " * " & [Forms]![Estrazione]![Combo0]) AND ((Records.[Fatto_SI/NO]) Like " * " & [Forms]![Estrazione]![Check20])AND ((Records.ID_categoria1) Like " * " & [Forms]![Estrazione]![Combo5]) AND ((Records.ID_categoria2) Like   " * " & [Forms]![Estrazione]![Combo7]));"
        Else
            codiceSQL = "SELECT * FROM Records WHERE (((Records.Persona_coinvolta_1) Like " * " & [Forms]![Estrazione]![Combo0]) AND ((Records.[Fatto_SI/NO]) Like " * " & [Forms]![Estrazione]![Check20]) AND ((Records.ID_categoria1) Like " * " & [Forms]![Estrazione]![Combo5]) AND  ((Records.ID_categoria2) Like " * " & [Forms]![Estrazione]![Combo7]) AND ((Records.Scadenza)<[Forms]![Estrazione]![text11]));"
        End If
    Else
        If IsNull(Me![Forms]![Estrazione]![Text11]) Then
            codiceSQL = "SELECT * FROM Records WHERE (((Records.Persona_coinvolta_1) Like " * " & [Forms]![Estrazione]![Combo0]) AND ((Records.[Fatto_SI/NO]) Like " * " & [Forms]![Estrazione]![Check20]) AND ((Records.ID_categoria1) Like " * " & [Forms]![Estrazione]![Combo5]) AND  ((Records.ID_categoria2) Like " * " & [Forms]![Estrazione]![Combo7]) AND ((Records.Scadenza)>[Forms]![Estrazione]![text9]));"
        Else
            codiceSQL = "SELECT * FROM Records WHERE (((Records.Persona_coinvolta_1) Like " * " & [Forms]![Estrazione]![Combo0]) AND ((Records.[Fatto_SI/NO]) Like " * " & [Forms]![Estrazione]![Check20]) AND ((Records.ID_categoria1) Like " * " & [Forms]![Estrazione]![Combo5]) AND  ((Records.ID_categoria2) Like " * " &[Forms]![Estrazione]![Combo7]) AND ((Records.Scadenza) BETWEEN [Forms]![Estrazione]![text9] AND [Forms]![Estrazione]![text11]));"
        End If
    End If

[Sweeda]

Thank you Mihail, it doesn't give me any error but the final new query "Estrazione " is still empty (SQL code that i find is: "SELECT;"). . :(

is this correct?

Set qdfNew = CurrentDb.Creat eQueryDef("Estr azione", codiceSQL)

[Mihail]

While I don't know SQL, I can't debug your code.
I only correct a little bit your "grammar" based on ADezii's logic and your code.
Read this article:

http://bytes.com/topic/access/insights/738863-debugging-vba-1-overview

understand it and try to see how is changed your codiceSQL string after every line where it appear. (After lines 5, 9, 11) and/or at the end, after line 13.

[Sweeda]

Ok, the problem is: SQL= ".... Like "*" ....;" I can't use it in vba. Alternatives?

[Sweeda]

And % or _ are not accepted, they give errors. It's strange 'cause " * " doesn't give error, but the query is created empty :S

[Mihail]

My way to use SQL in VBA (but I try to avoid that as more as I can) is to create an working query using Query Design interface.
Of course I use real criteria to filter my query.
After I obtain a good set of records based on this real criteria I switch to SQL View for query, I copy the SQL created by Access and paste it in VBA editor. After that I replace the real values for filter with custom values (strings).

This way I am sure that the entire syntax for SQL is OK (Access do not make mistakes when create SQL strings). So, if an error appear only my few custom strings are faulty. Usually by forget a space or to double the quotes.

Remember that a SQL string is a single row string even if Access use multiple lines to show it.
So my practice is to paste this string in VBA editor two times. First one is only for a backup. The second one is the string I work with.
After I make some changes in the second string I copy it and paste back in query SQL and I run the query. If all is OK then my new string is Ok so I can continue work with it.
The last thing is to replace real filter values with my string variables.

Well, I know that my English is not very good but I hope you understand (at least) the idea.

[NeoPa]

See ANSI Standards in String Comparisons for when these can be used.

[ChrisPadgham]

Here is a query that will achieve what you want

SELECT t2.recid, t2.EmployeeName, t2.targetDate
FROM   t2
WHERE (((t2.targetDate)>=nz([Forms]![Form1]![fromDate],[targetDate]) And (t2.targetDate)<=nz([Forms]![Form1]![toDate),[targetDate])));

[Sweeda]

The problem were the quotes " . In VBA they must be ' * ' instead of " * ". It works.
Thank you everybody guys, you've been very kind and usefull
@ChrisPadgham i'll study how to use this nz funcion to semplify very much my code, thanks

[NeoPa]

Sweeda:
The problem were the quotes " . In VBA they must be ' * ' instead of " * ". It works.

I wouldn't state it quite as simply as that, but fundamentally, that understanding will help you get past the problem at least ;-)