Weird Query problem

S

Suzette

I just discovered that my code on a recordset will not return records. If I
do a query using the same info, it will. This is too weird.


The code...

rsbonus.Open "SELECT * FROM qryBonusPaid WHERE BonusDate > #" _
& dteFileOld & "#", conn, adOpenDynamic, adLockReadOnly,
adCmdText


dteFileOld is a date filter used to only pull the most recent records (at
this point it's 11/30/04)

It returns no records. I created a query. The SQL on the query is

SELECT qryBonusPaid.* FROM qryBonusPaid WHERE BonusDate > #11/30/04#;
This one does return records.

Prior to this code, in the same module, other recordsets are opened and
dealt with. I'm stumped. Any ideas?

Suzette
 
M

Mike Labosh

I just discovered that my code on a recordset will not return records. If
I
do a query using the same info, it will. This is too weird.


The code...

rsbonus.Open "SELECT * FROM qryBonusPaid WHERE BonusDate > #" _
& dteFileOld & "#", conn, adOpenDynamic,
adLockReadOnly,
adCmdText
Click to expand...

I wonder if it's getting confused about adOpenDynamic + adLockReadOnly? The
Query window will give you a dynamic read+write datasheet. IIRC,
adOpenDynamic implies read+write, and then you are contradicting it with
adLockReadOnly. You might try adOpenSnapshot instead of adOpenDynamic.

Really, just a guess though.
 
S

Suzette

Nope. I tried it but that's not it. I changed it to adOpenDynamic and
adLockOptimistic (no OpenSnapshot available). (This is an ADODB.Recordset)
No records returned. I have other recordsets that open the same way with no
problem.

Suzette
 
M

Mike Labosh

Nope. I tried it but that's not it. I changed it to adOpenDynamic and
adLockOptimistic (no OpenSnapshot available). (This is an
ADODB.Recordset)
No records returned. I have other recordsets that open the same way with
no
problem.
Click to expand...

Is it throwing any kind of error message?
 
A

Andi Mayer

try with Format(dteFileOld,"\#mm\/dd\/yyyy hh\:nn\:ss\#;;;\N\u\l\l"

rsbonus.Open "SELECT * FROM qryBonusPaid WHERE BonusDate > " _

&Format(dteFileOld,"\#mm\/dd\/yyyyhh\:nn\:ss\#;;;\N\u\l\l") _
, conn, adOpenDynamic, adLockReadOnly,

If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW
 
S

Suzette

It's the weirdest thing. The original query had a "LIKE" formula in it.
When I took it out it fixed it. I created another field with the
characters needed and compared that. Fixed the whole problem.

Strange

Suzette
 
Top