Avatar of aplona
aplona
Flag 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]))
Microsoft AccessMicrosoft DevelopmentSQL

Avatar of undefined
Last Comment
aplona

8/22/2022 - Mon
IrogSinta

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.
aplona

ASKER
Yes, but i need to have the prompts.
IrogSinta

I understand.  I was just trying to determine if this was the cause of the error?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Jim Dettman (EE MVE)

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.
IrogSinta

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"
aplona

ASKER
Im in 2007 but do not have the option to save the export as macro
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
IrogSinta

It should be the last step, there is a checkbox to save the Export.
aplona

ASKER
i see it
aplona

ASKER
how do i call it from the form?  i have it saved as Export-1st_List_Bill
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
IrogSinta

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.
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.
IrogSinta

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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"
IrogSinta

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?
aplona

ASKER
this is what i have
Capture.JPG
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
IrogSinta

Looks like you have a space in there between List and _Bill.
Export-1st_List _Bill
aplona

ASKER
got it, now i get runtime error 3709, the search key was not found in any record
IrogSinta

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
aplona

ASKER
i get a syntax error for that line
IrogSinta

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.
aplona

ASKER
The query will get the results but then i get the error exporting to text.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
IrogSinta

Can you upload your database?
aplona

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

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
aplona

ASKER
It works, but its leaving the " in the txt file
IrogSinta

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 ".
aplona

ASKER
The only way to set it to none is to include the headers though.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
IrogSinta

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?
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.
IrogSinta

Try changing cDate to DateValue in the module.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
aplona

ASKER
That didnt work
IrogSinta

Which date field are you referring to?
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
Your help has saved me hundreds of hours of internet surfing.
fblack61
IrogSinta

Change them in the query to DateValue([Referral].Hosp_Discharge_Date) and DateValue([Referral].EnrollmentDT)
aplona

ASKER
I get a syntax error for that whole line
aplona

ASKER
Would it have anything to do with the form and the date pickers?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
IrogSinta

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.
aplona

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

ASKER
Would it work if the query was changed to say that that field should only be 10 characters?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
IrogSinta

Try CDate(CLng([Referral].Hosp_Discharge_Date))
and
CDate(CLng([Referral].EnrollmentDT))
aplona

ASKER
Just changed it, still adds the 0:00:00
IrogSinta

Okay, this should work:

Format([Referral].Hosp_Discharge_Date,"mm/dd/yy")
and
Format([Referral].EnrollmentDT,"mm/dd/yy")
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
aplona

ASKER
Still adds the 0:00:00
IrogSinta

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.
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?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
IrogSinta

I didn't.  I'll check it out later tonight.
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????
IrogSinta

That's up to you. I just don't have access to view your database till I get home tonight.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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()));
IrogSinta

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

aplona

ASKER
syntax error for that section
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
IrogSinta

Be sure you didn't remove the comma before this.  There should be 1 before and 1 after.
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()));
IrogSinta

You need to rebuild your Saved Export since 13 fields were replaced by 1.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
aplona

ASKER
I cant even save the query, it gives me the syntax error
IrogSinta

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

aplona

ASKER
syntax again
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
IrogSinta

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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