* not recognised in query

[LB79]

Hello,

I have a problem with the * that I cant seem to figure out.
I'm trying to create cbobox that has an “All” option (code below), but for some reason, when the “All” option is selected it returns 0 results. I've tested the other option which are working, and I've check that the * is being passed to he query. I've also checked that the query accepts * by typing it in manually.
Can anyone see where im going wrong?

Thanks


Cbobox:

[SIZE=3][COLOR=#000080][FONT=Arial]SELECT "All" AS [UN Code], "*" AS [Agent ID], "All Agents" AS [Agency Name]  [/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]FROM C20_tblTargetDetails  [/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]UNION SELECT PIC_tblPIC.[UN Code], C20_tblTargetDetails.[Agent ID], PIC_tblPIC.[Agency Name]  FROM C20_tblTargetDetails INNER JOIN PIC_tblPIC ON C20_tblTargetDetails.[Agent ID] = PIC_tblPIC.[AG Code]  [/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]GROUP BY PIC_tblPIC.[UN Code], C20_tblTargetDetails.[Agent ID], PIC_tblPIC.[Agency Name] [/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]ORDER BY [UN Code];[/FONT][/COLOR][/SIZE]

Query:

[SIZE=3][COLOR=#000080][FONT=Arial]SELECT C20_tblTargetDetails.[Agent ID], PIC_tblPIC.[Agency Name], "FY 20" & C20_tblTargetDetails.FY AS FY, Count(C20_tblTargetDetails.[Item ID]) AS Items, C20_tblTargetDetails.FY AS FY1[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]FROM C20_tblTargetDetails INNER JOIN PIC_tblPIC ON C20_tblTargetDetails.[Agent ID] = PIC_tblPIC.[AG Code][/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]GROUP BY C20_tblTargetDetails.[Agent ID], PIC_tblPIC.[Agency Name], "FY 20" & C20_tblTargetDetails.FY, C20_tblTargetDetails.FY[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]HAVING (((C20_tblTargetDetails.[Agent ID])=[Forms]![C20_frmDelete]![C20_cbo2]))[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]ORDER BY C20_tblTargetDetails.[Agent ID], "FY 20" & C20_tblTargetDetails.FY DESC;[/FONT][/COLOR][/SIZE]

 

[SimonB1978]

Hi,

If use = '*' in SQL, it will look for the character * (ascii 42)

If you want tu use the wildcard you'll have to use: field LIKE '*'

Simon B.

 

[Brianwarnock]

what you are testing for is C20_tblTargetDetails.[Agent ID] = "*" I take it you have no IDs like this, you need to code
(((C20_tblTargetDetails.[Agent ID])=[Forms]![C20_frmDelete]![C20_cbo2]) or ([Forms]![C20_frmDelete]![C20_cbo2] ="*"))


Brian