Select * from ... does not produce correct result

[XV1957]

Hello Access Specialists,

I just noticed that the following statement produces the wrong record count:
"Set rstPersons = db.openrecordset("select * from Persons where LastName like '" & strLastName & "'", dbOpenDynaset)"

In the Persons file I have two records where the LastName = "Hurni".

When I build a query with "*Hurni*" as criterium it correctly gives me two records.

When I execute the statement above in a program and display the rstPersons.recordcount, it only gives me 1

What did I do wrong?

 

[moke123]

Are you doing rstPersons.movelast before obtaining the count?

 

[XV1957]

Hi moke123,
Thanks a lot for this very fast reply.
no I do not, and I suspect your question is the answer to my problem.
Let me check right away.

 

[moke123]

Another reason may be using a like comparison without using wildcards.

 

[XV1957]

Hi Moke123,
thanks to your help the problem is solved.
movelast did the trick
thanks a lot

 

[arnelgp]

You need to add the wildcard *, otherwise your Like there will be same as = operator.

 

[XV1957]

Thanks arnelgp,
thanks for your comment but my pgm works fine without the wildcard.

 

[moke123]

Thanks arnelgp,
thanks for your comment but my pgm works fine without the wildcard.

Just be aware that Like "Smith" and Like "Smith " (trailing space) may not match.

 

[XV1957]

OK I will add the wildchard. Thanks again

 

[Mark_]

@OP,

Any specific reason you did not use DCount?

https://msdn.microsoft.com/en-us/library/office/aa211471(v=office.11).aspx