Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    DLast and criteria

    I have a table with 4 fields auto number"QBid", "Year","Sequence" and "Type". There are only 2 types "Revenue" & "Expenses".

    Trying to create a query or more accurately an SQL statement I can use in VBA, What I want to return is the last QBid where the type is "Revenue and or Expenses depending on the function calling it.

    I am having trouble with the where clause.
    this returns "missing operator"

    d = DLast("[QBid]", "[tblqbtrnrecord]", "Type =" & Revenue)
    this returns "you canceled the previous operation.
    d = DLast("[QBid]", "[tblqbtrnrecord]", "Type =" & " Revenue")

    This sequence returns invalid use of null.

    d = DLast("QBid", "tblqbtrnrecord")
    MsgBox d
    c = DLast("Type", "tblqbtrnrecord")
    MsgBox c
    e = DLast("QBid", "tblqbtrnrecord", "type" = c)
    MsgBox e

    I have tried all the ways I can think of any help greatly appreciated Since posting I checked another field "Year" (which does not help me) but this worked.
    e = DLast("QBid", "tblQBtrnrecord", "year = 2007")
    In case it was a problem with the field I created anither field Type2 and used an update query to transfer the dat from the original field to it.
    this
    d = DLast("QBid", "tblqbtrnrecord", "tblqbtrnrecord.Type2 = Revenue")
    returns you cancelled the previous operation.

    this TypeCntrl = "Revenue"
    d = DLast("QBid", "tblqbtrnrecord", "tblqbtrnrecord.Type2 =" & TypeCntrl)
    passing the criteria by control returns the same thing.

    However this still works
    d = DLast("QBid", "tblqbtrnrecord", "tblqbtrnrecord.year =" & 2007)

    Thanks

    Peter
    Last edited by mitchbvi; 2013-03-09 at 10:35. Reason: Additional Info

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Peter,

    Try it this way: d = DLast("[QBid]", "tblqbtrnrecord", "[Type] =" & Char(34) & "Revenue" & Char(34))
    OR
    d = DLast("[QBid]", "tblqbtrnrecord", "[Type] = 'Revenue' " )
    HTH
    Last edited by RetiredGeek; 2013-03-09 at 11:12.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Mar 2013
    Posts
    16
    Thanks
    0
    Thanked 1 Time in 1 Post

    The last what?

    Becareful with DLast() and DFirst().

    You want the last record based on what kind of a sort? Sort the domain one way and "last" might be different than when you sort the domain another way. I presume that DLast() will return the last record based upon the order of the primary key. But are you sure?

    I don't use First or Last in my queries unless I perform a sort myself, or if I don't really care what I get, as long as I get something. Using Domain Aggregate functions, you cannot specify a sort against a table. If your domain is a query, you can, of course, specify a sort and be comfortable with what these two functions return to you.

Posting Permissions

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