Finding the value in a string

[nadesico]

Hi,

I am trying to find a unique value in a string. For example, the value in
my [ServiceDate] field is [1, 2, 3, 4, 5, 6, 11, 12, 21] and another has a
value of [2, 10, 12, 15], and I am trying to create a query that will state
that if there is a “1†in the field then it is true. The query that I am
using now looks like this,

ServiceDate1: IIf(InStr([ServiceDate],"1")>0,True)

and when I run this query, both of the above fields return a true value,
because the query looks for any instance of “1†which includes 10, 12, and
15. Is there a way for me to change my query so that the first field comes
back as true and the second field as false instead of both being true? Any
help would be greatly appreciated.

Thanks

 

[John Spencer]

IIF(" " & [ServiceDate] & "," Like "* 1,*",True,False)

This relies on the multiple items in the ServiceDate field always have a
space before them and a comma after them.

Obviously, you have a design problem. You are storing multiple values
in one field. That is an indication that you need another table to
store these multiple values.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[nadesico]

Thanks it worked perfectly!!

IIF(" " & [ServiceDate] & "," Like "* 1,*",True,False)

This relies on the multiple items in the ServiceDate field always have a
space before them and a comma after them.

Obviously, you have a design problem. You are storing multiple values
in one field. That is an indication that you need another table to
store these multiple values.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi,

I am trying to find a unique value in a string. For example, the value in
my [ServiceDate] field is [1, 2, 3, 4, 5, 6, 11, 12, 21] and another has a
value of [2, 10, 12, 15], and I am trying to create a query that will state
that if there is a “1†in the field then it is true. The query that I am
using now looks like this,

ServiceDate1: IIf(InStr([ServiceDate],"1")>0,True)

and when I run this query, both of the above fields return a true value,
because the query looks for any instance of “1†which includes 10, 12, and
15. Is there a way for me to change my query so that the first field comes
back as true and the second field as false instead of both being true? Any
help would be greatly appreciated.

Thanks