Access Query

[Sarrkazztic]

Ok. I have a database and several tables and queries. I need to finish this off with either a make table query or an update query.

Right now my final query produces a table with the following columns

LDC Prospect Verify LDCAcct Billing Telephone Email Rate


In my Rate column I have codes such as

V139
VRC033
REN032
etc

Ok...here is where I hit the wall I need a query or Sql statment that will update the LDC column with the name of the utility. for example using wildcards as this has to work in the future on new rate codes

If "Rate Column" = V1** insert VDO in LDC column on the same row.

or IF Rate column = VRC*** insert COH in LDC column on same row

or if Rate column = REN*** insert DEO into LDC column on same row


Can anyone help with this code?

 

[PHV]

Brute force method:
UPDATE yourTable
SET LDC = IIf([Rate] Like 'V1*', 'VDO',
IIf([Rate] Like 'VRC*', 'COH',
IIf([Rate] Like 'REN*', 'DEO',
[LDC])))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Sarrkazztic]

Can I trouble you to tell me exactly where to put that code? I am fairly new to access but seem to be learning pretty quick.

 

[Sarrkazztic]

I am using Access 2002 on an XP machine.

 

[PHV]

The code I posted is the SQL code of an Update query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Sarrkazztic]

UPDATE BREM
SET LDC = IIf([Rate] Like 'V1*', 'VDO',
IIf([Rate] Like 'VRC*', 'COH',
IIf([Rate] Like 'REN*', 'DEO',
[LDC])))


had to figure a few things out...but I got it...had to add the UPDATE YOUR TABLE part...and change Your TABLE to the name of the table....

Works great....being able to automate this part makes my life much easier...

Thanks