SQL Query in VBA (1 Viewer)

Hi,
I was wondering if someone could help me.

I have a few tables in SQL that I wish to query. I have a form with a subform that displays my results. On my form I have 6 text boxes that will allow a user to fill in criteria.

Now my problem is that not all the fields could be filled in. Maybe only 1. So I need for my SQL query to be written on the fly via VBA.

My problem is that if one of the boxes if left blank, I get the "invalid use of Null" error.

Could someone please help me try and get the VBA code to skip the SQL where entry if that field has been left blank.

Here is my code:

Private Sub cmdStart_Click()
Dim strSQL As String
Dim qdf As DAO.QueryDef
Dim partno As String
Dim Customer As String
Dim Description As String
Dim CustomerNo As String
Dim StartDate As String
Dim EndDate As String


partno = Me.partno
Customer = Me.Customer
Description = Me.Description
CustomerNo = Me.CustomerNo
StartDate = Me.StartDate
EndDate = Me.EndDate


If IsNull(Me.StartDate) Then
Me.StartDate = "01/01/1980"
End If
If IsNull(Me.EndDate) Then
Me.EndDate = Date
End If

Set qdf = CurrentDb.QueryDefs("qry-OperationsSQLData")

strSQL = " SELECT sitem.son, scust.comp_name, sitem.pstk, sitem.desc1, sitem.ord_qty, sitem.unit_pr, shead.cust_no, shead.order_date" & _
" FROM (shead LEFT JOIN sitem ON shead.son = sitem.son) LEFT JOIN scust ON shead.comp_no = scust.comp_no" & _
" WHERE sitem.pstk = " & Chr$(39) & partno & Chr$(39) & _
" & sitem.desc1 Like " & Chr$(39) & Chr$(37) & Description & Chr$(37) & Chr$(39) & _
" & scust.comp_name Like " & Chr$(39) & Chr$(37) & Customer & Chr$(37) & Chr$(39) & _
" & shead.cust_no Like " & Chr$(39) & Chr$(37) & CustomerNo & Chr$(37) & Chr$(39) & _
" & shead.order_date >= " & Chr$(39) & StartDate & Chr$(39) & _
" & shead.order_date <= " & Chr$(39) & EndDate & Chr$(39)

qdf.SQL = strSQL

DoCmd.OpenQuery "Qry-OperationsSearch"
Me.Refresh
Set qdf = Nothing
Exit Sub

End Sub

Thanks

Simply use some IF to eliminate the empty stuff

Also don't forget that you still need AND or OR between each criteria.
I wonder if that SQL you posted actually works.

Hi,
I have corrected the & to an And.

I am unsure how your code is to be implemented though. I do not understand the Where 1=1 part.


Private Sub cmdStart_Click()
Dim strSQL As String
Dim qdf As DAO.QueryDef
Dim partno As String
Dim Customer As String
Dim Description As String
Dim CustomerNo As String
Dim StartDate As String
Dim EndDate As String


partno = Me.partno
Customer = Me.Customer
Description = Me.Description
CustomerNo = Me.CustomerNo
StartDate = Me.StartDate
EndDate = Me.EndDate


If IsNull(Me.StartDate) Then
Me.StartDate = "01/01/1980"
End If
If IsNull(Me.EndDate) Then
Me.EndDate = Date
End If

Set qdf = CurrentDb.QueryDefs("qry-OperationsSQLData")

strSQL = " SELECT sitem.son, scust.comp_name, sitem.pstk, sitem.desc1, sitem.ord_qty, sitem.unit_pr, shead.cust_no, shead.order_date" & _
" FROM (shead LEFT JOIN sitem ON shead.son = sitem.son) LEFT JOIN scust ON shead.comp_no = scust.comp_no" & _
" WHERE sitem.pstk = " & Chr$(39) & partno & Chr$(39) & _
" And sitem.desc1 Like " & Chr$(39) & Chr$(37) & Description & Chr$(37) & Chr$(39) & _
" And scust.comp_name Like " & Chr$(39) & Chr$(37) & Customer & Chr$(37) & Chr$(39) & _
" And shead.cust_no Like " & Chr$(39) & Chr$(37) & CustomerNo & Chr$(37) & Chr$(39) & _
" And shead.order_date >= " & Chr$(39) & StartDate & Chr$(39) & _
" And shead.order_date <= " & Chr$(39) & EndDate & Chr$(39)

qdf.SQL = strSQL

DoCmd.OpenQuery "Qry-OperationsSearch"
Me.Refresh
Set qdf = Nothing
Exit Sub

End Sub

1=1 is simply a dummy statement which is always true which will allow you to always append an AND without problems.
Otherwize you have to add a bit more of logic to the code...

What you do is cut off the sql at the where, add 1=1 to it
Then add the IF for each textbox you have you want to use in searching

That is still not working.

