View Single Post
 
Old June 6th, 2007, 05:28 AM
ayazhoda ayazhoda is offline
Friend of Wrox
 
Join Date: Apr 2007
Location: , , .
Posts: 110
Thanks: 1
Thanked 2 Times in 2 Posts
Send a message via MSN to ayazhoda
Default

 Hi mmcdonal

Thanks for your help its brilliant.

Now few things

when I use DoCmd.OpenQuery --> gives error [Datatype mismatch in Criteria expression]
DoCmd.OpenQuery "AppendTempTableProduct"
here is query
"INSERT INTO TempTableProduct ( PID, SID, SupplierNum, Description, BoxQuantity, ProductType )
SELECT Products.PID,
 Products.SID,
 Products.SupplierNum,
 Products.Description,
 Products.BoxQuantity,
 Products.ProductType
FROM Products INNER JOIN ProductTypes ON products.ProductType=ProductTypes.ProductType
WHERE (((ProductTypes.SID)='" & [Forms]![ExportToExcel].[cmbSupplierList] & "'))
And (((ProductTypes.ProductType)=" & [Forms]![ExportToExcel].[cmbProductTypes].Column(0) & "));
"
And with out column(0) is picking up first colum which i dont need in [Forms]![ExportToExcel].[cmbProductTypes].Column(0)

what i did

DoCmd.SetWarnings False
DoCmd.RunSQL "Delete from TempTableProduct"
'DoCmd.RunSQL "INSERT INTO TempTableProduct ( PID, SID, SupplierNum, Description, BoxQuantity,ProductType ) SELECT Products.PID, Products.SID, Products.SupplierNum, Products.Description, Products.BoxQuantity, Products.ProductType FROM Products INNER JOIN ProductTypes ON products.ProductType=ProductTypes.ProductType WHERE (((ProductTypes.SID)= '" & [Forms]![ExportToExcel].[cmbSupplierList] & "')) And (((ProductTypes.ProductType)= " & [Forms]![ExportToExcel].[cmbProductTypes].Column(0) & "))"
DoCmd.OpenQuery "AppendTempTableProduct"
DoCmd.SetWarnings True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "TempTableProduct", "c:\test2.xls"


any further advice

Regards

Ayaz