Problem with DLookup

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there,

I have a table which has the following structure from which I want to
extract some information:

--------------------------------------------------------------------
| LGID | LeaveGroupStYr | LeaveGroupEndYr | LeaveDays |
--------------------------------------------------------------------
| 1 | 0 | 2 | 10 |
| 2 | 2 | 5 | 12 |
| 3 | 5 | 99 | 16 |
-------------------------------------------------------------------

I am coding for a command button to get the LeaveDays if the ServiceYears
falls between the LeaveGroupStYr and LeaveGroupStYr

Private Sub cmdCalculateLeave_Click()

Dim ServiceYears As Double

ServiceYears = (Date - Forms![Leave Entry Form]!DateHired) / 365.25
Me!LeaveEntitlement = DLookup("LeaveDays", "Leave Group", _
"LeaveGroupStYr >= " & ServiceYears & _
" AND LeaveGroupEndYr < " & ServiceYears)

End Sub

I tested out that if I have:
DLookup("LeaveDays", "Leave Group", "LeaveGroupStYr >= " & ServiceYears & _
" AND LeaveGroupEndYr < " & ServiceYears)
Result: NULL.

However, if either of the following, i get results, in terms of numeric value:
DLookup("LeaveDays", "Leave Group", "LeaveGroupStYr >= " & ServiceYears)
Result: 16
DLookup("LeaveDays", "Leave Group", "LeaveGroupEndYr < " & ServiceYears)
Result: 10

Can anyone help me as I can't get the record I want.
 
Hi Kelvin,

To calculate date differences, you should use the DateDiff() function.

The reason you weren't getting the result you were looking for is that the
gT and LT signs are reversed.

Try this:

'------------------------------------
Private Sub cmdCalculateLeave_Click()
Dim ServiceYears As Double
Dim sCriteria As String

ServiceYears = DateDiff("yyyy", CDate(Me.DateHired), Date)
sCriteria = "LeaveGroupStYr <= " & ServiceYears & " AND LeaveGroupEndYr
" & ServiceYears
Click to expand...

' Me.Service = ServiceYears ' test control to see years diff

Me!LeaveEntitlement = DLookup("LeaveDays", "Leave Group", sCriteria)
End Sub
'------------------------------------

I used a variable for the criteria so I could see the criteria string when
stepping thru the code.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Kelvin Leong said:
Hi there,

I have a table which has the following structure from which I want to
extract some information:

--------------------------------------------------------------------
| LGID | LeaveGroupStYr | LeaveGroupEndYr | LeaveDays |
--------------------------------------------------------------------
| 1 | 0 | 2 | 10 |
| 2 | 2 | 5 | 12 |
| 3 | 5 | 99 | 16 |
-------------------------------------------------------------------

I am coding for a command button to get the LeaveDays if the ServiceYears
falls between the LeaveGroupStYr and LeaveGroupStYr

Private Sub cmdCalculateLeave_Click()

Dim ServiceYears As Double

ServiceYears = (Date - Forms![Leave Entry Form]!DateHired) / 365.25
Me!LeaveEntitlement = DLookup("LeaveDays", "Leave Group", _
"LeaveGroupStYr >= " & ServiceYears & _
" AND LeaveGroupEndYr < " & ServiceYears)

End Sub

I tested out that if I have:
DLookup("LeaveDays", "Leave Group", "LeaveGroupStYr >= " & ServiceYears & _
" AND LeaveGroupEndYr < " & ServiceYears)
Result: NULL.

However, if either of the following, i get results, in terms of numeric value:
DLookup("LeaveDays", "Leave Group", "LeaveGroupStYr >= " & ServiceYears)
Result: 16
DLookup("LeaveDays", "Leave Group", "LeaveGroupEndYr < " & ServiceYears)
Result: 10

Can anyone help me as I can't get the record I want.
Click to expand...
 
Hi Steve,

Thank you very much. It works.

Steve Sanford said:
Hi Kelvin,

To calculate date differences, you should use the DateDiff() function.

The reason you weren't getting the result you were looking for is that the
gT and LT signs are reversed.

Try this:

'------------------------------------
Private Sub cmdCalculateLeave_Click()
Dim ServiceYears As Double
Dim sCriteria As String

ServiceYears = DateDiff("yyyy", CDate(Me.DateHired), Date)
sCriteria = "LeaveGroupStYr <= " & ServiceYears & " AND LeaveGroupEndYr
" & ServiceYears
Click to expand...

