Solved

Access SQL Query with multiple parameters some may be null

Posted on 2013-11-07
14
509 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
 
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
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 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

Highfive Gives IT Their Time Back

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

757 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

18 Experts available now in Live!

Get 1:1 Help Now