Not sure how to make this work

[Adien]

What I want is a form where you enter the week # and year, and the
total costs and sales for that week/year would be outputted.

What I have is 3 tables. One that has all of the sales info on a daily
basis. One that has the invoices that have come in (so there can be
numerious invoices on any given day). And a Dates table that if
combined in a query with either the costs tbl or the sales tbl will
assign the proper week number to each entry.

Thanks
Adien

 

[Adien]

PS I'm using access 2000

 

[Dale Fye]

Adien,

What do you want to do with the first and last weeks of the year. If the
year starts on a Wed, are you only interested in the sales and invoice values
for Wed - Sat?

How do you want to output this data? If an unbound textbox on the form?

I would create a small subroutine in the forms code module. Then, in the
AfterUpdate event of the txt_Week and txt_Year textboxes, I would call this
subroutine:

Private txt_Week_AfterUpdate

Call WeeksSalesAndInvoices

Something like:

Private Sub WeeksSalesAndInvoices

Dim strCriteria as string

if LEN(me.txt_Week & "") = 0 then
msgbox "Enter the week number!"
me.txt_Week.setfocus
exit sub
elseif LEN(me.txt_Year & "") = 0 then
msgbox "Enter the year number!"
me.txt_Year.setfocus
exit sub
else
me.txt_SalesTotal = DSUM("Amount", "qry_Sales")
me.txt_InvoicesTotal = DSUM("Amount", "qry_Invoices")
endif

End Sub

Then, create two queries that look similar to:

qry_Sales:
SELECT SUM(tbl_Sales.AMOUNT) as Amount
FROM tblSales INNER JOIN tbl_Dates
ON tblSales.SalesDate = tbl_Dates.DateVal
WHERE tbl_Dates.Year = FORMS!YourFormName.txt_Year
AND tbl_Dates.Week = Forms!YourFormName.txt_Week

qry_Invoices:
SELECT Sum(tbl_Invoices.Amount) as Amount
FROM tbl_Invoices INNER JOIN tbl_Dates
ON tbl_Invoices.InvoiceDate = tbl_Dates.DateVal
WHERE tbl_Dates.Year = FORMS!YourFormName.txt_Year
AND tbl_Dates.Week = Forms!YourFormName.txt_Week

HTH
Dale

 

[Adien]

Dale,

Thanks a ton. That was exactly what I needed. I put all the info into
my database (altered as needed of course) and it's working perfectly.

Adien