Link to home
Start Free TrialLog in
Avatar of aplona
aplonaFlag for United States of America

asked on

Access Query export file

Hello,
    I have this query, which works great but i need it to export to  a txt file and in that txt file remove all the quatation marks and spaces except one space after 'Amount~~'  Here it is:
SELECT
    'Comp Name~~~~~~~~~~~',
    ',',
    'Comp Contact~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' ,
    ',',
    'ID' ,
    ',',
    'Phone Number~~~~~~~~' ,
    ',',
    'Email~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' ,
    ',',
 Referral.Hosp_Discharge_Date,
    ',',
    'Hosp~~~~~~~~~~~~~~~~~~~~~~~~~~~' ,
    ',',
    'HospID~~',
    ',',
 [Contact Info].Medicare_ID& String(12-len([Medicare_ID]), "~"),
    ',',
 [1)ID/Name/Goals].PLstName & String(30-len([PLstName]), "~"),
    ',',
 [1)ID/Name/Goals].PFstName & String(30-len([PFSTName]), "~"),
    ',',
 [Contact Info].Gender,
    ',',
 [Contact Info].Zip & String(10-len([Contact Info.Zip]), "~"),
    ',',
IIf([Hosp_Visit_24]=True,"T","F") AS Hosp_Visit,
    ',',
IIf([In_Home_Within 3]=True,"T","F") AS In_Home_Within,
    ',',
IIf([In_Home_After_3]=True,"T","F") AS In_Home_After,
    ',',
IIf([Tel_Within_1]=True,"T","F") AS Tel_Within,
    ',',
IIf([Tel_After_1]=True,"T","F") AS Tel_After,
    ',',
IIf([Other_Encounter]=True,"T","F") AS Other,
    ',',
IIf([Planning_Support]=True,"T","F") AS Planning,
    ',',
IIf([Med_Review]=True,"T","F") AS Med,
    ',',
IIf([Counseling_Self_mgmt]=True,"T","F") AS Counseling,
    ',',
IIf([Comm_Family]=True,"T","F") AS Comm,
    ',',
IIf([Timely_Interactions]=True,"T","F") AS Timely,
    ',',
IIf([Add_Health_Prob]=True,"T","F") AS Add_Health,
    ',',
IIf([Patient_Services]=True,"T","F") AS Patient,
    ',',
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~',
    ',',
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~',
    ',',
 Referral.EnrollmentDT,
    ',',
 [tblPhonePillars (3)].Ph14Date,
    ',',
    'Amount~~'
FROM
 (((([tblPhonePillars (4)] INNER JOIN ([tblPhonePillars (3)] INNER JOIN [1)ID/Name/Goals] ON [tblPhonePillars (3)].[StudyNo] = [1)ID/Name/Goals].[StudyNo]) ON [tblPhonePillars (4)].[StudyNo] = [1)ID/Name/Goals].[StudyNo]) INNER JOIN tblHomeVisitsPillars ON [1)ID/Name/Goals].[StudyNo] = tblHomeVisitsPillars.[StudyNo]) INNER JOIN tblHospitalVisitsPillars ON [1)ID/Name/Goals].[StudyNo] = tblHospitalVisitsPillars.[StudyNo]) INNER JOIN (([Contact Info] INNER JOIN Referral ON [Contact Info].[StudyNo] = Referral.[StudyNo]) INNER JOIN [tblPhonePillars (2)] ON Referral.[StudyNo] = [tblPhonePillars (2)].[StudyNo]) ON [1)ID/Name/Goals].[StudyNo] = Referral.[StudyNo]) INNER JOIN [tblPhonePillars (1)] ON Referral.[StudyNo] = [tblPhonePillars (1)].[StudyNo]
WHERE (((Referral.EnrollmentDT) Between [From Date] And [To Date]))
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

I don't think I follow your question.

This is the query SQL.  The spacing in the the sql has nothing to do with what gets exported or returned by the query.
That is down to what fields you are including in the query and you appear to be creating 'feidls' by putting in spurious quotation marks as part of your query design, although it is not obvious to me how you would be getting them there?


Are you creating the sql in code? If so then you need to look at that code.
ASKER CERTIFIED SOLUTION
Avatar of IrogSinta
IrogSinta
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of aplona

ASKER

irogsinta,
I still needed those commas, i have to follow these requirements.  The file is a field~~~~~~~~~~~,field1~~,field3~~~~~~~~~~~~,etc.  How do i tie the module to the query
Avatar of aplona

