Invoicing Module -- count number of completed tasks 1

[iuianj07]

Hello guys,

I created an invoicing module for one of our database in this office, what this does is if the Reporting Period is for Q the price is $45.00 per statement(job we do),and if a checkbox named 24HourRush is checked, the Q price would be $67.50 or if the checkbox named 1-3DayRush is checked, the Q price would be $56.25. We also have a Reporting Period of YE, the price for that is $50.00, if 24HourRush is checked the price would change to $75.00 and if 1-3DayRush is checked the price would change to $62.50.

The module I have for this is below:

Public Function GetAmount(O_ITV As Variant, Rush24 As Boolean, Rush36 As Boolean, RptPrd As String) As Currency

    Dim curO_ITV As Currency
    Dim curRush24 As Currency
    Dim curRush36 As Currency
    Dim curRptPrdQYE As Currency
    Dim curDefault As Currency
    Dim curRush24Q As Currency
    Dim CurRush36Q As Currency
    Dim curRptPrdQ As Currency
    

    curO_ITV = 12
    curRush24 = 75
    curRush36 = 62.5
    curRush24Q = 67.5
    CurRush36Q = 56.25
    curRptPrdQYE = 50
    curRptPrdQ = 45
    curDefault = 0


    Dim curAmt As Currency
    Select Case True
        Case IsNull(O_ITV) = False
            curAmt = curO_ITV
        Case RptPrd Like "*YE*" And Rush24 = True
            curAmt = curRush24
        Case RprPrd Like "*YE*" And Rush36 = True
            curAmt = curRush36
        Case RptPrd Like "*Q*" And Rush24 = True
            curAmt = curRush24Q
        Case RptPrd Like "*Q*" And Rush36 = True
            curAmt = CurRush36Q
        Case RptPrd Like "*YE*" And Rush36 = True
            curAmt = curRush36
        Case RptPrd Like "*YE*"
            curAmt = curRptPrdQYE
        Case RptPrd Like "*Q*"
            curAmt = curRptPrdQ
        Case Else
            curAmt = curDefault
    End Select
    GetAmount = curAmt
End Function

This is correctly pulling out the price for invoicing. However, things got complicated though that if the number of statements (jobs) reached 10,000 in one year (Jan-Dec) the price for Reporting Period Q will be reduced to $42.50, and if the 24HourRush for Reporting Period Q is checked the price will be $63.75 and if the 1-3DayRush is checked the price will be $53.13.

The field type to count to see if we already reached 10,000 statements completed within the year is Signed_Off (this is a date data type) we put the date here to mark it as complete and have been returned back to the client. So if the number of Signed_Off is 10,000 between January-December then the price will change to the above mentioned prices.


Any help is greatly appreciated.

Thanks

 

[Remou]

Are you thinking of DCount?

DCount("Signed_Off","TableName","CustomerID=" & ID)

http://lessthandot.com

 

[iuianj07]

I think so, as i am also researching on how to count the number of statements if it has reached 10,000 loans.. How am I going to incorporate this though in the above module? that if it has not yet reached 10,000 statements then the above module stays the same, but if it exceeded 10,000 statements it will change the pricing....

Take note also though that if example we do reach 10,000 loans within this year. But when January 2011 starts... the count should go back to 0... Basically it just needs to be 10,000 loans within a year period. Thanks

I guess I can create the new Select Case statement for the new price, I just need to know where to put the DCount (maybe) on the above module to meet the conditions I mentioned above. And also to check if it already reached 10,000 loans within this year.

Thanks for the prompt response.

Thanks

 

[Remou]

Something like:

