Case Statement problem

[krazykasper]

I really appreciate any help you can provide. Although I am only vaguely familiar with VBA I think my problem has to do with the way I am naming things or the “Select Case” statement in my code.
In my report:
The text box is named txt_MyPrice
The Control Source is =mod_CreditItem()
In my code:
The name of the Module is mod_CreditItem
In the database:
The table that houses the data is dbo_InvoiceDetail (accessed via ODBC)
The fields in the table are named: BillingType and ExtPrice

When using the following code, the report asks me to input data for that module name, when in fact it should get the fields from the database tables; modify them as per the Case and Case Else Statements and display them in the report.

Following is my current code:

Option Compare Database

Public Function mod_CreditItem()

Dim ShippingFactor As Integer

Select Case dbo_InvoiceDetail.BillingType
Case "RE", "G2", "S1", "ZLG2", "ZZSA", "ZZG2", "ZZSR"
ShippingFactor = -1
Case Else
ShippingFactor = 1
End Select
mod_CreditItem = dbo_InvoiceDetail.ExtPrice * ShippingFactor

End Function


Thanks for your help.

Krazy (Bill) Kasper

 

[doco]

[FONT=Times New Roman]Select Case dbo_InvoiceDetail.BillingType[/FONT]
[FONT=Times New Roman][SIZE=2]   Case "RE", "G2", "S1", "ZLG2", "ZZSA", "ZZG2", "ZZSR"[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=2]       ShippingFactor = -1[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=2]   Case Else[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=2]       ShippingFactor = 1[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=2]End Select[/SIZE][/FONT]
 
[FONT=Times New Roman][SIZE=2]mod_CreditItem = dbo_InvoiceDetail.ExtPrice * ShippingFactor[/SIZE][/FONT]
 
[FONT=Times New Roman][SIZE=2]End Function[/SIZE][/FONT]

You are referring to an entire table instead of a field in an individual record from that table

You need to extract the field from the table and review that field with the select case

[FONT=Courier New]Select Case RecordSet.MyField[/FONT]
[FONT=Courier New]   Case "RE", "G2", "S1", "ZLG2", "ZZSA", "ZZG2", "ZZSR"[/FONT]
[FONT=Courier New][SIZE=2]       ShippingFactor = -1[/SIZE][/FONT]
[FONT=Courier New][SIZE=2]   Case Else[/SIZE][/FONT]
[FONT=Courier New][SIZE=2]       ShippingFactor = 1[/SIZE][/FONT]
[FONT=Courier New][SIZE=2]End Select[/SIZE][/FONT]

 

[Banana]

Also, you shouldn't have a module and function with same name. This will create problems.

 

[krazykasper]

Thanks!
By "Record Set" I presume you mean the table that houses the field (BillingType)?
That being the case, my Record Set is dbo_InvoiceDetail

If you mean something else, then how does it know in which table the field is located?

Krazy (Bill) Kasper

 

[doco]

Thanks!
By "Record Set" I presume you mean the table that houses the field (BillingType)?
That being the case, my Record Set is dbo_InvoiceDetail

If you mean something else, then how does it know in which table the field is located?

Krazy (Bill) Kasper

Imagine you have left your car key which is one of many keys on a keychain on the kitichen table in your house. Now would you retrieve the house (dbo_InvoiceDetail) to get the key? Or retrieve the keychain (recordset) from the house (dbo_InvoiceDetail) then review the keys (field) to retrieve the key?

 

[doco]

Perhaps a simple example

Public Sub BlahBlah()
    Dim rs As DAO.Recordset
 
    Set rs = CurrentDb.OpenRecordset("dboInvoiceDetail", dbOpenDynaset)
 
    With rs
        .MoveFirst
 
        Do Until .EOF
            Select Case .Fields("BillingType")
                Case "RE", "G2", "S1", "ZLG2", "ZZSA", "ZZG2", "ZZSR"
                    ShippingFactor = -1
                Case Else
                    ShippingFactor = 1
            End Select
            mod_CreditItem = .Fields("ExtPrice") * ShippingFactor
 '    don't know what you do with mod_CreditItem but you would probably do 
 '    more than initialize it here. but maybe you get the overall idea?
            .MoveNext
        Loop
    End With
 
    Set rs = Nothing
End Sub

or something akin to that

 

[krazykasper]

Almost there (I hope)

Thanks for your help. (I couldn't use some of the code as it gave errors.)
I am now at the point where I have the field displaying in my report.
Here's the latest code. (F.Y.I. - I call the module into my report from the Control Source in an unbound text box named txt_MyPrice.)
Problem is it always shows as $0.00 (zero dollars).

Public Function CreditItem()
Dim ShippingFactor As Integer
Select Case BillingType
Case "RE", "G2", "S1", "ZLG2", "ZZSA", "ZZG2", "ZZSR"
ShippingFactor = -1
Case Else
ShippingFactor = 1
End Select
CreditItem = ExtPrice * ShippingFactor
End Function


Krazy K(Bill) Kasper

 

[doco]

Thanks for your help. (I couldn't use some of the code as it gave errors.)
I am now at the point where I have the field displaying in my report.
Here's the latest code. (F.Y.I. - I call the module into my report from the Control Source in an unbound text box named txt_MyPrice.)
Problem is it always shows as $0.00 (zero dollars).

Public Function CreditItem()
Dim ShippingFactor As Integer
Select Case BillingType
Case "RE", "G2", "S1", "ZLG2", "ZZSA", "ZZG2", "ZZSR"
ShippingFactor = -1
Case Else
ShippingFactor = 1
End Select
CreditItem = ExtPrice * ShippingFactor
End Function


Krazy K(Bill) Kasper

How does your function know what 'BillingType' is? It is not passed as an argument in your function; is it a global variable? This would be true for the other variables in the struct: { CreditItem, ExtPrice }. Do you have Option Explicit set in the Genearal Declarations section of the module?

If you set a recordset object then loop through each record of that resultset, you then can test the pertinent field for select case. Or you could use the function you have above but somehow the function needs to know what is being tested.

If you have undeclared and or uninitialized variables the function above will return 0 every time.

You said you tried something like the code I gave but was getting errors. What kind of errors?

 

[doco]

Here is an example of how that might work.

Option Compare Database
Option Explicit
Public Function ReviewFieldData()
Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("improvements", dbOpenDynaset)
 
    With rs
        .MoveFirst
        Do Until .EOF
            .Edit
            .Fields("modified_value") = .Fields("initial_value") * _
                GetDwellingFactor(.Fields("imp_type"))
            .Update
            .MoveNext
        Loop
    End With
 
    Set rs = Nothing
 
End Function
Public Function GetDwellingFactor(ByVal szImpType As String) As Single
    Select Case szImpType
        Case "DWELL", "MHOME"
            GetDwellingFactor = 1.5
        Case Else
            GetDwellingFactor = 1#
    End Select
 
End Function