check value of field in recordset

[Leslie]

I am trying to compare a date within a recordset. I have changed this code
around several different ways and my field ("DateSelected") in the recordset
is always null even though I know it's not. Any help on this is
appreciated.....below is a chunk of my code:

'Select Employees eligible for random selection
Set MyRS = MyDB.OpenRecordset("SELECT tblEmployeesx.ID,
tblEmployeesx.FullName, tblEmployeesx.RandomSelected, " & _
"tblEmployeesx.Type, tblEmployeesx.DateSelected FROM tblEmployeesx WHERE
(((tblEmployeesx.RandomSelected)=False)) AND ((tblEmployees.Type =
'Employee'));")

'Select random employee
If (MyRS.EOF Or MyRS.BOF) <> True Then
MyRS.MoveLast
NumOfRecords = MyRS.RecordCount
SpecificRecord = Int(NumOfRecords * Rnd)
If SpecificRecord = NumOfRecords Then
SpecificRecord = SpecificRecord - 1
End If
MyRS.MoveFirst
For i = 1 To SpecificRecord
If MyRS.Fields("DateSelected").Value = datSelected Then
MsgBox "Someone has already been selected for that date. Please
try again."
FindRandom = "Try Again"
Exit Function
End If
MyRS.MoveNext
Next i

 

[BeWyched]

Hi Leslie

It's not clear why the field is returning a null as the syntax looks OK
(other than I would use the form - myRS![Date Selected] to return the date
value).

1. Is it possible that your recordset does not contain any records perhaps?
This could be if your SELECT statement is incorrect - try debug printing the
value of the recordcount and make sure its > 0.

2. A general comment - your coding looks a bit sledgehammer/walnut'ish. Why
not use a the simple DCount method to count the number of records. Using
recordsets to establish the record count is inefficient (according to MS
anyway).

Then after selecting your record you could use another DCount to count the
number of records with a matching date rather than spinning through the
entire recordset (again inefficient). If the second RecordCount > 1 then you
know you have a duplicate.

I hope this helps - let me know if you want any help with coding the above
if you want to pursue that route.

Cheers.

BW