>How can I get the following code to grab ALL records if the user leaves the
>parameter blank (isnull)?  I don't know VBA (this code waw copied from the
>MS support site), so please be specific in your explanations!

>Thanks so much for your help!

>Linda  ... so close and yet so far

>Option Compare Database
>Option Explicit

>    Function GetToken(stLn, stDelim)
>         Dim iDelim As Integer, stToken As String
>         'Searches for commas as a delimiter
>         iDelim = InStr(1, stLn, stDelim)
>         If (iDelim <> 0) Then
>            stToken = LTrim$(RTrim$(Mid$(stLn, 1, iDelim - 1)))
>            stLn = Mid$(stLn, iDelim + 1)
>         Else
>            stToken = LTrim$(RTrim$(Mid$(stLn, 1)))
>            stLn = ""
>         End If
>         GetToken = stToken
>      End Function

>      Function InParam(Fld, Param)

>         Dim stToken As String
>         'The following two lines are optional, making queries
>         'case-insensitive
>         Fld = UCase(Fld)
>         Param = UCase(Param)
>         If IsNull(Fld) Then Fld = ""

>         'Parses out Values separated by commas
>         Do While (Len(Param) > 0)
>            stToken = GetToken(Param, ",")
>            If stToken = LTrim$(RTrim$(Fld)) Then
>               InParam = -1
>               Exit Function
>            Else
>               InParam = 0
>            End If
>         Loop
>      End Function

>--
>The opinions expressed in this communication are my own, and do not
>necessarily reflect those of my employer.

>You could do this through code, however, an easier way to do this would be
>to set your query criteria so it can handle potential Nulls.

>In the query criteria line define something like:

>tablename.somecol Like [user entered data] & "%"

>This allows the query to resolve a null to the wildcard field % (all).

>In code you code test the variant for IsNull then assign a wildcard to the
>value returned to the query.

>Steve

>>How can I get the following code to grab ALL records if the user leaves
the
>>parameter blank (isnull)?  I don't know VBA (this code waw copied from the
>>MS support site), so please be specific in your explanations!

>>Thanks so much for your help!

>>Linda  ... so close and yet so far

>>Option Compare Database
>>Option Explicit

>>    Function GetToken(stLn, stDelim)
>>         Dim iDelim As Integer, stToken As String
>>         'Searches for commas as a delimiter
>>         iDelim = InStr(1, stLn, stDelim)
>>         If (iDelim <> 0) Then
>>            stToken = LTrim$(RTrim$(Mid$(stLn, 1, iDelim - 1)))
>>            stLn = Mid$(stLn, iDelim + 1)
>>         Else
>>            stToken = LTrim$(RTrim$(Mid$(stLn, 1)))
>>            stLn = ""
>>         End If
>>         GetToken = stToken
>>      End Function

>>      Function InParam(Fld, Param)

>>         Dim stToken As String
>>         'The following two lines are optional, making queries
>>         'case-insensitive
>>         Fld = UCase(Fld)
>>         Param = UCase(Param)
>>         If IsNull(Fld) Then Fld = ""

>>         'Parses out Values separated by commas
>>         Do While (Len(Param) > 0)
>>            stToken = GetToken(Param, ",")
>>            If stToken = LTrim$(RTrim$(Fld)) Then
>>               InParam = -1
>>               Exit Function
>>            Else
>>               InParam = 0
>>            End If
>>         Loop
>>      End Function

>>--
>>The opinions expressed in this communication are my own, and do not
>>necessarily reflect those of my employer.

>Thanks Steve!

>can you show me exactly HOW In the code BELOW you could test the variant
for
>IsNull then assign a wildcard to the
>value returned to the query.

>I don't understand 1) how the parm  tablename.somecol Like [user entered
>data] & "%" would allow the user to enter multiple values and 2) where does
>tablename.somecol  belong on the grid ... same criterion row as Like, or is
>it the field name

>What's most important is to show me how you test for null and assign
>wildcard in the code below! :)

>THANKS SO MUCH!  :-)
>Linda

>--
>The opinions expressed in this communication are my own, and do not
>necessarily reflect those of my employer.

