Some language have an inlist function (inlist State = 'NY', 'CT',.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Some language have an inlist function (inlist State = 'NY', 'CT', 'NJ') is
there one in Access 2003? Or is there something equivalent?
 
Tupacmoche said:
Some language have an inlist function (inlist State = 'NY', 'CT', 'NJ') is
there one in Access 2003? Or is there something equivalent?
Click to expand...

Perhaps it should be intuitive what an "Inlist" function does, but it isn't
intuitive to me. If you will explain that, perhaps someone could suggest how
you accomplish the same purpose in Access.

To the best of my knowledge, there is no "Inlist" function in Access.

Larry Linson
Microsoft Access MVP
 
Hi Tupacmoche,

Access 2003 isn't a language.

In VBA, there's the Instr() function, e.g.:
If Instr("NY,CT,NJ", strState) > 0 Then
In SQL, there's the IN operator, e.g.:
WHERE State IN ('NY', 'CT', 'NJ')
 
Tupacmoche said:
Some language have an inlist function (inlist State = 'NY', 'CT',
'NJ') is there one in Access 2003? Or is there something equivalent?
Click to expand...

Is that function supposed to return true if State is in 'NY' or 'CT' or
'NJ'? Or is it to return the position of the matched entry?
Regardless, Access VBA doesn't have such a function, so you can't use it
in VBA code. Access SQL does have an In operator that can be used in
queries, if all you want to know is whether the item is in the list:

SELECT * FROM Addresses
WHERE State In ('NY', 'CT', 'NJ');

To get a similar effect in VBA code, I've been known to use the InStr
function:

If InStr(State, "NY,CT,NJ") > 0 Then
' it was in the list
End If

I seem to recall that you can also use the Eval function to get the In
operator to work:

If Eval("'" & State & "' In ('NY', 'CT', 'NJ')") Then
' it was in the list
End If

On the other hand, if you want the function to return the position of
the item in the list, there is no built-in function for that. Writing
one would be trivial, though.
 
Dirk Goldgar said:
If InStr(State, "NY,CT,NJ") > 0 Then
Click to expand...

I appear to have written this backward -- should have been

If InStr("NY,CT,NJ", State) > 0 Then

Sorry.
 
Dirk Goldgar said:
I appear to have written this backward -- should have been

If InStr("NY,CT,NJ", State) > 0 Then

Sorry.
Click to expand...

Just to be different, I typically include commas on either side, just to be
sure that it fails for, say, single characters ("N", "C"):

If InStr(",NY,CT,NJ,", ", " & State & ",") > 0 Then

(although I'll admit I've been known to go overboard...)
 
Douglas J. Steele said:
Just to be different, I typically include commas on either side, just
to be sure that it fails for, say, single characters ("N", "C"):

If InStr(",NY,CT,NJ,", ", " & State & ",") > 0 Then

(although I'll admit I've been known to go overboard...)
Click to expand...

No, you're quite right -- I've done it that way, too, but last night I
was obviously dopey and forgot that point along with getting the
arguments backward..
 
Back
Top