Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Syntax Help(Dlookup) (A2k)

    I cant seem to get the syntax right for the multiple criteria in the DLookup statement.
    Any Ideas?

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim rst1 As DAO.Recordset
    Dim x As Integer
    Dim strNAME, strNAME1 As String

    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblGRAPH_TEMP", dbOpenDynaset)
    Set rst1 = db.OpenRecordset("tblGRAPH")
    x = 0
    Do While Not rst.EOF
    Do Until x = -12
    rst1.AddNew
    strNAME = DateAdd("m", x, rst![End Date])
    rst1!Area = rst!Area2
    strNAME1 = rst!Area2
    rst1!SEGMENT = rst!SEG
    rst1!Date = strNAME
    rst1!Item = rst!Item
    strNAME = rst!Item
    rst1("Value") = DLookup("[" & x & "]", "tblGRAPH_TEMP", "[ITEM]= " & """ & strNAME & """ And [Area2] = " & """ & strNAME1 & """")
    rst1.Update
    x = x - 1
    Loop
    rst.MoveNext
    x = 0
    Loop

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Syntax Help(Dlookup) (A2k)

    It seems to me that you don't need a DLookup here - you're looking up a value in the table that is opened in rst, for the "current" Item and Area2. Isn't that the same as looking up a value in the "current" record of rst? If so, you would only need

    rst1("Value") = rst("[" & x & "]")

    Or am I completely off?

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax Help(Dlookup) (A2k)

    Hans,
    Because the table I'm working with basically has the rows and columns reversed(not my design) I need to make sure I import the correct row and column.
    See attachment
    Thanks
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Syntax Help(Dlookup) (A2k)

    Your criteria specify conditions for Item and Area2. However, the combination of these is not unique in tblGRAPH_TEMP, so DLookup will return an arbitrary value. Is that OK, or should it take SEG into account too?

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax Help(Dlookup) (A2k)

    Hans,
    I thought about that. I figured If I could get the syntax for 2 criteria I could add the third.
    Can a dlookup have 3 criteria?
    Or is there a better way to convert the data?
    Thanks

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Syntax Help(Dlookup) (A2k)

    Putting in the third criterion means that you're effectively looking up the record that you already have in rst, so - as I wrote in my first reply - you don't need the DLookup. Use

    rst1("Value") = rst("[" & x & "]")

    or

    rst1!Value = rst("[" & x & "]")

    This gets the value of the field whose name is the value of x, i.e. the field [0]. [-1], etc. up to [-11].

    Note: the text values in these fields contain commas; in my regional settings the comma is the decimal separator, so I had to expand it a bit to make it work:<pre>rst1!Value = Replace(rst("[" & x & "]"), ",", "")</pre>


  7. #7
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax Help(Dlookup) (A2k)

    Thanks,
    I see now that I was making it more complicated than it needed it to be. <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>
    Thanks as always <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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