 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / January 2008How do I save a query as a text file
I have a query with the following fields Id Staff Number First Name Surname I need to save the query as a text file using the Tilde to separate the fields, so it would look something like this. 1001~12345678~joe~smith 1002~87654321~fred~jones etc Using Access 97 & Windows XP Any help would be appreciated Steve You could create a query that has a single field: SELECT [Id] & "~" & [Staff Number] & "~" & [First Name] & "~" & [Surname] FROM MyTable ORDER BY [Id] You could then export that query using the TransferText method.  SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) >I have a query with the following fields > Id Staff Number First Name Surname [quoted text clipped - 12 lines] > > Steve Thanks Douglas I created the query as you suggested and it worked perfectly. How do I export the query using the TransferText method? And would it be possible to link it to a command button on my form? Regards Steve > You could create a query that has a single field: > [quoted text clipped - 20 lines] >> >> Steve Look up TransferText in the Help file. The code in your button's Click event will be something like: Private Sub MyButton_Click() DoCmd.TransferText asExportDelim, , "NameOfQuery", "C:\Folder\File.txt" End Sub If you want to prompt the user for the name of the file to use, see http://www.mvps.org/access/api/api0001.htm at "The Access Web". (Yes, I know that's overwhelming looking, but all you need to do is copy everything between Code Start and Code End and paste it into a new module. Once you've done that, put code like the sample at the top of the page into your routine) Private Sub MyButton_Click Dim strFilter As String Dim strSaveFileName As String strFilter = ahtAddFilterItem(myStrFilter, "Text Files (*.txt)", "*.txt") strSaveFileName = ahtCommonFileOpenSave( _ OpenFile:=False, _ Filter:=strFilter, _ Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY) If Len(strSaveFileName) > 0 Then DoCmd.TransferText asExportDelim, , "NameOfQuery", "C:\Folder\File.txt" End If End Sub Unfortunately, I don't believe you'll be able to get field names at the top of the file. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) > Thanks Douglas > [quoted text clipped - 29 lines] >>> >>> Steve Douglas I pasted your text into the buttons on click event and changed the text for my own query and location but when I click the button an error message is displayed stating "Compile error: Variable not defined" I click ok and it takes me back to the vb page with "asExportDelim" highlighted any idea what I'm doing wrong? thanks Steve > Look up TransferText in the Help file. > [quoted text clipped - 66 lines] >>>> >>>> Steve Sorry, my typo. It's acExportDelim. (I told you you should look TransferText up in the help file! <g>) SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) > Douglas > I pasted your text into the buttons on click event and changed the text [quoted text clipped - 75 lines] >>>>> >>>>> Steve Hi Douglas Corrected the typo and it worked - I'm nearly there! In between our posts I've been trying different things to try and get this to work, I created a macro using the transfer text action and added a button to my form to run it. This works the same as the code you supplied me with. Both methods place quotes at the beginning and end of the string of text. I also opened my query and selected save as from the file menu, save as type-text. When you click the Export button you get options, one of which allows you to remove the quotes.- which is the default setting. (select none from the Text Qualifier box) I would prefer just to click a button on my form as this is a job that will need doing ever 4 hours by 16 different people, some of which would not know how to export the query manually. The client has been very specific in how they want the text file, and they don't want quotes. My final question is there a small amendment that I could add to the macro or code that will produce a text file with no quotes" Thanks again for all your help Regards Steve > Sorry, my typo. It's acExportDelim. (I told you you should look > TransferText up in the help file! <g>) [quoted text clipped - 80 lines] >>>>>> >>>>>> Steve If you've save the specification that you create when you go through the File menu, you can tell the TransferText action (or method) to use that specification. You provide the name of the specification as the second argument. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) > Hi Douglas > [quoted text clipped - 108 lines] >>>>>>> >>>>>>> Steve Douglas Many thanks for all your help. It's working perfectly Regards Steve > If you've save the specification that you create when you go through the > File menu, you can tell the TransferText action (or method) to use that [quoted text clipped - 113 lines] >>>>>>>> >>>>>>>> Steve |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.