Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Can't Export Query as TXT (97)

    I took a query that worked and wanted to make it ask me which items to show so I took a number field called Chapter and added the following column to the query:

    Field: Chapter
    Table: Problems
    Sort: Ascending
    Show: NO
    Criteria: Like [Enter Chapter ] & "*"

    I did the same to a second field. The query works great. It asks me the two questions using a dialog box and only shows the records I want. But, when I go to export to a TXT files, I get the error message:

    "Two few parameters. Expected 2."

    I do not get this message exporting to RFT.

    If I delete the two "like" criteria, it exports just fine. I really want to be able to continue using the "like" criteria but I do have to export to TXT. Any suggestions?

    Ronny
    Ronny Richardson

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

    Re: Can't Export Query as TXT (97)

    Open the query in design view and click on the Query menu, then select parameters. Add each of the parameter expressions to the parameters, along with the datatype you expect it to return. For example, you could copy and paste the expression [Enter Chapter ] into a parameter and set the datatype to whatever you expect--text or a number. Then when you try to export, it should prompt you for the parameters.
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Re: Can't Export Query as TXT (97)

    Charlette,

    This does not work. If I copy the criteria as-is (or with quotes around it) to the parameters, Access tells me the names are invalid. I then tried using the field names associated with the criteria, but this just increased the number of questions that the query ask me from 2 to 4 and when I tried to export, I was told I have four "Too many parameters."

    It has to be sometime related to just TXT files since I can export to RTF without any problems.

    Ronny
    Ronny Richardson

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

    Re: Can't Export Query as TXT (97)

    No, you don't put quotes around it. Just copy the square brackets and what's inside them and paste that into the parameter line. You can even leave off the square brackets and Access will add them. However, you can't use the wildcards and such. Those stay in the criteria line. Creating the parameter just puts the query engine on notice that this is something it *must* deal with every time.
    Charlotte

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Re: Can't Export Query as TXT (97)

    Charlotte,

    If I just put the part inside the brackets into the parameter area, it does not cause any change at all. The extra prompts go away, the query works normally, the query WILL export to RTF but will not export to TXT.

    I had to cut most of the records to get it to compress to 100K but you can see the problem with the smaller attached version.

    It is a database of problems for a statistics textbook I am writing. The questions are exported to a text file to be inported into a specialized testbank program. The query adding the market code that the textbank uses for formatting when it imports the problem. The problem query is "Export Problems for MicroTest". (MicroTest is the testbank program.)

    Ronny
    Attached Files Attached Files
    Ronny Richardson

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

    Re: Can't Export Query as TXT (97)

    It's only a problem if you export to the Text Files type of file. If you export to Word Mail Merge, you'll get a tab-delimited text file that will export properly. The other format gives you a comma delimited text file.
    Charlotte

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Re: Can't Export Query as TXT (97)

    Charlotte,

    I tried the mail merge export format. If the problem with the text format cannot be resolved, I could live with this approach. However, it does have a couple of problems.

    The main problem is that it places each line inside quote marks. I already have to process the TXT file through a Word macro to clean up some problems with the Access output so I could add a couple of lines to the macro to strip off these extra quote marks.

    (The main problem I have to handle with the Word macro is that Access strips off any extra returns in a memo field and I need for the short answers, problems, and essay questions to leave the students room to work and respond. I got around that by having a numeric field where I enter the number of returns to add and then have the Word macro do a search and replace.)

    A second problem is that the "" that starts the code line now becomes a "". That is strange but, again, I can have the macro strip this off as well.

    A third problem is that words in quotation marks get a "" added to them so "statistics" becomes "statistics". Again, I believe that I can add a line to the Word macro to clean this out as well.

    I'm an Access user, not a developer, so I don't want to belabor the point but do you know what is causing this problem? Is there something wrong with the way I have set up the database, is this a bug in Access, or just one of those strange, un-understandable things that pops up with Office from time-to-time?

    Ronny
    Ronny Richardson

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

    Re: Can't Export Query as TXT (97)

    <hr>A second problem is that the "" that starts the code line now becomes a "".<hr>
    II'd like to help, but I have no idea what you're referring to. What code line and where? Is this Word code, Access code, data in the textfile, or what?
    Charlotte

  9. #9
    Star Lounger
    Join Date
    Mar 2001
    Location
    Eastern Pennsylvania, Pennsylvania, USA
    Posts
    97
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't Export Query as TXT (97)

    Ronnie, I have had the same problems, and have found no way to fix it so the query can be output. However, you can change the query to a make table query, and after running it, export the table. This way you are still getting the recordset within the applied parameters output. The other option is to build a report based on the query and expport the report to a .txt file (which was the way I went because it creates the text file in the same layout of the report). However, exporting a report as text tends to get tricky because access adds some random blank lines, and in report design mode you need to only drop the fields, not the labels that go with them.....Dave P.

Posting Permissions

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