BOF EOF partially works ??

[maverickmonster]

I have following code.

Which = "Select * from [BPD] where [BPD].dob = #" & defRsp & "# "

RSbpd.Open Which, cn, adOpenKeyset, adLockOptimistic

If Not RSbpd.EOF And Not RSbpd.BOF Then
'loading process

It seams to be tempermental as it works some times and not others, the dates i try to lookup do exist in the db, but it works on some cases and not on others.

The error occurs on the cases that dont work when it gets to the RSbpd.EOF and RSbpd.BOF and they both equal true. which errors.

Presumably this is because it can not be found in the db table althought it definatly is !!!

Thanks in advance

Maverickmonster

 

[hmckillop]

THE EOF and BOF are correct for the actual recordset.
I would guess the best way of ensuring the recordset is fine is to use

Which = "Select * from [BPD] where [BPD].dob = #" & defRsp & "# "
msgbox Which 'copy this code
RSbpd.Open Which, cn, adOpenKeyset, adLockOptimistic

And then paste the copied code into Access SQL and execute it. I think it mustnt be returning data.
If you do this and you get records back, then I can look into it more, but as stated before I would guess its not returning records.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[pkailas]

Excuse me if I seem ignorant, but why do you care if it's Not BOF? I would think that if there is only one record, you'd get BOF as true? I know you'd need the EOF in case there were no records, but BOF?

 

[bjd4jc]

Is [BPD].dob stored as a date/time field in your database?

 

[Sheco]

As a side note, be aware that checking EOF/BOF can give you an error when a record happened with the recordset itself. Checking the .State property can prevent this error.

Also pkailas brings up a very good point. Checking both BOF and EOF makes good sense when you are dealing with data files but is not needed when checking ADO Recordsets.

I think that your VB code would be better like this:

...
RSbpd.Open Which, cn, adOpenKeyset, adLockOptimistic

IF (RSbpd.State <> 1) THEN
  'Failure opening ADO recordset

  'ToDo: Add code for reporting error here.

  Exit Function    'or exit sub or property or whatever...
END IF

IF NOT RSbpd.EOF THEN
  'loading process
...

PS: That BOF/EOF thing makes me wonder if you might have some experience doing file manipulation in the C language.

 

[maverickmonster]

hi thanks for your replies C was my first programming lang. In my db dob is a date time field

 

[RoyVidar]

Two questions:

1 - what is your date settings? If you are on different settings than US, you'll need to format to an unambiguous format, for instance:

[tt]where [BPD].dob = #" & format$(defRsp, "yyyy-mm-dd") & "# "[/tt]

2 - is it possible that either the variable or the field also contains time (not just date)? If only the variable, the above would probably suffice, if it could be in the field, you'd need to do some formatting there too, or other means of stripping off the time part.

Roy-Vidar

 

[maverickmonster]

Sorted twas a formatting problem USA to UK