Solved

SQL VBA Syntax when using NULL

Posted on 2016-11-02
17
48 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 52

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 52

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 52

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

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.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

691 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