Problem with DLookup

K

Kelvin Leong

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.
 
S

Steve Sanford

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...
 
K

Kelvin Leong

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...
 
S

Steve Sanford

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Slideshow with black border around it 0
DLOOKUP Help Please! 2
Finding the right style field in VBA 0
Problem in a query with DLookUp 1
DLookup with mulitple criteria - error message 5
List all tables, including document structure 0
Finding the proper style field and the numbered paragraph value within the style 0
Access 2007 - DLookup Function 1

Top