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

asked on

Access Query to txt Two few paramters, expected 2

Hello All,
    This query runs great but i need to export it to a txt file.  When i do this i get an error stating Two few parameters, expected 2.  I must have the prompts for date range.  if i enter the date range directly into the query it will export to txt no problem.  I tried using a form to enter the date range but once again had the same error when trying to export to txt.  Here's the query:
Select PadT("Comp Name",20),
PadT("Comp Contact",58),
"ID",
PadT("Phone Number",20),
PadT("Email",76),
[Referral].Hosp_Discharge_Date,
PadT("Hosp",31), PadT("HospID",8),
PadT([Contact Info].Medicare_ID,12),
PadT([1)ID/Name/Goals].PLstName,30),
PadT([1)ID/Name/Goals].PFstName,30),
[Contact Info].Gender,
PadT([Contact Info].Zip,10),
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,
PadT("",200), PadT("",200), [Referral].EnrollmentDT, [tblPhonePillars (3)].Ph14Date, PadT("Amount",8)
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 IrogSinta
IrogSinta
Flag of United States of America image

Does it work if you change your Between statement to Between #6/1/2012# And #7/1/2012# ?  Assuming you have data between those dates of course.
Avatar of aplona

ASKER

Yes, but i need to have the prompts.
I understand.  I was just trying to determine if this was the cause of the error?
When executing this in code, you need to supply the actual values for the parameters, not the parameter names.  If your using a saved querydef, then you need to open that and set the parameters before trying to execute it.

If your using a querydef and your parameters are form references, then it's easy to handle:

Dim db As Database
Dim qdef As QueryDef
Dim rs As Recordset


Set db = CurrentDb()
Set qdef = db.QueryDefs(source)
qdef.Parameters(0) = Eval(qdef.Parameters(0).Name)
Set rs = qdef.OpenRecordset()


Since the parameter name is a reference to the form and control, using Eval() gets the value for you. If you had multiple parameters, then you would just loop on the parameters collection doing a Eval() for each like this:

Dim db As Database
Dim qdef As QueryDef
Dim prm as Parameter
Dim rs As Recordset


Set db = CurrentDb()
Set qdef = db.QueryDefs(source)
For Each prm in qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = qdef.OpenRecordset()  


You could also set the paramertes by name individually:

qdef.Parameters("<name>") = <some value>

or by index:

qdef.Parameters(0) = <some value>
qdef.Parameters(1) = <some value>

JimD.
Using actual dates for now, follow the export steps and save the export macro (this is the last step in the export wizard if you are using 2007/2010).  

Then in your query put Between [Forms]![NameOfForm]![NameOfDateControl1] AND [Forms]![NameOfForm]![NameOfDateControl2].

Once these dates are entered in your form, call your Saved Export with:
DoCmd.RunSavedImportExport "Name Of Your Saved Export"
Avatar of aplona

ASKER

Im in 2007 but do not have the option to save the export as macro
It should be the last step, there is a checkbox to save the Export.
Avatar of aplona

ASKER

i see it
Avatar of aplona

ASKER

how do i call it from the form?  i have it saved as Export-1st_List_Bill
I haven't seen the form you created but if you have a button on there to export then add this to its OnClick event:
DoCmd.RunSavedImportExport "Export-1st_List_Bill"

Be sure to do a check first to make sure that there are dates entered in your date controls.
Avatar of aplona

ASKER

I get if 'DoCmd' is a new macro or macro group, make sure you have saved it.  When i saved the export it saved it to the external data tab, saved exports.
I believe you just pasted this code in the property section of the OnClick event.  Remove that and click the elipses [...] to the right of that so that it opens the VB editor.  That's where you would put it.
Avatar of aplona

ASKER

Got it but it spits out an error, Runtime error 31602, The specification with the specified index does not exist. Specify a different index.  Export-1st_List-Bill.  When i debug it highlights DoCmd.RunSavedImportExport "Export-1st_List_Bill"
When you go to the External Data tab and click on the SavedExports icon in the export section, Is your specification listed there?  Is it spelled exactly like Export-1st_List_Bill?
Avatar of aplona

ASKER

this is what i have
Capture.JPG
Looks like you have a space in there between List and _Bill.
Export-1st_List _Bill
Avatar of aplona

ASKER

