Solved

Access SQL Query with multiple parameters some may be null

Posted on 2013-11-07
14
512 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
  • 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 48

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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.

828 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