Need SQL-function

[Bob Stelman]

Tony, Ronald

I get the impression that there are multiple values held within a single
field and that the idea is to test for a specific value within that set in
the field. I think the WHERE X=Y statement would fail because it is a
field-to-value comparison which returns true only when the field contains
only and exactly the value (case insensitive).

Consider the LIKE operator with the '*' wildcard. (Assumes MS Jet, ANSI std
would use '%' )

strSearchString = "Value"
"SELECT COUNT([sField]) FROM tblTable WHERE sField LIKE *" &
strSearchString & "*"

This would return all records that had the search string anywhere in the
field.

let me know what you think,
Bob Stelman

"Tony Oakley" <To...@Oakley.co.uk> wrote in message
news:3D525B28...@Oakley.co.uk...
> Ronald wrote:
> > Hi!
> >
> > Is there an SQL-statement that returns a count of which a specified
string
> > is in a field.
> >
> > For example, I've got a field with this value: "value1 value2 value3"
> >
> > Now I want a query like this "SELECT tally(sField,'value') FROM
tblTable"
> > which in this case would return 3.
> >
> > Regards,
> >
> > Ronald
> >
> >
>
> SELECT COUNT([sField]) FROM tblTable WHERE sField = "value"
>
> --
>
> Tony Oakley (Microsoft Access MVP)
> RF900RT
>