Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jul 2001
    Location
    Oakland, California, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    puzzled by DMax error (A97 SR2)

    As part of a function to generate a custom counter, I use DMax to find out the highest existing value in a table. Unfortunately, it isn't working, and I'm baffled. <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

    This is what the code looks like:
    <pre>Function GetNextCounter(intWhichProject As Integer) As String
    'returns a string containing the next sequential serial # for
    'a particular project. Format is project's 3-letter abbrev. plus
    '4 numerals. Numeric part gets incremented by 1 for each new serial #.

    On Error GoTo GetNextCounter_Err

    Dim strCurrCounter As String
    Dim strProjectPrefix As String
    Dim intCurrValue As Integer

    'get current highest serial number for this project, or null if none exists
    <font color=red>'The next line is where the trouble occurs. </font color=red>
    strCurrCounter = DMax("[TrackingNumber]", "tblIRBInteractions", _
    "[WhichProject] = " & intWhichProject)

    If IsNull(strCurrCounter) Then 'no records exist for this project
    intCurrValue = 1 'so this is transaction #1
    Else
    'get numeric value of highest serial # and increment by one
    intCurrValue = Val(Right(strCurrCounter, 4)) + 1
    End If

    'get the project's 3-letter initials
    strProjectPrefix = DLookup("[ProjectInit]", "tblProjectInfo", _
    "[ID] = intWhichProject")

    'now return the value of the new serial #
    GetNextCounter = strProjectPrefix & Format(intCurrValue, 0)

    GetNextCounter_Exit:
    Exit Function

    GetNextCounter_Err:
    MsgBox Err.Description
    Resume GetNextCounter_Exit

    End Function
    </pre>

    The error description says "The expression you entered as a query parameter produced this error: 'Microsoft Access can't find the name 'intWhichProject' you entered in the expression." I gather that getting criteria right for domain functions is tricky - is there something in the way I'm calling DMax that isn't right?

    I also tried it with the questionable line like so:
    <pre> strCurrCounter = DMax("[TrackingNumber]", "tblIRBInteractions", _
    "[WhichProject] = intWhichProject")
    </pre>

    It gave the same error message. I thought it might be a problem with intWhichProject somehow not being visible within the function, but using a different variable name resulted in the same error message, just with a different name. (And just for that extra dash of frustration, I could swear this function used to work, and I don't remember editing it since then. Argh!)
    <img src=/S/help.gif border=0 alt=help width=23 height=15>

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

    Re: puzzled by DMax error (A97 SR2)

    You may be running into a problem because you're assigning the DMax result to a string variable. However, if DMax doesn't *find* a value, it returns a Null, and you can't assign a Null to a string variable. Try wrapping the DMax in an Nz function, like this:

    <pre>strCurrCounter = Nz(DMax("[TrackingNumber]", "tblIRBInteractions", _
    "[WhichProject] = " & intWhichProject), "")</pre>


    You'll have the same problem with the Dlookup you use later on if it doesn't find a value, so you'll need the Nz there as well. Since strings can't be Null, you'll need to change your If IsNull(strCurrCounter) to something like If Len(strCurrCounter)> 0.
    Charlotte

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: puzzled by DMax error (A97 SR2)

    I strongly suspect Charlotte has hit the nail on the head, as you probably don't have any data in the table yet. However our experience suggests using domain aggregate functions like DMax and DLookup is fraught with problems in this situation.

    For one thing, assuming that your system will be multiuser, it is possible for DMax to allow two people to try to enter the same number. Suppose Person1 starts to enter a record, then is interrupted for some reason, and Person2 starts to enter a record. Person1 then saves their record. When Person2 tries to save their record, either they are successful and you have a duplicate integer part of the Project Number, or if you have that as your primary key, they get an error saying that you can't have a duplicate key.

    The other problem is that if you get lots of records (say several thousand), the DMax function can get pretty slow. That would not be an issue to start with, but over time it could get to be one. The fact that your numeric part is only 4 digits suggest you don't ever expect to get more than 9,999 projects, so maybe this isn't an issue for you.

    In any event, the approach we generally favor is to store the number being incremented in a one row table. When a person starts to add a new record, the first thing we do is to get the number for that record, increment it by 1 and save it. That way if the scenario above occurs, Person2 gets the next number correctly. To be fair, this approach does mean that you could end up with gaps in the sequence if a person starts to add a record and then changes their mind. If your system must be fully auditable, then that could be a problem.

    I hope this adds more clarity than confusion to the situation.
    Wendell

  4. #4
    New Lounger
    Join Date
    Jul 2001
    Location
    Oakland, California, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: puzzled by DMax error (A97 SR2) followup

    Thanks for the advice about nulls and strings, Charlotte. That doesn't seem to be what was causing the trouble with DMax, but I'm sure it would have been the next thing I tripped over. Is this explicitly stated in the help files? I searched using all the combinations of search terms that I could come up with but couldn't find anything in the online help or in the Knowledge Base, and if any of the books I have mention this, they don't have it indexed under any term I checked (null, string, text, value, assign - that sort of thing). <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

    On the DMax problem, the error message that was appearing made me think the problem was in how I was specifying the criteria. Adding a new variable (strCriterion) and concatenating the parts that it comprises before calling DMax solved it:
    <pre> strCriterion = "[WhichProject] = " & intWhichProject

    'get current highest serial number for this project, or null if none exists
    strCurrCounter = Nz(DMax("[TrackingNumber]", "tblIRBInteractions", _
    strCriterion))
    </pre>

    This does the trick - my table of dummy data is now happily filled with the appropriate tracking numbers.

    Wendell, I thought about the concerns you raised, and in this case I don't think they'll cause problems. There might be several people with read-only access, but only one person with permissions to add and update. (All our dealings with the IRB are supposed to go to one person in our department, who then submits the paperwork. If anyone in my department suggests having an additional IRB liaison, I plan to resist vigorously. <img src=/S/hosed.gif border=0 alt=hosed width=73 height=24>) Additionally, this function is called from the BeforeUpdate event of the form, and getting and incrementing the tracking number is the last thing done before update. I think this will prevent any conflicts. Just in case, I think I'll look into adding some code to check for duplicate values before writing the number to the table.

    The problem of DMax slowing when there are too many records is unlikely to arise, I think. We generally have about 12 projects going at any one time (with about 2 ending each year and 2 new ones starting), and even the most active ones rarely have more than 20 interactions in a year. The more typical ones have 2 to 4 interactions in a year. The next time I have something like this to set up, I'll try using the one-row table approach -- although, since it needs to be able to generate tracking numbers for many different projects, it'd have to be more than one row. One per project, I'd guess -- but that'll have to wait till next time.

    Thanks again for the advice!

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

    Re: puzzled by DMax error (A97 SR2) followup

    All the domain aggregate functions (DMax, DLookup, DSum, etc.) return nulls if they don't find records matching the specified criteria. String variables can only accept strings. Only variants and dates can accept Nulls. You kind of have to work this out from synthesizing multiple pieces of information from the help files. Plus, try assigning a Null to a string variable, and you'll get a type mismatch error every time. That has a high learning reinforcement value when you're under the gun to make things work. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    PS/ Look up "using data types efficiently" in help. For information on the domain aggregates, lookup "domain aggregate functions". Here's a snippet from the help on DMax/DMin:
    <hr>The DMin and DMax functions return the minimum and maximum values that satisfy criteria. If expr identifies numeric data, the DMin and DMax functions return numeric values. If expr identifies string data, they return the string that is first or last alphabetically.

    The DMin and DMax functions ignore Null values in the field referenced by expr. However, if no record satisfies criteria or if domain contains no records, the DMin and DMax functions return a Null.<hr>
    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
  •