I forgot you were linking into an Accounting system - didn't know it was Quickbooks. That explains a lot - forget the renaming comments..... My apologies....
I haven't worked with Access/Quickbooks in over 8 years. I had to put data INTO the Quickbook tables to be able to print checks. Took a long time to get it correct (over a month of research and testing).
Originally Posted by
TanyaCVV
This is all going far beyond my scope. I had thought creating a form would be much more simple. Unfortunately that file continued to go "not responding"
Not sure what "File" you are referring to.
Questions:
-- If you manually open query qselSalesLineGPbyDateByProductCode , do you get results? Are the results correct?
If you create a temporary saved query (like qselSalesLineGPbyDateByProductCode) using the following SQL:
Code:
SELECT SalesLine.TxnDate, SalesLine.CustomerRefFullName, SalesLine.RefNumber, SalesLine.SalesLineDesc, IIf(IsNull([PurchaseCost])=True,0,[PurchaseCost]) AS PurchCost, IIf(IsNull([AverageCost])=True,0,[AverageCost]) AS AvgCost, SalesLine.SalesLineRate, SalesLine.SalesLineQuantity, IIf([SalesLineQuantity]<>0,[SalesLineAmount]-([SalesLineQuantity]*[PurchCost]),[LineAmount]) AS GPPurchCost, IIf([SalesLine.Type]="CreditMemo",([SalesLineAmount]-([SalesLineQuantity]*[PurchCost]))*-1,[GPPurchCost]) AS GPPurchCM, IIf([SalesLine.Type]="CreditMemo",([SalesLineAmount]-([SalesLineQuantity]*[AvgCost]))*-1,[GPAvgCost]) AS GPAvgCM, IIf([LineAmount]<>0,[GPPurchCM]/[LineAmount],1) AS [GP%], IIf([SalesLineQuantity]<>0,[LineAmount]-([SalesLineQuantity]*[AvgCost]),[LineAmount]) AS GPAvgCost, IIf([SalesLine.Type]="CreditMemo",[SaleslineAmount]*-1,[SalesLineAmount]) AS LineAmount, SalesLine.Type, Item.CustomFieldProductCode
FROM Item INNER JOIN SalesLine ON Item.FullName = SalesLine.SalesLineItemRefFullName
WHERE CustomFieldProductCode = 'ABC123' AND TxnDate Between #1/1/2019# And #10/1/2019#
Change the CustomFieldProductCode and the 2 Dates to valid selections.
-- When you execute this saved query, are results returned? Are the results correct?