Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2009
    Posts
    67

    Unanswered: Dlookup not looking up smetimes

    I have table name TbleDailySales and Form name FrmSalesEntry and i have these fields in the table

    [ReportDate], [EndingStick], [Amount] Reportdate is Key and date and EndingStick in number and amount is currency.

    In the form I'm using Dlookup to bring the previous EndingStick and here is the codes

    =Dlookup("[EndingStick]","TbleDailySales","[ReportDate]=Forms![FrmSalesEntry]![ReportDate]-1")

    Sometimes I'm getting the previous day's record and sometimes nothing even when there is previous day in the table. Please help as these driving me crazy.

    Thanks

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    I don't know how much you know about how Access stores data, but I'd caution you to be careful what you're actually storing in that "date" field. You see when Access stores a date it's actually storing a Date and Time. So it could be that if you store the "ReportDate" to be a Tuesday, and Access stores Tuesday at 9:00pm, and you're Forms's Report Date is Wednesday at 8:00am, it could be finding Monday's date instead. I'd first try something along the lines of:
    Code:
    =Dlookup("[EndingStick]","TbleDailySales","Format([ReportDate], 'Short Date') = Format(Forms![FrmSalesEntry]![ReportDate]-1, 'Short Date')")
    If you are, in fact, explicitly storing only the Date in that field and only calling a date from the form, then it may be an issue with Dlookup. Dlookup doesn't give the option to sort, so it just grabs whatever record it finds first that matches your criteria. This might also be a source of headache for you.
    Me.Geek = True

  3. #3
    Join Date
    Aug 2003
    Location
    Bulgaria, Plovdiv
    Posts
    36

    Beter LookUp function

    Private Function DBLookUp(ByRef FieldName As String, _
    ByRef TableName As String, _
    ByRef strWhere As String) As Variant

    ' Purpose: Database String Look Up
    Dim varValue As Variant

    Set conConnection = CurrentProject.Connection

    StrSQL = "SELECT [" & FieldName & "] FROM [" & TableName & "] WHERE " & strWhere

    Set rst = New ADODB.Recordset
    rst.Open StrSQL, conConnection, adOpenForwardOnly, adLockReadOnly, adCmdText

    If Not rst.BOF Then
    rst.MoveFirst
    varValue = Nz(rst(0), "0")
    Else
    varValue = "0"
    End If

    DBLookUp = varValue

    rst.Close
    conConnection.Close
    Set rst = Nothing
    Set conConnection = Nothing

    End Function

  4. #4
    Join Date
    Aug 2003
    Location
    Bulgaria, Plovdiv
    Posts
    36

    More sophisticated Lookups

    ' This is a code snipet how ReadFields is used
    ' Read Fields uses DBLookUps function shown bellow


    intArgs = Cache.ReadFields("Field1,Field2,Field3,Field4," & _
    "Field5,Field6,Field7,Field8", _
    "Tablee", _
    "Search Criteria")

    If CBool(intArgs) Then

    ' Cache Hit
    ReDim strgetOpenArg(intArgs)
    For idx = 1 To intArgs
    strgetOpenArg(idx) = getOpenArg(Cache.Words, idx)
    Next idx
    Field1= strgetOpenArg(1)
    Field2= Format(CSng(strgetOpenArg(2)), "#0.00")
    Field3= Format(CSng(strgetOpenArg(3)), "#0.00")
    ....
    Field8 = Format(CSng(strgetOpenArg(8)), "#0.00")

    End If


    '-------------
    ' Description : Returns string variable from cache or database.
    ' Accepts : Fields="Field1,Field2..........."
    ' TableName = "TableName"
    ' strWhere = "FieldCriteria= " & Criteria
    ' Returns : String value
    '-------------

    Public Function ReadFields(ByRef Fields As String, _
    ByRef TableName As String, _
    ByRef strWhere As String) As Long
    On Error GoTo ReadFields_Err

    ' Assume failure
    ReadFields = 0
    mWords = ""

    ' Compile Key
    Dim vKey As String
    vKey = CStr(Fields) & ";" & CStr(TableName) & ";" & CStr(strWhere)

    ' Check if exists in memory
    If mDBLookUp.Exist(vKey) Then
    mCacheHits = mCacheHits + 1
    ' Get data
    mWords = mDBLookUp.Item(vKey)
    ReadFields = Cache.intCountWords(mWords)
    Exit Function
    End If

    ' Get From Database
    'If Len(vKey) < 128 Then
    mCacheMisses = mCacheMisses + 1
    ' Read from database
    If DBLookUps(Fields, TableName, strWhere) Then
    ' Add to collection for later use
    Call mDBLookUp.Add(mWords, vKey)
    ReadFields = Cache.intCountWords(mWords)
    Else
    'MsgBox "DBLookUps failed", vbCritical + vbOKOnly
    ReadFields = 0
    End If
    'Else
    ' MsgBox "ReadFields Assertion failed: vKey > 128", vbCritical + vbOKOnly
    ' ReadFields = 0
    'End If

    WrapUp:

    ReadFields_Exit:
    Exit Function

    ReadFields_Err:
    Call LogMsgError(Err.Number, Err.Description, ModuleName$, "ReadFields")
    Resume ReadFields_Exit
    End Function


    ' and here is DBLookUps function

    Private Function DBLookUps(ByRef Fields As String, _
    ByRef TableName As String, _
    ByRef strWhere As String) As Boolean
    ' Purpose: Get values for FieldName1, FieldName2, FieldName3, ... from Table: TableName
    On Error GoTo DBLookUps_Err

    ' Assume Failure
    DBLookUps = False

    intArgs = intCountWords(Fields): ReDim strgetOpenArg(intArgs)
    For idx = 1 To intArgs
    strgetOpenArg(idx) = getOpenArg(Fields, idx)
    Next idx

    ' Compile SQL string
    StrSQL = " SELECT [" & strgetOpenArg(1) & "]"
    If intArgs > 1 Then
    For idx = 2 To intArgs
    StrSQL = StrSQL & "," & "[" & strgetOpenArg(idx) & "]"
    Next idx
    End If
    StrSQL = StrSQL & " FROM [" & TableName & "]"
    StrSQL = StrSQL & " WHERE " & strWhere & ";"

    Set conConnection = CurrentProject.Connection
    Set rst = New ADODB.Recordset
    rst.Open StrSQL, conConnection, adOpenForwardOnly, adLockReadOnly, adCmdText

    If Not rst.BOF Then
    rst.MoveFirst
    mWords = CStr(Nz(rst(0), ""))
    For idx = 2 To intArgs
    mWords = mWords & "," & CStr(Nz(rst(idx - 1), ""))
    Next idx
    ' Success
    DBLookUps = True
    Else
    mWords = ""
    For idx = 2 To intArgs
    mWords = mWords & "," & ""
    Next idx
    End If

    WrapUp:
    rst.Close
    conConnection.Close

    Exit_DBLookUps:
    Set rst = Nothing
    Set conConnection = Nothing
    Exit Function

    DBLookUps_Err:
    If Not rst Is Nothing Then
    If rst.State = adStateOpen Then rst.Close
    End If
    If Not conConnection Is Nothing Then
    If conConnection.State = adStateOpen Then conConnection.Close
    End If
    MsgBox Err.Description
    Resume Exit_DBLookUps
    End Function

  5. #5
    Join Date
    May 2005
    Posts
    1,191
    Why not just use Allen Brown's Elookup() that I linked to in post #2.
    Me.Geek = True

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •