Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parsing a field (2003)

    I have a table that has a field called Provision Detail which could have up to 12 lines of verbage. That means that a group number could appear 12 times according to how many lines this Provision Detail field has. I am trying to take this field and put it in one line going across by using the following:
    Expr12: IIf([LINE_ORDER]=12,[PROVISION_DETAIL],Null). I create a column for each line. My problem is if the verbage spans 12 lines, I am not getting it to go across in one line. I am still getting 12 lines per group. How would I parse this field so it goes straight across. There is a line order field that has 1 through however lines there are. I am not sure this is possible.

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

    Re: Parsing a field (2003)

    Do you mean that the text field Provision Detail contains multiple lines, separated by line breaks, or that there are multiple records, each with a single value in Provision Detail?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parsing a field (2003)

    Do you mean that the text field Provision Detail contains multiple lines, separated by line breaks, or that there are multiple records, each with a single value in Provision Detail?

    There are multiple records, each with a single value in Provision Detail.

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

    Re: Parsing a field (2003)

    Thanks. You could try the code from the Denormalize demo database from Roger's Access Library.

  5. #5
    Lounger
    Join Date
    Sep 2003
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parsing a field (2003)

    I am not sure how to use this code. The table that has the field that is in multiple lines is in a query connected to another query. This table has millions of records. I have to join it to this other table and set some criteria to get it down to what I need. Is there any other way to get this field into columns going across in one line? I work for a health company and the table that I am trying to get this field into multiple columns going across is health provisions. One group can have thousands of provisions and services. Multple that by a million groups and you have got a hugh file.

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

    Re: Parsing a field (2003)

    First I thought you might be able to use a crosstab query, but wait a minute - a table or query can have at most 255 columns (fields). So if there are thousands of health provisions, there is no way you'll get them across columns. Perhaps you should rethink what you're trying to do.

  7. #7
    Lounger
    Join Date
    Sep 2003
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parsing a field (2003)

    The provisions table is just a table of provisions for a particular product id. The way the provisions work is when I connect it to this other table (query) that has the group numbers. The provision table is connected to a product table through a product ID and then the product table is connected to the table that has the group number. I designate what provision I want (in the provision table) and in the provision table there is a field (provision detail) that has multiple lines making the group number duplicate for as many lines that this fields has - up to 12 lines. The first line could say "Yes", the second line could say "deductible 50 dollars", the third line could say "Family deductible" and so on. The number of lines varies - some have 2 lines, some have one line etc Instead of going down in rows, I would like each line to populate a column.

    Would that code in the database create all the fields? I am just not sure how to make the code work. I guess I am looking for a miracle.

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

    Re: Parsing a field (2003)

    I don't think you want to use the code after all - it creates a fixed table, but I think you'd want the result to be dynamic. I don't know whether it's easy to do that without knowing the exact structure of the tables.

    Instead of showing the values in several columns, you could perhaps show them in one column, concatenated. The attachment to <post#=301,393>post 301,393</post#> contains the code for a function named Concat that you can use to concatenate values into one long string. Copy the function into a standard module. You can then use it in expressions in queries and in the control source of text boxes on forms/reports.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parsing a field (2003)

    I copied the concat function into a module. When I run the query, it can't find the function concat. I used the following: ProvDetail:Concat("v Mktdec Provisions","Provision Detail","[ICIS_Product_ID]="&[ICIS_Product_ID],"Line ID",Chr(13) & Chr(10))

    I wasn't sure what the Line Number part was for. Do I need that? I have a field called Line Id but I just want to concat according the the Product ID.

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

    Re: Parsing a field (2003)

    Concat should be in a standard module - the kind of module that you create by clicking New in the Modules section of the database window or by selecting Insert | Module in the Visual Basic Editor. It should *not* be in the module belonging to a form or report.

    The next to last argument of Concat is an optional field to sort on; if you don't want to use it, you can use "":

    ProvDetail: Concat("v Mktdec Provisions","Provision Detail","[ICIS_Product_ID]="&[ICIS_Product_ID],"",Chr(13) & Chr(10))

  11. #11
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parsing a field (2003)

    I did put it in a standard module. I noticed when I pasted it in, some parts turned red.

    (aRSet As String, _

    aField As String, _

    Optional aCondition As String, _

    Optional aOrderField As String, _

    Optional aSeparator As String) As String


    and this part turned red:

    strSQL = "SELECT [" & aField & "] FROM [" & aRSet & "]" & _

    strSQL & " ORDER BY [" & aOrderField & "];"

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

    Re: Parsing a field (2003)

    It looks like blank lines have been inserted in between the text lines; this sometimes happens when you copy/paste code from a web page. If you delete the blank lines, the red should go away.

  13. #13
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parsing a field (2003)

    This is what I am entering in my query:
    Det: Concat("V Mktdec Provisions","Provision_Detail","[Icis_Product_Id]=" & [Icis_Product_Id],"Line_Order",Chr(13) & Char(10))

    I am getting an error in the following: Set rst = dbs.OpenRecordset(strSQL)
    3062 Duplicate output Alias 'Det'

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

    Re: Parsing a field (2003)

    Use another name than Det for the concatenated value, for example

    DetList: Concat(...)

  15. #15
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parsing a field (2003)

    I did that but I get the same error - Duplicate output alias 'Det'

Page 1 of 2 12 LastLast

Posting Permissions

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