If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Can I call a function in query criteria?
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. |
#2
|
|||
|
|||
Can I call a function in query criteria?
If the function returns multiple categories then I doubt it will work. What
type of value is returned from the function? -- Duane Hookom Microsoft Access MVP "Maarkr" wrote: 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. |
#3
|
|||
|
|||
Can I call a function in query criteria?
On Thu, 3 Dec 2009 12:25:01 -0800, Maarkr
wrote: 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. -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Can I call a function in query criteria?
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 wrote: 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. -- Please Rate the posting if helps you. Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200912/1 |
#5
|
|||
|
|||
Can I call a function in query criteria?
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" wrote: 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. |
#6
|
|||
|
|||
Can I call a function in query criteria?
never mind...figured out most of it
"Maarkr" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|