Link to home
Start Free TrialLog in
Avatar of Laurence Martin
Laurence MartinFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Transferspreadsheet

Hi,

I am completely puzzled by what Transferspreadsheet is doing, please can you help?

When I run the query in Access I get 18 records, but when I put the following code on a command button I only get 1 record.

  Private Sub cmdqry2811b_Click()
  Dim stFile As String

  stFile = "H:\Fits by Fitter by month.xlsx"

  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry2811bFitsbyFitterbymonth", stFile, True

  Workbooks.Open stFile
  Excel.Application.Visible = True
End Sub

Why would the results be different?

Thanks

LJM
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

check if the query "qry2811bFitsbyFitterbymonth" is using a criteria that limits the number of records return.

open query in design view, select SQL view
copy the displayed SQL statement and post here.
Avatar of Laurence Martin

ASKER

SELECT MonthName(Month([f28iFitDate]),True) AS [Fit Month], tbl28Quote.f28fFitter AS Fitter, Count(Orders.f1cSCOrder) AS [Number], Sum(Orders.f1tNetBalance) AS [Net Value], Sum(Orders.f1rFreight) AS SumOff1rFreight, Sum(Orders.f1rDelCharge) AS SumOff1rDelCharge, Sum(Orders.f1uNetProducts) AS ProductValue, Sum([f28kFitChargeEx]+[f28lAddFitChargeEx]) AS Fitting, Sum(tbl28Quote.f28jTotFitHours) AS Hours, Sum(tbl28Quote.f28tSqM) AS SqM, [ProductValue]/[sqm] AS [£/SqM]
FROM (tbl28Quote INNER JOIN (Orders INNER JOIN tbl34QuoteOrders ON Orders.f1aID = tbl34QuoteOrders.f1cSCOrder) ON tbl28Quote.f28aQuoteNo = tbl34QuoteOrders.f28aQuoteNo) INNER JOIN tbl25Leads ON tbl28Quote.f25Estimate = tbl25Leads.fld25ID

WHERE (((tbl28Quote.f28iFitDate) Between [Forms]![frm04RetailReports]![date1] And [Forms]![frm04RetailReports]![date2]) AND ((Orders.f1lStatus)<>"cancelled"))

GROUP BY MonthName(Month([f28iFitDate]),True), tbl28Quote.f28fFitter, Month([f28ifitdate])
ORDER BY Month([f28ifitdate]), tbl28Quote.f28fFitter;
create a copy of the query,
in the copy remove the "Where" clause so your query SQL will be


SELECT MonthName(Month([f28iFitDate]),True) AS [Fit Month], tbl28Quote.f28fFitter AS Fitter, Count(Orders.f1cSCOrder) AS [Number], Sum(Orders.f1tNetBalance) AS [Net Value], Sum(Orders.f1rFreight) AS SumOff1rFreight, Sum(Orders.f1rDelCharge) AS SumOff1rDelCharge, Sum(Orders.f1uNetProducts) AS ProductValue, Sum([f28kFitChargeEx]+[f28lAddFitChargeEx]) AS Fitting, Sum(tbl28Quote.f28jTotFitHours) AS Hours, Sum(tbl28Quote.f28tSqM) AS SqM, [ProductValue]/[sqm] AS [£/SqM]
FROM (tbl28Quote INNER JOIN (Orders INNER JOIN tbl34QuoteOrders ON Orders.f1aID = tbl34QuoteOrders.f1cSCOrder) ON tbl28Quote.f28aQuoteNo = tbl34QuoteOrders.f28aQuoteNo) INNER JOIN tbl25Leads ON tbl28Quote.f25Estimate = tbl25Leads.fld25ID
GROUP BY MonthName(Month([f28iFitDate]),True), tbl28Quote.f28fFitter, Month([f28ifitdate])
ORDER BY Month([f28ifitdate]), tbl28Quote.f28fFitter;


run the query and see how many records are returned.
Well, I was going to try that, but I can't reproduce the problem now.

I am based in the UK, would transferspreadsheet change the inpretation of dates?  IE is 1/11/2012 1st Nov or Jan 11th?
the date format will be interpreted in the format set in your Regional setting.
anyway, you can try this where clause, formatting the dates "YYYYMMDD"


WHERE (((Format([tbl28Quote].[f28iFitDate],"yyyymmdd")) Between Format([Forms]![frm04RetailReports]![date1],"yyyymmdd") And Format([Forms]![frm04RetailReports]![date2],"yyyymmdd")) AND ((Orders.f1lStatus)<>"cancelled"))
I'll give it a try.  Does this technique just ensure that all the dates are talking the same language?

The controls in the forms will have values that are dd/mm/yyyy.  Should I still use what you have suggested or change to ddmmyyyy?

NB: Regional settings are not consistently effective.
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
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