Calculating days!

[drago]

Hi guys, good to be here... I am struggling to calculated days in invoice system...Ok...The dates fields are of two kinds...One is the invoice start date and end date... The other one is the contract start date and end date...I have to calculate days of charging according to the dates involved...
Suppose INVOICE start date: 01-01-07; INVOICE end date: 31-01-07

Conditions are:

1. IF the contract start date is BEFORE(<=) the invoice start date AND contract end date is AFTER(>=) or equal to invoice end date THEN use the invoice start and end dates for calculation.

2. IF contract start date is AFTER(>) the invoice start date AND contract end date is BEFORE (<) the invoice end date THEN use contract start date and contract end date for calculation.

3. IF contract start date is AFTER(>) the invoice start date AND contract start date is BEFORE (<) the invoice end date THEN use contract start date and invoice end date for calculation.

4. IF contract start date is BEFORE(<) the invoice start date AND contract end date is also BEFORE (<) the invoice end date THEN use Invoice start date and contract end date for calculation.

5. IF contract start date is AFTER (>) the invoice end date THEN do not show the record.

6. IF contract end date is BEFORE (<) the invoice start date THEN do not show the records.

I know it’s a bit of nuisance, to me it is :P... Well it’s been 3 days working on it and I am not that much familiar with Access... Any kind of help will be appreciated... Thanking you guys in advance...

[ADezii]

Hi guys, good to be here... I am struggling to calculated days in invoice system...Ok...The dates fields are of two kinds...One is the invoice start date and end date... The other one is the contract start date and end date...I have to calculate days of charging according to the dates involved...
Suppose INVOICE start date: 01-01-07; INVOICE end date: 31-01-07

Conditions are:

1. IF the contract start date is BEFORE(<=) the invoice start date AND contract end date is AFTER(>=) or equal to invoice end date THEN use the invoice start and end dates for calculation.

2. IF contract start date is AFTER(>) the invoice start date AND contract end date is BEFORE (<) the invoice end date THEN use contract start date and contract end date for calculation.

3. IF contract start date is AFTER(>) the invoice start date AND contract start date is BEFORE (<) the invoice end date THEN use contract start date and invoice end date for calculation.

4. IF contract start date is BEFORE(<) the invoice start date AND contract end date is also BEFORE (<) the invoice end date THEN use Invoice start date and contract end date for calculation.

5. IF contract start date is AFTER (>) the invoice end date THEN do not show the record.

6. IF contract end date is BEFORE (<) the invoice start date THEN do not show the records.

I know it’s a bit of nuisance, to me it is :P... Well it’s been 3 days working on it and I am not that much familiar with Access... Any kind of help will be appreciated... Thanking you guys in advance...

I will post the solution in a day or two, as soon as I get the opportunity to work on it.

[MikeTheBike]

Hi

I think this might be something like what tou want, if I understant it correctly

SELECT  
    Field1, Field2, ....,
    IIF(ConStartDate<= InvStartDate, InvStartDate,ConStartDate)-IIF(ConEndDate>InvEndDate,InvEndDate,ConEndDate) As NumDays
FROM YourTable  
WHERE ConStartDate<=InvEndDate AND ConEndDate >= InvStatDate

Does that do it??


MTB

[kepston]

Hi

I think this might be something like what tou want, if I understant it correctly

SELECT  
    Field1, Field2, ....,
    IIF(ConStartDate<= InvStartDate, InvStartDate,ConStartDate)-IIF(ConEndDate>InvEndDate,InvEndDate,ConEndDate) As NumDays
FROM YourTable  
WHERE ConStartDate<=InvEndDate AND ConEndDate >= InvStatDate

Does that do it??


MTB

Maybe. But you'll get a negative number taking the end date from the start date.
Try

SELECT  
    Field1, Field2, ....,
    IIF(ConEndDate>InvEndDate,InvEndDate,ConEndDate) - IIF(ConStartDate<= InvStartDate, InvStartDate,ConStartDate) As NumDays
FROM YourTable  
WHERE ConStartDate<=InvEndDate AND ConEndDate >= InvStartDate

[drago]

Guys thank your for your prompt replies...u ppl are amazing...let me try what MTB and Kepston have suggested...I will get back to you soon. If you ask me I can see the hurdle here... Now each client have 1 or more than one contracts and for that I am using a subform in invoice form... so at the moment it is bringing all the contract records related to the selected client by using the invoice ID...the calculation is done on the sub forms.... Anyway let me try this first...