>>You could do this through code, however, an easier way to do this would be
>>to set your query criteria so it can handle potential Nulls.

>>In the query criteria line define something like:

>>tablename.somecol Like [user entered data] & "%"

>>This allows the query to resolve a null to the wildcard field % (all).

>>In code you code test the variant for IsNull then assign a wildcard to the
>>value returned to the query.

>>Steve

>>>How can I get the following code to grab ALL records if the user leaves
>the
>>>parameter blank (isnull)?  I don't know VBA (this code waw copied from
the
>>>MS support site), so please be specific in your explanations!

>>>Thanks so much for your help!

>>>Linda  ... so close and yet so far

>>>Option Compare Database
>>>Option Explicit

>>>    Function GetToken(stLn, stDelim)
>>>         Dim iDelim As Integer, stToken As String
>>>         'Searches for commas as a delimiter
>>>         iDelim = InStr(1, stLn, stDelim)
>>>         If (iDelim <> 0) Then
>>>            stToken = LTrim$(RTrim$(Mid$(stLn, 1, iDelim - 1)))
>>>            stLn = Mid$(stLn, iDelim + 1)
>>>         Else
>>>            stToken = LTrim$(RTrim$(Mid$(stLn, 1)))
>>>            stLn = ""
>>>         End If
>>>         GetToken = stToken
>>>      End Function

>>>      Function InParam(Fld, Param)

>>>         Dim stToken As String
>>>         'The following two lines are optional, making queries
>>>         'case-insensitive
>>>         Fld = UCase(Fld)
>>>         Param = UCase(Param)
>>>         If IsNull(Fld) Then Fld = ""

>>>         'Parses out Values separated by commas
>>>         Do While (Len(Param) > 0)
>>>            stToken = GetToken(Param, ",")
>>>            If stToken = LTrim$(RTrim$(Fld)) Then
>>>               InParam = -1
>>>               Exit Function
>>>            Else
>>>               InParam = 0
>>>            End If
>>>         Loop
>>>      End Function

>>>--
>>>The opinions expressed in this communication are my own, and do not
>>>necessarily reflect those of my employer.

>Hi Linda,

>Well, I wouldn't use that code for what you need to accomplish. It appears
>that the functions you posted allow retrieval of individual items from a
>delimited string.

>BTW, you need to substitute "*" for the "%" that I posted. (I've been
buried
>in SQL Server lately :)

>The code would really depend on how the user enters the parameter.  If you
>just needed a test for is null this would work:

>Function PassValue(varData As Variant) As String

>    If IsNull(varData) Then PassValue = "*"

>End Function

>If the data was entered in a form text control, you could do this without
>any code and code the query criteria for that table field as:

>Like Forms!frmTest!txtText & "*"

>Which will return all rows if the user leaves the text control blank, null
>or zero length string!

>Hope this helps!

>Steve

>>Thanks Steve!

>>can you show me exactly HOW In the code BELOW you could test the variant
>for
>>IsNull then assign a wildcard to the
>>value returned to the query.

>>I don't understand 1) how the parm  tablename.somecol Like [user entered
>>data] & "%" would allow the user to enter multiple values and 2) where
does
>>tablename.somecol  belong on the grid ... same criterion row as Like, or
is
>>it the field name

>>What's most important is to show me how you test for null and assign
>>wildcard in the code below! :)

>>THANKS SO MUCH!  :-)
>>Linda

>>--
>>The opinions expressed in this communication are my own, and do not
>>necessarily reflect those of my employer.

>>>You could do this through code, however, an easier way to do this would
be
>>>to set your query criteria so it can handle potential Nulls.

>>>In the query criteria line define something like:

>>>tablename.somecol Like [user entered data] & "%"

>>>This allows the query to resolve a null to the wildcard field % (all).

>>>In code you code test the variant for IsNull then assign a wildcard to
the
>>>value returned to the query.

>>>Steve

>>>>How can I get the following code to grab ALL records if the user leaves
>>the
>>>>parameter blank (isnull)?  I don't know VBA (this code waw copied from
>the
>>>>MS support site), so please be specific in your explanations!

>>>>Thanks so much for your help!

>>>>Linda  ... so close and yet so far

