Solved

Access SQL Query with multiple parameters some may be null

Posted on 2013-11-07
14
515 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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 500 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

Technology Partners: 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!

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

724 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