?
Solved

SQL VBA Syntax when using NULL

Posted on 2016-11-02
17
Medium Priority
?
50 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 53

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 53

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

Accepted Solution

by:
Ryan Chong earned 1000 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

770 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