Thank you once again guys...you are life savers....

[ADezii]

Guys thank your for your prompt replies...u ppl are amazing...let me try what MTB and Kepston have suggested...I will get back to you soon. If you ask me I can see the hurdle here... Now each client have 1 or more than one contracts and for that I am using a subform in invoice form... so at the moment it is bringing all the contract records related to the selected client by using the invoice ID...the calculation is done on the sub forms.... Anyway let me try this first...

Thank you once again guys...you are life savers....

1. Assumptions
   1. Table Name: tblInvoice
   2. Field Name: Contract Start Date
   3. Field Name: Contract End Date
   4. Field Name: Invoice Start Date
   5. Field Name: Invoice End Date
2. Query ==> New ==> Design View ==> SQL View
3. Paste the following SQL String into the SQL View Window
   

   SELECT [Contract Start Date], [Contract End Date], [Invoice Start Date], [Invoice End Date], 
   fCalculateDays([Contract Start Date],[Contract End Date],[Invoice Start Date],[Invoice End Date]) AS Days_Differential
   FROM tblInvoice
   WHERE fCalculateDays([Contract Start Date],[Contract End Date],[Invoice Start Date],[Invoice End Date])<>999999;

4. This Query consists of the 4 Date Fields and a Calculated Field named [Days_Differential] which calls the fCalculateDays() Function. This Function is passed all 4 Dates as Arguments and returns the appropriate Response including a Value of -32767 for any generated Errors in the Routine. On those conditions where no Records are to be returned, (conditions 5 and 6), this Function returns 999999. Criteria specified in the Query itself, namely <> 999999, will now allow these Records to be shown. All Logic and Data Comparisons are performed within the Function itself with the Return Value populating the Calculated Field, (Days_Differential). The code is basically self-documenting, but if you are confused on anything, please feel free to ask. You must, of course, Copy and Paste the Function Procedure below to a Standard Code Module. This Function must be declared as Public in order to be functional. It has been debugged and is fully operational, I just hope it provides the answer to your dilemma. Let me know how you make out.
   

   Public Function fCalculateDays(dteContractStart As Date, dteContractEnd As Date, dteInvoiceStart As Date, dteInvoiceEnd As Date)
   On Error GoTo Err_fCalculateDays
    
   'Satifies Condition #1 in outline
   If (dteContractStart <= dteInvoiceStart) And (dteContractEnd >= dteInvoiceEnd) Then
     fCalculateDays = DateDiff("d", dteInvoiceStart, dteInvoiceEnd)
   'Satifies Condition #2 in outline
   ElseIf (dteContractStart > dteInvoiceStart) And (dteContractEnd < dteInvoiceEnd) Then
     fCalculateDays = DateDiff("d", dteContractStart, dteContractEnd)
   'Satifies Condition #3 in outline
   ElseIf (dteContractStart > dteInvoiceStart) And (dteContractStart < dteInvoiceEnd) Then
     fCalculateDays = DateDiff("d", dteContractStart, dteInvoiceEnd)
   'Satifies Condition #4 in outline
   ElseIf (dteContractStart < dteInvoiceStart) And (dteContractEnd < dteInvoiceEnd) Then
     fCalculateDays = DateDiff("d", dteInvoiceStart, dteContractEnd)
   Else
     fCalculateDays = 999999       'Doesn't fit in any Category - DON'T SHOW? ------------|
   End If                          '                                                      |
                                   '                                                      |
                                   '                                                      |
   'Satifies Conditions 5 and 6 in outline - Higher Priority then Items 1 thru 4          |
   If (dteContractStart > dteInvoiceEnd) Or (dteContractEnd < dteInvoiceStart) Then  '    |
     fCalculateDays = 999999       'DON'T SHOW (filtered in the Query) -------------------|
   End If
    
   Exit_fCalculateDays:
     Exit Function
    
   Err_fCalculateDays:
     fCalculateDays = -32767       'Error indicator
     Resume Exit_fCalculateDays
   End Function

[MikeTheBike]

Maybe. But you'll get a negative number taking the end date from the start date

OOPS!

Close but no cigar.

MTB

[drago]

