Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2004
    Posts
    173

    Unanswered: Query a List with Values and Commas

    I have a hidden textbox on a form that is populated with unique id values from a selection of records that is formatted like this - 5,6,7,14,19,20



    I want to print a report based on the data that is in the textbox?

    I tried using the criteria below on the id field in a query, but it does not work..


    id

    in(forms!form1.[txtSelected])


    what criteria do I put in my query

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    NOT a good way to store data. If there is any way you can avoid putting multiple values in a single control, I strongly encourage you to do so.

    If there is no other way around it, you might try something like this:

    ("," & id & ",") in ("," & forms!form1.[txtSelected] & ",")

    A better option might be dynamic SQL, where your report's source query is constructed by the OnOpen event.

    ...but it is a lousy way to design an application.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Aug 2004
    Posts
    173

    Query Selected Records - More Info and DB Attached

    Maybe more info can help. Here is what I need to do


    Select some records from a datasheet (sub-form on a form)

    then print a report based on that selection

    I have the selection part working, which puts the selected records in a textbox txtSelected


    then I want a report to print showing which records were selected

    Any help is appreciated

    Thanks - AB















    Quote Originally Posted by blindman
    NOT a good way to store data. If there is any way you can avoid putting multiple values in a single control, I strongly encourage you to do so.

    If there is no other way around it, you might try something like this:

    ("," & id & ",") in ("," & forms!form1.[txtSelected] & ",")

    A better option might be dynamic SQL, where your report's source query is constructed by the OnOpen event.

    ...but it is a lousy way to design an application.
    Attached Files Attached Files

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You'd think this would be easy, but it is not.

    There are a lot of ways to solve this problem, but none of them are simple.

    1) Dynamic SQL, as mentioned above.
    2) You can try the SQL Statement I suggested, for what it is worth.
    3) Instead of your current method, have your users select the values they want from a listbox that allows multiple selections, and then create a temporary table or dataset from their selection and use it to filter your report.
    4) In a single user environment you can add a boolean field to your list table and than have a subform where the user can check or uncheck it. Then it is a simple matter of filtering on that value in your report query.
    5) In a multi-user environment, you can have a separate table that stores filter definitions for different users.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Printing selected records

    How about this?

    It's a little different way of doing it but it does what you want it to do (you wouldn't necessarily need to write to the txtselected box.) Basically it just appends !id to a TempEmployees table and links that table back to the table Employees by the id in the report.
    Attached Files Attached Files
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Aug 2004
    Posts
    173

    Thanks - That will work wit a little adjustment

    Thanks for all your help pkstormy and blindman:

    That will work with a little adjustment...

    I was trying to do it at all at the form level, since this is a multi-user application, but I can create a temporary table on the user's pc to accommodate for that, then delete it after the report closes.

    I also plan to use the same selection procedure in order to update a selection of records elsewhere in the application.


    I agree that it is not easy , and it is a requirement set by my employer and their client that they do not wish to change.

    I sent a basic form, the actual form is for an inventory system where edits are allowed to the list price, markup, etc. in the datasheet, but they need to update many at once...

    The idea is to select 100 records with a 10% markup, and be able to update all of them to 15% at the same time...

    or, select 10 items from a specific department, and change the department for all of them at once...



    In other languages, they use a "fill down" function to do it...




    AB




    Quote Originally Posted by pkstormy
    How about this?

    It's a little different way of doing it but it does what you want it to do (you wouldn't necessarily need to write to the txtselected box.) Basically it just appends !id to a TempEmployees table and links that table back to the table Employees by the id in the report.

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    For Multi-User

    You can try this if the last code is going to be a problem in a multi-user environment.
    Attached Files Attached Files
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    In is an operator, not a function. Did you try it with a space between "IN" and your first paranthesis?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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