VBA/SQL: How to build SQL with Function in WHERE clause?

Question

I have a case in which I am trying to pull the state value from a description field. However, I need to change some columns depending on the state. So here is what I have

Description Example: Management EXECUTIVE CTJACKSONVILLE.FL.32216-4041

Function getState(description As String) As String
    Dim s() As String
    s = Split(description, ".")
    getState = s(UBound(s) - 1)
End Function

I want to change some columns depending on if it returns a correct State abbreviation or not. If the value does not return a valid state I want to flag the invoice. So something like this...

UPDATE tblInvoices
SET [Invoice Flag] = True
Where getState(description) <> ValidStates.Abbreviation

Something along those lines. Sorry for the newbie-ness. Any help is appreciated!

ashareef · Accepted Answer · 2013-06-27 14:36:56Z

2

I would create a function called isValidState() that returns a boolean

Function isValidState(st As String) As Boolean
    Select Case st
        Case "AL", "FL", "NY" ...
            isValidState = True
        Case Else
            isValidState = False
    End Select
End Function

Another way could be is to have a table with all the state codes (Let's call it MyStateTable)

Function isValidState(st As String) As Boolean
    Dim db As Database
    Dim rs As Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("MyStateTable")
    rs.FindFirst ("StateFieldName = '" & st & "'")
    If rs.NoMatch Then
        isValidState = False
    Else
        isValidState = True
    End If
    rs.Close
End Function

So your query will look something like

UPDATE tblInvoices
SET [Invoice Flag] = True
Where isValidState(getState(description)) = True

Edit:

Function getState(description As String) As String
    Dim s() As String
    s = Split(description, ".")
    If (UBound(s) > 1) then
        getState = s(UBound(s) - 1)
    Else
        getState = vbNullString 'Or change this to "" or something else that makes sense for your usage'
    End if
End Function

edited Jun 27, 2013 at 14:36

answered Jun 27, 2013 at 13:18

ashareef

1,84613 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

user2296381 Over a year ago

Sorry - so how would I put this in to the SQL Query where I need to change the Invoice Flag to True? I guess my question is, how do I combine running the function inside a SQL query?

user2296381 Over a year ago

Sorry, having one last problem: Since I haven't run a function - I'm not sure what needs to be quoted and such in Access. I keep getting an error: "Compile Error: Expected Expression"... Not sure where I need the quotes and & CurrentDb.Execute "UPDATE Processing " & _ "SET [Comments] = 'Review All PO Lines. Ship To State Cannot be Determined.', [Invoice Flag] = True" & _ "WHERE " & isValidState(getState(&"[Inv Description]"&))

user2296381 Over a year ago

Nevermind - Now I'm having a problem with isValidState. For rs.FindFirst ("StateFieldName = '" & st & "'") I am returning "Operation is not supported for this type of object"

ashareef Over a year ago

Think you need a space between the True and WHERE. A good way to check your SQL strings is to place a breakpoint on the line of code you want to check then in your Immediate Window (Ctrl+G) you can type in ?<your expression and you can see the result. E.g. ?1+1 will return 2, so in your case of SQL you'd have something like `?"UPDATE ......." & "..." etc

ashareef Over a year ago

Try Set rs = db.OpenRecordset("MyStateTable",dbOpenSnapshot) and if that doesn't work try Set rs = db.OpenRecordset("MyStateTable", dbOpenDynaset)

|

Collectives™ on Stack Overflow

VBA/SQL: How to build SQL with Function in WHERE clause?

1 Answer 1

9 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Related