Help recordset clone & date

[Tracy]

Hi,

PLease can anyone tell me why the following code won't work:

dim rs as recordset
set rs = Me.RecordsetClone
rs.FindFirst "TestDate = " & Me!txtFormDate

TestDate: a field in a table of date type
txtFormDate: a text box in a form bound to a field in a table of date type

There is a record in the table with TestDate = 11/10/02 and my form field has 11/10/02 entered.

So rs.nomatch should be false! But it comes out as true.

However the following does work:

dim rs as recordset
set rs = Me.RecordsetClone
rs.MoveLast '** i.e. to record that i know has TestDat =11/10/02
If rs!TestDate = me!txtFormDate then
MsgBox "This does work"
End If

So in the first bit of code the match is NOT found, but in the second bit is IS found.

How can I make the first bit of code work.

Thansk for any help
Tracy

 

[cpod]

You just have to tell it that it's a date:

dim rs as recordset
set rs = Me.RecordsetClone
rs.FindFirst "TestDate = #" & Me!txtFormDate & "#"

 

[Tracy]

I've tried this but it still won't work. An other ideas?

 

[cpod]

Sorry, that should work. I even tried the code in my own database and it worked.

 

[Rich]

"TestDate = #" & Format(Me!txtFormDate,"mm/dd/yyyy") & "#"

 

[Tracy]

Nope, neither of these work. I've even created a new blank database to try it out, and it still won't match the two values.

Is there no other way this can be done?

Thanks for trying anyway.
Tracy

 

[Tracy]

It seems it can't compare 11/10/2002 with 11/10/02, even whan I enclose both values with "#" to say it's a date. Dates just don't make sense to me.

 

[Jeff Bailey]

Tracy

I've done a bit of testing and I have 2 ideas to offer.

First, as you have noted, you must have dates in US format in VB coding (MM/DD/YY) - but even that doesn't solve your problem.

So,

Second - your 'date' field may well be storing time info as well as just dates. When I tested I found that

rs.FindFirst "MatchRunDate = #10/02/2002 13:52:35#"

worked where

rs.FindFirst "MatchRunDate = #10/02/2002#"

did not for one of my tables.

So maybe that's what's driving you nuts!

If you stored the dates using Now() you'll find they contain time info as well as date info. Use Date() instead of Now() to avoid this.

HTH

Jeff

 

[Tracy]

Nope, still no luck. Time wasn't included in my dates. The starnge thing is that when I use the FindFirst method i.e.

rs.FindFirst "TestDate = " & Me!txtFormDate it doesn't work, but when I use

If rs!TestDate = Me!txtFormDate it does work.

Haven't used Now() or Date() either.

I am totally baffled.

However I have realised the safest way to do normal (i.e. non recordset) date comparisons is to declare a variable of date type and set your field to it. Then the comparison seems to work ok(ish)!

So sorry, no payment!
Cheers
Tracy

 

[Jeff Bailey]

OK

Use another criterion to get rs to the relevant row and then
look to see what you get if you use a msgbox to show you the 2 dates ...

msgbox "rs date is " & rs!TestDate
msgbox " Form date is " & Me!txtFromDate

Jeff

 

[Tracy]

The problem is you can't do a message box for the value of TestDate when you use

rs.FindFirst "TestDate = " & Me!txtFormDate.

When I use If rs!testDate = Me!txtForm Date it's fine.

I can get round the problem by not using the FindFirst method, but it is such an efficient method that I really would like to use it.

CHeers
Tracy

 

[Jeff Bailey]

Tracy

If you import enough stuff to show your problem into a test MDB and email me it I'll have a look to see if I can figure out what's going on ...

Jeff

jeff@dateline.gg

 

[Jeff Bailey]

In case anyone is interested ...

It turned out that the problem was with the recordsetclone. The reason the search was failing was that the form's Data Entry property had been set to Yes, meaning that there were no rows in the recordsetclone.

Very easy to miss that!

Jeff