table field's value based on value of another field same table

[aonefun]

I would like to set the value of an Access database table field to "Yes" if another field in the same record (of the same table) contains the value "invoice".

Can I insert If statements into the SQL table view?
If yes, what is the proper syntax to achieve this result?
If not, what is the solution. (When responding please bear in mind that I have a very limited programming knowledge)

 

[Remou]

I cannot see why you would wish to do this. A query will allow you to show 'invoice' as 'yes' where ever you wish, without having an additional field. You may wish to read:

http://www.r937.com/relational.html

 

[aonefun]

I would like to exclude any customer that has an invoice in our system from a fax campaign. If the docstatus="invoice" then I would like faxremove to equal"yes

 

[Remou]

You do not need an additional field, you need a query. You can build the query using the query design window. In SQL view, it will look something like:

[tt]SELECT Forename, Surname, Address, Fax
FROM tblCustomers
WHERE docstatus<>"Invoice" OR docstatus Is Null[/tt]

 

[PHV]

In the SQL view pane:
UPDATE [your table name here]
SET faxremove='yes'
WHERE docstatus='invoice'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886