Can I call a function in query criteria?

  • Thread starter Thread starter Maarkr
  • Start date Start date
M

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.
 
If the function returns multiple categories then I doubt it will work. What
type of value is returned from the function?
 
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.
Click to expand...

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.
 
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
 
Back
Top