SQL with NZ in VBA (1 Viewer)

NotAnExpert

Registered User.
Local time
Today, 21:03
Joined
Feb 3, 2017
Messages
43
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.

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
    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

I’m here to help
Staff member
Local time
Today, 14:03
Joined
Oct 29, 2018
Messages
21,358
Hi. Try using single quotes. For example:

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

NotAnExpert

Registered User.
Local time
Today, 21:03
Joined
Feb 3, 2017
Messages
43
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

I’m here to help
Staff member
Local time
Today, 14:03
Joined
Oct 29, 2018
Messages
21,358
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:

Code:
...WHERE Field=Something OR Field Is Null
 

NotAnExpert

Registered User.
Local time
Today, 21:03
Joined
Feb 3, 2017
Messages
43
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:

Code:
...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’m here to help
Staff member
Local time
Today, 14:03
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 21:03
Joined
Feb 3, 2017
Messages
43
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

I’m here to help
Staff member
Local time
Today, 14:03
Joined
Oct 29, 2018
Messages
21,358
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

Super Moderator
Staff member
Local time
Today, 21:03
Joined
Feb 19, 2013
Messages
16,553
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:03
Joined
May 7, 2009
Messages
19,169
modify the code:
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

Registered User.
Local time
Today, 21:03
Joined
Feb 3, 2017
Messages
43
modify the code:
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:03
Joined
May 7, 2009
Messages
19,169
it is in the Where clause that we need to Filter the resulting recordset.
 

NotAnExpert

Registered User.
Local time
Today, 21:03
Joined
Feb 3, 2017
Messages
43
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...

Code:
 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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:03
Joined
May 7, 2009
Messages
19,169
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

Registered User.
Local time
Today, 21:03
Joined
Feb 3, 2017
Messages
43
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

I’m here to help
Staff member
Local time
Today, 14:03
Joined
Oct 29, 2018
Messages
21,358
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.
 

Users who are viewing this thread

Top Bottom