SQL with NZ in VBA

[NotAnExpert]

Hi, i'm struggling with using Nz with SQL in VBA, I believe it's the use of " that might be the issue. Would one of you kind fellows be helpful enough to show me where I might be going wrong?

The idea is that there is some data in a single table which isn't relational, but the user wants to be able to search any of the fields on there. This is an example of what is there so far. Text1 has multiple search options on the main form hence the searchArray(). The reason I want to use Nz is to replace null values in the fields with a blank so the row isn't ignored when using the query. I need all rows to show even if the field/column is null or blank.

Private Sub CreateStrSQL()
   
    Dim sqlSELECT As String, sqlFROM As String, sqlWHERE As String, sqlORDER As String, strSQL As String, searchArray() As String
    Dim i As Integer

    'sqlSELECT = "SELECT * " '-- Original catch all - works fine
    sqlSELECT = "SELECT Acc, Nz([qryTransactions].[Recnum], "") AS [Recnum], [Cust ref], [Del dt] " '-- "" within Nz not working as expected...

    sqlFROM = "FROM qryTransactions "
   
    sqlWHERE = "WHERE [Acc] LIKE '*" & Me.cboCustAccRef & "*' AND [Recnum] LIKE '*" & Me.txtRecNo & "*' AND [Cust ref] LIKE '*" & Me.txtOrdNo & "*'"
                 
    sqlORDER = "ORDER BY Recnum DESC"
   
    searchArray() = Split(Me.txtText1.Value & "", ";")
    For i = 0 To UBound(searchArray)
           
        sqlWHERE = sqlWHERE & " AND Text1 LIKE '*" & searchArray(i) & "*'"
   
    Next i

    strSQL = sqlSELECT & sqlFROM & sqlWHERE & sqlORDER

    Me.sfrmTransactions.Form.RecordSource = strSQL

End Sub

 

[theDBguy]

Hi. Try using single quotes. For example:

Nz([qryTransactions].[Recnum], '')

 

[NotAnExpert]

Hi. Try using single quotes. For example:

Nz([qryTransactions].[Recnum], '')

Thanks for that one, i've tried that but it still didn't include the rows where the value is null. I'm not sure what to try next...

 

[theDBguy]

Thanks for that one, i've tried that but it still didn't include the rows where the value is null. I'm not sure what to try next...

Well, the Nz() function wouldn't normally have anything to do with returning null records, because it's main purpose is to replace null values. You may have to fix your query first without using NZ() to try to get the null records.

In some cases, I do something like this:

...WHERE Field=Something OR Field Is Null

 

[NotAnExpert]

Well, the Nz() function wouldn't normally have anything to do with returning null records, because it's main purpose is to replace null values. You may have to fix your query first without using NZ() to try to get the null records.

In some cases, I do something like this:

...WHERE Field=Something OR Field Is Null

I appreciate that, I have tried just using a query in query design and this works perfectly, essentially replacing null values with an empty string, which stops the row from being ignored, but doing this in VBA and passing that as the query through that way is the ideal solution for me here. I have 40k rows and only 16k are being returned using the vba route when all fields on the form are left blank, so I have some fixing to do.

 

[theDBguy]

I appreciate that, I have tried just using a query in query design and this works perfectly, essentially replacing null values with an empty string, which stops the row from being ignored, but doing this in VBA and passing that as the query through that way is the ideal solution for me here. I have 40k rows and only 16k are being returned using the vba route when all fields on the form are left blank, so I have some fixing to do.

Hi. If you have a saved query that returns all the records you need, then why can't you just use it in your VBA. If you don't want to save the query, then maybe you can just use the same SQL statement from the working query in your VBA?

 

[NotAnExpert]

Hi. If you have a saved query that returns all the records you need, then why can't you just use it in your VBA. If you don't want to save the query, then maybe you can just use the same SQL statement from the working query in your VBA?

Because the example code I posted above has a loop in it that a standard query wont allow for.

 

[theDBguy]

Because the example code I posted above has a loop in it that a standard query wont allow for.

Sorry, I don't completely follow. I thought you just said you have a query that works?

If you want to post a sample db with test data to help explain better what you're trying to do, please consider doing so.

 

[CJ_London]

