Cannot export query to text file (CSV)

[JeremiahClinkerbuilt]

Hi all,

I have a query that I can run and show in datasheet view.

If I try to export the file to a text file manually (using the ribbon) I get an error: "Too few parameters. Expected 1" (There are no parameters)
If I try to export the file to text using VBA (DoCmd.TransferText acExportDelim...) I get an error: "Cannot reference a table with a multi-valued field using an IN clause that refers to another database." (I don't believe that i am doing this either)

Because I'm getting different errors I have a feeling something else is going on. It was working but then after some changes it stopped working but I can't figure out which change has broken it.

I'd appreciate any help or clues. I've scoured the net looking and can't see anyone having the same combination of problems!

Thanks

(I've been using Access for 25 years and I'm still learning, mostly because it's not my day-job.)

 

[Ranman256]

If your query references a form object, that's a parameter.
If your query has a sub query that may reference a form, it's a param.

Sometimes if you start with an existing query,then modify it,and remove the params,it may remember them. So you must start the query from scratch.

And for CSV export ,you must build a schema definition (as 1 param of the docmd.exportText cmd.)

 

[Gasman]

Of course, you couldn't post the actual query?

Within code tags as well would be nice? (See my signature)

 

[The_Doc_Man]

OK, let's break down the common causes of what you saw.

When a query asks for a parameter, the number-one cause of that is that you spelled something incorrectly. The message is telling you that it cannot find what you told it to find.

For single-table queries, the error is a field name that is misspelled OR perhaps a function name that was spelled wrong or was user-defined in a context where it could not properly be seen as Public. If your query involves a JOIN to another table, then either the field name or the source-name (table name) is spelled badly.

That more complex message is one I've never actually seen before, but it contains a few nuggets.

First, do you have a multi-valued field in any of the tables contributing to the query? This forum contains many articles on why not to use an MVF. They are trouble-makers at many levels.

Second, for you to see a reference to an IN clause that references another DB, you would definitely know that you had one. That is so unusual that you would have had a bit of trouble and a bit of research on your hands to have done it. You could not have sleepwalked through it. Therefore, if you say you didn't do that, I believe you. But there is a clue because that kind of IN clause only occurs near FROM or JOIN clauses. So do you have a field name of ""IN" or something that LOOKS like "IN" for which it could have been fat-fingered?

 

[theDBguy]

Hi Jeremiah. Welcome to AWF!

 

[Isaac]

There are a lot of nasty consequences of using multi-valued fields. I'd say don't use them

 

[JeremiahClinkerbuilt]

Thanks for your replies. I've been mulling it over and going back through my query and code.

I don't have any MVF. I tried, and failed. I've removed it now. Could it be lingering? I don't see it anymore.

I don;t have any IN clause or the other thing.

Below is my query... (I really didn't think it would help as I don't think it's easy to understand in SQL). The export data is to provide data to import into Xero (the cloud accounting software) - what we call "pushing". GetAddress() is a custom function that retrieves the relevant bits of an address from multi-line text.

It does reference two controls on the form, which if they are treated as parameters, seem to work fine.

What I don't understand is that the query runs, I can see the data in datasheet view. At the moment we are having to copy the output from the datasheet view paste it into Excel and then save as a CSV file from there. The export to text file "expects a parameter" which doesn't happen when running the query normally (and never did when the export function worked, which it did once).

I have never used a schema and it used to work.

SELECT Relatives.Name AS [*ContactName], [First e-mail address for contact].[FirstOfContact number] AS EmailAddress, GetAddress([Relatives]![Address],"House") AS POAddressLine1, GetAddress([Relatives]![Address],"AddressLine2") AS POAddressLine2, GetAddress([Relatives]![Address],"AddressLine3") AS POAddressLine3, GetAddress([Relatives]![Address],"AddressLine4") AS POAddressLine4, GetAddress([Relatives]![Address],"Town") AS POCity, GetAddress([Relatives]![Address],"County") AS PORegion, IIf(IsNull([Relatives]![Postcode]),GetAddress([Relatives]![Address],"Postcode"),[Relatives]![Postcode]) AS POPostalCode, GetAddress([Relatives]![Address],"Country") AS POCountry, [Objects to account]![AccountID] & "/" & [InvoiceID] & " - " & [Accounts].[Account name] AS Reference, IIf(IsNull([Date pushed]),DateValue(Now()),[Date pushed]) AS [*InvoiceDate], DateAdd("d",30,IIf(IsNull([Date pushed]),DateValue(Now()),[Date pushed])) AS [*DueDate], "" AS Total, "" AS InventoryItemCode, Groups.[Print line] AS [*Description], 1 AS [*Quantity], Sum(CCur((([Quantity]*([Price]+[TypeVAT])*(1-([Objects to account]![Discount]/100)))+[Variable costs]+[Objects to account].[VAT]))) AS [*UnitAmount], 0 AS Discount, IIf([Disbursement],250,200) AS [*AccountCode], IIf([Disbursement],"Disbursement",[Xero]) AS [*TaxType], "Department" AS TrackingName1, "Funeral Director" AS TrackingOption1, "Location" AS TrackingName2, "24-26 High Street" AS TrackingOption2
FROM (((([VAT types] RIGHT JOIN ((Invoices RIGHT JOIN ([Report formats] RIGHT JOIN ((Objects LEFT JOIN (Groups RIGHT JOIN [Objects to Groups] ON Groups.GroupID = [Objects to Groups].Group) ON Objects.[Object ID] = [Objects to Groups].Object) RIGHT JOIN [Objects to account] ON Objects.[Object ID] = [Objects to account].ObjectID) ON [Report formats].FormatID = Groups.[Report ID]) ON Invoices.InvoiceID = [Objects to account].[Invoice number]) LEFT JOIN Types ON [Objects to account].[Type ID] = Types.[Type ID]) ON [VAT types].[VAT type ID] = Types.[VAT type]) LEFT JOIN Relatives ON Invoices.Recipient = Relatives.RelationID) LEFT JOIN [First e-mail address for contact] ON Relatives.RelationID = [First e-mail address for contact].RelationID) LEFT JOIN Prices ON [Objects to account].PriceID = Prices.PriceID) LEFT JOIN Accounts ON [Objects to account].AccountID = Accounts.Reference
WHERE (((Invoices.InvoiceID)=[Forms]![Accounts]![Invoices1]![InvoiceID]) AND (([Report formats].[Format name])="Invoice"))
GROUP BY Relatives.Name, [First e-mail address for contact].[FirstOfContact number], GetAddress([Relatives]![Address],"House"), GetAddress([Relatives]![Address],"AddressLine2"), GetAddress([Relatives]![Address],"AddressLine3"), GetAddress([Relatives]![Address],"AddressLine4"), GetAddress([Relatives]![Address],"Town"), GetAddress([Relatives]![Address],"County"), IIf(IsNull([Relatives]![Postcode]),GetAddress([Relatives]![Address],"Postcode"),[Relatives]![Postcode]), GetAddress([Relatives]![Address],"Country"), [Objects to account]![AccountID] & "/" & [InvoiceID] & " - " & [Accounts].[Account name], IIf(IsNull([Date pushed]),DateValue(Now()),[Date pushed]), DateAdd("d",30,IIf(IsNull([Date pushed]),DateValue(Now()),[Date pushed])), Groups.[Print line], IIf([Disbursement],250,200), IIf([Disbursement],"Disbursement",[Xero])
ORDER BY First(Objects.Disbursement), Min(Objects.Priority);

 

[JeremiahClinkerbuilt]

Hi Jeremiah. Welcome to AWF!

Thanks.

 

[bastanu]

Just curious if you can convert it into a make table and see if runs (then export the resultant table).
Cheers,

 

[Pat Hartman]

There's a couple of things
Instead of --- DateValue(Now()) -- use Date()
Don't start column names with *

The query is impossible to parse with all the spaces and special characters and unnecessary parentheses that Access adds for you but you might not have all the fields in the group by.

You can also test by breaking up the query and export the first few fields, then add a few more and export, then a few more and export until you hit the expression causing the problem.

 

[JeremiahClinkerbuilt]

There's a couple of things
Instead of --- DateValue(Now()) -- use Date()
Don't start column names with *

The query is impossible to parse with all the spaces and special characters and unnecessary parentheses that Access adds for you but you might not have all the fields in the group by.

You can also test by breaking up the query and export the first few fields, then add a few more and export, then a few more and export until you hit the expression causing the problem.

Thanks,

Like I mentioned, the query runs: (the "*" are necessary for the file import to Xero, all the fields are in the group (or it wouldn't run) - but I hadn't thought about deconstructing the query to find out which part is stopping it working! So thanks for that, I'll give it a try.