MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

Problem with DLookup

  • Follow


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.
0
Reply Utf 11/1/2007 4:47:03 AM 

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

    '    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" wrote:

> 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.
0
Reply Utf 11/1/2007 6:18:01 AM 


Hi Steve,

Thank you very much. It works.

"Steve Sanford" wrote:

> 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
> 
>     '    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" wrote:
> 
> > 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.
0
Reply Utf 11/1/2007 7:12:03 AM 

You're very welcome.

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


"Kelvin Leong" wrote:

> Hi Steve,
> 
> Thank you very much. It works.
> 
> "Steve Sanford" wrote:
> 
> > 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
> > 
> >     '    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" wrote:
> > 
> > > 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.
0
Reply Utf 11/1/2007 7:23:02 AM
microsoft.public.access.formscoding 7496 articles. 31 followers.

3 Replies
187 Views

(page loaded in 0.094 seconds)

Similiar Articles:

validation rule (for a record in a table) with dlookup - microsoft ...
Hi In a table with a double primary key, I want, before the input of a new record, to check it doesn't exist. To solve this problem, I presum...

DLookup - why this syntax? - microsoft.public.access.forms ...
Microsoft Access: DlookUp Syntax problem - database.itags.org database.itags.org: Microsoft Access question: DlookUp Syntax problem, created at:Sat, 31 May 2008 22:11:00 ...

Difference from previous week - report - microsoft.public.access ...
I had to use a TOTAL field for use with the DLookup I thought I needed. I still don't know how to work out the difference which is the problem for me.

using dlookup in a query - microsoft.public.access
I'm trying to refer to that query in a dlookup statement in my BOM query. ... Query Problem - can't use field with ampersand (&) in its ... Problem in a query with DLookUp ...

Can you look for an image/OLE Object using dlookup - microsoft ...
I don't want to use external path referencing as I have already had problems with ... Chk is a variable (and PictID is a numeric field), that should be > >test = DLookup ...

Validation rule to prevent overlapping time periods - microsoft ...
validation rule (for a record in a table) with dlookup - microsoft ... To solve this problem, I presume, using a validation rule is the best ... Validation rule to prevent ...

DLookup (populating text based on other selections on form ...
... End Sub 2( this may be the problem)) - On the measure text box (the box i want to populate "You have to do something good for 20 people in 1 year"): =DLookUp ...

dlookup with multiple criteria - microsoft.public.access.forms ...
I can't figure out how to do dlookup with multiple criteria ... to something else as this is a reserved word in Access, and it can cause problems.

Configuration Progress - microsoft.public.word.docmanagement ...
Strange problem with Dlookup on Network share Windows 7 ... Strange problem with Dlookup on Network share Windows 7 ... with XP client to XP server configuration.

sharing files between win7 64 bit and wireless xp laptop ...
Problem with WMC for Win7 x64 -- live TV setup problem - microsoft ... sharing files between win7 64 bit and wireless xp laptop ... Problem with WMC for Win7 x64 -- live ...

Problems with DLookUp - Microsoft Access / VBA
Problems with DLookUp. Microsoft Access / VBA Forums on Bytes.

Problem with DMax & DLookup - Microsoft Access / VBA
Problem with DMax & DLookup. Microsoft Access / VBA Forums on Bytes.

Problem with report using DLookup DataBase
I am trying to put together an expense report based on two tables: 'Accounts' and 'Payments'. By using DLookup, I am able to show desired records on the

New User, having problems with DLookup DataBase
First of all, this is the first time I have tried using Access, let alone trying to build a database from scratch, so if I have made a ton of mistakes, pl

Problems with DLookUp in Access 2003 - Microsoft Answers
Hi, Trying to use DLookUp but keeps getting error - I cannot see where I'm going wrong I want to look up Technicians name in the tabel Technicians based on the ...

7/21/2012 12:58:17 AM


Reply:



Privacy Policy | All Times Are GMT(UTC) | powered by