Solved

SQL VBA Syntax when using NULL

Posted on 2016-11-02
17
43 Views
Last Modified: 2016-11-03
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
Comment
Question by:Lawrence Salvucci
  • 8
  • 5
  • 3
17 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41870418
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
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41870424
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
 
LVL 50

Expert Comment

by:Ryan Chong
ID: 41870481
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41870489
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41870496
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
 
LVL 50

Expert Comment

by:Ryan Chong
ID: 41870503
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
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41870512
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
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41870516
Nevermind. I put the missing quote in there. It's working now!
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 250 total points
ID: 41870521
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41870525
I think you selected the one as the solution that is returning NULL as "NULL" which is not correct.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41870536
Whoops! How can I fix that?
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41870537
I requested attention to fix it. Sorry about that.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41870539
click on Request Attention below the box where you posted your problem and ask to reopen the thread.
0
 
LVL 50

Accepted Solution

by:
Ryan Chong earned 250 total points
ID: 41871451
@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
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41872183
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
 
LVL 1

Author Closing Comment

by:Lawrence Salvucci
ID: 41873088
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question