The if statement seems to do nothing as I still get the invalid use of null.

Private Sub cmdStart_Click()
Dim strSQL As String
Dim qdf As DAO.QueryDef
Dim partnumber As String
Dim Customer As String
Dim Description As String
Dim CustomerNo As String
Dim StartDate As String
Dim EndDate As String


partnumber = Me.PartNo
'Customer = Me.Customer
Description = Me.Description
'CustomerNo = Me.CustomerNo
'StartDate = Me.StartDate
'EndDate = Me.EndDate


If IsNull(Me.StartDate) Then
Me.StartDate = "01/01/1980"
End If
If IsNull(Me.EndDate) Then
Me.EndDate = Date
End If

Set qdf = CurrentDb.QueryDefs("qry - OperationsSQLData")

strSQL = " SELECT sitem.son, scust.comp_name, sitem.pstk, sitem.desc1, sitem.ord_qty, sitem.unit_pr, shead.cust_no, shead.order_date" & _
" FROM (shead LEFT JOIN sitem ON shead.son = sitem.son) LEFT JOIN scust ON shead.comp_no = scust.comp_no" & _
" WHERE 1=1 "
If Me.PartNo Is Not Null And Me.PartNo <> "" Then strSQL = strSQL & " sitem.pstk Like " & Chr$(39) & Chr$(37) & partnumber & Chr$(37) & Chr$(39)
If Me.Description Is Not Null And Me.Description <> "" Then strSQL = strSQL & " And sitem.desc1 Like " & Chr$(39) & Chr$(37) & Description & Chr$(37) & Chr$(39)

Since you have at least 1 'LIKE' criteria, you don't need to add 1=1.
Since LIKE '%%' will give you all the records. Just make sure that you start with that criteria. Than add any additional criteria to the WHERE.

Example:

Still stopping when it hits a null field.

Private Sub cmdStart_Click()
Dim strSQL As String
Dim qdf As DAO.QueryDef
Dim partnumber As String
Dim Customer As String
Dim Description As String
Dim CustomerNo As String
Dim StartDate As String
Dim EndDate As String


partnumber = Me.PartNo
Customer = Me.Customer
Description = Me.Description
CustomerNo = Me.CustomerNo
StartDate = Me.StartDate
EndDate = Me.EndDate


If IsNull(Me.StartDate) Then
Me.StartDate = "01/01/1980"
End If
If IsNull(Me.EndDate) Then
Me.EndDate = Date
End If

Set qdf = CurrentDb.QueryDefs("qry - OperationsSQLData")

strSQL = " SELECT sitem.son, scust.comp_name, sitem.pstk, sitem.desc1, sitem.ord_qty, sitem.unit_pr, shead.cust_no, shead.order_date" & _
" FROM (shead LEFT JOIN sitem ON shead.son = sitem.son) LEFT JOIN scust ON shead.comp_no = scust.comp_no" & _
" WHERE shead.order_date >= " & Chr$(39) & StartDate & Chr$(39) & _
" And shead.order_date <= " & Chr$(39) & EndDate & Chr$(39)

If Me.PartNo Is Not Null Then
strSQL = strSQL & " sitem.pstk Like " & Chr$(39) & Chr$(37) & partnumber & Chr$(37) & Chr$(39)
End If

If Me.Description Is Not Null Then
strSQL = strSQL & " And sitem.desc1 Like " & Chr$(39) & Chr$(37) & Description & Chr$(37) & Chr$(39)
End If

If Me.Customer Is Not Null Then
strSQL = strSQL & " And scust.comp_name Like " & Chr$(39) & Chr$(37) & Customer & Chr$(37) & Chr$(39)
End If

If Me.CustomerNo Is Not Null Then
strSQL = strSQL & " And shead.cust_no Like " & Chr$(39) & Chr$(37) & CustomerNo & Chr$(37) & Chr$(39)
End If

qdf.SQL = strSQL

DoCmd.OpenQuery "Qry-OperationsSearch"
Me.Refresh
Set qdf = Nothing

End Sub

You need to use If Not IsNull(Partnumber) Then....

Grumm said:

Since you have at least 1 'LIKE' criteria, you don't need to add 1=1.
Since LIKE '%%' will give you all the records. Just make sure that you start with that criteria. Than add any additional criteria to the WHERE.

Click to expand...

Like %% will not give you all records!!! Like %% will remove any NULL fields since NULL is never equal to anything.

Minty said:

You need to use If Not IsNull(Partnumber) Then....

Click to expand...

TO much sql on the mind, Minty is right offcourse...

My bad about the Like %%...
I didn't know that having a customer Number=null was a common thing in a well designed database.

Anyway, he can use isnull(shead.cust_no,'') LIKE '%%' to get all the null customers.

(Maybe he can make the cust_no field a not null one ? That will solve a lot of problems and allow %% to work correctly.)