Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting in a Query Causes a Parameter Prompt (A2K-SR1)

    Hi Again,

    I have a table where I am storing material receipt information. One of the fields is a string called SampleID. The SampleID is of the form "XXXXX-DDMM-I", where the trailing I is an index indicating the sequence number for the day DDMM of that particular truck load. As you can imagine I have encountered the familiar problem of trying to sort strings where the sequence of indices ends up as e.g. 1,10,11,12,2,3,4, etc. It is easy to extract the portion of the string after the second dash and then convert it to a number to use for numeric sorting.

    The problem comes when I try and do the extraction AND the sorting in the same query. If I just do the index extraction using a query whose SQL is :

    SELECT SampleID, InStr(InStr(1,[SampleID],"-",1)+1,[SampleID],"-",1) AS SecondDash, Len([SampleID]) AS SampleIDLength, Val(Right$([SampleID],[SampleIDLength]-[SecondDash])) AS ID_Index
    FROM [Dump Slag Receipts];

    it works fine. However, as soon as I try and sort on the calculated index using :

    SELECT SampleID, InStr(InStr(1,[SampleID],"-",1)+1,[SampleID],"-",1) AS SecondDash, Len([SampleID]) AS SampleIDLength, Val(Right$([SampleID],[SampleIDLength]-[SecondDash])) AS ID_Index
    FROM [Dump Slag Receipts]
    ORDER BY Val(Right$([SampleID],<font color=red>[SampleIDLength]</font color=red>-<font color=red>[SecondDash]</font color=red>));

    it prompts me for the <font color=red>[SampleIDLength]</font color=red> and <font color=red>[SecondDash]</font color=red> as parameters. Bear in mind that I am doing this in the graphical query design window, but showing you the resulting SQL to describe what I am doing.

    At this point in writing this post, because I have been looking at the SQL code which I wasn't doing before when using the graphical query window, I got the query to do what I wanted it to do as described below. I thought I would submit the following question anyway to see if anyone knew the answer.

    Why can one use derived fields in other derived fields, but not as components of the sort argument ? I say this because I have found if I put the calculations directly into the ID_Index calculation and do away with the contributing derived fields i.e. [SampleIDLength] and [SecondDash], it works OK - like this :

    SELECT SampleID, Val(Right$([SampleID],CInt(Len([SampleID])-InStr(InStr(1,[SampleID],"-",1)+1,[SampleID],"-",1)))) AS ID_Index
    FROM [Dump Slag Receipts]
    ORDER BY Val(Right$([SampleID],CInt(Len([SampleID])-InStr(InStr(1,[SampleID],"-",1)+1,[SampleID],"-",1))));

    It seems long-winded to have to repeat the whole index extraction calculation in the ORDER BY clause.

    Any comments please,

    Regards,

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

    Re: Sorting in a Query Causes a Parameter Prompt (A2K-SR1)

    Hi Adrian,

    I can't enlighten you on your main question, but I do have a suggestion: VBA in Office 2000 and up has a useful InStrRev function. It works like InStr, but searches from the end of the suppllied string.

    So you can replace

    InStr(InStr(1,[SampleID],"-",1)+1,[SampleID],"-",1)

    by

    InStrRev([SampleID], "-")

    Regards,
    Hans

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thanks for that

    <P ID="nt"><font size=-1>(No Text)</font>

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

    Re: Sorting in a Query Causes a Parameter Prompt (A2K-SR1)

    Adrian,

    This time I'm replying to your question about sorting on calculated fields. This seems to be possible in some versions of SQL, for instance in Paradox, but certainly not in standard SQL. See for instance here.

    However, I found something I wasn't aware of. You can sort on ANY field, including calculated fields, by referring to its index (first field = 1, second field = 2, etc.)

    Example: your SQL

    SELECT SampleID, Val(Right$([SampleID],CInt(Len([SampleID])-InStr(InStr(1,[SampleID],"-",1)+1,[SampleID],"-",1)))) AS ID_Index
    FROM [Dump Slag Receipts]
    ORDER BY Val(Right$([SampleID],CInt(Len([SampleID])-InStr(InStr(1,[SampleID],"-",1)+1,[SampleID],"-",1))));

    can be replaced by

    SELECT SampleID, Val(Right$([SampleID],CInt(Len([SampleID])-InStr(InStr(1,[SampleID],"-",1)+1,[SampleID],"-",1)))) AS ID_Index
    FROM [Dump Slag Receipts]
    ORDER BY 2;

    I don't know if this is more efficient; the query optimizer might "compile" both queries the same way.

  5. #5
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting in a Query Causes a Parameter Prompt (A2K-SR1)

    Hi Hans,

    I tried using the <font color=red>InStrRev</font color=red> function in a query but I get the error message <font color=448800>Undefined function 'InStrRev' in expression.</font color=448800>

    I checked in VBA, using the object browser, and the function exists and is part of the VBA.Strings class (is that the correct terminology ?). Obviously the reference to VBA exists in my VBA project because it cannot be removed from the reference list.

    The function works fine in VBA code. I was initially confused because I thought it would return the position from the end of the string. I found this is not the case - it returns the position from the beginning of the string - it just starts searching from the end !

    Any ideas why the function is not available in a query ?

    Thanks,

  6. #6
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting in a Query Causes a Parameter Prompt (A2K-SR1)

    Hi Hans,

    I am glad you learnt something as a result of my probing <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    That <font color=red>ORDER BY indexnum</font color=red> worked like a charm. It was quite interesting implementing that - you have to edit the SQL code itself. After one has added e.g. <font color=448800>ORDER BY 7</font color=448800> before the terminating <font color=448800>;</font color=448800>, when you return to the graphical query designer, it shows you the number 7 in the <font color=448800>Field</font color=448800> row and then "Ascending" in the <font color=448800>Sort</font color=448800> row. You cannot edit it in the graphical design view, because Access insists on prefixing any digit you type in the <font color=448800>Field</font color=448800> row with <font color=448800>Expr1 :</font color=448800>.

    Certainly a trick to keep in mind for the future,

    Regards,

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

    Re: Sorting in a Query Causes a Parameter Prompt (A2K-SR1)

    Hello Adrian,

    I don't have Access 2000, so I can't test InStrRev in a query. (I know about the function because I have VB6 and I know that Office 2000 VBA is based upon VB6).

    Can somebody else comment on this?

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

    Re: Sorting in a Query Causes a Parameter Prompt (A2K-SR1)

    I'm not sure what use it would be in a query, but it doesn't work there in my Access 2000 either.
    Charlotte

  9. #9
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting in a Query Causes a Parameter Prompt (A2K-SR1)

    I am glad to see it isn't only me that can't get it to work <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    The reason why I want to use it is because I have a text SampleID of the form X****X-DDMM-i. Since the number of characters before the first "-" is not fixed, I need to work out the position of the second "-" so I can extract the digit/s thereafter to use for numeric sorting in a query. It would be simpler to use one <font color=red>InStrRev()</font color=red> rather than two nested <font color=red>InStr()</font color=red> functions. When I described the problem previously, I think I stated the SampleID format as XXXXX-DDMM-i which made it look like the first portion was a fixed number of characters, which is not the case.

    I will look on TechNet and see if there is anything about <font color=red>InStrRev()</font color=red>,

    Thanks,

  10. #10
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting in a Query Causes a Parameter Prompt (A2K-SR1)

    I found the answer :

    MS KB article <font color=448800>ACC2000: Cannot Use New VBA Functions as Expressions [Q225956]</font color=448800>

    <font color=blue>This article applies to a Microsoft Access database (.mdb) and a Microsoft Access project (.adp).

    SYMPTOMS
    ========

    When you use one of the new Visual Basic for Applications functions as an
    expression, you may receive the #Name? error value. For example, if you set the
    ControlSource property of a text box to the expression =WeekdayName(6), the text
    box returns #Name? instead of the value Friday.

    CAUSE
    =====

    The FormatCurrency(), FormatDateTime(), FormatNumber(), FormatPercent(),
    InStrRev(), MonthName(), StrReverse(), and WeekdayName() functions are not
    supported as expressions.
    . . . . . </font color=blue>

    I won't clog up the forum with the complete solution, but In essence, the way around it is to write your own user-defined function and then call that from the query or ControlSource property or wherever you wanted to use an expression that wouldn't work.

Posting Permissions

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