Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query MAX (2000)

    Hi, I thought I would post this oddity I found when wanting to do a DLookup in a Query with a Max Total.

    I had two fields, say [DocId] and [Revision Number]. I had the 'Totals' showing and had [DocId] on 'Group by', and [Revsion Number] on 'Max'.

    When I ran this query it worked fine, displaying the maximum number of revisions for each DocId. However, when I tried to use a DLookup :

    val = DLookup("RevNo","Query1","[DocId] = '" & str & "'"

    it gave me the classic error: 'you have cancelled the previous operation' !!

    I have got around this by keeping the 'Group By' total for [DocId], but using the following expression in the next field, rather than the inbuilt 'Max' function.

    RevNo: Int(Max([Reviion Number]))

    When run the query's output is identical, and when run from VBA the DLookup works smoothly.

    Strange but tue!!

    (I know, I know, why not use the DMAX function in VBA ...!)

    <img src=/S/tongue.gif border=0 alt=tongue width=15 height=15>
    Thanks,

    pmatz

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

    Re: Query MAX (2000)

    If you use Max as Total option, Access will automatically name the column MaxOfRevisionNumber, unless you provide a column name yourself. So perhaps DLookup didn't recognize the field name. I just experimented, and could use DLookup without problem if I used the correct column name.

    Note: the code you posted is probably just an illustration, but for the record: it is not a good idea to use val and str as variable names, since they are also the names of VBA functions.

  3. #3
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query MAX (2000)

    Thanks Hans, yes, the variable names were just illustrative, it is true they are not good choices!

    well, that must have been it as i just re did the query and code, and its fine. I couldnt have noticed that it was renaming the coloumn.!

    thanks <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    im gonna post another Q <img src=/S/coffeetime.gif border=0 alt=coffeetime width=32 height=48>
    Thanks,

    pmatz

Posting Permissions

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