got it, now i get runtime error 3709, the search key was not found in any record
Hmm, try changing your query to:
WHERE (((Referral.EnrollmentDT) Between #" & [Forms]![NameOfForm]![NameOfDateControl1] & "# AND #" & [Forms]![NameOfForm]![NameOfDateControl2] & "#"))

Open in new window

See it that works.
Avatar of aplona

ASKER

i get a syntax error for that line
Forget my last suggestion.  That was a bad one.  Can you try putting dates in your form and then open your query to see if you get any results.
Avatar of aplona

ASKER

The query will get the results but then i get the error exporting to text.
Can you upload your database?
Avatar of aplona

ASKER

Look at the 1st list bill query and form.  This sample version doesnt have the saved export in it.
sample.zip
Try this instead.

Add this to your module (before the PadT function):
Then revise your query to use
Between GetDate1() And GetDate2()

Then in  your form button's OnClickEvent add:
gloDate1 = cDate(Me.FromDate)
gloDate2 = cDate(Me.ToDate)
DoCmd.RunSavedImportExport "Export-1st_List _Bill"

Open in new window

I'm assuming that the Saved Export still has a space between List and _Bill.
Avatar of aplona

ASKER

It works, but its leaving the " in the txt file
Are you talking about your fields being in quotes?  If that is the case then you need to recreate your Saved Export (or perhaps you can edit the one you have) and make sure that Text Qualifier is set to none instead of ".
Avatar of aplona

ASKER

The only way to set it to none is to include the headers though.
I don't have access to Access right now to see the steps in the wizard.  Do you see an Advanced or Customize button in one of the steps so that you could change your settings?
Avatar of aplona

ASKER

I got it, just overlooked something.  I noticed though that now that when it pulls the date it adds the 00:00:00 after the date.
Try changing cDate to DateValue in the module.
Avatar of aplona

ASKER

That didnt work
Which date field are you referring to?
Avatar of aplona

ASKER

There are two date fields in the query [Referral].Hosp_Discharge_Date and [Referral].EnrollmentDT,  they used to come out as example 07/11/2012, now they are 07/11/2012 0:00:00
Change them in the query to DateValue([Referral].Hosp_Discharge_Date) and DateValue([Referral].EnrollmentDT)
Avatar of aplona

ASKER

I get a syntax error for that whole line
Avatar of aplona

ASKER

Would it have anything to do with the form and the date pickers?
You didn't put DateValue([Referral].Hosp_Discharge_Date) and DateValue([Referral].EnrollmentDT) in one line, right?  I meant for you to change them in their respective lines.
Avatar of aplona

ASKER

I did, i just corrected it and it saved correctly but still ouputs the 0:00:00
Avatar of aplona

ASKER

Would it work if the query was changed to say that that field should only be 10 characters?
Try CDate(CLng([Referral].Hosp_Discharge_Date))
and
CDate(CLng([Referral].EnrollmentDT))
Avatar of aplona

ASKER

Just changed it, still adds the 0:00:00
Okay, this should work:

Format([Referral].Hosp_Discharge_Date,"mm/dd/yy")
and
Format([Referral].EnrollmentDT,"mm/dd/yy")
Avatar of aplona

ASKER

Still adds the 0:00:00
That last one should have definitely worked.  You are saving the query, right?  :-)
You probably need to rebuild the Saved Export with that last change.
Avatar of aplona

ASKER

That did it.  Thx.  I have a long way to go with this and i just got a change in specs for the file but i dont think it should be major.  I am going to post that one very soon.  Much appreciated.  Btw, did you get a chance to look at that question from acouple days ago about the tab problems i was having on the main form?
I didn't.  I'll check it out later tonight.
Avatar of aplona

ASKER

I can post the question here and up the points, that might be easier since i think you can handle it qucikly and already understand whats going on????
That's up to you. I just don't have access to view your database till I get home tonight.
Avatar of aplona

ASKER

In this query, the section where the T and F are the output needs to be ,PadT([Contact Info].Zip,10),T-T-T-T-F-T,T-T-F-T-F-T-T,PadT("",200), etc.  Does this make sense?   Here is the query:
Select PadT("Comp Name",20),
PadT("Comp Contact",58),
"ID",
PadT("Phone Number",20),
PadT("Email",76),
Format([Referral].Hosp_Discharge_Date,"mm/dd/yy"),
PadT("Hosp",31), PadT("HospID",8),
PadT([Contact Info].Medicare_ID,12),
PadT([1)ID/Name/Goals].PLstName,30),
PadT([1)ID/Name/Goals].PFstName,30),
[Contact Info].Gender,
PadT([Contact Info].Zip,10),
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,
PadT("",200), PadT("",200), Format([Referral].EnrollmentDT,"mm/dd/yy"), [tblPhonePillars (3)].Ph14Date, PadT("Amount",8)
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 GetDate1() And GetDate2()));
Change this section:
IIf([Hosp_Visit_24]=True,"T-","F-") & _
IIf([In_Home_Within 3]=True,"T-","F-") & _
IIf([In_Home_After_3]=True,"T-","F-") & _
IIf([Tel_Within_1]=True,"T-","F-") & _
IIf([Tel_After_1]=True,"T-","F-") & _
IIf([Other_Encounter]=True,"T-","F-") & _
IIf([Planning_Support]=True,"T-","F-") & _
IIf([Med_Review]=True,"T-","F-") & _
IIf([Counseling_Self_mgmt]=True,"T-","F-") & _
IIf([Comm_Family]=True,"T-","F-") & _
IIf([Timely_Interactions]=True,"T-","F-") & _
IIf([Add_Health_Prob]=True,"T-","F-") & _
IIf([Patient_Services]=True,"T","F") AS PatientTF,

