Can I call a function in query criteria?

[Maarkr]

Hope I'm not being too vague... I built a function to sort people into
categories, most are in one, some are in many... the function works well when
I use it to filter reports and forms, but I would like to call the function
to filter a combo query. The query currently returns all categories... I
want to filter it using the function the same as for the reports. I tried
putting in the query criteria =CatSelect() it ran but showed no
records. Should I be able to run a function in the query criteria? It may
just not have the right syntax for the query vs being used for the Where
clause for reports.

 

[Duane Hookom]

If the function returns multiple categories then I doubt it will work. What
type of value is returned from the function?

 

[John W. Vinson]

Hope I'm not being too vague... I built a function to sort people into
categories, most are in one, some are in many... the function works well when
I use it to filter reports and forms, but I would like to call the function
to filter a combo query. The query currently returns all categories... I
want to filter it using the function the same as for the reports. I tried
putting in the query criteria =CatSelect() it ran but showed no
records. Should I be able to run a function in the query criteria? It may
just not have the right syntax for the query vs being used for the Where
clause for reports.

A query can use a function in a criterion IF it just returns one value. You
cannot use a function which returns a list of values, because the criterion
cannot contain operators such as OR, IN(), commas, etc. - just actual data.

It would help a lot if you would post the SQL of the query, and the VBA for
CatSelect.

 

[AccessVandal via AccessMonster.com]

Maybe you're looking for something like cascading combo boxes filtering or
filter base on the form's textboxes, checkboxes, radio buttons, etc? You can
use it in your query criteria to filter to like "[Forms]![YourFormName]!
[YourTextBox]" rather than using a function.

 

[Maarkr]

SELECT DISTINCT tblCAP.CAP_No, tblCAP.fkUnit, Left([CAP_Desc],50) & "..." AS
CAP, tblStandards.Std_ID
FROM tblCAP INNER JOIN tblStandards ON tblCAP.CAP_No = tblStandards.fkCAP
WHERE (((tblCAP.fkUnit)=UnitSel()));
'--- tried to cal the function using =UnitSel() in the criteria but returned
no records
'function looks up username and unitID which can be many units

Function UnitSel()
Dim lngArea As Long
Dim stLinkCriteria As String
lngArea = DLookup("UnitID", "Users", "FLName = '" & fOSUserName & "'")

Select Case lngArea 'still working on areas
Case Is = 28 'LRS
stLinkCriteria = "[Unit_No] = 14 OR [Unit_No] =24 OR
[Unit_No] =22 OR [Unit_No] =23"
Case Is = 29 'MSG
stLinkCriteria = "[Unit_No] = 2 OR [Unit_No] = 4 OR
[Unit_No] =6 OR [Unit_No] =7 OR [Unit_No] =8 OR [Unit_No] = 12 OR [Unit_No] =
13 OR [Unit_No] = 18 OR [Unit_No] = 19"
Case Is = 30 'OPG
stLinkCriteria = "[Unit_No] = 14OR [Unit_No] =10 "
Case Is = 31 'WAgencies
stLinkCriteria = "[Unit_No] =3 OR [Unit_No] =5 OR
[Unit_No] =9 OR [Unit_No] = 15 OR [Unit_No] = 16 OR [Unit_No] = 17 OR
[Unit_No] = 20 OR [Unit_No] = 21 OR [Unit_No] = 27"
Case Is = 32 'Wing
stLinkCriteria = ""
Case Else
stLinkCriteria = "[Unit_No] =" & lngArea
End Select
Debug.Print stLinkCriteria
End Function

 

[Maarkr]

never mind...figured out most of it