extracting dates and modifying data

[Guest]

Dear all,

I have a transactions table in which I have items rented out with Startdate
and enddate. I generate monthly invoices and for this I decided to use an
unbound form having two combo boxes one for month and one for year. I give
discounts for users who take items from me where startdate =< 15 of invoice
month and return >=15 of invoice month.
After I select the month and year I need to pass this parameter to the table
and I need to select all the transactions where return date is null and for
the invoice month the criteria mentioned above to be carried out. Pls do kick
me in the right direction.

Regards,

 

[mcescher]

Dear all,

I have a transactions table in which I have items rented out with Startdate
and enddate. I generate monthly invoices and for this I decided to use an
unbound form having two combo boxes one for month and one for year. I give
discounts for users who take items from me where startdate =< 15 of invoice
month and return >=15 of invoice month.
After I select the month and year I need to pass this parameter to the table
and I need to select all the transactions where return date is null and for
the invoice month the criteria mentioned above to be carried out. Pls do kick
me in the right direction.

Regards,

Assuming you want StartDate <= 15 day before the start of the month
(the first day) and Return >= 15 days after the end of the month (last
day), paste the following code into a module. (You will want to add
some error checking)

Public Function MMStart() As Date

MMStart = DateSerial(cboYear, cboMonth, 1)
End Function

Public Function MMEnd() As Date
MMEnd = DateSerial(cboYear, cboMonth + 1, 0)
End Function

 

[mcescher]

I have a transactions table in which I have items rented out with Startdate
and enddate. I generate monthly invoices and for this I decided to use an
unbound form having two combo boxes one for month and one for year. I give
discounts for users who take items from me where startdate =< 15 of invoice
month and return >=15 of invoice month.
After I select the month and year I need to pass this parameter to the table
and I need to select all the transactions where return date is null and for
the invoice month the criteria mentioned above to be carried out. Pls do kick
me in the right direction.

Regards,

Assuming you want StartDate <= 15 day before the start of the month
(the first day) and Return >= 15 days after the end of the month (last
day), paste the following code into a module. (You will want to add
some error checking)

Public Function MMStart() As Date
MMStart = DateSerial(Forms!frmInvoice!cboYear, Forms!frmInvoice!
cboMonth, 1)
End Function

Public Function MMEnd() As Date
MMEnd = DateSerial(Forms!frmInvoice!cboYear, Forms!frmInvoice!
cboMonth + 1, 0)
End Function

Then you can create a query like this one.

SELECT tblInvoices.StartDate, tblInvoices.ReturnDate, tblInvoices.Data
FROM tblInvoices
WHERE (((tblInvoices.StartDate)<=(mmstart()-15)) AND
((tblInvoices.ReturnDate)>=(mmend()+15)));

Your question is a little confusing, because you want ReturnDate > End
of month +15, but you also want ReturnDate = null. I didn't allow for
nulls, please clarify if you need this criteria.

HTH,
Chris M.

Sorry for the double post, PC was running slow, and accidently pushed
send.

 

[Guest]

Dear Chris,
Thank you very much for the reply. Allow me to clarify the details;
1. I need to generate invoices for customers who rent out stuff from me.
This includes all customers whose end Date is null or items are yet to be
returned whose allocateddate or StartDate may be in Jan 2007. When I generate
invoices I need to include these items in the invoice.
2. The discount criteria is;
If the invoice month is June 2007 (which I generate at the end of this
month) & if any customers have items with allocationdate on or before the 15
of June 2007 then do not charge him for that item this month if after 15 then
charge him. Also if any customers returns items rented out to them before the
15th of June 2007 then do not charge them for June 2007 and if after 15th of
June then Charge him.
3. Please keep in mind that in the invoice I need to include all the
customers who still haven't returned items allocated to them. returnDate is
NULL

start |Allocation=charge |Allocation=Nocharge |End
of |----------------------|------------------------ | of
invoice |return=nocharge | return=Charge |invoice month
Month 1st 15

hope I have explained myself clearly