Open in new window

Avatar of aplona

ASKER

syntax error for that section
Be sure you didn't remove the comma before this.  There should be 1 before and 1 after.
Avatar of aplona

ASKER

This is what i have and i get the error:

Select PadT("Comp Name",20),
PadT("Comp Contact",58),
"ID",
PadT("Phone Number",20),
PadT("Email",76),
Format([Referral].Hosp_Discharge_Date,"mm/dd/yy"),
PadT("Hosp",31), PadT("HospID",8),
PadT([Contact Info].Medicare_ID,12),
PadT([1)ID/Name/Goals].PLstName,30),
PadT([1)ID/Name/Goals].PFstName,30),
[Contact Info].Gender,
PadT([Contact Info].Zip,10),
IIf([Hosp_Visit_24]=True,"T-","F-") & _
IIf([In_Home_Within 3]=True,"T-","F-") & _
IIf([In_Home_After_3]=True,"T-","F-") & _
IIf([Tel_Within_1]=True,"T-","F-") & _
IIf([Tel_After_1]=True,"T-","F-") & _
IIf([Other_Encounter]=True,"T-","F-") & _
IIf([Planning_Support]=True,"T-","F-") & _
IIf([Med_Review]=True,"T-","F-") & _
IIf([Counseling_Self_mgmt]=True,"T-","F-") & _
IIf([Comm_Family]=True,"T-","F-") & _
IIf([Timely_Interactions]=True,"T-","F-") & _
IIf([Add_Health_Prob]=True,"T-","F-") & _
IIf([Patient_Services]=True,"T","F") AS PatientTF,
PadT("",200), PadT("",200), Format([Referral].EnrollmentDT,"mm/dd/yy"), [tblPhonePillars (3)].Ph14Date, PadT("Amount",8)
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 GetDate1() And GetDate2()));
You need to rebuild your Saved Export since 13 fields were replaced by 1.
Avatar of aplona

ASKER

I cant even save the query, it gives me the syntax error
Oops, here you go:
IIf([Hosp_Visit_24]=True,"T-","F-") & _
IIf([In_Home_Within 3]=True,"T-","F-") & 
IIf([In_Home_After_3]=True,"T-","F-") & 
IIf([Tel_Within_1]=True,"T-","F-") & 
IIf([Tel_After_1]=True,"T-","F-") & 
IIf([Other_Encounter]=True,"T-","F-") & 
IIf([Planning_Support]=True,"T-","F-") & 
IIf([Med_Review]=True,"T-","F-") & 
IIf([Counseling_Self_mgmt]=True,"T-","F-") & 
IIf([Comm_Family]=True,"T-","F-") & 
IIf([Timely_Interactions]=True,"T-","F-") & 
IIf([Add_Health_Prob]=True,"T-","F-") & 
IIf([Patient_Services]=True,"T","F") AS PatientTF, 

Open in new window

Avatar of aplona

ASKER

syntax again
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

You're the best.  I'll be posting more questions soon, also let me know on that other outstanding issue i have with the tabs,  Thanks again