DLOOKUP with IIF Statements

[Scrone18]

I am in the process of building a billing system through access and I have run into a stumbling block. I am trying to modify a lookup of product infomation with a case such as "if calling charges is selected, open CDRQ, sum all calling charges for the current date where the current open form's customerID is equal to that of CDRQ's CustomerID" This has been a very tricky equation taht eludes me. Here is the code that I have written thus far:

CDRQ contains Calling Data with "CustomerID" and "Charge" as the only relevant fields and CDRQ is a Query

Manage_Orders is the current open form with "customerID" as the only relevant field

Private Sub Product_AfterUpdate()
On Error GoTo Err_Product_AfterUpdate

Dim strFilter As String
Dim CDRQ As Recordset
Dim Manage_Orders As Recordset
Dim Sumit As String


Set CDRQ = [CDRQ]
Set Manage_Orders = [Forms]![Manage Orders].[CustomerID]
Set Sumit = DSum("Charge", "CDRQ", [CDRQ]![CustomerID] = [Manage Orders]![CustomerID])

' Evaluate filter before it's passed to DLookup function.
strFilter = "ProductID = " & Me.ProductID


' Look up product's unit price and assign it to UnitPrice control.

Me.UP = IIf(DLookup("ProductID", "Products 1", strFilter) = "158", Sumit, DLookup("UnitPrice", "Products 1", strFilter))

' Me.UP = DLookup("UnitPrice", "Products 1", strFilter)

' Look up product's accounting number and assign it to AccountingID control.

Me.AID = DLookup("AccountingID", "Products 1", strFilter)

Exit_Product_AfterUpdate:
Exit Sub

Err_Product_AfterUpdate:
MsgBox Err.Description
Resume Exit_Product_AfterUpdate

End Sub

Any help would be appreciated. I need this to basically sum all the charges in CDRQ when Challing Charges is selected and then apply that sum to the unit price where the customerID's Match.

Thanks.

Tony

 

[DCrake]

What you need is some along the lines of the following

Dim Rs As DAO.Recordset
Dim sStr As String
Dim TotalCharge As Double

sStr = "Select * From CDRQ Where CustomerID =" & Me.CustomerID & " And [InvoiceDate] =#" & Me.FormDate & "#"


Set Rs. = CurrentDb.OpenRecordset(sStr)

If Not Rs.EOF And Not Rs.BOF Then
    Do Until Rs.EOF
        TotalCharge = TotalCharge + Rs("Charge")
         Rs.MoveNext
    Loop
    Rs.Close
End If

In the above example I have opened recordset from the CDRQ table for a specific customer That has invoices on a particular date. (Supplied by the form)

I have tested to see if any records existed

Next I have started with a zero balance and looped through each record in the recordset and added up all the charges to give me a total figure.

When it reached the end of the recordset I closed the Recordset and released it from memory (This is important and should be done on all occasions)

If you want to do different things to diffferent fields in the recordset you can do so during the Do Until ... Loop section.

Hope this helps


Set Rs = Nothing

 

[Scrone18]

I tried out a few variants of the code that you provided with no avail. This coding will have to have an IIF statement within it for if the selected product is "Calling Charges" or "158" (which is the real productID) then search for the calling charges within CDRQ (Which is a query) and sum up the "charges" within the current month only (from the 1st of the month to the last day of the current month) THEN place that total in the ME.UP field of the sub-form that is currently open AND if "Calling Charges" is is not selected DLookup("Unit Price", "Products 1", strFilter).

I want to be able to add more IIF's within this statement as I build this database. For example we have certain products that deduct a certain amount based on calling data. I want to beable to do this automatically instead of manually doing it every time...very time consuming.

Is there anyway that I can use the what you have given me and build out Case statements that will allow more expandability....for example....

DLookup("Unit Price", "Products 1", strFilter)
Case
IIF(ProductID=###, blah blah blah, 0)
Case
IIF(ProductID=###, blah blah blah, 0)
and so on.....

otherwise if anything else is selcted do the normal DLookup from above.

 

[Scrone18]

GREAT NEWS! I was able to get the code working after several tries here is the process!!!

Private Sub Product_AfterUpdate()
On Error GoTo Err_Product_AfterUpdate

Dim 'Your Database Name' As Database
Dim CDRSumCharge As Recordset ' your record Name
Dim strFilter As String 'String below that is declared
Dim strLook As String
Dim strDomestic As String
Dim Manage_Orders As [Form_Manage Orders] 'form that is currently open
Dim CDRQ As Recordset

' Evaluate filter before it's passed to DLookup function.

strFilter = "ProductID = " & Me.ProductID 'this makes sure that the productID's are equal
strLook = "[CDRSumCharge]![CustomerID] = " & [Forms]![Manage Orders]![CustomerID] ' This makes sure that the CustomerID's are equal

' Look up product's unit price and assign it to UnitPrice control.

Select Case Me.ProductID 'The field in wich cases will be built
Case Is = "158" 'This is the Item Number/Product name
Me.UP = DLookup("SumCharges", "CDRSumCharge", strLook) 'this is where you want the value to be and how to look it up
Case Is = "176"
Me.UP = DLookup("SumCharges", "CDRSumCharge", strLook)
Case Else
Me.UP = DLookup("UnitPrice", "Products 1", strFilter)
End Select

Exit_Product_AfterUpdate:
Exit Sub

Err_Product_AfterUpdate:
MsgBox Err.Description
Resume Exit_Product_AfterUpdate

'Essentially this is an IIF statement with DLookup's except that my IIF's are now cases which allows for more expandability as you custom tailor other calculations for other items. This takes the hassel out of manually calculating things. If you want to add more cases just copy and past and change the numbers or calculations. I hope this helps