Creating an update using "if, then, else" conditions

[Guest]

-Using Access 2000-

I have a customer who wants to update their database (which is the default
db for a program called OpenTrackPlus, so all of the field names are
required to
remain the same)from a Comma-Delimited txt file from an old DBase II
program. Needless to say the field names and data do not match on either side.
The txt file contains 2 fields "Unit Address" (which is the address of a
homeowner in a community), and "Paid" (which has 5 variables, one is Paid and
the others mean that they have not paid). I've imported the txt file into the
db and named it Pools (because that's what this is all about, pool access).
The table in the database has many fields, so I created a query to draw out
the fields "Address1" (which contains multiples of the same address based on
the number of people living in the household) and "Member_Auth" (which states
Active or Declined)
Now, if a household has not paid and has one of the other "Paid" status',
then the "Member_Auth" needs to say Declined, and yes this would affect the
entire
household.
So, what I am looking for is how to write a module using the following:

If "Paid" (from table "Pools") is equal to Paid
Then "Member_Auth" (from "Tbl_Members") says Active
Else "Member_Auth" says Declined

As you can see, I have the condition, but I am lost on the rest of the code.
Can someone help me?!?!

 

[Arvin Meyer [MVP]]

In your query add a column:

Member_AuthNew: IIf([Pools].[Paid] = "Paid", "Active", "Declined")

That will give you a new column with the correct data. Now you need to use
an Update query with the 2 tables linked (probably on MemberID) to Update
the Member_Auth field with the data in Member_AuthNew

 

[Guest]

Is the Member_AuthNew the field name and the IIf condition the field data?

-Daniel Horowitz

In your query add a column:

Member_AuthNew: IIf([Pools].[Paid] = "Paid", "Active", "Declined")

That will give you a new column with the correct data. Now you need to use
an Update query with the 2 tables linked (probably on MemberID) to Update
the Member_Auth field with the data in Member_AuthNew

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

-Using Access 2000-

I have a customer who wants to update their database (which is the default
db for a program called OpenTrackPlus, so all of the field names are
required to
remain the same)from a Comma-Delimited txt file from an old DBase II
program. Needless to say the field names and data do not match on either
side.
The txt file contains 2 fields "Unit Address" (which is the address of a
homeowner in a community), and "Paid" (which has 5 variables, one is Paid
and
the others mean that they have not paid). I've imported the txt file into
the
db and named it Pools (because that's what this is all about, pool
access).
The table in the database has many fields, so I created a query to draw
out
the fields "Address1" (which contains multiples of the same address based
on
the number of people living in the household) and "Member_Auth" (which
states
Active or Declined)
Now, if a household has not paid and has one of the other "Paid" status',
then the "Member_Auth" needs to say Declined, and yes this would affect
the
entire
household.
So, what I am looking for is how to write a module using the following:

If "Paid" (from table "Pools") is equal to Paid
Then "Member_Auth" (from "Tbl_Members") says Active
Else "Member_Auth" says Declined

As you can see, I have the condition, but I am lost on the rest of the
code.
Can someone help me?!?!

 

[Arvin Meyer [MVP]]

Member_AuthNew is an alias column (a calculated column that only exists in
the query) The Immediate If (IIf) function is an expression that returns the
data that you wish. If you can be satisfied with the query showing in a form
and/or report, no further work needs to be done. If you need to store the
value, you'll need to build an Update query which will allow you to insert
the data into the table, either as a replacement for existing data, or as a
new field.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Is the Member_AuthNew the field name and the IIf condition the field data?

-Daniel Horowitz

In your query add a column:

Member_AuthNew: IIf([Pools].[Paid] = "Paid", "Active", "Declined")

That will give you a new column with the correct data. Now you need to
use
an Update query with the 2 tables linked (probably on MemberID) to Update
the Member_Auth field with the data in Member_AuthNew

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

-Using Access 2000-

I have a customer who wants to update their database (which is the
default
db for a program called OpenTrackPlus, so all of the field names are
required to
remain the same)from a Comma-Delimited txt file from an old DBase II
program. Needless to say the field names and data do not match on
either
side.
The txt file contains 2 fields "Unit Address" (which is the address of
a
homeowner in a community), and "Paid" (which has 5 variables, one is
Paid
and
the others mean that they have not paid). I've imported the txt file
into
the
db and named it Pools (because that's what this is all about, pool
access).
The table in the database has many fields, so I created a query to draw
out
the fields "Address1" (which contains multiples of the same address
based
on
the number of people living in the household) and "Member_Auth" (which
states
Active or Declined)
Now, if a household has not paid and has one of the other "Paid"
status',
then the "Member_Auth" needs to say Declined, and yes this would affect
the
entire
household.
So, what I am looking for is how to write a module using the following:

If "Paid" (from table "Pools") is equal to Paid
Then "Member_Auth" (from "Tbl_Members") says Active
Else "Member_Auth" says Declined

As you can see, I have the condition, but I am lost on the rest of the
code.
Can someone help me?!?!