Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL, DMax, query???? (2003 (SP2))

    Could do with some advice on the optimal way of automatically putting a value into an unbound textbox.

    The value is from one field of a query that has a Max criteria on another field.

    This is the query;
    SELECT Last(Tbl52Data.StDateTime) AS LastOfStDateTime
    FROM Tbl52Data;

    TIA Paul

    Nearly forgot, the table changes according to a combo box (only the number changes though) e.g. if cboPress value is 06 then the query would be;
    SELECT Last(Tbl06Data.StDateTime) AS LastOfStDateTime
    FROM Tbl06Data;

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

    Re: SQL, DMax, query???? (2003 (SP2))

    Note: Last is not the same as Max!

    You can set the control source of the text box to

    =DLookup("LastOfStDateTime","NameOfTheQuery")

    You must recalculate the form (Me.Recalc) in the After Update event of the combo box.

  3. #3
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL, DMax, query???? (2003 (SP2))

    Yeah, I got a bit skewy there. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    I wanted the result from the query to show only the value from one field but the Max criteria set on another. I hadn't spotted that the criteria field is left out if the 'show' tick box is left blank.

    I had also hoped to be able to have one dynamic query (changing the source table in the FROM) instead of 5 or 6 versions.

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

    Re: SQL, DMax, query???? (2003 (SP2))

    It may well be possible to do it all in VBA code, but we'd need to have more info if you want to pursue that direction.

  5. #5
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL, DMax, query???? (2003 (SP2))

    VBA is fine; I'm hoping to learn more of that, through usage.

    What info do you need?

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

    Re: SQL, DMax, query???? (2003 (SP2))

    > What info do you need?

    Everything! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    Please provide detailed information about the tables, queries and forms involved. If you wish, you can post a stripped down copy of your database. See <post#=401925>post 401925</post#> for instructions.

  7. #7
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL, DMax, query???? (2003 (SP2))

    OK! I'm doing this at work & will have to be off in a few mins, so I'll do stripped down versions (FE & BE) for posting tomorrow.

    When I've posted these, you may wonder at there being 5 almost identical tables. This is because there are about 50,000 records produced each month. So to aid performance, I've split the data into the different machines (5 of).

    Catcha tomorrow [img]/forums/images/smilies/smile.gif[/img]

  8. #8
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL, DMax, query???? (2003 (SP2))

    OK, here
    Attached Files Attached Files

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

    Re: SQL, DMax, query???? (2003 (SP2))

    What a mess! (The setup, not your database)

    Does this do what you want?

    Me.txtDtFrom.Value = DMax("CDate(StDateTime)", strCrit)

  10. #10
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL, DMax, query???? (2003 (SP2))

    Mess, you ain't kidding. I only have to simplify it <img src=/S/cooked.gif border=0 alt=cooked width=50 height=46>

    Anyway, using CDate was just what I needed. Thank you <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

Posting Permissions

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