Solved

SQL VBA Syntax when using NULL

Posted on 2016-11-02
17
33 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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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 49

Expert Comment

by:Ryan Chong
Comment Utility
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
 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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 49

Expert Comment

by:Ryan Chong
Comment Utility
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
Comment Utility
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
Comment Utility
Nevermind. I put the missing quote in there. It's working now!
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 250 total points
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
Whoops! How can I fix that?
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
I requested attention to fix it. Sorry about that.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
click on Request Attention below the box where you posted your problem and ask to reopen the thread.
0
 
LVL 49

Accepted Solution

by:
Ryan Chong earned 250 total points
Comment Utility
@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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now