>>>>Option Compare Database
>>>>Option Explicit

>>>>    Function GetToken(stLn, stDelim)
>>>>         Dim iDelim As Integer, stToken As String
>>>>         'Searches for commas as a delimiter
>>>>         iDelim = InStr(1, stLn, stDelim)
>>>>         If (iDelim <> 0) Then
>>>>            stToken = LTrim$(RTrim$(Mid$(stLn, 1, iDelim - 1)))
>>>>            stLn = Mid$(stLn, iDelim + 1)
>>>>         Else
>>>>            stToken = LTrim$(RTrim$(Mid$(stLn, 1)))
>>>>            stLn = ""
>>>>         End If
>>>>         GetToken = stToken
>>>>      End Function

>>>>      Function InParam(Fld, Param)

>>>>         Dim stToken As String
>>>>         'The following two lines are optional, making queries
>>>>         'case-insensitive
>>>>         Fld = UCase(Fld)
>>>>         Param = UCase(Param)
>>>>         If IsNull(Fld) Then Fld = ""

>>>>         'Parses out Values separated by commas
>>>>         Do While (Len(Param) > 0)
>>>>            stToken = GetToken(Param, ",")
>>>>            If stToken = LTrim$(RTrim$(Fld)) Then
>>>>               InParam = -1
>>>>               Exit Function
>>>>            Else
>>>>               InParam = 0
>>>>            End If
>>>>         Loop
>>>>      End Function

>>>>--
>>>>The opinions expressed in this communication are my own, and do not
>>>>necessarily reflect those of my employer.

