output/print to text

[maximark]

I have researched on here how to print the results of a query to a text file
I put the code in and I get an error on the openrecordset line
the error says "too few parameters, expected 4"

I tried the query in another report I export to excel and the query works
I tried printing the whole table using "Select * from tbl_Customers" and it works????

here is the code:
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("qryBell1", dbOpenSnapshot)
Dim fs, TextFile
Set fs = CreateObject("Scripting.FileSystemObject")
Set TextFile = fs.CreateTextFile(pathname, True)

Do Until rst.EOF = True
TextFile.writeline (rst!Phone & rst!CustID & rst!AccountNumber & rst!Name & rst!LatestStatus & rst!LatestContactDate)
rst.MoveNext
Loop
TextFile.Close

rst.Close
Set rst = Nothing



Here is the query qryBell1
SELECT tbl_Customers.Phone, tbl_Customers.CustID, tbl_Customers.AccountNumber, tbl_Customers.Name, tbl_Status.Description AS LastStatus, tbl_Customers.LatestContactDate AS LastContactDate
FROM (tbl_Customers INNER JOIN tbl_Campaigns ON tbl_Customers.CampaignID = tbl_Campaigns.CampaignID) LEFT JOIN tbl_Status ON tbl_Customers.LatestStatus = tbl_Status.StatusID
WHERE (((tbl_Customers.LatestContactDate)>=[Forms]![frmQueryMenu]![StartDate] & " 0:00:01 AM" And (tbl_Customers.LatestContactDate)<=[Forms]![frmQueryMenu]![EndDate] & " 11:59:59 PM") AND ((tbl_Customers.CampaignID) Like "*" & [Forms]![frmQueryMenu]![cboCampaign]) AND ((tbl_Status.StatusID) Like "*" & [Forms]![frmQueryMenu]![cboStatus])) OR (((tbl_Customers.LatestContactDate)>=[Forms]![frmQueryMenu]![StartDate] And (tbl_Customers.LatestContactDate)<=[Forms]![frmQueryMenu]![EndDate]) AND ((tbl_Status.StatusID) Is Null));

the query returns 6 fields for printing and can filter based on whether 3 fields are filled or not on the form - Date, Campaign, Status

 

[maximark]

I tried changing the openrecordset line to this
Set rst = CurrentDb.OpenRecordset("qryBell1", , , dbReadOnly)

the error says invalid argument now!

 

[JHB]

I have researched on here how to print the results of a query to a text file
I put the code in and I get an error on the openrecordset line
the error says "too few parameters, expected 4"

Do you have the form "frmQueryMenu" open, when you view the report?

To your post #2, try:

Set rst = CurrentDb.OpenRecordset("qryBell1", , dbReadOnly)

 

[maximark]

No that doesn't work - still says too few parameters expected 4

 

[pr2-eugin]

You cannot Open a recordset if the Query is expecting parameters. You need to do this is run time..

Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT tbl_Customers.Phone, tbl_Customers.CustID, tbl_Customers.AccountNumber, tbl_Customers.Name, tbl_Status.Description AS LastStatus, " & _
         "tbl_Customers.LatestContactDate AS LastContactDate FROM (tbl_Customers INNER JOIN tbl_Campaigns ON " & _
         "tbl_Customers.CampaignID = tbl_Campaigns.CampaignID) LEFT JOIN tbl_Status ON tbl_Customers.LatestStatus = tbl_Status.StatusID " & _
         "WHERE (((tbl_Customers.LatestContactDate) >= " & Format(Me.StartDate & " 0:00:01 AM", "\#mm\/dd\/yyyy hh\:nn\:ss\#") & _
         " And (tbl_Customers.LatestContactDate) <= " & Format(Me.EndDate & " 11:59:59 PM", "\#mm\/dd\/yyyy hh\:nn\:ss\#") & _
         "AND ((tbl_Customers.CampaignID) Like *" & Me.cboCampaign & ") AND ((tbl_Status.StatusID) Like *" & Me.cboStatus & ")) " & _
         "OR (((tbl_Customers.LatestContactDate) >= " & Format(Me.StartDate, "\#mm\/dd\/yyyy\#") & " And " & _
         "(tbl_Customers.LatestContactDate) <= " & Format(Me.EndDate, "\#mm\/dd\/yyyy\#") & ") AND ((tbl_Status.StatusID) Is Null));"

Set rst = CurrentDB.OpenRecordset(strSQL)

Although I am not sure how the Query will work as the field LatestContactDate seems to store Date & Time, in the second part of the Condition you seem to check only the Date. If that is what you intend then use DateValue function..

 

[maximark]

I think that has it. took a while to tweek the query to what I want but I agree that the condition part needed to be done at run time.

THank you very much

 

[pr2-eugin]

Not a problem ! Good Luck !