asked on

How do I add a "show only these" checkbox to an Access form that exports a query?

I have a form that let's the user export a query to Excel. I give them a combobox that let's them choose the year so my query will only return the year they select. I also have a status field, but I want them to be able to check a box on the form that says "show only completed and in progress." If that box isn't checked, I want it to just return all records.

This isn't working: Like IIf([forms]![frm_SEERRatings]![Check17]="TRUE",([dbo_tbl_ProjectTracking].[ptStatus])="In Progress" Or ([dbo_tbl_ProjectTracking].[ptStatus])="Completed",'*')

What am I doing wrong?

Thanks!

Rey Obrero (Capricorn1)

try removing the "" from TRUE

Like IIf([forms]![frm_SEERRatings]![Check17]=TRUE,([dbo_tbl_ProjectTracking].[ptStatus])="In Progress" Or ([dbo_tbl_ProjectTracking].[ptStatus])="Completed",'*')

or
use -1

Like IIf([forms]![frm_SEERRatings]![Check17]=-1,([dbo_tbl_ProjectTracking].[ptStatus])="In Progress" Or ([dbo_tbl_ProjectTracking].[ptStatus])="Completed",'*')

Kevin Smith

ASKER

Not getting it. It returns nothing if I check it and freezes if I leave it unchecked. Here's the complete sql:

SELECT dbo_tbl_ProjectTracking.ptStatus AS Status, dbo_tbl_ProjectTracking.BudgetYear AS [Budget Year], dbo_tbl_Jobs.JobNumber AS [MSS Job Number], dbo_tbl_Locations.UnitNumber AS [Hotel #], dbo_tbl_Locations.PropertyTitle AS [Hotel Name], dbo_tbl_ProjectTracking.MProjectNumber AS [Marriott Project #], dbo_tbl_ProjectTracking.ProjectTitle AS [Project Title], dbo_tbl_ProjectTracking.Scope AS [Project Scope], dbo_tbl_SEERRating.UnitName AS [Unit Type], dbo_tbl_SEERRating.NumberofUnits AS [# of Units], dbo_tbl_SEERRating.SEER AS [SEER Rating], dbo_tbl_SEERRating.EER AS [EER Rating], dbo_tbl_SEERRating.RatingNotes AS Notes, dbo_tbl_PM.PMName AS [Project Manager]
FROM (((dbo_tbl_ProjectTracking LEFT JOIN dbo_tbl_Jobs ON dbo_tbl_ProjectTracking.ProjectID = dbo_tbl_Jobs.ProjectID) LEFT JOIN dbo_tbl_Locations ON dbo_tbl_Jobs.Location = dbo_tbl_Locations.LocationID) LEFT JOIN dbo_tbl_SEERRating ON dbo_tbl_ProjectTracking.PTID = dbo_tbl_SEERRating.ProjectTrackingID) LEFT JOIN dbo_tbl_PM ON dbo_tbl_Jobs.PM = dbo_tbl_PM.PMID
WHERE (((dbo_tbl_ProjectTracking.ptStatus) Like IIf([forms]![frm_SEERRatings]![Check17]=-1,([dbo_tbl_ProjectTracking].[ptStatus])="In Progress" Or ([dbo_tbl_ProjectTracking].[ptStatus])="Completed",'*')) AND ((dbo_tbl_ProjectTracking.BudgetYear) Like Nz([Forms]![frm_SEERRatings]![Combo7],'*')) AND ((dbo_tbl_Jobs.CustomerID)=71));

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Kevin Smith

ASKER

That got it, thanks once again Cap!

Kevin Smith

ASKER

Uh oh, it lost the AND. If I put a year in it returns nothing...ideas?

How do I add a &quot;show only these&quot; checkbox to an Access form that exports a query?

How do I add a "show only these" checkbox to an Access form that exports a query?