Hi Adezii thank you very much for your help. I have created queries now i will try to create the logic which you have written... I will send you the feedback once its working or working...
Thanks again :)

[ADezii]

Hi Adezii thank you very much for your help. I have created queries now i will try to create the logic which you have written... I will send you the feedback once its working or working...
Thanks again :)

We're here whenever you need us.

[drago]

We're here whenever you need us.

Hi fellows! Me back. I tried the later code, when i run it, it says "Data type mismatch in criteria expression". Iam sorry, I should have told you guys earlier about my Access , so it Ms Access 07 using vista 07.

I have few queries which run fine. They are:

1. The result of this query will allow invoice system to use invoice start date and end date.

SQL code:
SELECT Invoice.invoice_from_date, Invoice.invoice_to_date, Contract_Elements.c_e_start_date, Contract_Elements.c_e_end_date, Contract_Elements.c_e_total_days
FROM Invoice LEFT JOIN Contract_Elements ON Invoice.invoice_ID = Contract_Elements.invoice_ID
WHERE (((Contract_Elements.c_e_start_date)<=([Invoice].[invoice_from_date])) AND ((Contract_Elements.c_e_end_date)>=([Invoice].[invoice_to_date])));

2. The result of this give you all contracts between invoicing date. So the contract start and end date will be used to calculate No. of days

SELECT Invoice.invoice_from_date, Invoice.invoice_to_date, Contract_Elements.c_e_start_date, Contract_Elements.c_e_end_date, Contract_Elements.c_e_total_days
FROM Invoice LEFT JOIN Contract_Elements ON Invoice.invoice_ID = Contract_Elements.invoice_ID
WHERE (((Contract_Elements.c_e_start_date)>([Invoice].[invoice_from_date])) AND ((Contract_Elements.c_e_end_date)<([Invoice].[invoice_to_date])));

3. This query should later on use values of contract start date and the invoice end date

SELECT Invoice.invoice_from_date, Invoice.invoice_to_date, Contract_Elements.c_e_start_date, Contract_Elements.c_e_end_date, Contract_Elements.c_e_total_days
FROM Invoice LEFT JOIN Contract_Elements ON Invoice.invoice_ID = Contract_Elements.invoice_ID
WHERE ( (Contract_Elements.c_e_start_date)>([Invoice].[invoice_from_date]) AND (Contract_Elements.c_e_end_date)> ([Invoice].[invoice_to_date]) AND (Contract_Elements.c_e_start_date) < ([Invoice].[invoice_to_date]) );

4. This is opposite of the above. should use values of invoice start date and contract end date

SELECT Invoice.invoice_from_date, Invoice.invoice_to_date, Contract_Elements.c_e_start_date, Contract_Elements.c_e_end_date, Contract_Elements.c_e_total_days
FROM Invoice LEFT JOIN Contract_Elements ON Invoice.invoice_ID = Contract_Elements.invoice_ID
WHERE ( (Contract_Elements.c_e_end_date)<([Invoice].[invoice_to_date]) AND (Contract_Elements.c_e_start_date)< ([Invoice].[invoice_from_date]) AND (Contract_Elements.c_e_end_date) > ([Invoice].[invoice_from_date]) );

5. This should not be excluded from the invoice. Old dates less than the contract start

SELECT Invoice.invoice_from_date, Invoice.invoice_to_date, Contract_Elements.c_e_start_date, Contract_Elements.c_e_end_date, Contract_Elements.c_e_total_days
FROM Invoice LEFT JOIN Contract_Elements ON Invoice.invoice_ID = Contract_Elements.invoice_ID
WHERE ( (Contract_Elements.c_e_end_date)<([Invoice].[invoice_from_date]) );


6. This should not be excluded from the invoice. Future dates greater than the contract end date

SELECT Invoice.invoice_from_date, Invoice.invoice_to_date, Contract_Elements.c_e_start_date, Contract_Elements.c_e_end_date, Contract_Elements.c_e_total_days
FROM Invoice LEFT JOIN Contract_Elements ON Invoice.invoice_ID = Contract_Elements.invoice_ID
WHERE ( (Contract_Elements.c_e_start_date)>([Invoice].[invoice_to_date]) );


Now, can I use these queries in a vba to run appropriate query and to perform certain calculations on selected fields and save it in days column. All this will be done in subform.

My brain is totally out of order. Isnt it...Hope you are not confused...

[drago]

