?
Solved

Access SQL Query with multiple parameters some may be null

Posted on 2013-11-07
14
Medium Priority
?
517 Views
Last Modified: 2013-11-08
Hello, in MS SQL I can do something like this to query a table base on multiple parameters with some of the parameters being null.

Given my statement below is it possible to do the same in Access SQl 2003? If so how?

Select * From Surveys
where (rtrim(@CompanyId)  is NUll or rtrim(CompanyId)  = rtrim(@CompanyId))
And (rtrim(@Platform)  is NUll or rtrim([Platform])  like rtrim(@Platform) + '%')
And (rtrim(@SurveyNumber)  is NUll or rtrim(SurveyNumber)  like rtrim(@SurveyNumber) + '%')
And (rtrim(@Year)  is NUll or rtrim([Year])  like rtrim(@Year) + '%')
0
Comment
Question by:gogetsome
[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
14 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39631473
The RTrim function (trimming trailing spaces) is available in Access too. The wild cards are different; and Access doesn't use the @ sign

Try this:


Select * From Surveys
where  rtrim(CompanyId)  = rtrim('' & [ParamCompanyId])
And  rtrim([Platform])  like rtrim('' & [ParamPlatform] ) & '*'
And rtrim(SurveyNumber)  like rtrim('' & [ParamSurveyNumber]) & "*"
And  rtrim([Year])  like rtrim( '' & Year) & '*'

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39632743
bit off-topic

I'm not so sure about your MS SQL example, looks to me like there are many redundant RTRIM() calls that will adversley affect performance AND the RTRIM(@variable) probably isn't achieving much of anything either (and might actually be wrong).

try this:
declare @CompanyId varchar = ''
declare @Platform varchar = ''
declare @SurveyNumber varchar = ''
declare @Year varchar = ''

/* variables are "empty strings", & the RTRIM() does NOT work */

Select 'test 1', * From Surveys
where (rtrim(@CompanyId)  is NUll or rtrim(CompanyId)  = rtrim(@CompanyId))
And (rtrim(@Platform)  is NUll or rtrim([Platform])  like rtrim(@Platform) + '%')
And (rtrim(@SurveyNumber)  is NUll or rtrim(SurveyNumber)  like rtrim(@SurveyNumber) + '%')
And (rtrim(@Year)  is NUll or rtrim([Year])  like rtrim(@Year) + '%')
;

declare @CompanyId varchar
declare @Platform varchar
declare @SurveyNumber varchar
declare @Year varchar

/* variables are NULL, and the RTRIM() is redundant */

Select 'test 2', * From Surveys
where (rtrim(@CompanyId)  is NUll or rtrim(CompanyId)  = rtrim(@CompanyId))
And (rtrim(@Platform)  is NUll or rtrim([Platform])  like rtrim(@Platform) + '%')
And (rtrim(@SurveyNumber)  is NUll or rtrim(SurveyNumber)  like rtrim(@SurveyNumber) + '%')
And (rtrim(@Year)  is NUll or rtrim([Year])  like rtrim(@Year) + '%')
;

declare @CompanyId varchar
declare @Platform varchar
declare @SurveyNumber varchar
declare @Year varchar

/* variables are NULL, and the RTRIM() on @variable is removed */

Select 'test 3', * From Surveys
where (@CompanyId    is NUll or rtrim(CompanyId)  = rtrim(@CompanyId))
And   (@Platform     is NUll or rtrim([Platform])   like rtrim(@Platform) + '%')
And   (@SurveyNumber is NUll or rtrim(SurveyNumber) like rtrim(@SurveyNumber) + '%')
And   (@Year         is NUll or rtrim([Year])       like rtrim(@Year) + '%')

/* ask yourself, do you REALLY NEED all those rtrim()s? probably not
   and they WILL SLOW IT DOWN */
;

Open in new window

see: http://sqlfiddle.com/#!3/64d90/6
0
 

Author Comment

by:gogetsome
ID: 39634469
Sorry for the delay, been busy with learning more about Access SQL.

I'm attempting to use the following to populate a report. If I leave the supplier null I get the following error:

Invalid use of null

If I give it a value the report renders correctly.

I'm assuming with my limited Access knowledge that the SQL statement is incorrect.

What is wrong with this statement:

Dim strSQL As String
Dim mySupplier As String
mySupplier = Me.SUPPLIER.Value
strSQL = "Select * from tblSupplierDebitSummary " _
            & "Where [Date] Between #" & Me.Start_Date.Value & "# and #" & Me.End_Date.Value & "#" _
            & " And ([Supplier])  like ('' & [mySupplier] ) & '*'"

DoCmd.OpenReport "New Supplier Debit Summary", acViewPreview, , , , strSQL

Open in new window

0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 61

Expert Comment

by:mbizup
ID: 39634505
A couple of issues with the quotes and the way you are concatenating mSupplier to the rest of the query string:

Dim strSQL As String
Dim mySupplier As String
mySupplier = Me.SUPPLIER.Value
strSQL = "Select * from tblSupplierDebitSummary " _
            & "Where [Date] Between #" & Me.Start_Date.Value & "# and #" & Me.End_Date.Value & "#" _
            & " And ([Supplier])  like '" & mySupplier & "*'"

DoCmd.OpenReport "New Supplier Debit Summary", acViewPreview, , , , strSQL

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 39634507
Hmm... Are you sure you posted this to the right question?  

It is quite different from what we started with here.
0
 

Author Comment

by:gogetsome
ID: 39634538
Yes, sorry I just used a MS SQL example from another project where I used those where conditions.

This is the correct statement; however, I will be adding two more conditions.

my VBA is complaining about the syntax:

Dim strSQL As String
Dim mySupplier As String
mySupplier = Me.SUPPLIER.Value
strSQL = "Select * from tblSupplierDebitSummary " _
            & "Where [Date] Between #" & Me.Start_Date.Value & "# and #" & Me.End_Date.Value & "#" _
            & " And ([Supplier])  like '" & mySupplier & "*'"

DoCmd.OpenReport "New Supplier Debit Summary", acViewPreview, , , , strSQL


Might be easier to put in all on one line?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39634580
>> my VBA is complaining about the syntax:

What exactly is it complaining about ?  Error message?  Screenshots might help too...

The SQL string looks syntactically good, and nothing is highlighted when I paste the code in to my vba editor.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 39634602
Can the supplier contain single quotes/apostrophes? eg: Bob's Electronics ?

If so, use chr(34) instead of single quotes:


strSQL = "Select * from tblSupplierDebitSummary Where [Date] Between #" & Me.Start_Date.Value & "# and #" & Me.End_Date.Value & "# And ([Supplier])  like " & chr(34) & mySupplier & "*" & chr(34)

Open in new window


Also changed it to one line for you..
0
 

Author Comment

by:gogetsome
ID: 39634605
It is displaying the query in red and when I attempt to run the application I get a message box which says compile error: syntax error
0
 

Author Comment

by:gogetsome
ID: 39634610
screenshot of error
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39634616
Sounds like custom error handling.  Try my last post, and see if that still errors...

If that doesn't help, post a sample database (delete or mask any sensitive data before posting it)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39634628
Okay :-)

I think we are crossposting here, because the code in your image doesn't match either of my last two posts.

Try both of my last posts in order and let me know how it goes...
0
 

Author Closing Comment

by:gogetsome
ID: 39634638
Thank you so much for hanging in there with me on this one! We have it working now!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39634685
Glad to help out :)
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

764 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