Results 1 to 8 of 8
  1. #1
    Alex Motilal is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2008
    Location
    Coimbatore, India
    Posts
    192

    DLookup issue

    Hello everybody,
    I am returning to the Forum after a long gap.

    I created a Function as follows:



    Public Function GetLintBudget(MktSeason As Integer, MktCenter As Integer, MktVty As Integer, MktDt As Date, factoryType As String) As Double
    If [factoryType] = "TMC" Then
    GetLintBudget = Nz(DLookup("budgetedLint", "tblMarketAndBudget", "dateOfMarket=#" & MktDt & "# And[marketSeason]=" & [MktSeason] & "And[marketInCenter]=" & [MktCenter] & "And[varietyArrived]=" & [MktVty]), 0)
    Else
    GetLintBudget = Nz(DLookup("budgetedLint-Con", "tblMarketAndBudget", "dateOfMarket=#" & MktDt & "# And[marketSeason]=" & [MktSeason] & "And[marketInCenter]=" & [MktCenter] & "And[varietyArrived]=" & [MktVty]), 0)
    End If
    End Function

    In the query based upon the Function, for certain dates, the Budgeted Lint returns the value, but for the remaining dates 0 is returned, even though values are in the table for the corresponding dates.
    I will be very happy if some experts can solve the problem.
    Regards
    Alex

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    No reason for VBA declared variables within [], although doesn't seem to hurt

    Probably should be a space after each And operator, especially if the [] are removed from variables.

    Otherwise can't see anything wrong with code.

    Have you step debugged?
    Last edited by June7; 03-26-2013 at 09:47 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Alex Motilal is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2008
    Location
    Coimbatore, India
    Posts
    192
    Thanks June7.
    Yes. When the "Invalid use of null" message came, I included Nz function.
    I tried removing the [], and again I get the same results.
    Now I tried Allen Brown's ELookup, and that also gives the same result.
    Alex

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    All of the function arguments are declared as Integer or String. Those variable types cannot handle null. Is it possible one or more are being fed with a null by the function call? Where is that function called from?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Alex Motilal is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2008
    Location
    Coimbatore, India
    Posts
    192
    Thanks June7
    I have checked all the records. No possibility of a null.
    This problem started after I Upgraded my OS to Windows 7 Professional. Previously it was Windows 7 Home base. It works perfectly in other systems with Windows XP, vista and Windows7 Home base. I am yet to check with systems having Windows7 Professional. Do you think it may be the problem and if so how can I rectify it.
    Alex

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    If it's the OS, have no idea how to fix. I have db that has been running on XP for years and now two new Windows 7 computers in the last 4 months. On the older W7 machine one procedure won't execute properly, on the other it is just fine. Fortunately, the user can live without that procedure because I gave up trying to fix.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    JrMontgom is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Sep 2012
    Location
    Vero Beach, FL USA
    Posts
    124

    DLookup problem try separatte debug.print on each variable

    When it starts to get weird I revert to basics and I make sure each DLookUp is returning a value using debug.print statements. As stated NZ doesn't work with strings and integers so that maybe the problem but my best guess is don't assume anything, test it and see. It is a pain but I have been astounded how may times my "assumptions" were wrong.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    JrMontgom, not sure what you mean by Nz doesn't work with strings and integers. What I said was string and integer variables cannot handle a Null value, only variant can hold Null.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Dlookup issue
    By Gilgamesh in forum Forms
    Replies: 5
    Last Post: 12-22-2012, 10:26 PM
  2. DLOOKUP issue
    By gemadan96 in forum Forms
    Replies: 7
    Last Post: 11-01-2012, 06:21 PM
  3. Dlookup issue
    By brharrii in forum Programming
    Replies: 3
    Last Post: 06-22-2012, 07:08 PM
  4. Dlookup annoying issue
    By Lupson2011 in forum Forms
    Replies: 8
    Last Post: 02-14-2012, 08:28 AM
  5. DLookup issue
    By seth1685 in forum Programming
    Replies: 5
    Last Post: 01-12-2012, 08:55 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums