Can you use IF/THEN/ELSE in an Update Query? 1

[superfcbear]

I know this is at someone's fingertips....

Can I use and if/then/else in an update query? Or SQL code??

I would've used iif but then I have more than 2 conditions.

I need something like this:


if [Table1]![field1] = "y" then [Table2]![field1]=1
else if [Table1]![field1] = "n" then [Table2]![field1]=0
else [Table1]![field1] = null


THANK YOU SO MUCH!!!!!

 

[culshaja]

I would try IIF instead as that works in select queries.

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[MichaelRed]

superfcbear,

There are (at least) two approaches to accomplish this:

A)[tab]place you standard If statement structure in a code module. This requires you to repllace all of the Table!Column variable necessary with ordinary variable names, and place the table!column names in the call to the function. then, You 'call' the function from the query.

Function VarVal(Tbl1Fld1 as String) as Integer

If (Tbl1Fld1 = "y&quot Then
VarVal = 1
ElseIf (tbl1Fld1 = "n&quot then
VarVal = 0
Else
VarVal = "" 'This may need to be changed?
End If
End Function

if [Table1]![field1] = "y" then [Table2]![field1]=1
else if [Table1]![field1] = "n" then [Table2]![field1]=0
else [Table1]![field1] = null
B)[tab] Nest the IIF's within the query.

Field1: IIF([table1]![Field1] = "y", 1, IIF([table1]![field1] = "n", 0))

For this approach, there is probably a syntax error - I always miscount the Parens, and I did NOT run this in a query.

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[superfcbear]

Michael,

Another really simple question......

in my "Function VarVal(Tbl1Fld1 As String) As Integer" statment, how do I declare my tbl1fld1 ? is it

tbl1.fld1
or
[tbl1]![fld1]


THanks Michael

-superfcbear

 

[culshaja]

[tbl1].[fld1] is how I usually do things

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[superfcbear]

I am feeling really stupid........

OK. I after I did my functions, HOW DO I CALL IT in my update query?

I have two tables that I've joined. And do I put it in the "update to" box? and just call it "VarVal()" (per Michael's example)? Apparently that's not right 'coz it said that I had the wrong argument....

Or do I need to just run the code from MODULE? if so, how do I specify the joins of my tables?

THANKS A MILLION!!!!!!!!!!

 

[culshaja]

is the set statement you would use:

set colA = function([colb])

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[MichaelRed]

in the query (using hte grid as the reference point for discussion), Drag [field1] from the [table2] listbox to a column (cell) in the query (topRow). in the UpdateTo row, type VarVal([table1]![Field1]). This, of course, actually changes table1!field1, but that is what your question suggested.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[culshaja]

Sorry for confusing things! I am used to using straight SQL ojn SQL Server and Oracle and forget about Accesses query builder.

(a sheepish..) James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[MichaelRed]

James,

Either is acceptable, I just used the query builder as, in general, I find Ms. Access Users more 'comfortable' w/ the query builder. I even find myself using it (to much) after being immersed in Ms. Access for a while.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over