Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Aug 2001
    New York, NY
    Thanked 0 Times in 0 Posts

    Queries in VBA (Access 2000)

    Where can I get a sample of code that will create a new record with a given date (probably the current date) and put the next available number in sequence for that date unless no record has been created yet for that date, in which case I'd like it to use 1. Basically, I need code to query for the TOTAL/ MAX job number of all records on a certain date, and have the code use the number 1 if no records have been retrieved (because none were created yet for that date). i.e. I want the first record created for each date to start with 1.


  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Crystal Beach, FL, Florida, USA
    Thanked 33 Times in 33 Posts

    Re: Queries in VBA (Access 2000)

    Can be done with a simple DMax:

    NewJobno = NZ( DMax("jobno","tblJobs","jobDate=" & txtDate),0) + 1

    This gets the biggest JobNo in the table for a date specified in a control box. The NZ function ensures that if it doesn't find anything, it returns 0. Then you just add "1".
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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