LIKE in DLookup After Syntax

[Adam McReynolds]

I am looking to use a "If" statement with DLookup to find a record and see if the field name begins with "TW" as the characters. Here is what I have so far:

Dim matchCriteria As String

matchCriteria = "LIKE 'TW*'"

If DLookup("end_user", "tbl_module_repairs", "prikey = " & Me.txt_rid1.Value) = matchCriteria Then
MsgBox "Success"
Else
MsgBox "FAILURE"
End If

P.S. Further, I am trying to switch between two sets of pricing for customers that begin with "TW" and then all else. Would the use of a "case" statement be better? If so I do not have the "end_user" bound to the form so I would need to embed Dlookup in the "case" method.

Any help would be appreciated!

 

[jdraw]

Perhaps you should tell us more about the levels of pricing and what triggers who gets what price...

 

[Adam McReynolds]

Perhaps you should tell us more about the levels of pricing and what triggers who gets what price...

Thanks for the reply. Here is my code so far. This is just one of 10 because I am using 10 unbound "lines" so to speak to effect 10 records at once when a submit button is entered. This code is for a "apply price" button that applies to all 10 lines:

Dim strCriteria As String
Dim matchCriteria As String

matchCriteria = "LIKE '*TW*'"

'Field Line 1 Pricing
If Not IsNull(Me.txt_rid1) And Me.txt_customer1 = "Alpha" Then
If IsNull(Me.cmb_client_item1) Then
If DLookup("[end_user]", "tbl_module_repairs", "prikey = ' & Me.txt_rid1 & '") = matchCriteria Then
MsgBox "Success"
Else
With Me
    Select Case .txt_incoming1
    Case "Completed"
        With Me
            Select Case .txt_mod1
            Case "Line Extender"
                strCriteria = "repair_item = 'Line Extender  Repair' AND profile_types = 'Alpha'"
                .txt_rate1 = DLookup("[flat_rate_values]", "tbl_module_repairs_client_item_details", strCriteria)
            Case "Trunk Amplifier"
                strCriteria = "repair_item = 'Trunk Amplifier Repair' AND profile_types = 'Alpha'"
                .txt_rate1 = DLookup("[flat_rate_values]", "tbl_module_repairs_client_item_details", strCriteria)
            Case "System Amplifier"
                strCriteria = "repair_item = 'Trunk Amplifier Repair' AND profile_types = 'Alpha'"
                .txt_rate1 = DLookup("[flat_rate_values]", "tbl_module_repairs_client_item_details", strCriteria)
            Case "Network Amplifier"
                strCriteria = "repair_item = 'Trunk Amplifier Repair' AND profile_types = 'Alpha'"
                .txt_rate1 = DLookup("[flat_rate_values]", "tbl_module_repairs_client_item_details", strCriteria)
           
            Case Else
                MsgBox "No Matches for Record ID 1 Equipment. Please Manually Enter the Price"
        End Select
        End With

    Case "BER"
        .txt_rate1 = 0
        .cmb_client_item1 = "Non-Repairable Module"
    Case "Warranty"
        .txt_rate1 = 0
        .cmb_client_item1 = "Warranty Repair"
    

End Select
End With
End If
End If
End If

Where the MsgBox says "Success" is where I would apply code similar to the other lines of "strCriteria" code. The code for the "TW" price would be the same but pull from different price sets as such indicated by the red:

Case "Line Extender"
                strCriteria = "repair_item = [COLOR="Red"]'TW Line Extender  Repair'[/COLOR] AND profile_types = 'Alpha'"
                .txt_rate1 = DLookup("[flat_rate_values]", "tbl_module_repairs_client_item_details", strCriteria)

So I basically just need to get the MsgBox "Success" to fire and this method will work, but also I wanted to know if someone knows a better method(case?) to switch between multiple pricing options as opposed to just 2 designated by the "If" and "Else" option.

P.S. the end_user field would look like this "TW Garden Grove" or "TW Costa Mesa". I am looking for records with the "TW" as first 2 characters to determine which price set.

 

[jdraw]

If Left(end_userField,2) = "TW" Then
.......set TW price
else
.......set different price
end if

You should not have to code field values explicitly as your code shows ---way too inflexible.

I have a feeling your table structures may not be designed to serve your needs/business.
Can you post a jpg of your tables and relationships?

 

[Adam McReynolds]

If Left(end_userField,2) = "TW" Then
.......set TW price
else
.......set different price
end if

You should not have to code field values explicitly as your code shows ---way too inflexible.

I have a feeling your table structures may not be designed to serve your needs/business.
Can you post a jpg of your tables and relationships?

Thanks again for the reply. Can I use the "If Left" function to call specific criteria to find a specific record? My form is all unbound so that I can effect 10 records with one button.

As to the table it is my bosses database that I have come into and it is built very wrong. The basics are there though. I have posted screen shots of my form so you can see what it looks like.

 

[Adam McReynolds]

I think I got it. I am still open to any suggestions on using a case method as opposed to a If/Else method.

If Left(DLookup("[end_user]", "tbl_module_repairs", "prikey = " & Me.txt_rid1.Value), 2) = "TW" Then