[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 56
  • Last Modified:

SQL VBA Syntax when using NULL

I have the following SQL vba that I am trying to build for a pass-thru query. The last 2 parameters can be NULL which means I need to pass the word NULL to the pass-thru query. I have it using the Nz function and it passes the word NULL as it should. But it's also passing along the quotes around the word NULL too. If those 2 parameters are using the word NULL then how do I remove the quotes around those values? I always have a hard time writing SQL vba with quotes and double-quotes, etc.

            strOldSQL = ChangeSQL(strQry, strSQL & "'" & _
                    Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtDateFrom] & "', " & _
                    Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtDateTo] & "', '" & _
                    Forms![frmSalesOrderQuoteHistoryBrowseBeta]![cboCustomer] & "', '" & _
                    Forms![frmSalesOrderQuoteHistoryBrowseBeta]![cboGroupCode] & "', '" & _
                    Nz(Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtQtyMin], "NULL") & "', '" & _
                    Nz(Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtQtyMax], "NULL") & "'")

Open in new window

0
Lawrence Salvucci
Asked:
Lawrence Salvucci
  • 8
  • 5
  • 3
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
try

            strOldSQL = ChangeSQL(strQry, strSQL & "'" & _
                    Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtDateFrom] & "', " & _
                    Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtDateTo] & "', '" & _
                    Forms![frmSalesOrderQuoteHistoryBrowseBeta]![cboCustomer] & "', '" & _
                    Forms![frmSalesOrderQuoteHistoryBrowseBeta]![cboGroupCode] & "', '" & _
                    Nz(Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtQtyMin], NULL) & "', '" & _
                    Nz(Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtQtyMax], NULL) & "'")
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Nope. I need to either have the word NULL as the value that gets put into the pass-thru query for the last 2 parameters or the values that are entered into those controls on my form. But when the value NULL is passed to the query I need to remove the quotes around it. What you posted does the same thing I had which puts single quotes around the word NULL for both of those parameters. See below.

Execute upMySprocHistory '11/1/2014', 11/2/2016', '100987', '465', 'Null', 'Null'

Open in new window


I need it to look like this:

Execute upMySprocHistory '11/1/2014', 11/2/2016', '100987', '465', Null, Null

Open in new window

0
 
Ryan ChongCommented:
try this..

strOldSQL = ChangeSQL(strQry, strSQL & "'" & _
                    Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtDateFrom] & "', " & _
                    Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtDateTo] & "', '" & _
                    Forms![frmSalesOrderQuoteHistoryBrowseBeta]![cboCustomer] & "', '" & _
                    Forms![frmSalesOrderQuoteHistoryBrowseBeta]![cboGroupCode] & "', " & _
					IF(ISNULL(Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtQtyMin]),"NULL", "'" & Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtQtyMin] & "'") & ", " & _
					IF(ISNULL(Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtQtyMax]),"NULL", "'" & Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtQtyMax] & "'") & " ")
			

Open in new window

0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I'm getting a compile error "Expected Expression" and it's looking at the first IF. I'm assuming it needs an "End If" for both of those? Where would they go?
0
 
Rey Obrero (Capricorn1)Commented:
try this

 strOldSQL = ChangeSQL(strQry, strSQL & "'" & _
                    Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtDateFrom] & "', " & _
                    Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtDateTo] & "', '" & _
                    Forms![frmSalesOrderQuoteHistoryBrowseBeta]![cboCustomer] & "', '" & _
                    Forms![frmSalesOrderQuoteHistoryBrowseBeta]![cboGroupCode] & "', " & _
                    IIf(IsNull(Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtQtyMin]), Null, "'" & Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtQtyMin] & "'") & ", " & _
                    IIf(IsNull(Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtQtyMax]), Null, "'" & Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtQtyMax] & "'"))
0
 
Ryan ChongCommented:
yea, that was a typo, should be as :

strOldSQL = ChangeSQL(strQry, strSQL & "'" & _
                    Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtDateFrom] & "', " & _
                    Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtDateTo] & "', '" & _
                    Forms![frmSalesOrderQuoteHistoryBrowseBeta]![cboCustomer] & "', '" & _
                    Forms![frmSalesOrderQuoteHistoryBrowseBeta]![cboGroupCode] & "', " & _
					IIF(ISNULL(Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtQtyMin]),"NULL", "'" & Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtQtyMin] & "'") & ", " & _
					IIF(ISNULL(Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtQtyMax]),"NULL", "'" & Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtQtyMax] & "'") & " ")

Open in new window

a bit different from Rey's solution
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Almost there. Now there's a single quote missing at the beginning of the second parameter. See the query syntax below:


Execute upMySprocHistory '20141101', 20161102', '100987', '', NULL, NULL 

Open in new window

0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Nevermind. I put the missing quote in there. It's working now!
0
 
Rey Obrero (Capricorn1)Commented:
oops


 strOldSQL = ChangeSQL(strQry, strSQL & "'" & _
                     Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtDateFrom] & "', '" & _
                    Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtDateTo] & "', '" & _
                     Forms![frmSalesOrderQuoteHistoryBrowseBeta]![cboCustomer] & "', '" & _
                     Forms![frmSalesOrderQuoteHistoryBrowseBeta]![cboGroupCode] & "', " & _
                     IIf(IsNull(Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtQtyMin]), Null, "'" & Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtQtyMin] & "'") & ", " & _
                     IIf(IsNull(Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtQtyMax]), Null, "'" & Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtQtyMax] & "'"))
0
 
Rey Obrero (Capricorn1)Commented:
I think you selected the one as the solution that is returning NULL as "NULL" which is not correct.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Whoops! How can I fix that?
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I requested attention to fix it. Sorry about that.
0
 
Rey Obrero (Capricorn1)Commented:
click on Request Attention below the box where you posted your problem and ask to reopen the thread.
0
 
Ryan ChongCommented:
@Lawrence

isn't this solved your issue?

strOldSQL = ChangeSQL(strQry, strSQL & "'" & _
                    Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtDateFrom] & "', '" & _
                    Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtDateTo] & "', '" & _
                    Forms![frmSalesOrderQuoteHistoryBrowseBeta]![cboCustomer] & "', '" & _
                    Forms![frmSalesOrderQuoteHistoryBrowseBeta]![cboGroupCode] & "', " & _
					IIF(ISNULL(Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtQtyMin]),"NULL", "'" & Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtQtyMin] & "'") & ", " & _
					IIF(ISNULL(Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtQtyMax]),"NULL", "'" & Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtQtyMax] & "'") & " ")

Open in new window


since your checking of NULL should returned with a string, hence you should return with "NULL" instead of NULL.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
No, I needed the word "Null" passed to the parameters of my pass-thru query. The last 2 parameters required the word Null when it gets to the stored procedure in SQL.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I did select the correct one the first as the best choice. I needed to return a value of NULL in my string which is the one that has "NULL" written in the code. Sorry for all the confusion.
1

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 8
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now