Problem with DLookup

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

Guest

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

Guest

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

Guest

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

dialog message tied to dlookup 4
DLookup with dates 1
DLookup? 1
dlookup in Control Source on a textbox 2
DLookUp problem 1
Problem with update coding 2
Default value of combo box based on another that uses DLookUp - continued 1
simple Dlookup problem 6

Top