>Thanks Steve, tried both options, neither works! :-(  ugh!

>Hmmm, what I really need is ....

>Protocols Table has a multi-control Primary Key consisting of:
>                                        primary key      DrugCode  /primary
>key   Protocol /  primary key       Center#
>                            ex>                                    25000
>/546 / 1
>                             ex>                                    25000
>/546 /  5
>                            ex >                                   25000
>/548 / 1
>                             ex>                           SB265805 / 546
/1
>                            ex>                            SB265805 / 548
/1
>                             ex>                           SB265805 / 548
/3

>must have this combination in order to have a unique record

>1.  enable user to select MULTIPLE DrugCode/Protocol/Center# COMBINATIONS

>user wants to only see   ex1> DrugCode:  25000         with Protocol
>546,548, 549 and All Center#s within those protocols (546,548,549)
>                               access should return:   25000/546,548,549
and
>all associated center#s

>                                           ex2> DrugCode:  25000       with
>Protocol 1243,363      and only Center#s 1,5,99,135
>                                                    DrugCode: SB265805
>withProtocol 112,196,450 and ALL Center#s within those protocols
>(112,196,450)
>                              access should return:   25000/1234,363 and
>Center#s 1,5,99,135

>                                          ex3>  Drug Code:SB265805  with
All
>Protocols and only Center#s 35,66,78,234
>                              ACCESS should return:  SB265805/112,196,450
>and only center#s 35,66,78,234

>2.  if null, bring in ALL recs for that control (either DrugCode.Protocol
or
>Center#)

>                                          ex4> DrugCode:  is Null  with
>Protocols 112,548 and Center# Is Null
>                                                           this would bring
>in DrugCode: 25000/548 protocol and All center#s having 25000/548
>                                               AND  would also bring in
>DrugCode: Sb265805/112 protocol and ALL center#s having Sb265805/112

>                                          ex5>  DrugCode: 25000  Protocols:
>is Null    and Center# IS Null
>                                                        this would bring in
>Drug Code: 25000/546,548,549,1243,363 and ALL center#s within those 5
>Protocols

>SELECT PROTOCOLs.DrugCode, PROTOCOLs.Protocol, PROTOCOLs.[Center#]
>FROM PROTOCOLs
>WHERE (((InParam([DrugCode],[Enter Drug Code(s): 25000,SB265805]))=True)
AND
>((InParam([Protocol],[Enter Protocol(s): 111,546,206]))=True) AND
>((InParam([Center#],[Enter Center#(s): 1,6,25,156,208]))=True))
>ORDER BY PROTOCOLs.DrugCode, PROTOCOLs.Protocol, PROTOCOLs.[Center#];

>Appreciate all words of wisdom, even if I can't get it to work!  :-))))))

>Many thanks again Steve!

>Linda

>--
>The opinions expressed in this communication are my own, and do not
>necessarily reflect those of my employer.

>>Hi Linda,

>>Well, I wouldn't use that code for what you need to accomplish. It appears
>>that the functions you posted allow retrieval of individual items from a
>>delimited string.

>>BTW, you need to substitute "*" for the "%" that I posted. (I've been
>buried
>>in SQL Server lately :)

>>The code would really depend on how the user enters the parameter.  If you
>>just needed a test for is null this would work:

>>Function PassValue(varData As Variant) As String

>>    If IsNull(varData) Then PassValue = "*"

>>End Function

>>If the data was entered in a form text control, you could do this without
>>any code and code the query criteria for that table field as:

>>Like Forms!frmTest!txtText & "*"

>>Which will return all rows if the user leaves the text control blank, null
>>or zero length string!

>>Hope this helps!

>>Steve

>>>Thanks Steve!

>>>can you show me exactly HOW In the code BELOW you could test the variant
>>for
>>>IsNull then assign a wildcard to the
>>>value returned to the query.

>>>I don't understand 1) how the parm  tablename.somecol Like [user entered
>>>data] & "%" would allow the user to enter multiple values and 2) where
>does
>>>tablename.somecol  belong on the grid ... same criterion row as Like, or
>is
>>>it the field name

>>>What's most important is to show me how you test for null and assign
>>>wildcard in the code below! :)

>>>THANKS SO MUCH!  :-)
>>>Linda

>>>--
>>>The opinions expressed in this communication are my own, and do not
>>>necessarily reflect those of my employer.

>>>>You could do this through code, however, an easier way to do this would
>be
>>>>to set your query criteria so it can handle potential Nulls.

>>>>In the query criteria line define something like:

>>>>tablename.somecol Like [user entered data] & "%"

>>>>This allows the query to resolve a null to the wildcard field % (all).

>>>>In code you code test the variant for IsNull then assign a wildcard to
>the
>>>>value returned to the query.

>>>>Steve

>>>>>How can I get the following code to grab ALL records if the user leaves
>>>the
>>>>>parameter blank (isnull)?  I don't know VBA (this code waw copied from
>>the
>>>>>MS support site), so please be specific in your explanations!

>>>>>Thanks so much for your help!

>>>>>Linda  ... so close and yet so far

>>>>>Option Compare Database
>>>>>Option Explicit

>>>>>    Function GetToken(stLn, stDelim)
>>>>>         Dim iDelim As Integer, stToken As String
>>>>>         'Searches for commas as a delimiter
>>>>>         iDelim = InStr(1, stLn, stDelim)
>>>>>         If (iDelim <> 0) Then
>>>>>            stToken = LTrim$(RTrim$(Mid$(stLn, 1, iDelim - 1)))
>>>>>            stLn = Mid$(stLn, iDelim + 1)
>>>>>         Else
>>>>>            stToken = LTrim$(RTrim$(Mid$(stLn, 1)))
>>>>>            stLn = ""
>>>>>         End If
>>>>>         GetToken = stToken
>>>>>      End Function

>>>>>      Function InParam(Fld, Param)

>>>>>         Dim stToken As String
>>>>>         'The following two lines are optional, making queries
>>>>>         'case-insensitive
>>>>>         Fld = UCase(Fld)
>>>>>         Param = UCase(Param)
>>>>>         If IsNull(Fld) Then Fld = ""

>>>>>         'Parses out Values separated by commas
>>>>>         Do While (Len(Param) > 0)
>>>>>            stToken = GetToken(Param, ",")
>>>>>            If stToken = LTrim$(RTrim$(Fld)) Then
>>>>>               InParam = -1
>>>>>               Exit Function
>>>>>            Else
>>>>>               InParam = 0
>>>>>            End If
>>>>>         Loop
>>>>>      End Function

>>>>>--
>>>>>The opinions expressed in this communication are my own, and do not
>>>>>necessarily reflect those of my employer.