Hi Aaron,
I have integrated your logic into the code and added an error handler.
You need to take time to understand each line of code before you use it.
If any statement confuses you, please specify the line and we will
explain it
'~~~~~~~~~~~~~~~~~~~~~~
Function IsInGroup( _
pTestString as string _
, gbl_parm as string _
) as boolean
'set up Error Handler
On Error GoTo Proc_Err
IsInGroup = false
if Forms!Criteria!TALL = true then
if gbl_parm = "ToGroup" OR gbl_parm = "FrmGroup" then
IsInGroup = true
end if
exit function
end if
SELECT CASE gbl_parm
Case "ToGroup"
If Forms!Criteria!TFRC = True Then
If pTestString = "FRC" then
IsInGroup = true: exit function
end if
End If
If Forms!Criteria!TMALS = True Then
If pTestString = "MALS" then
IsInGroup = true: exit function
end if
End If
If Forms!Criteria!TOCONUS = True Then
If pTestString = "OCONUS" then
IsInGroup = true: exit function
end if
End If
If Forms!Criteria!TBOATS = True Then
If pTestString = "BOATS" OR pTestString = "CVN"then
IsInGroup = true: exit function
end if
End If
Case "FrmGroup"
If Forms!Criteria!FFRC = True Then
If pTestString = "FRC" then
IsInGroup = true: exit function
end if
End If
If Forms!Criteria!FMALS = True Then
If pTestString = "MALS" then
IsInGroup = true: exit function
end if
End If
If Forms!Criteria!FOCONUS = True Then
If pTestString = "OCONUS" then
IsInGroup = true: exit function
end if
End If
If Forms!Criteria!FBOATS = True Then
If pTestString = "BOATS" OR pTestString = "CVN" then
IsInGroup = true: exit function
end if
End If
End Select
Proc_Exit:
Exit function
Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " IsInGroup"
Resume Proc_Exit
'if you want to single-step code to find error, CTRL-Break at MsgBox
'then set this to be the next statement
Resume
end Function
'~~~~~~~~~~~~~~~~~~~~~~
'~~~~~~~~~ Compile ~~~~~~~~~
Whenever you change code or references, your should always compile
before executing.
from the menu in a VBE (module) window: Debug, Compile
fix any errors on the yellow highlighted lines
keep compiling until nothing happens (this is good!)
Warm Regards,
Crystal
Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm
*
have an awesome day
*
Hi Aaron,
if you are going to use a function, why not just send the field itself
and return True or False?
WHERE IsInGroup([Fieldname],"GroupName" ) = true
'~~~~~~~~~~~~
Function IsInGroup( _
pTestString as string _
, pGroupName as string _
) as boolean
IsInGroup = false
SELECT CASE pGroupName
Case "Group1"
SELECT CASE pTestString
Case "Choice1", "Choice2", "Choice3"
IsInGroup = true
END Select
exit function
Case "Group2"
SELECT CASE pTestString
Case "Option1", "Option2", "Option3"
IsInGroup = true
END Select
End Select
end Function
'~~~~~~~~~~~~
~~~
do not use GROUP as a fieldname, it is a reserved word
Problem names and reserved words in Access, by Allen Brownehttp://
www.allenbrowne.com/AppIssueBadWord.html
Warm Regards,
Crystal
Access Basics
8-part free tutorial that covers essentials in Accesshttp://
www.accessmvp.com/Strive4Peace/Index.htm
*
have an awesome day
*
Hi,
I have a section of code that returns a string of group names
from a user controlled criteria form. I want to pass this string to a
query and use it to limit the records returned to only those groups.
the code works great, at least in the immediate window but when I put
it in an "in" experession in the query nothing is returned.
Right now I am only using a test query referencing a table with only 2
columns, one is the fields I need and the other is the group names.
here is the SQL for the query
*************************************
SELECT ICRLORG.ORG, ICRLORG.GROUP
FROM ICRLORG
WHERE (((ICRLORG.GROUP) In (get_group("ToGroup"))));
*************************************
the get_group function returns the selected groups in the following
format: group1, group2, etc.
So I thought that would drop great into the query, but it doesn't.
Yes I have tried using "" and '' around each group name with no luck.
Any ideas???
Thanks,
Aaron- Hide quoted text -
- Show quoted text -
Crystal,
Thanks for the reply! I'm not sure if that would work though. Could
be I am just not following the example. Here is the function I am
using:
*********************************************************
Public Function get_group(gbl_parm)
get_group = ""
ToGroup = ""
FrmGroup = ""
Select Case gbl_parm
Case "ToGroup"
If Forms!Criteria!TALL = True Then
ToGroup = "'*'"
Else
If Forms!Criteria!TFRC = True Then
ToGroup = ToGroup & "FRC,"
End If
If Forms!Criteria!TMALS = True Then
ToGroup = ToGroup & "MALS,"
End If
If Forms!Criteria!TOCONUS = True Then
ToGroup = ToGroup & "OCONUS,"
End If
If Forms!Criteria!TBOATS = True Then
ToGroup = ToGroup & "BOATS,CVN"
End If
End If
get_group = ToGroup
Case "FrmGroup"
If Forms!Criteria!FALL = True Then
FrmGroup = "'*'"
Else
If Forms!Criteria!FFRC = True Then
FrmGroup = FrmGroup & "'FRC',"
End If
If Forms!Criteria!FMALS = True Then
FrmGroup = FrmGroup & "'MALS',"
End If
If Forms!Criteria!FOCONUS = True Then
FrmGroup = FrmGroup & "'OCONUS',"
End If
If Forms!Criteria!FBOATS = True Then
FrmGroup = FrmGroup & "'BOATS','CVN'"
End If
End If
get_group = "(" & FrmGroup & ")"
End Select
End Function
**************************************************
