Solved

Query to exclude value based on form selection

Posted on 2009-05-08
2
184 Views
Last Modified: 2012-05-06
I have an Access 2003 database with a basic query that pulls parameters from a form. I want to exclude the last parameter of the WHERE clause (L.LicensedById = Forms!frmApplicationsTotal!LicensedBy) if the value passed to it is 0. The column that holds the values is an integer and will contain a 1 or 2. Basically if the user doesn't select a value (1 or 2) then I want to return all results whether its a 1 or a 2.

Query below:
SELECT
     Count(*) AS Total
FROM
     dbo_tblApplications AS A
INNER JOIN
     dbo_tblLicense AS L ON A.CredentialID=L.LicenseID
WHERE
     (A.DateReceived>=Forms!frmApplicationsTotal!FromDate And A.DateReceived<=Forms!frmApplicationsTotal!ToDate)
     And (L.LicenseTypeID=Forms!frmApplicationsTotal!LicenseType)
     And (A.ApplicationType=Forms!frmApplicationsTotal!ApplicationType)
     And (L.LicensedById = Forms!frmApplicationsTotal!LicensedBy);

Open in new window

0
Comment
Question by:adamch25
2 Comments
 
LVL 2

Accepted Solution

by:
shoppedude earned 250 total points
ID: 24339061
I believe this should work for you:


SELECT
     Count(*) AS Total
FROM
     dbo_tblApplications AS A
INNER JOIN
     dbo_tblLicense AS L ON A.CredentialID=L.LicenseID
WHERE
     (A.DateReceived>=Forms!frmApplicationsTotal!FromDate And A.DateReceived<=Forms!frmApplicationsTotal!ToDate)
     And (L.LicenseTypeID=Forms!frmApplicationsTotal!LicenseType)
     And (A.ApplicationType=Forms!frmApplicationsTotal!ApplicationType)
     And (Forms!frmApplicationsTotal!LicensedBy = 0 
      Or (Forms!frmApplicationsTotal!LicensedBy <> 0 And L.LicensedById = Forms!frmApplicationsTotal!LicensedBy))

Open in new window

0
 

Author Closing Comment

by:adamch25
ID: 31579580
Thanks that worked! That was easy huh? Sometimes the easy things are the hardest to overcome... for me that is. ;)
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

777 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