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

    Next Available Job Number (2000)

    I've asked this question before, but I'm having difficulty implementing the suggestion, which is to use the NZ function in conjunction with the DMax function. I have an application where I need to assign the "next available job number", in sequence, for a specific date, where each date starts with job number 1 (start with job number 1 if no records have yet been created for the specific date, otherwise, start with the highest job number for that date and add 1). The suggestion that was made to me was:

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

    The problem, however, was that I needed to get the value of txtDate from a the Date function. I used the Date function in conjuction with the Str function, but the entire result of the NZ function call kept returning 1 even when there was a record already present having that particular date. I then looked up in the Access Help the syntax for implementing the DMax, and got the following syntax.

    curX = DMin("[Freight]", "Orders", "[ShipCountry] = 'UK'")

    I then realized that the look-up field, in this case [ShipCountry] was being compared with a value in single quotes, and figured that the there should also be quotes around my string date value, so I appended a single quote on either side of the returned date string as follows.

    MyDateStr = "'" & Str(Date) & "'"
    NewJobNo = Nz(DMax("[logJobNo]", "tblDayWPDocLog", "[LogDate] = " & MyDateStr), 0) + 1

    NOW.... I'm getting a type mismatch!

    Can someone just point me in the right direction? What is it, if anything, that I'm overlooking?

    Thanks sincerely,
    Steve

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Next Available Job Number (2000)

    >>MyDateStr = "'" & Str(Date) & "'"<<

    Try this instead:

    MyDatestr="#" & str(date) & "#"
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Next Available Job Number (2000)

    Is the date value in the field a date or a string? If it is really a string then the quotes should work. If it is a true date, however, you either need to wrap the string variable in the DMax function in a CDate() function to convert it to a date or delimit the value with hash marks (#) to make sure Access knows it's a date.
    Charlotte

  4. #4
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Next Available Job Number (2000)

    rather than

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

    Use

    NewJobno = NZ( DMax("jobno","tblJobs","jobDate=#" & format(txtDate,"mm/dd/yyyy") & "#"),0) + 1

    The # tells access that it is a date value. The format forces american date format as that is what VBA understands.

    After all the american date format is standard all accross the world, sarcasm off now.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Next Available Job Number (2000)

    If your date in the table is stored without a time then you can use a conversion of the criterion date into an integral value eg.
    "jobDate=" & clng(txtDate)

    The 'date' component in a date is stored as the integral part of a floating point number, the time as the floating point part.

Posting Permissions

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