Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Parameter Error (2002)

    Hi,

    I'm using some code to separate fields in a query that Hans gave me way back and always works. In this instance I keep getting prompted for a parameter value for [PosofSpace] and I'm not sure why. The SQL that I'm using in the query is:

    SELECT qryAnnualReportTotalCensusUnionQuery.OfficeName, qryAnnualReportTotalCensusUnionQuery.diag1, Count(qryAnnualReportTotalCensusUnionQuery.diag1) AS CountDX, InStr([diag1]," ") AS PosOfSpace, Left([diag1],[PosOfSpace]-1) AS ICD9Number
    FROM qryAnnualReportTotalCensusUnionQuery
    GROUP BY qryAnnualReportTotalCensusUnionQuery.OfficeName, qryAnnualReportTotalCensusUnionQuery.diag1, InStr([diag1]," "), Left([diag1],[PosOfSpace]-1)
    HAVING (((qryAnnualReportTotalCensusUnionQuery.diag1) Is Not Null))
    ORDER BY qryAnnualReportTotalCensusUnionQuery.OfficeName, Count(qryAnnualReportTotalCensusUnionQuery.diag1) DESC;


    Any ideas? If it helps, when first define [PosofSpace] the query runs fine and returns the number of characters before the first pace. This field reads as:

    PosOfSpace: InStr([diag1]," ")

    It is only when I use the second piece to try to separate the data. The field in the query that is giving me trouble reads:

    ICD9Number: Left([diag1],[PosOfSpace]-1)

    Thanks,
    Leesha

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

    Re: Parameter Error (2002)

    I think this is because you're grouping by the ICD9Number column. Try the following:

    1. Create a query based on qryAnnualReportTotalCensusUnionQuery. Add qryAnnualReportTotalCensusUnionQuery.* to the query grid, and the calculated columns

    PosOfSpace: InStr([diag1]," ")

    and

    ICD9Number: Left([diag1],[PosOfSpace]-1)

    Also add diag1, clear the Show check box and set the Criteria to Is Not Null. This query should NOT be a totals query. See if it works. If it does, save it.

    2. Create a new query based on the query you just saved, change it to a Totals query and add the fields you need.

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Parameter Error (2002)

    God Hans, that worked beautifully!

    Thanks so much!

    Leesha

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

    Re: Parameter Error (2002)

    Just "Hans" willl do, I don't insist on "God Hans". <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Parameter Error (2002)

    I'm howling!! I am convinced you walk on water!

    Thanks again,
    Leesha

Posting Permissions

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