Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2013
    Posts
    18

    Red face Unanswered: DlookUp error !!!

    Hi All,
    I'm faceing problime in Dlookup in vba, Need assistance.

    I'm getting error "access can't find the field '|' referred to in expression" while tring to execute the dllokup in vba

    below is the code which im tring to execure;

    Private Sub cmdPrg_Click()
    'Dim rsc As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tbl_Trans")
    Set rsc = db.OpenRecordset("tbl_DocType")
    Dim i As Integer, j As Integer, dl As Variant
    rs.MoveFirst
    Do Until rs.EOF
    For i = 2 To 7
    If Not IsNull(rs.Fields("Act8")) Then
    rs.Edit
    rs.Fields("Step") = 100
    rs.Update
    End If

    If Not IsNull(rs.Fields("Act1")) Then
    rs.Edit
    rs.Fields("Step") = 0
    rs.Update
    End If

    If IsNull(rs.Fields("Act" & i)) And Not IsNull(rs.Fields("Act" & i - 1)) Then
    dl = DLookup("C1", "tbl_DocType", rs.Fields("DOCID") = rsc.Fields("DOCID"))
    rs.Edit
    rs.Fields("Step") = dl
    rs.Update
    MsgBox DLookup("C" & i - 1, "tbl_DocType", [tbl_Trans]![DOCID] = [tbl_DocType]![DOCID])
    Else
    j = i
    End If
    Next i
    rs.MoveNext
    Loop
    End Sub

    Thanks in advance
    Prasanna R.D

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Dlookup requires 3 parameters
    the first is the column you want to return (the expression)
    the second is the table or query that you want to search in (the domain)
    the third is the criteria (effectively a where clause, without using he word where)
    Code:
    dl = DLookup("C1", "tbl_DocType", rs.Fields("DOCID") = rsc.Fields("DOCID"))
    so you want the value of column c1 in table tbl_doc_type WHERE rs.Fields("DOCID") = rsc.Fields("DOCID")
    which frankly is garbage. the first 2 parameters are fine, its the third parameter that is causing the problem

    What I suspect you want is where the document id is tbl_DocType = rs.Fields("DOCID") OR tbl_DocType = rsc.Fields("DOCID")

    so your dlookup should probably read:-
    Code:
    dl = DLookup("C1", "tbl_DocType", "DOCID = " & rs.Fields("DOCID"))
    if doc id is string/text/char then you need to delimit the value from rs with a ' or "
    Code:
    dl = DLookup("C1", "tbl_DocType", "DOCID = '" & rs.Fields("DOCID") & "'")
    ..in this case use a ' as you are already using " to delimit other literals
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2013
    Posts
    18
    Hi healdem,

    Thank u for your instant reply, and its working perfectly.

    need inputs,
    can i get the sum of column using DSUM
    like

    "C1"+"C"&i

    Thanks,
    Prasanna R.D

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I don't know, why not try it
    DSum Function - Access - Office.com
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2013
    Posts
    18

    sum of cloumns using Dllokup

    Hi Healdem,

    I have come to a conclusion for the sum of columns issue..
    kindly let me know is there any better way to do this...

    Private Sub cmdStpPrg_Click()
    Dim rsc As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tbl_Trans")
    Set rsc = db.OpenRecordset("tbl_DocType")
    Dim i As Integer, sm As Variant
    rs.MoveFirst
    Do Until rs.EOF
    For i = 1 To 7
    If Not IsNull(rs.Fields("Act" & i)) And IsNull(rs.Fields("Act" & i + 1)) Then
    Select Case i
    Case 1
    sm = DLookup("C1", "tbl_DocType", "DOCID = '" & rs.Fields("DOCID") & "'")

    Case 2
    sm = DLookup("C1", "tbl_DocType", "DOCID = '" & rs.Fields("DOCID") & "'") + _
    DLookup("C2", "tbl_DocType", "DOCID = '" & rs.Fields("DOCID") & "'")

    Case 3
    sm = DLookup("C1", "tbl_DocType", "DOCID = '" & rs.Fields("DOCID") & "'") + _
    DLookup("C2", "tbl_DocType", "DOCID = '" & rs.Fields("DOCID") & "'") + _
    DLookup("C3", "tbl_DocType", "DOCID = '" & rs.Fields("DOCID") & "'")

    Case 4
    sm = DLookup("C1", "tbl_DocType", "DOCID = '" & rs.Fields("DOCID") & "'") + _
    DLookup("C2", "tbl_DocType", "DOCID = '" & rs.Fields("DOCID") & "'") + _
    DLookup("C3", "tbl_DocType", "DOCID = '" & rs.Fields("DOCID") & "'") + _
    DLookup("C4", "tbl_DocType", "DOCID = '" & rs.Fields("DOCID") & "'")

    Case 5
    sm = DLookup("C1", "tbl_DocType", "DOCID = '" & rs.Fields("DOCID") & "'") + _
    DLookup("C2", "tbl_DocType", "DOCID = '" & rs.Fields("DOCID") & "'") + _
    DLookup("C3", "tbl_DocType", "DOCID = '" & rs.Fields("DOCID") & "'") + _
    DLookup("C4", "tbl_DocType", "DOCID = '" & rs.Fields("DOCID") & "'") + _
    DLookup("C5", "tbl_DocType", "DOCID = '" & rs.Fields("DOCID") & "'")

    Case 6
    sm = DLookup("C1", "tbl_DocType", "DOCID = '" & rs.Fields("DOCID") & "'") + _
    DLookup("C2", "tbl_DocType", "DOCID = '" & rs.Fields("DOCID") & "'") + _
    DLookup("C3", "tbl_DocType", "DOCID = '" & rs.Fields("DOCID") & "'") + _
    DLookup("C4", "tbl_DocType", "DOCID = '" & rs.Fields("DOCID") & "'") + _
    DLookup("C5", "tbl_DocType", "DOCID = '" & rs.Fields("DOCID") & "'") + _
    DLookup("C6", "tbl_DocType", "DOCID = '" & rs.Fields("DOCID") & "'")

    Case 7
    sm = DLookup("C1", "tbl_DocType", "DOCID = '" & rs.Fields("DOCID") & "'") + _
    DLookup("C2", "tbl_DocType", "DOCID = '" & rs.Fields("DOCID") & "'") + _
    DLookup("C3", "tbl_DocType", "DOCID = '" & rs.Fields("DOCID") & "'") + _
    DLookup("C4", "tbl_DocType", "DOCID = '" & rs.Fields("DOCID") & "'") + _
    DLookup("C5", "tbl_DocType", "DOCID = '" & rs.Fields("DOCID") & "'") + _
    DLookup("C6", "tbl_DocType", "DOCID = '" & rs.Fields("DOCID") & "'") + _
    DLookup("C7", "tbl_DocType", "DOCID = '" & rs.Fields("DOCID") & "'")
    End Select
    rs.Edit
    rs.Fields("Step") = sm
    rs.Update
    End If
    If IsNull(rs.Fields("Act1")) And IsNull(rs.Fields("Act2")) Then
    sm = DLookup("C1", "tbl_DocType", "DOCID = '" & rs.Fields("DOCID") & "'")
    rs.Edit
    rs.Fields("Step") = sm
    rs.Update
    End If
    If Not IsNull(rs.Fields("Act8")) Then
    rs.Edit
    rs.Fields("Step") = 100
    rs.Update
    End If
    Next i
    rs.MoveNext
    Loop
    End Sub

    Thanks
    Prasanna R.D

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I bet thats a perfomance pig

    generally you are better off doing as much as you can within SQL.
    its rarely clever to iterate through a SQL using loops
    its never clever to use domain functions such as dlookup or dsum)
    to repeat the dlookups within a loop is frankly a scary approach.

    if I were you I'd grab all the rows you need
    sum all the various types in a single SQL 'hit'
    then add together your various elements as required in VBA after running the SQL

    first off join the tables (presumably on the doc id)
    then sum the various columns
    eg:-
    strSQL = "select sum(c1) as SC1, sum(c2) as SC2, sum(c3) as SC3, sum(c4) as SC4, sum(c5) as SC5, sum(c6) as SC6, sum(c7) as SC7 from tbl_Trans
    join tbl_DocType on tbl_DocType.docid = tbl_Trans.docid"

    Set rs = db.OpenRecordset(strSQL)
    base on that you then have 7 items in the recordset
    var1 = rs!SC1
    var2 = rs!SC1 & rs!SC2
    .....
    or if you like
    var1 = rs!SC1
    var2 = var1 & rs!SC2
    var3 = var2 & rs!SC3
    .....
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Sep 2013
    Posts
    18
    Thank u for your valuable advice Healdem, actually I'm very new to access and sql,

    Thank u once again...
    i'll try it out as you suggested.

Posting Permissions

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