Can I call a function in query criteria?

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.
 
D

Duane Hookom

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

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.
 
M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top