(whispering)..Hello?

[ADezii]

Hi fellows! Me back. I tried the later code, when i run it, it says "Data type mismatch in criteria expression". Iam sorry, I should have told you guys earlier about my Access , so it Ms Access 07 using vista 07.

I have few queries which run fine. They are:

1. The result of this query will allow invoice system to use invoice start date and end date.

SQL code:
SELECT Invoice.invoice_from_date, Invoice.invoice_to_date, Contract_Elements.c_e_start_date, Contract_Elements.c_e_end_date, Contract_Elements.c_e_total_days
FROM Invoice LEFT JOIN Contract_Elements ON Invoice.invoice_ID = Contract_Elements.invoice_ID
WHERE (((Contract_Elements.c_e_start_date)<=([Invoice].[invoice_from_date])) AND ((Contract_Elements.c_e_end_date)>=([Invoice].[invoice_to_date])));

2. The result of this give you all contracts between invoicing date. So the contract start and end date will be used to calculate No. of days

SELECT Invoice.invoice_from_date, Invoice.invoice_to_date, Contract_Elements.c_e_start_date, Contract_Elements.c_e_end_date, Contract_Elements.c_e_total_days
FROM Invoice LEFT JOIN Contract_Elements ON Invoice.invoice_ID = Contract_Elements.invoice_ID
WHERE (((Contract_Elements.c_e_start_date)>([Invoice].[invoice_from_date])) AND ((Contract_Elements.c_e_end_date)<([Invoice].[invoice_to_date])));

3. This query should later on use values of contract start date and the invoice end date

SELECT Invoice.invoice_from_date, Invoice.invoice_to_date, Contract_Elements.c_e_start_date, Contract_Elements.c_e_end_date, Contract_Elements.c_e_total_days
FROM Invoice LEFT JOIN Contract_Elements ON Invoice.invoice_ID = Contract_Elements.invoice_ID
WHERE ( (Contract_Elements.c_e_start_date)>([Invoice].[invoice_from_date]) AND (Contract_Elements.c_e_end_date)> ([Invoice].[invoice_to_date]) AND (Contract_Elements.c_e_start_date) < ([Invoice].[invoice_to_date]) );

4. This is opposite of the above. should use values of invoice start date and contract end date

SELECT Invoice.invoice_from_date, Invoice.invoice_to_date, Contract_Elements.c_e_start_date, Contract_Elements.c_e_end_date, Contract_Elements.c_e_total_days
FROM Invoice LEFT JOIN Contract_Elements ON Invoice.invoice_ID = Contract_Elements.invoice_ID
WHERE ( (Contract_Elements.c_e_end_date)<([Invoice].[invoice_to_date]) AND (Contract_Elements.c_e_start_date)< ([Invoice].[invoice_from_date]) AND (Contract_Elements.c_e_end_date) > ([Invoice].[invoice_from_date]) );

5. This should not be excluded from the invoice. Old dates less than the contract start

SELECT Invoice.invoice_from_date, Invoice.invoice_to_date, Contract_Elements.c_e_start_date, Contract_Elements.c_e_end_date, Contract_Elements.c_e_total_days
FROM Invoice LEFT JOIN Contract_Elements ON Invoice.invoice_ID = Contract_Elements.invoice_ID
WHERE ( (Contract_Elements.c_e_end_date)<([Invoice].[invoice_from_date]) );


6. This should not be excluded from the invoice. Future dates greater than the contract end date

SELECT Invoice.invoice_from_date, Invoice.invoice_to_date, Contract_Elements.c_e_start_date, Contract_Elements.c_e_end_date, Contract_Elements.c_e_total_days
FROM Invoice LEFT JOIN Contract_Elements ON Invoice.invoice_ID = Contract_Elements.invoice_ID
WHERE ( (Contract_Elements.c_e_start_date)>([Invoice].[invoice_to_date]) );


Now, can I use these queries in a vba to run appropriate query and to perform certain calculations on selected fields and save it in days column. All this will be done in subform.

My brain is totally out of order. Isnt it...Hope you are not confused...

Sorry, but I am totally confused!

[drago]

Sorry, but I am totally confused!

Hi ADezii, sorry about that... its just iam new to access and trying to work out what i can... Sorry about that :)

[eskelies]

If you have a datatype mismatch you are trying to reference to seperate things. I would pay close attention to the coding.