Exporting queries to Excel

  • Thread starter Thread starter Denis
  • Start date Start date
D

Denis

I have two similar queries which I am exporting (using
the DoCmd.TransferSpreadsheet function in VBA) to Excel.

The first one is:
SELECT Accounts.AccountNumber, Accounts.AccountName,
JournalRecords.Date,
JournalRecords.TaxExclusiveAmount
FROM JournalRecords INNER JOIN Accounts ON
JournalRecords.AccountID =
Accounts.AccountID
WHERE (((Accounts.AccountNumber)="1-1110") AND
((JournalRecords.Date) Between
#12/1/2003# And #12/10/2003#)))
ORDER BY JournalRecords.Date;

This works fine.
The second one is:
SELECT JournalRecords.Date, Accounts!AccountNumber & " "
& Accounts!AccountName
AS Account, JournalSets.Memo, JournalTypes.Description,
JournalRecords!TaxExclusiveAmount AS Amount
FROM ((JournalRecords INNER JOIN JournalSets ON
JournalRecords.SetID=JournalSets.SetID) INNER JOIN
Accounts ON
JournalRecords.AccountID=Accounts.AccountID) INNER JOIN
JournalTypes ON
JournalSets.JournalTypeID=JournalTypes.JournalTypeID
WHERE (((JournalRecords.Date) Between #12/1/2003# And
#12/10/2003#) AND
((Accounts.AccountNumber)="1-1110"));

This produces the error 3190 Too many fields.

Wht am I doing wrong and how do I get around the problem?

Any help will be much appreciated. Thanks
 
At a first glance you have put '!' between your table and field names, eg
'Accounts!AccountNumber' should be 'Accounts.AccountNumber'

try this

SELECT JournalRecords.Date, Accounts.AccountNumber & " "
& Accounts.AccountName
AS Account, JournalSets.Memo, JournalTypes.Description,
JournalRecords.TaxExclusiveAmount AS Amount
FROM ((JournalRecords INNER JOIN JournalSets ON
JournalRecords.SetID=JournalSets.SetID) INNER JOIN
Accounts ON
JournalRecords.AccountID=Accounts.AccountID) INNER JOIN
JournalTypes ON
JournalSets.JournalTypeID=JournalTypes.JournalTypeID
WHERE (((JournalRecords.Date) Between #12/1/2003# And
#12/10/2003#) AND
((Accounts.AccountNumber)="1-1110"));
 
Back
Top