Access 2003 VBA If Then Help

[dprichard]

I have a client with an access 2003 database and I need to change something for them. I normally work in php mysql so I am a little out of my element. They are submitting a form that processes and checks for fees. They have a sale number that is normally based on the date.

20080612 for example.

Well on fridays they are changing the sale number to

20080612FR to indicate it is friday. Normall their buyer and seller fees are based on numbers entered in the database, but on Fridays they will be the same for everyone. $200.00

So, what I need to do is say if the SelectAuction field ends in FS then skip searching the database for buyer and seller fees and just make each of those numbers 200. I really appreciate any help anyone can give on this. I am just totally out of my element here. I can kind of see where the changes need to be made, but don't know how to make them.

I guess I need to say if SelectAuction = wildcard with FR at the end then skip the seller fee and buyer fee lookup and make the seller and buyer fee 200.00

getterms
' Calculate Fees
Dim x As Variant

    If Me![SaleStatus] > 1 Then
        'calc Buyers fee
        Me![BuyerFees] = DLookup("BuyerFees", "qryBuyerSpecialFees_SaleID")
            If IsNull(Me![BuyerFees]) Then
                Me![BuyerFees] = DLookup("BuyerFees", "qrybuyersfee_SaleID")
            End If
        'calc Sellers fee
        Me![SellerFees] = DLookup("SellerFees", "QrySellerSpecialFees_SaleID")
            If IsNull(Me![SellerFees]) Then
                Me![SellerFees] = DLookup("SellerFees", "qrysellersfee_SaleID")
            End If
        'calc Automatic Transfer Fee
        If Me![AutoMaticTransFeeDone] <> True Then
        Me![AutoMaticTransFeeDone] = True
        Dim AutoTransFee As Double
        AutoTransFee = 0
        AutoTransFee = Nz(DLookup("TransportFees", "qrySellerAutoTransferFee_SaleID"))
            
            If AutoTransFee > 0 Then
                DoCmd.SetWarnings False
                DoCmd.OpenQuery "qrySellerAutoTransferFee_Append_SaleID"
                DoCmd.SetWarnings True
            End If
        End If
        
        
    End If

 

[pono1]

See the Right or Mid functions in Help, as well as DLookup. Also, consider creating a "controller procedure" to first do the "Friday" check...

If it's Friday
 Call new procedure ' to be written.
Else
 Call old procedure ' leave the old one alone if it works OK.
End If

More than anything though...I would urge your client not to use the Sale Number to indicate it's Friday. Packing extra meaning into a single field usually results in maintenance nightmares...

Regards,
Tim

 

[dprichard]

Unfortunately they already did the FR part on their own. :S

Okay, I am going to attempt this one piece at a time.

It looks like Dim declares a variable. So, would this work to isolate the last two characters and put them in a variable SpecialSale?

Dim SpecialSale As String
SpecialSale = Right([SelectAuction], 2)

getterms
' Calculate Fees
Dim x As Variant
Dim SpecialSale As String
SpecialSale = Right([SelectAuction], 2)


    If Me![SaleStatus] > 1 Then
        'calc Buyers fee
        Me![BuyerFees] = DLookup("BuyerFees", "qryBuyerSpecialFees_SaleID")
            If IsNull(Me![BuyerFees]) Then
                Me![BuyerFees] = DLookup("BuyerFees", "qrybuyersfee_SaleID")
            End If
        'calc Sellers fee
        Me![SellerFees] = DLookup("SellerFees", "QrySellerSpecialFees_SaleID")
            If IsNull(Me![SellerFees]) Then
                Me![SellerFees] = DLookup("SellerFees", "qrysellersfee_SaleID")
            End If
        'calc Automatic Transfer Fee
        If Me![AutoMaticTransFeeDone] <> True Then
        Me![AutoMaticTransFeeDone] = True
        Dim AutoTransFee As Double
        AutoTransFee = 0
        AutoTransFee = Nz(DLookup("TransportFees", "qrySellerAutoTransferFee_SaleID"))
            
            If AutoTransFee > 0 Then
                DoCmd.SetWarnings False
                DoCmd.OpenQuery "qrySellerAutoTransferFee_Append_SaleID"
                DoCmd.SetWarnings True
            End If
        End If
        
        
    End If

 

[dprichard]

Or will something like this work?

getterms 
' Calculate Fees 
Dim x As Variant 

 IF Right(Me![SelectAuction], 2) = FR Then
    Me![BuyerFees] = 200.00
    Else 
        'calc Buyers fee 
        Me![BuyerFees] = DLookup("BuyerFees", "qryBuyerSpecialFees_SaleID") 
            If IsNull(Me![BuyerFees]) Then 
                Me![BuyerFees] = DLookup("BuyerFees", "qrybuyersfee_SaleID") 
            End If 
        End If
        
IF Right(Me![SelectAuction], 2) = FR Then
    Me![SellerFees] = 200.00
    Else
        'calc Sellers fee 
        Me![SellerFees] = DLookup("SellerFees", "QrySellerSpecialFees_SaleID") 
            If IsNull(Me![SellerFees]) Then 
                Me![SellerFees] = DLookup("SellerFees", "qrysellersfee_SaleID") 
            End If 
End If
        'calc Automatic Transfer Fee 
        If Me![AutoMaticTransFeeDone] <> True Then 
        Me![AutoMaticTransFeeDone] = True 
        Dim AutoTransFee As Double 
        AutoTransFee = 0 
        AutoTransFee = Nz(DLookup("TransportFees", "qrySellerAutoTransferFee_SaleID")) 
             
            If AutoTransFee > 0 Then 
                DoCmd.SetWarnings False 
                DoCmd.OpenQuery "qrySellerAutoTransferFee_Append_SaleID" 
                DoCmd.SetWarnings True 
            End If 
        End If 
         
         
End If

 

[pono1]

Not to sound flippant...but you'll have to test it...

An easy-to-use compiler tool: debug.print.

Example syntax... Debug.print <Variable>

The value of <variable> will show up in the VBA Immediate window (press Ctrl-G to toggle this window). So you might do this right in the code...

debug print Right("test",2)

Or you can also use msgbox to see a value...

msgbox <variable>

You may want to also look up "break point" and "Stepping through code" in VBA Help. Since you have programming experience in PHP I am sure you will catch on quickly... By the way, the Mid function is basically VBA's equivalent to PHP's Substr function. And VBA's Len function equates to PHP's strLen() function...

Regards,
Tim

 

[dprichard]

Thanks for your help Tim. Does my syntax look okay through this section though?

 IF Right(Me![SelectAuction], 2) = FR Then
    Me![BuyerFees] = 200.00
    Else 
        'calc Buyers fee 
        Me![BuyerFees] = DLookup("BuyerFees", "qryBuyerSpecialFees_SaleID") 
            If IsNull(Me![BuyerFees]) Then 
                Me![BuyerFees] = DLookup("BuyerFees", "qrybuyersfee_SaleID") 
            End If 
        End If
        
IF Right(Me![SelectAuction], 2) = FR Then
    Me![SellerFees] = 200.00
    Else
        'calc Sellers fee 
        Me![SellerFees] = DLookup("SellerFees", "QrySellerSpecialFees_SaleID") 
            If IsNull(Me![SellerFees]) Then 
                Me![SellerFees] = DLookup("SellerFees", "qrysellersfee_SaleID") 
            End If 
End If