Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2007
    Posts
    2

    Unanswered: Need hlep with DLookup in a query

    I have used DLookUp("[OdomEnd]","tblExpense","[ExpenseID]=" & [ExpenseID]-1) in a query to calculate mileage based on ending mileage from a previous record. It works perfectly UNLESS a record has been deleted from the underlying table. ExpenseID is the Key field I am using. I have 13 records but one was deleted so it goes from 11 to 13, 14, etc.. Is there a way to get this to work correctly every time even if a record has been deleted?

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Can you do this?

    =iif(isnull(DLookUp("[OdomEnd]","tblExpense","[ExpenseID]=" & [ExpenseID]-1)),"",DLookUp("[OdomEnd]","tblExpense","[ExpenseID]=" & [ExpenseID]-1))
    or
    iif(DLookUp("[OdomEnd]","tblExpense","[ExpenseID]=" & [ExpenseID]-1) = "", "",DLookUp("[OdomEnd]","tblExpense","[ExpenseID]=" & [ExpenseID]-1))

    disregard...I thought you were actually having problems with the dlookup return in the query.

    No. You cannot get MSAccess to readjust the number for deleted ID AutoNumbers. The best you can do is create a new field with a number field type verses the autonumber field so you have more control over the numbering (ie. increase or decrease this number on the form when a record is added/deleted). You can also delete and recreate the autonumber field but I seriously wouldn't recommend doing this, especially if it's a relational field to other tables.
    Last edited by pkstormy; 07-24-07 at 21:52.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    myle,

    Maybe I'm missing something but I didn't see anything related to re-using/re-adjusting deleted autonumbers in these threads.
    Last edited by pkstormy; 07-24-07 at 21:51.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Jul 2007
    Posts
    2
    Since I am just creating this db, I can delete the AutoNumber colume and then recreate it. I only have 13 records in the db to play with so that is ok.
    What I will need to do is make sure a user cannot delete a record and all will be well. DLookup works well but since the autonumber is not chronological if a user deletes, the DLookup skips calculating the previous record if it is not chronological ie 1,2,3,4,5. If record goes 1,2,3,5,6 the it does not calucate the value at record 5 since there is no record 4...lol
    Oh, well, I could do it with a function in the form but I would rather do it in the query.

    I thought there might be a way to write some code that if next number is null, move to next previous record ID. hmmm...let me try something.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If you need to count records in a report, you can very easily set an expression (ie. CC:1) in the query and then do a runningsum on that field in a report and it will count in the order you want it to. I think you can also do a CC:1 and count in the query but I'm not sure how to do it without a report. There's probably an expresion you can use to do this using just a query. Maybe that's what myle was referring to in his link. You create a function which adds 1 in a variable and returns that to the expression ie....
    Option Compare
    dim CN as integer
    Function CountingFunction() as integer
    CN = CN + 1
    CountingFunction = CN
    End Function

    and call the function in the query as an expression (ie. CC:CountingFunction())
    Last edited by pkstormy; 07-24-07 at 23:35.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I'm with myle, because I'd say to get away from any method that requires the autonumbers to be perfectly sequential. In real life, you'll end up with skipped numbers, so your method needs to allow for that. There were a couple of methods posted there to get the reading from the previous record.
    Paul

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    For my 2 cents again, if I had to do something chronological, I'd simply create a number field where I can manipulate the numbers.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    for what its worth I would store a date/time indicating what time period the expense claim was for (you can either make it the actual period, or just the date it was submitted.. it doesn't matter...

    then I'd look for the most recent date/time values to give the most recent claims... (settign the order to the date/time value DESCENDING and pulling the top n values would work


    ...bearing in mind an autonumber column has no express meaning to the outside (non computer world).. attempts to coerce meaning generally fail and cause symptoms such as you are experiencing.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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