Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    Ventura, California, USA
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting Items (VBA 2000)

    I have an access Database table that has multiple status's that can exist for a client. i.e. A01 = Current, A02 = Recert, P00 = Active and so on. I need to be able to grab the last status to pull the information but I have not been able to figure it out. The client can at first be current then changede to active then to current again. I want to pull the last status and use it to filter.

    If [status] = A01 then [UserID]

    But I keep pulling the first status line only. I need the last line.

    Any suggestions??

    Dan

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Counting Items (VBA 2000)

    How do you know the sequence? Can your query be modified to ORDER BY that field (e.g., DESCending)?

  3. #3
    Star Lounger
    Join Date
    Mar 2002
    Location
    Ventura, California, USA
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Items (VBA 2000)

    Unfortunately no. A patient can be discharged then be re-admited then sent for evaluations all in the same day.

    Dan

  4. #4
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: Counting Items (VBA 2000)

    You would be better to ask this question on the Access Forum but I would be looking at the DMax function.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  5. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Counting Items (VBA 2000)

    is there some kind of timestamp (DateTime field) you can order by? What fields do you have available? I think going by the physical order in the file might be an option, but I wouldn't know how to do it. Maybe I should slide this on over to Access...

  6. #6
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Items (VBA 2000)

    A clumsy way that may work would be, in summary:
    1. dim a recordset which retrieves the status's for the client
    2. Count the number of records using recordset.count
    3. Move to the first record
    4. Move.Next the number of records returned
    5. Voila, grab the field you require and place it into the query

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Counting Items (VBA 2000)

    As Andrew pointed out, you would have been better advised to post this in the Access forum, since Access VBA is somewhat different from that in the other Office apps and it's nearly impossible to build a decent Access database without using VBA code. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>.

    When you mention multiple statuses and "Lines", it makes me wonder what exactly you're describing. Do you mean that there are multiple records in the table for a client, each with a different status, and some with possible repeating status values (I.e., more than one current status record for the same client)? How can you determine the "last" status? Do you have a date stamp in the record? I hope you aren't talking about multiple status indications in the same record, which is what the reference to "lines" makes me suspect. Give us more information on the table structure.
    Charlotte

Posting Permissions

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