Like and Where clauses in a query?????

[D.Sn]

I have an Access 2000 database table that has a numeric field called
"Amount" in a table called STMASTER read by a query called qrySTMASTER that
queries the table STMASTER. "Amount" is numeric field in STMASTER.

I am creating a recordset object in VB6 to filter records in qrySTMASTER
which have the value 100 in the "Amount" field. I have created a field that
has the following string for the filter :

"[Amount] LIKE '%100%'"

I wish to get the recordset to return all values that have the string 100 in
the Amount field including values such as 100,2100,3100 etc.

However, I find that the above construct does not work when used as a filter
such as rs.Filter = "[Amount] Like '%100%'" but works fine when used in a
WHERE clause in an SQL statement.

I keep getting an error message that says " Run time
error -2147217825(80040e5f)

Filter Cannot be opened"

The filter "[Amount] LIKE '100'" works fine and returns all records with
"100" in the [Amount] field but does not return "2100" , "3100" etc.

Any idea why this is happening and how I get around it?

Thanks,

D.Sn.

 

[Van T. Dinh]

(newsgroups trimmed. Please restrict to max 3 relevant newsgroups).

Isn't it inefficient to use type-casting to Text and the Text comparison
while you could have used numeric comparison?

If you want [Amount] that ends in 100, you could use:

[Amount] MOD 1000 = 100

 

[D.Sn]

Like you, I first thought that the problem was related to the fact that
"100" is numeric and the LIKE operator is for strings. However, if you go
through my e mail message, you will find that I have stated that [Amount]
LIKE '100' throws up records that contain 100 in the "Amount" field. Hence,
it would seem to work with numerics as well. That is why I am not able to
understand why LIKE '%100%' does not work and I am looking for a solution
for this situation.

Also, as I have said " [Amount] LIKE '%100%' works fine in a WHERE clause of
an SQL statement. I do not know why it will not work in a Filter of a
recordset object.

By the way, I thought the wild card character for a string which VB6 uses
while accessing an Access db is "%" and not "*". Is this incorrect?

I do not think "*" & '100' & "*" will work.

(newsgroups trimmed. Please restrict to max 3 relevant newsgroups).

Isn't it inefficient to use type-casting to Text and the Text comparison
while you could have used numeric comparison?

If you want [Amount] that ends in 100, you could use:

[Amount] MOD 1000 = 100

--
HTH
Van T. Dinh
MVP (Access)

I have an Access 2000 database table that has a numeric field called
"Amount" in a table called STMASTER read by a query called qrySTMASTER that
queries the table STMASTER. "Amount" is numeric field in STMASTER.

I am creating a recordset object in VB6 to filter records in qrySTMASTER
which have the value 100 in the "Amount" field. I have created a field that
has the following string for the filter :

"[Amount] LIKE '%100%'"

I wish to get the recordset to return all values that have the string

100

in
the Amount field including values such as 100,2100,3100 etc.

However, I find that the above construct does not work when used as a filter
such as rs.Filter = "[Amount] Like '%100%'" but works fine when used in a
WHERE clause in an SQL statement.

I keep getting an error message that says " Run time
error -2147217825(80040e5f)

Filter Cannot be opened"

The filter "[Amount] LIKE '100'" works fine and returns all records with
"100" in the [Amount] field but does not return "2100" , "3100" etc.

Any idea why this is happening and how I get around it?

Thanks,

D.Sn.

 

[John Vinson]

I have an Access 2000 database table that has a numeric field called
"Amount" in a table called STMASTER read by a query called qrySTMASTER that
queries the table STMASTER. "Amount" is numeric field in STMASTER.

I am creating a recordset object in VB6 to filter records in qrySTMASTER
which have the value 100 in the "Amount" field. I have created a field that
has the following string for the filter :

"[Amount] LIKE '%100%'"

In Access the wildcard is * rather than %: try

[Amount] LIKE '*100*'

However, if Amount is truly a numeric field, this seems like a VERY
odd thing to do. Why are you trying to lump 1003, 910024, 100, 2100
and 10041 together? If it's actually being used as a text string,
might it not be better to use a Text datatype rather than number? The
same LIKE criterion will still work, but Access won't be required to
convert the number to text before doing the filtering.

One request: PLEASE limit your post to appropriate newsgroups. This
question has nothing to do with activexcontrols, devtoolkits, macros,
ado or most of the other newsgroups in your list. I'd have EMailed and
limited the response to microsoft.public.access.queries if you had an
email address I could figure out.

 

[Van T. Dinh]

No. My point is that if it is a numeric value, you should try to use
numeric comparison for *efficiency* rather than relying on JET to do the
type-casting (to String).

As soon as you use LIKE, it is a String comparison and NOT numerical
comparison so don't get confused.

For wild cards, "*" works for DAO and "%" works for ADO.

--
HTH
Van T. Dinh
MVP (Access)

Like you, I first thought that the problem was related to the fact that
"100" is numeric and the LIKE operator is for strings. However, if you go
through my e mail message, you will find that I have stated that [Amount]
LIKE '100' throws up records that contain 100 in the "Amount" field. Hence,
it would seem to work with numerics as well. That is why I am not able to
understand why LIKE '%100%' does not work and I am looking for a solution
for this situation.

Also, as I have said " [Amount] LIKE '%100%' works fine in a WHERE clause of
an SQL statement. I do not know why it will not work in a Filter of a
recordset object.

By the way, I thought the wild card character for a string which VB6 uses
while accessing an Access db is "%" and not "*". Is this incorrect?

I do not think "*" & '100' & "*" will work.

(newsgroups trimmed. Please restrict to max 3 relevant newsgroups).

Isn't it inefficient to use type-casting to Text and the Text comparison
while you could have used numeric comparison?

If you want [Amount] that ends in 100, you could use:

[Amount] MOD 1000 = 100

--
HTH
Van T. Dinh
MVP (Access)

I have an Access 2000 database table that has a numeric field called
"Amount" in a table called STMASTER read by a query called qrySTMASTER that
queries the table STMASTER. "Amount" is numeric field in STMASTER.

I am creating a recordset object in VB6 to filter records in qrySTMASTER
which have the value 100 in the "Amount" field. I have created a field that
has the following string for the filter :

"[Amount] LIKE '%100%'"

I wish to get the recordset to return all values that have the string

100

in
the Amount field including values such as 100,2100,3100 etc.

However, I find that the above construct does not work when used as a filter
such as rs.Filter = "[Amount] Like '%100%'" but works fine when used

in

a

WHERE clause in an SQL statement.

I keep getting an error message that says " Run time
error -2147217825(80040e5f)

Filter Cannot be opened"

The filter "[Amount] LIKE '100'" works fine and returns all records with
"100" in the [Amount] field but does not return "2100" , "3100" etc.

Any idea why this is happening and how I get around it?

Thanks,

D.Sn.