Add "ALL" selection to combo box (1 Viewer)

brharrii

Registered User.
Local time
Yesterday, 21:56
Joined
May 15, 2012
Messages
272
Hi there,

I have a combo box that filters records on a subform by user name. I'd like to add an "all" option to the combo box list so that all records regardless of user name may be displayed at once. I have attempted a few approaches to this using a "union query" but am failing to achieve the correct syntax. The query I'm using is this:

Code:
select top 1 0,"**ALL**" from tblauditstaff 
 union All 
SELECT tblAuditStaff.AuditStaffID, [tblauditstaff].[auditstafflastname] & ", " & [tblauditstaff].[auditstafffirstname] AS [Auditor Name], tblAuditStaff.AuditStafffirstName, tblAuditStaff.AuditStaffLastName, tblAuditStaff.AuditStaffResponsible, tblDiscrepancy.DiscrepancyAssignedTo, tblDiscrepancy.DiscrepancyActualCompDate, tblDiscrepancy.DiscrepancyOngoing, tblDiscrepancy.DiscrepancyNonGMP, tblDiscrepancy.DiscrepancyCompleted, tblDiscrepancy.DiscrepancyDuplicate
FROM tblAuditStaff INNER JOIN tblDiscrepancy ON tblAuditStaff.AuditStaffID = tblDiscrepancy.DiscrepancyAssignedTo
WHERE (((tblAuditStaff.AuditStaffResponsible)=True) AND ((tblDiscrepancy.DiscrepancyAssignedTo) Is Not Null))
GROUP BY tblAuditStaff.AuditStaffID, [tblauditstaff].[auditstafflastname] & ", " & [tblauditstaff].[auditstafffirstname], tblAuditStaff.AuditStafffirstName, tblAuditStaff.AuditStaffLastName, tblAuditStaff.AuditStaffResponsible, tblDiscrepancy.DiscrepancyAssignedTo, tblDiscrepancy.DiscrepancyActualCompDate, tblDiscrepancy.DiscrepancyOngoing, tblDiscrepancy.DiscrepancyNonGMP, tblDiscrepancy.DiscrepancyCompleted, tblDiscrepancy.DiscrepancyDuplicate
HAVING (((tblDiscrepancy.DiscrepancyActualCompDate) Is Null) AND ((tblDiscrepancy.DiscrepancyOngoing)=False) AND ((tblDiscrepancy.DiscrepancyNonGMP)=False) AND ((tblDiscrepancy.DiscrepancyCompleted)=False) AND ((tblDiscrepancy.DiscrepancyDuplicate)=False));

This is the error message I'm getting:

The number of columns in the two selected tables or queries of a union query do not match.

Could anyone help point me back in the right direction?


Thank you! :)
 

boblarson

Smeghead
Local time
Yesterday, 21:56
Joined
Jan 12, 2001
Messages
32,059
Code:
SELECT tblAuditStaff.AuditStaffID, [tblauditstaff].[auditstafflastname] & ", " & [tblauditstaff].[auditstafffirstname] AS [Auditor Name], tblAuditStaff.AuditStafffirstName, tblAuditStaff.AuditStaffLastName, tblAuditStaff.AuditStaffResponsible, tblDiscrepancy.DiscrepancyAssignedTo, tblDiscrepancy.DiscrepancyActualCompDate, tblDiscrepancy.DiscrepancyOngoing, tblDiscrepancy.DiscrepancyNonGMP, tblDiscrepancy.DiscrepancyCompleted, tblDiscrepancy.DiscrepancyDuplicate[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]FROM tblAuditStaff INNER JOIN tblDiscrepancy ON tblAuditStaff.AuditStaffID = tblDiscrepancy.DiscrepancyAssignedTo[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]WHERE (((tblAuditStaff.AuditStaffResponsible)=True) AND ((tblDiscrepancy.DiscrepancyAssignedTo) Is Not Null))[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]GROUP BY tblAuditStaff.AuditStaffID, [tblauditstaff].[auditstafflastname] & ", " & [tblauditstaff].[auditstafffirstname], tblAuditStaff.AuditStafffirstName, tblAuditStaff.AuditStaffLastName, tblAuditStaff.AuditStaffResponsible, tblDiscrepancy.DiscrepancyAssignedTo, tblDiscrepancy.DiscrepancyActualCompDate, tblDiscrepancy.DiscrepancyOngoing, tblDiscrepancy.DiscrepancyNonGMP, tblDiscrepancy.DiscrepancyCompleted, tblDiscrepancy.DiscrepancyDuplicate[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]HAVING (((tblDiscrepancy.DiscrepancyActualCompDate) Is Null) AND ((tblDiscrepancy.DiscrepancyOngoing)=False) AND ((tblDiscrepancy.DiscrepancyNonGMP)=False) AND ((tblDiscrepancy.DiscrepancyCompleted)=False) AND ((tblDiscrepancy.DiscrepancyDuplicate)=False))[/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=red][B]UNION [/B][/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=red][B]SELECT "**ALL**", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL[/B][/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=red][B]FROM tblAuditStaff
[/B][/COLOR]
 

brharrii

Registered User.
Local time
Yesterday, 21:56
Joined
May 15, 2012
Messages
272
Beautiful, it worked perfectly,

Thank you :)
 

Users who are viewing this thread

Top Bottom