suspect your problem is here

AND [Recnum] LIKE '*" & Me.txtRecNo & "*'


if Recnum is null, it won't be like anything

you could try

AND nz([Recnum],"") LIKE '*" & Me.txtRecNo & "*'

This assumes Recnum is a string and not a number

or you could use

AND ([Recnum] LIKE '*" & Me.txtRecNo & "*' OR [Recnum] is null)"

Note that using the initial * will negate the use of indexes so if you have a large dataset you are querying, this will be a very slow query

 

[arnelgp]

modify the code:

Private Sub CreateStrSQL()
  
    Dim sqlSELECT As String, sqlFROM As String, sqlWHERE As String, sqlORDER As String, strSQL As String, searchArray() As String
    Dim i As Integer

    'sqlSELECT = "SELECT * " '-- Original catch all - works fine
    'Do not Use NZ.. we'll use it in the Where Clause
    sqlSELECT = "SELECT Acc, [Recnum], [Cust ref], [Del dt] " '-- "" within Nz not working as expected...

    sqlFROM = "FROM qryTransactions "
  
    sqlWHERE = "WHERE [Acc] LIKE '*" & Me.cboCustAccRef & "*' AND Nz([Recnum], " & Me.txtRecNo & ") LIKE '*" & Me.txtRecNo & "*' AND [Cust ref] LIKE '*" & Me.txtOrdNo & "*'"
                
    sqlORDER = "ORDER BY Recnum DESC"
  
    searchArray() = Split(Me.txtText1.Value & "", ";")
    For i = 0 To UBound(searchArray)
          
        sqlWHERE = sqlWHERE & " AND Text1 LIKE '*" & searchArray(i) & "*'"
  
    Next i

    strSQL = sqlSELECT & sqlFROM & sqlWHERE & sqlORDER

    Me.sfrmTransactions.Form.RecordSource = strSQL

End Sub

 

[NotAnExpert]

modify the code:

Private Sub CreateStrSQL()
 
    Dim sqlSELECT As String, sqlFROM As String, sqlWHERE As String, sqlORDER As String, strSQL As String, searchArray() As String
    Dim i As Integer

    'sqlSELECT = "SELECT * " '-- Original catch all - works fine
    'Do not Use NZ.. we'll use it in the Where Clause
    sqlSELECT = "SELECT Acc, [Recnum], [Cust ref], [Del dt] " '-- "" within Nz not working as expected...

    sqlFROM = "FROM qryTransactions "
 
    sqlWHERE = "WHERE [Acc] LIKE '*" & Me.cboCustAccRef & "*' AND Nz([Recnum], " & Me.txtRecNo & ") LIKE '*" & Me.txtRecNo & "*' AND [Cust ref] LIKE '*" & Me.txtOrdNo & "*'"
               
    sqlORDER = "ORDER BY Recnum DESC"
 
    searchArray() = Split(Me.txtText1.Value & "", ";")
    For i = 0 To UBound(searchArray)
         
        sqlWHERE = sqlWHERE & " AND Text1 LIKE '*" & searchArray(i) & "*'"
 
    Next i

    strSQL = sqlSELECT & sqlFROM & sqlWHERE & sqlORDER

    Me.sfrmTransactions.Form.RecordSource = strSQL

End Sub

Out of curiosity, why would this work from the WHERE clause and not the initial SELECT part?

 

[arnelgp]

it is in the Where clause that we need to Filter the resulting recordset.

 

[NotAnExpert]

it is in the Where clause that we need to Filter the resulting recordset.

I have just tried the code and it worked, however I have another question...

 Nz([Recnum], " & Me.txtRecNo & ") LIKE '*" & Me.txtRecNo & "*'

I'm not sure if I understand this right but it looks like the NULL replacement is whatever is in the textbox on the mainform?

 

[arnelgp]

yes, you got the logic.
so if your txtRecNo value is, say, 101, it will return record with recnum=101 (or *101") and all also records with Null recnum.

 

[NotAnExpert]

Thank you all for your responses, finally got it working. Now I need to have the subform use this instead of the standard query when it loads up

 

[theDBguy]

Thank you all for your responses, finally got it working. Now I need to have the subform use this instead of the standard query when it loads up

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.