' Me.Service = ServiceYears ' test control to see years diff

Me!LeaveEntitlement = DLookup("LeaveDays", "Leave Group", sCriteria)
End Sub
'------------------------------------

I used a variable for the criteria so I could see the criteria string when
stepping thru the code.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Kelvin Leong said:
Hi there,

I have a table which has the following structure from which I want to
extract some information:

--------------------------------------------------------------------
| LGID | LeaveGroupStYr | LeaveGroupEndYr | LeaveDays |
--------------------------------------------------------------------
| 1 | 0 | 2 | 10 |
| 2 | 2 | 5 | 12 |
| 3 | 5 | 99 | 16 |
-------------------------------------------------------------------

I am coding for a command button to get the LeaveDays if the ServiceYears
falls between the LeaveGroupStYr and LeaveGroupStYr

Private Sub cmdCalculateLeave_Click()

Dim ServiceYears As Double

ServiceYears = (Date - Forms![Leave Entry Form]!DateHired) / 365.25
Me!LeaveEntitlement = DLookup("LeaveDays", "Leave Group", _
"LeaveGroupStYr >= " & ServiceYears & _
" AND LeaveGroupEndYr < " & ServiceYears)

End Sub

I tested out that if I have:
DLookup("LeaveDays", "Leave Group", "LeaveGroupStYr >= " & ServiceYears & _
" AND LeaveGroupEndYr < " & ServiceYears)
Result: NULL.

However, if either of the following, i get results, in terms of numeric value:
DLookup("LeaveDays", "Leave Group", "LeaveGroupStYr >= " & ServiceYears)
Result: 16
DLookup("LeaveDays", "Leave Group", "LeaveGroupEndYr < " & ServiceYears)
Result: 10

Can anyone help me as I can't get the record I want.
Click to expand...
Click to expand...
 
You're very welcome.

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Kelvin Leong said:
Hi Steve,

Thank you very much. It works.

Steve Sanford said:
Hi Kelvin,

To calculate date differences, you should use the DateDiff() function.

The reason you weren't getting the result you were looking for is that the
gT and LT signs are reversed.

Try this:

'------------------------------------
Private Sub cmdCalculateLeave_Click()
Dim ServiceYears As Double
Dim sCriteria As String

ServiceYears = DateDiff("yyyy", CDate(Me.DateHired), Date)
sCriteria = "LeaveGroupStYr <= " & ServiceYears & " AND LeaveGroupEndYr
" & ServiceYears
Click to expand...

' Me.Service = ServiceYears ' test control to see years diff

Me!LeaveEntitlement = DLookup("LeaveDays", "Leave Group", sCriteria)
End Sub
'------------------------------------

I used a variable for the criteria so I could see the criteria string when
stepping thru the code.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Kelvin Leong said:
Hi there,

I have a table which has the following structure from which I want to
extract some information:

--------------------------------------------------------------------
| LGID | LeaveGroupStYr | LeaveGroupEndYr | LeaveDays |
--------------------------------------------------------------------
| 1 | 0 | 2 | 10 |
| 2 | 2 | 5 | 12 |
| 3 | 5 | 99 | 16 |
-------------------------------------------------------------------

I am coding for a command button to get the LeaveDays if the ServiceYears
falls between the LeaveGroupStYr and LeaveGroupStYr

Private Sub cmdCalculateLeave_Click()

Dim ServiceYears As Double

ServiceYears = (Date - Forms![Leave Entry Form]!DateHired) / 365.25
Me!LeaveEntitlement = DLookup("LeaveDays", "Leave Group", _
"LeaveGroupStYr >= " & ServiceYears & _
" AND LeaveGroupEndYr < " & ServiceYears)

End Sub

I tested out that if I have:
DLookup("LeaveDays", "Leave Group", "LeaveGroupStYr >= " & ServiceYears & _
" AND LeaveGroupEndYr < " & ServiceYears)
Result: NULL.

However, if either of the following, i get results, in terms of numeric value:
DLookup("LeaveDays", "Leave Group", "LeaveGroupStYr >= " & ServiceYears)
Result: 16
DLookup("LeaveDays", "Leave Group", "LeaveGroupEndYr < " & ServiceYears)
Result: 10

Can anyone help me as I can't get the record I want.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top