ASKER

I got the module set, when i try to export to txt a get an error, Two few paramters, expected 2. I can export to excel though
The commas get added when you do the export.
Avatar of aplona

ASKER

IrogSinta, did you see my last post?
Do you get an error when use just view the results of the query
Avatar of aplona

ASKER

No, just going to txt file.  i can view in excel but if i try to then save that as a txt the file isnt correct.
You're not trying to save it from Excel to a text file are you?  Are you using the Text File export wizard in Access I mentioned?
Avatar of aplona

ASKER

Yes, i'm am trying to go straight to txt from the wizard and i get that error.  As an FYI i can export to excel with no problems, but doesnt help me.
Are you saying you get an error while doing the steps in the wizard or when it tries to do the export or inside the text file it creates?
aplona,

Can you just post a sample DB with this source table. (or a scaled down version of it)
Then post a clear Graphical example of the "exact" output you require...?
Avatar of aplona

ASKER

Once i click OK on the export wizard it gives me that error.
As I stated, we are all just guessing here without a sample of the DB and a clear Graphical example of the "exact" output you require.
As boag said, this may be a good time to upload a sample db.
Avatar of aplona

ASKER

Look at query1
Sample-DB.zip
OK.

This problem was introduced in I think Access 2000 - and after this length of time is not going to be fixed any day now.

The most sensible solution (in my opinion) is to use a form to enter the 'from' and 'to' dates and then set your parameters to look at the form, instead of being prompted for values
Avatar of aplona

ASKER

Can you please elaborate?  How do i tie a new form to this and what code do i change in the query?
<Look at query1 >
...and?
Again, what is the *exact* output you require?
Either you add two textboxes to an existing form or you create a new form with the two textboxes.

You then set each of the criteria in the query using a pattern like...

Forms!YourFormNameHere!YourTextboxNameHere

If you are using A2007/2010 then you should set the textboxes to use the built-in datepicker.
Avatar of aplona

ASKER

its all listed above, it needs to go into txt file  here is an example:
comp name~~~~~~~~~~~~~~~,Comp contact~~~~~~~,ID~~~~~,10/04/2011,123456789~~~~~,
etc.  Thats just an example the actual number of tildes are in the query but the commas need to come into the txt file also as in this example.
As irogSinta said earlier, the commas will be added automatically to separate each field.
Avatar of aplona

ASKER

i have Forms!1stListBill!FromDate and Forms!1stListBill!ToDate.  How do i set that criteria for one field?
Using    Between ..And....        in exactly the same way you were doing it before
Avatar of aplona

ASKER

Thats what i did but i get Invalid bracketing of name 'Forms!1stListBill!FromDate'
I'm confused...?

What is the question here?
How to format the query with commas in the export...
This issue has been resolved (use an Export Spec)

Or
...How to "Fix" the query's "Invalid bracketing"?

Do we have 2 questions here, ...or one?
<Using    Between ..And....        in exactly the same way you were doing it before >
Pete is correct
<Thats what i did but i get Invalid bracketing of name 'Forms!1stListBill!FromDate' >
When you do what?

I simply deleted the parameter (as it was not relevant to the "Formatting" issue.

...besides I got no such error when I entered a date range of 1/1/2012 and 12/31/2012
Avatar of aplona

ASKER

I got it it was Between [Forms]![1stListBill].[FromDate] And [Forms]![1stListBill].[ToDate]
This is all apart of the same question.  Peter is saying use a form to run the query since no one can find out the original way to get it to txt.
Avatar of aplona

ASKER

Peter, i get the same problem exporting to text when  i use the form.
<This is all apart of the same question.>
...Not really, ...if the query was not working, then that should have been your (one) first question...

Then the "Formatting" issue should have been a new separate question...

So where do we currently stand on each part of this question?
Avatar of aplona

ASKER

The question is to get this query into the correct format that was desribed above, the code was provided but with error exporting into txt.  If I enter the dates directly as you mentioned, the data comes out very well but leaves the " in it which i stated needs to be eliminated.  I need to have the prompts though for dates as the above query has which brings us back to the original problem.  I have no problem asking more questions, i will be asking tons of them on this, i'm not trying to double dip.
Avatar of aplona

ASKER

Guys, i'll go ahead and point this one out.  I don't want to cause any confusion.  I'll repost about the error message.  Thanks for all your help, much appreciated and hope to hear from you on the next one.