Function help

[belsha]

Could someone tell me the code for a function that will look at Gender from the demographics table and update it in the table to a 0 if it = "Male" or a 1 if it = "Female". Thanks in advance.

 

[modest]

You could just do this with 2 queries... but here

Function ChangeGenderToNum()
    Dim strSQL As String                        'declare sql string variable

    
    strSQL = "UPDATE TableName SET TableName.[Column Name] = '0' " & _
             "WHERE (TableName.[Column Name]='male');"
    DoCmd.RunSQL strSQL                         'run the query
 
   
    strSQL = "UPDATE TableName SET TableName.[Column Name] = '1' " & _
             "WHERE (TableName.[Column Name]='female');"
    DoCmd.RunSQL strSQL                         'run the query
    
    
End Function

 

[Rich]

Why bother to change the table when a simple query or calculated control will return the value you want
ie Iif(YourField="Female",1,0)
if you insist on changing the table just use a simple update query

 

[modest]

You could just do this with 2 queries

if you insist on changing the table just use a simple update query

...hehe repost?

Belsha, Rich and I agree. A SELECT query could bring up your whole table, and you could change the output... w/o changing the data of the table. This is good for future circumstances where you might need "male/female"

Additionally, you should use the query-builder and call those queries... instead of putting it in code as I have. Regardless, my function will meet your needs as you had requested.

Call it with: ChangeGenderToNum from anywhere.

You should know that you only need to call it once though... because when you do it... it will change your table for good.