If then else

[dmcgimpsey]

this must be a simple question, and I am finding round-about ways to do what I want ...

is there a simple way to create a variable, and assign a value based on the value of another column?

for example: if source_code = 'a' then source_code_flag = 'y' ???

Thanks in advance

Don

 

[mdmilner]

Sure, this'd be basic material.

You've really got two basic methods. Second is designed for many options.

IF (condition_to_test) THEN
' what to do when it's true
ELSE
' What to do when it's false (optional)
END IF

Second is CASE

SELECT CASE (variable_value)
Case (value1):
Case (value2):
Case (value3):
Case Else: ' All other conditions not explictly named
END SELECT

And you can obviously mix and put IF conditions inside a SELECT CASE or vice versa.

Now, you mentioned columns. We're not Excel here but the principle is the same as the following.

Sub SetFlaG()
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set dbs = CurrentDb()   ' Assigns currently open file as database

strSQL = "SELECT * FROM tblName"        'defines recordset to open
Set rs = dbs.OpenRecordset(strSQL, dbOpenSnapShot)  'opens

With rs
  Do Until rs.EOF           ' Walk through ALL records until you find end of file
    IF !source_code = "a" THEN     'test fieldname source_code for value
      !source_code_flag = "y"        'set fieldname source_code_flag if true
    ELSE
      !source_code_flag = "n"       ' if false
    END IF
    .MoveNext
  Loop
End With

Set rs = nothing
Set dbs = nothing
End Sub

The above is a little grander than you requested, but would be an example of how to walk through an entire recordset and use a simple if...then test to set the values of a specific field.

And in SQL it would be briefer.

Sub SetFlaG()
Dim strSQL As String

strSQL = "UPDATE tblName SET [source_code_flag] = 'y' "
strSQL = strSQL & "WHERE [source_code] = 'a'"
DoCmd.RunSQL strSQL

End Sub

Optionally, you can embed IIF (very similar to if then else structure but works within SQL queries) - works similarly to excel =IF(a,b,c) functions.

<edit and an insert>

Here's a thought. Based on the last SQL version of the editor.
You can try this as a quick way to create a querydefs object (a query listed under the query tab) to look at. If the queryname exists already, this will cause an error message - change the name to be created. Assuming that your fieldnames are correct and you change the tablename should generate a query.

Sub SetFlaG()
Dim qdf As QueryDef
Dim strSQL As String

strSQL = "UPDATE tblName SET [source_code_flag] = 'y' "
strSQL = strSQL & "WHERE [source_code] = 'a'"

Set qdf = dbs.CreateQueryDef("NameofyourQuery", strSQL)

End Sub

Mike

 

[dmcgimpsey]

Thanks very much Mike, the Select statement meets my needs.

I appreciate the time you took to go into such great detail, people such as yourself truly make this forum a great resource.

Thanks again

Don