Quick Select Case query

[JeffBarker]

Hey all, just a quickie as I've been searching for an answer to this for a while, but so far kept turning up blanks - so I'm happy to defer to the forum on this one!

Is it possible to build a Select Case statement based on two seperate fields on a form?

So, if Field A = 1 and Field B = 2 then docmd.dosomething, but if Field A = 2 and Field B = 3 then docmd.dosomethingelse??

If Field A contains a string value then Field B may or may not be populated, so depending on whether Field B is populated or not will be the catalyst to trigger one event - if Field A is populated and Field B isn't then something else will happen.

If neither Field is populated then I need to display something else.

Thanks in advance, y'all!

 

[Rx_]

There are probably several ways. Here is one quick example:
Concat the two values together then build your Case statement off the result.
Example: of me.fieldA & Me.fieldB
1 & "0" & 2 = 102
Null & "0" & 1 = 01
1 & "0" & Null = 10
2 & "0" & 3 = 203

By adding the "0" inbetween - it prevents the null & 1 resulting in the same answer as 1 & Null. Since you mentioned null - no math function (e.g. "+", "X" ..) can work with null. By useing the "&", it will just combine the two values.

Now that you have a list of unique return values, build your case statement.
Be sure to use the Case Else in the event something is added later you didn't handle.

Sure there will be other answers for this too. This is just one example.

 

[mdlueck]

I am confused what exactly you are endevoring to do.

SQL (the query language) has a CASE keyword which is able to consider many columns and ultimately selects one value for one column.

VBA has a Select Case statement which is quite different than SQL's CASE. VBA's is able to perform multiple lines of code (LOC's) for each Case being evaluated for. Quick example of this:

  Select Case strCommitMode
    Case "INSERT"
      Commit = Me.Insert(MePointer)
    Case "UPDATE"
      If Me.Update(MePointer) Then
        Commit = Me.aid
      Else
        Commit = 0
      End If
    Case Else
      Commit = 0
  End Select

 

[JeffBarker]

Hi guys, thanks for replying - Rx_, I think you may have hit the nail on the head there and I'll try that solution.

mdlueck, it's a bit hard to explain, but let's say that Field A is a unique identifier for a contact on our database, and Field B is their email address.

The db I'm working on right now allows the user to 'link' contacts to each other via a form - so if Contact 1 bought some beans for Contact 2, then Contact 1's unique identifier would appear somewhere in Contact 2's record - thus making Contact 1 the 'Primary Contact'.

What I'm currently trying to do is raise and send an invoice for the purchase of the aforementioned beans, but there a few scenarios that would affect whether the invoice would be sent by email or via snailmail.

Here's an example of some of the outcomes:

Scenario 1: A 'Primary Contact' exists and has an email address, so the invoice would be sent via email to the Primary Contact.

Scenario 2: A 'Primary Contact' exists but we don't have an email address for them, so the invoice would be sent via snailmail.

I am familiar with the Case Statement function as you've detailed above, but what I'm looking for is something along these lines (if it exists):

dim PrimaryContact as integer
dim PrimaryContactEmail as string

PrimaryContact = FieldA
PrimaryContactEmail = Field B

Select Case PrimaryContact and PrimaryContactEmail
Case >0 and is not null
docmd.OpenInvoiceViaEmail

Case >0 and isnull
docmd.OpenInvoiceViaSnailMail

Case = 0
docmd.DoSomethingElse

end select

If that makes sense?

Thanks in advance!

 

[mdlueck]

I perceive you are trying to accomplish this in VBA code, not SQL.

A couple of examples which might help you:

    Select Case lLen
      Case 0
        revconv_ConvA2N = -1
      Case 1
        If (strInput = "-") Then
          revconv_ConvA2N = -1
        Else
          revconv_ConvA2N = 99 + (Asc(strInput) - 64)
        End If
      Case 2
        If (strInput = "--") Then
          revconv_ConvA2N = -1
        Else
          intN = 26 * (Asc(Mid(strInput, 1, 1)) - 64)
          intR = Asc(Mid(strInput, 2, 1)) - 64
          revconv_ConvA2N = 99 + (Int(Trim(Str(intN + intR))))
        End If
      Case Else
        revconv_ConvA2N = -1
    End Select

  'If Fandango quote is selected
  If (Me.quoteid > 0) And (Me.prodteamid > 0) Then
    BurdenedCost = ObjQuotesTbl.poprice * Me.prodteamburdenrate
  'If JDE is selected
  ElseIf Me.prodpartflg = True Then
    BurdenedCost = Me.jdestdcst
  Else
    BurdenedCost = -1
  End If

  'Custom logic to mimic SQL logic for JDE overriding Fandango pricing as in
  'LocatePartByID and LocatePartByPartNumber methods of the DB Object
  If (Me.quoteid = -1) Or (Me.quoteid = 0) Then
    If Me.jdesip = "N/A" Then
      Me.prodpartflg = False
      MePointer.fldprodpartflg.Value = Me.prodpartflg
    Else
      Me.prodpartflg = True
      MePointer.fldprodpartflg.Value = Me.prodpartflg
    End If
  Else
    Me.prodpartflg = False
    MePointer.fldprodpartflg.Value = Me.prodpartflg
  End If

  Select Case strThisKey
    'AOE entries
    Case "G1N1"
      Call AOECmplStatus_Click
    Case "G1N2"
      Call AOEFixtureTeamLocation_Click
    Case "G1N3"
      Call AOEFixtureType_Click
  End Select

Note: No ELSE in this example... ELSE events end up in the garbage as they are not handled.

And just as a comparison, the SQL CASE would look like the following:

                CASE
                  WHEN (([p].[rev] IS NOT NULL)
                        AND ([p].[ver] IS NOT NULL)
                        AND ([b].[farev] IS NOT NULL)
                        AND ([b].[faver] IS NOT NULL)
                        AND ([p].[rev] = [b].[farev])
                        AND ([p].[ver] = [b].[faver])) THEN 1
                  ELSE 0
                END AS [revvermatchesflg],

 

[JeffBarker]

Hi Michael,

Thanks for posting all that up - my apologies for not specifying whether I was using VBA or SQL!

I didn't realise you could place an If...THEN...ELSE statement in a Select Case statement, so that's interesting.

I think between this and Rx_'s suggestion I have what I need - so I'll post back here when/if this solves my original query!

 

[mdlueck]

I didn't realise you could place an If...THEN...ELSE statement in a Select Case statement, so that's interesting.

Which later I needed to port VBA business logic to SQL UDF's and found it challenging to port the code with the less powerful SQL CASE.

Assistance req translating VBA into SQL User Defined Function (UDF)
http://www.access-programmers.co.uk/forums/showthread.php?t=230186

So, VBA's SELECT CASE may have LOTS of code within each CASE, SQL's is far more limited.