Problem with .EOF and .BOF

[rede96]

I'm using the DOA.Recordset method to open a recordset based on a SQL statement. (SELECT * FROM ....)

I then use 'If Not (rst.EOF and rst.BOF) then' ... to check if the recordset is populated.

This normally works fine except when I use some sort of Group by, Max etc. in the SQL statement. In this case, even when there are no records, the .EOF and .BOF read false. I even get a record count of 1.

Am I doing something wrong in the way that I am generating the recordset?

An example of my SQL would be as follows:

    strSQL = "SELECT Max(Tbl_ScanResults.EndTimeStamp) AS EndTimeStamp " & _
    "FROM Tbl_ScanResults WHERE (((Tbl_ScanResults.ScannerID)='" & MyScanID(i) & "') AND " & _
    "((DateValue([EndTimeStamp]))=DateValue(Now())));"

Any help would be much appreciated!

 

[Minty]

If you debug.print your sql statement and run it in the query window what result do you get?

I'm also a little wary of calling the max() value alias the same as the field name, pretty sure access has a bit of a hissy fit when you do that sometimes...

 

[NauticalGent]

...access has a bit of a hissy fit when you do that sometimes...

"Hissy fit"...industry term?

 

[Minty]

"Hissy fit"...industry term?

Yup - same one as when your wife says "It's fine"

 

[NauticalGent]

Must be hard-coded into the female OS!

 

[isladogs]

I find Access throws a 'massive wobbly' when it doesn't like what I want it to do...

 

[NauticalGent]

Only if the settings are set to "Queen's English"...

 

[NauticalGent]

Sorry for hijacking your thread Rede96...I would offer a suggestion, but I can't improve upon Minty's.

 

[rede96]

If you debug.print your sql statement and run it in the query window what result do you get?

I'm also a little wary of calling the max() value alias the same as the field name, pretty sure access has a bit of a hissy fit when you do that sometimes...

The SQL looked fine and worked fine for records that were populated.

However...after putting the SQL back into a normal query and playing around with it, I noticed it changed the SQL to using HAVING and GROUP BY instead of WHERE.

    strSQL = "SELECT Max(Tbl_ScanResults.EndTimeStamp) AS EndTimeStamp FROM Tbl_ScanResults " & _
    "GROUP BY Tbl_ScanResults.ScannerID, DateValue([EndTimeStamp])HAVING " & _
    "(((Tbl_ScanResults.ScannerID)='" & MyScanID(i) & "') AND ((DateValue([EndTimeStamp]))=DateValue(Now())));"

So I changed my SQL for my recordset and .EOF/.BOF now work...

Typical female operating system. There's always a number of ways to ask, but if you don't ask in the right way...

 

[NauticalGent]

Typical female operating system. There's always a number of ways to ask, but if you don't ask in the right way...

Quite right, thanks for marking the thread as "solved"

 

[Minty]

Glad you sorted it - I think it would work quicker with

((DateValue([EndTimeStamp]))=Date())

As you wouldn't be converting Now() to the current date for each record. (I suspect we're not talking earth shattering performance differences here...)

 

[rede96]

Glad you sorted it - I think it would work quicker with

((DateValue([EndTimeStamp]))=Date())

Ah ok. Thanks for the tip. It all adds up.