If DCount("Signed_Off","TableName","CustomerID=" _
  & ID & " And DateField Between #" _
  & DateSerial(Year(Date(),1,1) & "# And #" _
  & DateSerial(Year(Date(),12,31) & "#") < 10000 Then
    curO_ITV = 12
    curRush24 = 75
    curRush36 = 62.5
    curRush24Q = 67.5
    CurRush36Q = 56.25
    curRptPrdQYE = 50
    curRptPrdQ = 45
    curDefault = 0
Else
    'Other prices
End If

I'd consider getting these prices into a table if I were you.

http://lessthandot.com

 

[Andrzejek]

Your variables declared at the top of your Function are not really variables, they are constants. Consider:

Const curO_ITV As Currency = 12
Const curRush24 As Currency = 75
Const curRush36 As Currency = 62.5
Const curRptPrdQYE As Currency = 50
Const curDefault As Currency = 0
Const curRush24Q As Currency = 67.5
Const CurRush36Q As Currency = 56.25
Const curRptPrdQ As Currency = 45

Also:

checkbox named 24HourRush is checked, the Q price would be $67.50 or if the checkbox named 1-3DayRush is checked

If there are checkboxes, what if both (24HourRush and 1-3DayRush) are checked at the same time? You can do that with checkboxes, they work independently

Just my $0.02

Have fun.

---- Andy

 

[iuianj07]

@Remou

Thanks... so should it look something like this:

Public Function GetAmount(O_ITV As Variant, Rush24 As Boolean, Rush36 As Boolean, RptPrd As String) As Currency

    Dim curO_ITV As Currency
    Dim curRush24 As Currency
    Dim curRush36 As Currency
    Dim curRptPrdQYE As Currency
    Dim curDefault As Currency
    Dim curRush24Q As Currency
    Dim CurRush36Q As Currency
    Dim curRptPrdQ As Currency
    

 If DCount("Signed_Off","TableName","CustomerID=" _
  & ID & " And DateField Between #" _
  & DateSerial(Year(Date(),1,1) & "# And #" _
  & DateSerial(Year(Date(),12,31) & "#") < 10000 Then
    curO_ITV = 12
    curRush24 = 75
    curRush36 = 62.5
    curRush24Q = 67.5
    CurRush36Q = 56.25
    curRptPrdQYE = 50
    curRptPrdQ = 45
    curDefault = 0


    Dim curAmt As Currency
    Select Case True
        Case IsNull(O_ITV) = False
            curAmt = curO_ITV
        Case RptPrd Like "*YE*" And Rush24 = True
            curAmt = curRush24
        Case RprPrd Like "*YE*" And Rush36 = True
            curAmt = curRush36
        Case RptPrd Like "*Q*" And Rush24 = True
            curAmt = curRush24Q
        Case RptPrd Like "*Q*" And Rush36 = True
            curAmt = CurRush36Q
        Case RptPrd Like "*YE*" And Rush36 = True
            curAmt = curRush36
        Case RptPrd Like "*YE*"
            curAmt = curRptPrdQYE
        Case RptPrd Like "*Q*"
            curAmt = curRptPrdQ
        Case Else
            curAmt = curDefault
    End Select
    GetAmount = curAmt

Else
    curO_ITV = 12
    curRush24 = 75
    curRush36 = 62.5
    curRush24Q = 63.75
    CurRush36Q = 53.13
    curRptPrdQYE = 50
    curRptPrdQ = 42.50
    curDefault = 0
Dim curAmt As Currency
    Select Case True
        Case IsNull(O_ITV) = False
            curAmt = curO_ITV
        Case RptPrd Like "*YE*" And Rush24 = True
            curAmt = curRush24
        Case RprPrd Like "*YE*" And Rush36 = True
            curAmt = curRush36
        Case RptPrd Like "*Q*" And Rush24 = True
            curAmt = curRush24Q
        Case RptPrd Like "*Q*" And Rush36 = True
            curAmt = CurRush36Q
        Case RptPrd Like "*YE*" And Rush36 = True
            curAmt = curRush36
        Case RptPrd Like "*YE*"
            curAmt = curRptPrdQYE
        Case RptPrd Like "*Q*"
            curAmt = curRptPrdQ
        Case Else
            curAmt = curDefault
    End Select
    GetAmount = curAmt


End Function

Does the count go back to zero once January 2011 comes?

Thanks again!

 

[Remou]

Lots of duplicate code there. This bit:

... " And DateField Between #" _
& DateSerial(Year(Date(),1,1) & "# And #" _
& DateSerial(Year(Date(),12,31) & "#")

Says count records between 1st January and 31st December of the current year, so it counts the records only for one year. You will need to add the correct names for your date & customer id (grouping id) fields (columns)

Public Function GetAmount(O_ITV As Variant, Rush24 As Boolean, Rush36 As Boolean, RptPrd As String) As Currency

    Dim curAmt As Currency

    Dim curO_ITV As Currency
    Dim curRush24 As Currency
    Dim curRush36 As Currency
    Dim curRptPrdQYE As Currency
    Dim curDefault As Currency
    Dim curRush24Q As Currency
    Dim CurRush36Q As Currency
    Dim curRptPrdQ As Currency
    
 'Count of records for a particular customer in the current year
 If DCount("Signed_Off","TableName","CustomerID=" _
  & ID & " And DateField Between #" _
  & DateSerial(Year(Date(),1,1) & "# And #" _
  & DateSerial(Year(Date(),12,31) & "#") < 10000 Then
    curRush36 = 62.5
    curRush24Q = 67.5
    CurRush36Q = 56.25
    curRptPrdQYE = 50
    curRptPrdQ = 45
  Else
    curRush36 = 62.5
    curRush24Q = 63.75
    CurRush36Q = 53.13
    curRptPrdQYE = 50
    curRptPrdQ = 42.50
  End If

'Values that do not change
    curDefault = 0
    curO_ITV = 12
    curRush24 = 75


    Select Case True
        Case IsNull(O_ITV) = False
            curAmt = curO_ITV
        Case RptPrd Like "*YE*" And Rush24 = True
            curAmt = curRush24
        Case RprPrd Like "*YE*" And Rush36 = True
            curAmt = curRush36
        Case RptPrd Like "*Q*" And Rush24 = True
            curAmt = curRush24Q
        Case RptPrd Like "*Q*" And Rush36 = True
            curAmt = CurRush36Q
        Case RptPrd Like "*YE*" And Rush36 = True
            curAmt = curRush36
        Case RptPrd Like "*YE*"
            curAmt = curRptPrdQYE
        Case RptPrd Like "*Q*"
            curAmt = curRptPrdQ
        Case Else
            curAmt = curDefault
    End Select
    GetAmount = curAmt


End Function

http://lessthandot.com

 

[iuianj07]

Thanks againg Remou...

Forgot to mention that this module is being used in a query (invoicing query) for a new column on that query... for me to call this module into the query I write:

Amount: GetAmount([OSAR_ImagetoVendor],[24HourRush],[1-3DayRush],[ReportingPeriod])

What if I do not have any "Customer ID"/grouping fields? How will I re-write the DCount()? I tried:

 If DCount("Signed_Off", "Job_Tracking", , And DateField Between #" _
  & DateSerial(Year(Date, 1, 1) & "# And #" & DateSerial _
  (Year(Date, 12, 31) & "#") < 10000)) Then

But it gives a compile error, I apologize, still learning the ropes of debugging....

Any help is greatly appreciated.

 

[Remou]

If this is for a query, you need to put those amounts into a table and go about it a whole different way. Let me think about it and get back to you. It may take a few hours, because it is getting late here.

http://lessthandot.com

 

[iuianj07]

Thanks Remou---

I appreciate the time in helping me out.

 

[Remou]

I have just though - please post your current query, thanks.

http://lessthandot.com

 

[iuianj07]

Hello Remou---

Below you will find the query:

SELECT Job_Tracking.InvestorNumber, Job_Tracking.LoanNumber, Job_Tracking.ProspectusID, Job_Tracking.PropertyNumber, Job_Tracking.InvoiceNumber, Job_Tracking.InvoiceDate, Job_Tracking.ReportingPeriod AS Quarter, Switch([OSAR_ImagetoVendor] Is Not Null,"OSAR Submitted by Sub for Data Entry",[ReportingPeriod] Like "*Q*","Quarterly Financial Statement Spread",[ReportingPeriod] Like "*YE*","Annual Financial Statement Spread") AS [Payment Type], "" AS Respread, GetAmount([OSAR_ImagetoVendor],[24HourRush],[1-3DayRush],[ReportingPeriod]) AS Amount, Job_Tracking.[Signed Off] AS [Completed Date], Job_Tracking.ConsolidatedStatement, Job_Tracking.[24HourRush], Job_Tracking.[1-3DayRush], Job_Tracking.[East-West]

FROM Job_Tracking

WHERE (((Job_Tracking.PropertyNumber)<>"SUM") AND ((Job_Tracking.InvoiceNumber) Is Null) AND ((Job_Tracking.[Signed Off])<[Select all records signed off before this date]) AND ((Job_Tracking.ConsolidatedStatement)<>-1) AND ((Job_Tracking.[East-West])="CMS-East")) OR (((Job_Tracking.PropertyNumber)="SUM") AND ((Job_Tracking.InvoiceNumber) Is Null) AND ((Job_Tracking.[Signed Off])<[Select all records signed off before this date]) AND ((Job_Tracking.ConsolidatedStatement)=-1) AND ((Job_Tracking.[East-West])="CMS-East"));

Thanks again....

 

[Remou]

There are one or two places where I wondered why there wasn't Or Is Null in your query, but that is up to you. I have use aliases to make life easier.

SELECT jt.InvestorNumber,
jt.LoanNumber,
jt.ProspectusID,
jt.PropertyNumber,
jt.InvoiceNumber,
jt.InvoiceDate,
jt.Quarter,
jt.[Payment Type],
jt.Respread,
jt.[Completed Date],
jt.ConsolidatedStatement,
jt.[24HourRush],
jt.[1-3DayRush],
jt.[East-West],
c.Amount

FROM Job_TrackingCharges c 

RIGHT JOIN (SELECT j.InvestorNumber, 
                   j.LoanNumber, 
                   j.ProspectusID, 
                   j.PropertyNumber, 
                   j.InvoiceNumber, 
                   j.InvoiceDate, 
                   j.ReportingPeriod AS Quarter, 
                   Switch([OSAR_ImagetoVendor] Is Not Null,"OSAR Submitted by Sub for Data Entry",
                        [ReportingPeriod] Like "*Q*","Quarterly Financial Statement Spread",
                        [ReportingPeriod] Like "*YE*","Annual Financial Statement Spread") AS [Payment Type], 
                   "" AS Respread, 
                   j.[Signed Off] AS [Completed Date], 
                   j.ConsolidatedStatement, j.[24HourRush], 
                   j.[1-3DayRush], 
                   j.[East-West],
                   
                   IIf(IsNull([OSAR_ImageToVendor]),IIf([ReportingPeriod] Like "*YE*","YE",
                        IIf([ReportingPeriod] Like "*Q*","Q","N/A")),"O_ITV") AS RptType, 

                   IIf(DCount("*","Job_Tracking","[Signed Off] Between DateSerial(Year(Date()),1,1) 
                         And DateSerial(Year(Date()),12,31) And InvestorNumber=" & [InvestorNumber])>10000
                         And IsNull([OSAR_ImagetoVendor]),"10K","N/A") AS 10K, 

                    IIf([24HourRush] And IsNull([OSAR_ImageToVendor]),"Rush24",
                         IIf([1-3DayRush] And IsNull([OSAR_ImageToVendor]),"Rush36","N/A")) AS Rush

             FROM Job_Tracking j
             
             WHERE (((j.PropertyNumber)<>"SUM") 
                      AND ((j.InvoiceNumber) Is Null) 
                      AND ((j.[Signed Off])<[Select all records signed off before this date]) 
                      AND ((j.ConsolidatedStatement)<>-1) 
                      AND ((j.[East-West])="CMS-East")) 
                OR (((j.PropertyNumber)="SUM") 
                      AND ((j.InvoiceNumber) Is Null) 
                      AND ((j.[Signed Off])<[Select all records signed off before this date]) 
                      AND ((j.ConsolidatedStatement)=-1) 
                      AND ((j.[East-West])="CMS-East")))  AS jt 

ON (c.[10K] = jt.[10K]) 
AND (c.Rush = jt.Rush) 
AND (c.ReptType = jt.RptType);

Job_TrackingCharges table:
[tt]ID ReptType 10K Rush Amount
1 O_ITV N/A N/A €12.00
2 YE N/A N/A €50.00
3 Q N/A N/A €45.00
4 YE N/A Rush24 €75.00
5 Q N/A Rush24 €67.50
6 YE N/A Rush36 €62.50
7 Q N/A Rush36 €56.25
8 YE 10K N/A €50.00
9 Q 10K N/A €42.50
10 YE 10K Rush24 €75.00
11 Q 10K Rush24 €63.75
12 YE 10K Rush36 €62.50
13 Q 10K Rush36 €53.13[/tt]

http://lessthandot.com

 

[iuianj07]

Hello Remou---

Thanks for taking time to re-create the query, I created the Job_TrackingCharges Table and copied the code you created to create the query. However, when I try to run it, it has already been more than 2 hours and it's still not running... it goes to a Not Responding State.. Would you know what could have caused this?

I appreciate your help

 

[Remou]

Nope, no idea. I do not have any real data. Perhaps you can try with a much smaller table? Or post some sample data.

http://lessthandot.com

 

[iuianj07]

It is kinda weird though,, with the query I posted above (the original query) it doesn't have this problem, it'll take a maximum of 3sec to run the query, but for this one it takes a lot of time and freezes the database.

 

[Remou]

The problem is probably the DCount, it would probably be better to create a count table as a sub query. You original table does not include counting. You could try changing this:

IIf(DCount("*","Job_Tracking","[Signed Off] Between DateSerial(Year(Date()),1,1) 
                         And DateSerial(Year(Date()),12,31) And InvestorNumber=" & [InvestorNumber])>10000
                         And IsNull([OSAR_ImagetoVendor]),"10K","N/A") AS 10K

To a literal:

"10K" AS 10K

Which gives the wrong result, but is a good test.

http://lessthandot.com

 

[iuianj07]

I took off

IIf(DCount("*","Job_Tracking","[Signed Off] Between DateSerial(Year(Date()),1,1) 
                         And DateSerial(Year(Date()),12,31) And InvestorNumber=" & [InvestorNumber])>10000
                         And IsNull([OSAR_ImagetoVendor]),"10K","N/A") AS 10K

and just replaced it to "10K" AS 10K

and the query was able to run pretty fast. So it could be because of the DCount? Is there a way to workaround this?

Thanks for your help and patience

 

[Remou]

Please try this.

SELECT jt.InvestorNumber,
jt.LoanNumber,
jt.ProspectusID,
jt.PropertyNumber,
jt.InvoiceNumber,
jt.InvoiceDate,
jt.Quarter,
jt.[Payment Type],
jt.Respread,
jt.[Completed Date],
jt.ConsolidatedStatement,
jt.[24HourRush],
jt.[1-3DayRush],
jt.[East-West],
c.Amount

FROM Job_TrackingCharges c 

RIGHT JOIN (SELECT j.InvestorNumber, 
                   j.LoanNumber, 
                   j.ProspectusID, 
                   j.PropertyNumber, 
                   j.InvoiceNumber, 
                   j.InvoiceDate, 
                   j.ReportingPeriod AS Quarter, 
                   Switch([OSAR_ImagetoVendor] Is Not Null,"OSAR Submitted by Sub for Data Entry",
                        [ReportingPeriod] Like "*Q*","Quarterly Financial Statement Spread",
                        [ReportingPeriod] Like "*YE*","Annual Financial Statement Spread") 
                        AS [Payment Type], 
                   "" AS Respread, 
                   j.[Signed Off] AS [Completed Date], 
                   j.ConsolidatedStatement, j.[24HourRush], 
                   j.[1-3DayRush], j.[East-West], 
                   
                   IIf(IsNull([OSAR_ImageToVendor]),IIf([ReportingPeriod] Like "*YE*","YE",
                        IIf([ReportingPeriod] Like "*Q*","Q","N/A")),"O_ITV") AS RptType, 
                        
                   IIf([24HourRush] And IsNull([OSAR_ImageToVendor]),"Rush24",
                        IIf([1-3DayRush] And IsNull([OSAR_ImageToVendor]),"Rush36","N/A")) AS Rush, 
                        
                   IIf([JobCount]>10 And IsNull([OSAR_ImageToVendor]),"10K","N/A") AS 10K
                   
            FROM Job_Tracking AS j 
            
            INNER JOIN (SELECT j.InvestorNumber, 
                        Count(j.InvestorNumber) AS JobCount
                        FROM Job_Tracking j
                        WHERE j.[Signed Off] Between DateSerial(Year(Date()),1,1) 
                              And DateSerial(Year(Date()),12,31)
                        GROUP BY j.InvestorNumber)  AS jc 
            ON j.InvestorNumber = jc.InvestorNumber
            
             WHERE (((j.PropertyNumber)<>"SUM") 
                      AND ((j.InvoiceNumber) Is Null) 
                      AND ((j.[Signed Off])<[Select all records signed off before this date]) 
                      AND ((j.ConsolidatedStatement)<>-1) 
                      AND ((j.[East-West])="CMS-East")) 
                OR (((j.PropertyNumber)="SUM") 
                      AND ((j.InvoiceNumber) Is Null) 
                      AND ((j.[Signed Off])<[Select all records signed off before this date]) 
                      AND ((j.ConsolidatedStatement)=-1) 
                      AND ((j.[East-West])="CMS-East")))  AS jt 

ON (c.[10K] = jt.[10K]) 
AND (c.Rush = jt.Rush) 
AND (c.ReptType = jt.RptType);

http://lessthandot.com

 

[iuianj07]

Thanks Remou--

the new query you wrote ran after approx 15-20secs which is so much better than freezing up

As of today we currently signed off more than 10,000 statements, so based on the query you wrote it is pulling out the correct prices... Thank you very much! for me to test if it will pull out the correct prices if we signed off less than 10,000 statements, I need to wait til start January 2011 if it will charge at the correct price, but I trust your code

I cannot explain how greatful I am for your assistance in my problem. Of course I will mark a Star to thank you for your valuable post, but if there are any way that I could do to put up a recognition for helping me in this forum, please let me know and I will be glad to do so.

Hopefully you could still help me in my future posts! ;-)

Thanks again!