Returning A Value from a recordset - Dlookup?

[tembenite]

I've got a table that I've run a query on to get a recordset of. Of these values I want to find values that match a certain criteria, and then return the value in a cell of that record. VBA doesn't seem to like my syntax. Is there an easy way to do this. (Code is below) Thanks for your help!



daYEARS = Int((Now() - (DLookup("[hiredate]", "employees", "ID = " & empID))) / 365.25) ' Number Emp has been in company
SQL = "SELECT TableOfDaysEarned.type, TableOfDaysEarned.numberdays FROM TableOfDaysEarned WHERE (((TableOfDaysEarned.begyear)<="
SQL = SQL & daYEARS & ") AND ((TableOfDaysEarned.uptoyear)>" & daYEARS & "));"
Set daRECORDS = db.Openrecordset(SQL)
'This Section checks type of day, then Looks up the number of days earned based on recordset
'and calls AddDays to add these days
If (daType = "b") Then
daDAYS = DLookup("numberdays", daRECORDS, "TYPE = 'b'")
addDays empID, daType, daDAYS, daEXPIRE, "Accrual"
Else
daDAYS = DLookup("numberdays", daRECORDS, "TYPE = 'v'")
addDays empID, daType, daDAYS, daEXPIRE, "Accrual"
daDAYS = DLookup("numberdays", daRECORDS, "TYPE = 'p'")
addDays empID, daType, daDAYS, daEXPIRE, "Accrual"
End If

 

[jgc31]

I believe that the problem is the date strings syntax should be similar to the following

strSQL = "field = #" & datDate & "#"

 

[tembenite]

Clarification

Sorry I should have clarified. The part it doesn't like is all the Dlookup entries.

daDAYS = DLookup("numberdays", daRECORDS, "TYPE = 'b'")

 

[WayneRyan]

t,

Need quotes:

daDAYS = DLookup("numberdays", "daRECORDS", "TYPE = 'b'")

Wayne