Updating column based on varying criteria

I have 3 columns (taxable_ac, zone, levy) in a table named "temp".
I would like to update levy with taxable_ac*10 where zone=1 and 
taxable_ac*20 where zone=2. I would prefer to avoid using multiple imbedded 
if-statements if at all possible. Here is where I got stuck.

UPDATE temp SET temp.levy = taxable_ac*"10"
WHERE temp.zone="1";

I tried the following, but it obviously doesn't work. It should give you an 
idea of what i'm trying to do though.

UPDATE temp SET temp.levy = taxable_ac*"10"
WHERE temp.zone="1" AND
UPDATE temp SET temp.levy = taxable_ac*"20"
WHERE temp.zone="2";

I just started using Access yesterday and am trying to get the hang of it. 
Thanks for your help.


0
Utf
6/14/2007 12:23:00 AM
access.queries 6343 articles. 1 followers. Follow

14 Replies
722 Views

Similar Articles

[PageSpeed] 7

Rob,

You will need to write a function and write the query as below

update temp set temp.levy = GetTaxable_ac(taxable_ac,temp.zone)

In the VBA editor you will need to add a module like below:

Public Function GetTaxable_ac(ByVal Taxable_ac As Double, ByVal Zone As 
String) As Double

     Select Case Zone
       Case "1"
           GetTaxable_ac = Taxable_ac * 10
       Case "2"
           GetTaxable_ac = Taxable_ac * 20
       Case Else
            GetTaxable_ac = 0
     End Select
End Function

I hope this helps.  

Wade


> I have 3 columns (taxable_ac, zone, levy) in a table named "temp".
> I would like to update levy with taxable_ac*10 where zone=1 and 
> taxable_ac*20 where zone=2. I would prefer to avoid using multiple imbedded 
> if-statements if at all possible. Here is where I got stuck.
> 
> UPDATE temp SET temp.levy = taxable_ac*"10"
> WHERE temp.zone="1";
> 
> I tried the following, but it obviously doesn't work. It should give you an 
> idea of what i'm trying to do though.
> 
> UPDATE temp SET temp.levy = taxable_ac*"10"
> WHERE temp.zone="1" AND
> UPDATE temp SET temp.levy = taxable_ac*"20"
> WHERE temp.zone="2";
> 
> I just started using Access yesterday and am trying to get the hang of it. 
> Thanks for your help.
> 
> 
0
Utf
6/14/2007 12:38:23 PM
If your are multiplying by the Zone value then the following might work

UPDATE Temp
SET Levy = [Temp].[Taxable_AC] * 10 * [Temp].[Zone]
WHERE Zone is Not Null

If  Zone value is not a one to one correspondence with the multiplier, then 
I would suggest adding a table that has the zone and the multiplier for that 
zone and using that in the update query.

UPDATE Temp INNER JOIN tableZoneValue
ON Temp.Zone = tableZoneValue.Zone
SET Temp.Levy = [Temp].[Taxable_AC] * [tableZoneValue].[Multiplier]

-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Rob" <Rob@discussions.microsoft.com> wrote in message 
news:821BDC86-37CA-4E66-AEBB-1B306B6F7D8B@microsoft.com...
>I have 3 columns (taxable_ac, zone, levy) in a table named "temp".
> I would like to update levy with taxable_ac*10 where zone=1 and
> taxable_ac*20 where zone=2. I would prefer to avoid using multiple 
> imbedded
> if-statements if at all possible. Here is where I got stuck.
>
> UPDATE temp SET temp.levy = taxable_ac*"10"
> WHERE temp.zone="1";
>
> I tried the following, but it obviously doesn't work. It should give you 
> an
> idea of what i'm trying to do though.
>
> UPDATE temp SET temp.levy = taxable_ac*"10"
> WHERE temp.zone="1" AND
> UPDATE temp SET temp.levy = taxable_ac*"20"
> WHERE temp.zone="2";
>
> I just started using Access yesterday and am trying to get the hang of it.
> Thanks for your help.
>
> 


0
John
6/14/2007 2:45:24 PM
I am trying to do something similar.  I have a spreadsheet that I import, but 
I would like to change the data in one of the fields and update it to 
another.  I've modified the function you created, but it isn't working.    
What it is supposed to do is check the string in the field "OldReligion", and 
based on the word in the Case statment, update the field "Religion" to 
something else.  Here's my code:

Public Function GetReligion(ByVal OldReligion As String) As String

     Select Case OldReligion
       Case "*catholic*"
           GetReligion = "Roman Catholic"
       Case "*baptist*"
           GetReligion = "Baptist"
       Case "*preference*"
           GetReligion = "No Preference"
       Case "*lutheran*"
           GetReligion = "Lutheran"
       Case Else
           GetReligion = "0"
     End Select
End Function

"Wade" wrote:

> Rob,
> 
> You will need to write a function and write the query as below
> 
> update temp set temp.levy = GetTaxable_ac(taxable_ac,temp.zone)
> 
> In the VBA editor you will need to add a module like below:
> 
> Public Function GetTaxable_ac(ByVal Taxable_ac As Double, ByVal Zone As 
> String) As Double
> 
>      Select Case Zone
>        Case "1"
>            GetTaxable_ac = Taxable_ac * 10
>        Case "2"
>            GetTaxable_ac = Taxable_ac * 20
>        Case Else
>             GetTaxable_ac = 0
>      End Select
> End Function
> 
> I hope this helps.  
> 
> Wade
> 
> 
> > I have 3 columns (taxable_ac, zone, levy) in a table named "temp".
> > I would like to update levy with taxable_ac*10 where zone=1 and 
> > taxable_ac*20 where zone=2. I would prefer to avoid using multiple imbedded 
> > if-statements if at all possible. Here is where I got stuck.
> > 
> > UPDATE temp SET temp.levy = taxable_ac*"10"
> > WHERE temp.zone="1";
> > 
> > I tried the following, but it obviously doesn't work. It should give you an 
> > idea of what i'm trying to do though.
> > 
> > UPDATE temp SET temp.levy = taxable_ac*"10"
> > WHERE temp.zone="1" AND
> > UPDATE temp SET temp.levy = taxable_ac*"20"
> > WHERE temp.zone="2";
> > 
> > I just started using Access yesterday and am trying to get the hang of it. 
> > Thanks for your help.
> > 
> > 
0
Utf
6/14/2007 2:47:02 PM
What are you getting for a result (or error)?  Seeing your code would be 
helpful in determining the problem.  Thanks.

Wade

"Gntlhnds" wrote:

> I am trying to do something similar.  I have a spreadsheet that I import, but 
> I would like to change the data in one of the fields and update it to 
> another.  I've modified the function you created, but it isn't working.    
> What it is supposed to do is check the string in the field "OldReligion", and 
> based on the word in the Case statment, update the field "Religion" to 
> something else.  Here's my code:
> 
> Public Function GetReligion(ByVal OldReligion As String) As String
> 
>      Select Case OldReligion
>        Case "*catholic*"
>            GetReligion = "Roman Catholic"
>        Case "*baptist*"
>            GetReligion = "Baptist"
>        Case "*preference*"
>            GetReligion = "No Preference"
>        Case "*lutheran*"
>            GetReligion = "Lutheran"
>        Case Else
>            GetReligion = "0"
>      End Select
> End Function
> 
> "Wade" wrote:
> 
> > Rob,
> > 
> > You will need to write a function and write the query as below
> > 
> > update temp set temp.levy = GetTaxable_ac(taxable_ac,temp.zone)
> > 
> > In the VBA editor you will need to add a module like below:
> > 
> > Public Function GetTaxable_ac(ByVal Taxable_ac As Double, ByVal Zone As 
> > String) As Double
> > 
> >      Select Case Zone
> >        Case "1"
> >            GetTaxable_ac = Taxable_ac * 10
> >        Case "2"
> >            GetTaxable_ac = Taxable_ac * 20
> >        Case Else
> >             GetTaxable_ac = 0
> >      End Select
> > End Function
> > 
> > I hope this helps.  
> > 
> > Wade
> > 
> > 
> > > I have 3 columns (taxable_ac, zone, levy) in a table named "temp".
> > > I would like to update levy with taxable_ac*10 where zone=1 and 
> > > taxable_ac*20 where zone=2. I would prefer to avoid using multiple imbedded 
> > > if-statements if at all possible. Here is where I got stuck.
> > > 
> > > UPDATE temp SET temp.levy = taxable_ac*"10"
> > > WHERE temp.zone="1";
> > > 
> > > I tried the following, but it obviously doesn't work. It should give you an 
> > > idea of what i'm trying to do though.
> > > 
> > > UPDATE temp SET temp.levy = taxable_ac*"10"
> > > WHERE temp.zone="1" AND
> > > UPDATE temp SET temp.levy = taxable_ac*"20"
> > > WHERE temp.zone="2";
> > > 
> > > I just started using Access yesterday and am trying to get the hang of it. 
> > > Thanks for your help.
> > > 
> > > 
0
Utf
6/14/2007 3:25:01 PM
Instead of using a function I would build a translation table with two 
columns PreferredValue and CurrentValue and then use that table in an update 
query to fix all the values in the imported table.

But if you use the function - I believe you cannot use Select Case with 
wildcards.

Public Function GetReligion(ByVal OldReligion As String) As String

     IF OldReligion Like "*catholic*" Then
           GetReligion = "Roman Catholic"
       Elseif OldReligion Like "*baptist*" Then
           GetReligion = "Baptist"
       Elseif OldReligion Like "*preference*" Then
           GetReligion = "No Preference"
       Elseif OldReligion Like "*lutheran*" Then
           GetReligion = "Lutheran"
       Else
           GetReligion = "0"
     End if
End Function

-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Wade" <Wade@discussions.microsoft.com> wrote in message 
news:397CBD65-8FFF-40E7-BDAC-C9F4BA1891A5@microsoft.com...
> What are you getting for a result (or error)?  Seeing your code would be
> helpful in determining the problem.  Thanks.
>
> Wade
>
> "Gntlhnds" wrote:
>
>> I am trying to do something similar.  I have a spreadsheet that I import, 
>> but
>> I would like to change the data in one of the fields and update it to
>> another.  I've modified the function you created, but it isn't working.
>> What it is supposed to do is check the string in the field "OldReligion", 
>> and
>> based on the word in the Case statment, update the field "Religion" to
>> something else.  Here's my code:
>>
>> Public Function GetReligion(ByVal OldReligion As String) As String
>>
>>      Select Case OldReligion
>>        Case "*catholic*"
>>            GetReligion = "Roman Catholic"
>>        Case "*baptist*"
>>            GetReligion = "Baptist"
>>        Case "*preference*"
>>            GetReligion = "No Preference"
>>        Case "*lutheran*"
>>            GetReligion = "Lutheran"
>>        Case Else
>>            GetReligion = "0"
>>      End Select
>> End Function
>>
>> "Wade" wrote:
>>
>> > Rob,
>> >
>> > You will need to write a function and write the query as below
>> >
>> > update temp set temp.levy = GetTaxable_ac(taxable_ac,temp.zone)
>> >
>> > In the VBA editor you will need to add a module like below:
>> >
>> > Public Function GetTaxable_ac(ByVal Taxable_ac As Double, ByVal Zone As
>> > String) As Double
>> >
>> >      Select Case Zone
>> >        Case "1"
>> >            GetTaxable_ac = Taxable_ac * 10
>> >        Case "2"
>> >            GetTaxable_ac = Taxable_ac * 20
>> >        Case Else
>> >             GetTaxable_ac = 0
>> >      End Select
>> > End Function
>> >
>> > I hope this helps.
>> >
>> > Wade
>> >
>> >
>> > > I have 3 columns (taxable_ac, zone, levy) in a table named "temp".
>> > > I would like to update levy with taxable_ac*10 where zone=1 and
>> > > taxable_ac*20 where zone=2. I would prefer to avoid using multiple 
>> > > imbedded
>> > > if-statements if at all possible. Here is where I got stuck.
>> > >
>> > > UPDATE temp SET temp.levy = taxable_ac*"10"
>> > > WHERE temp.zone="1";
>> > >
>> > > I tried the following, but it obviously doesn't work. It should give 
>> > > you an
>> > > idea of what i'm trying to do though.
>> > >
>> > > UPDATE temp SET temp.levy = taxable_ac*"10"
>> > > WHERE temp.zone="1" AND
>> > > UPDATE temp SET temp.levy = taxable_ac*"20"
>> > > WHERE temp.zone="2";
>> > >
>> > > I just started using Access yesterday and am trying to get the hang 
>> > > of it.
>> > > Thanks for your help.
>> > >
>> > > 


0
John
6/14/2007 4:11:23 PM
John,

First, you definitely can't use wildcards in a select statement.  As for 
using a join to set the value in the update statment, it is a matter of 
taste.  I like the functions to allow me to use more complex calculations, 
but for what the original poster wanted, your solution is much better.

Wade

"John Spencer" wrote:

> Instead of using a function I would build a translation table with two 
> columns PreferredValue and CurrentValue and then use that table in an update 
> query to fix all the values in the imported table.
> 
> But if you use the function - I believe you cannot use Select Case with 
> wildcards.
> 
> Public Function GetReligion(ByVal OldReligion As String) As String
> 
>      IF OldReligion Like "*catholic*" Then
>            GetReligion = "Roman Catholic"
>        Elseif OldReligion Like "*baptist*" Then
>            GetReligion = "Baptist"
>        Elseif OldReligion Like "*preference*" Then
>            GetReligion = "No Preference"
>        Elseif OldReligion Like "*lutheran*" Then
>            GetReligion = "Lutheran"
>        Else
>            GetReligion = "0"
>      End if
> End Function
> 
> -- 
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> ..
> 
> "Wade" <Wade@discussions.microsoft.com> wrote in message 
> news:397CBD65-8FFF-40E7-BDAC-C9F4BA1891A5@microsoft.com...
> > What are you getting for a result (or error)?  Seeing your code would be
> > helpful in determining the problem.  Thanks.
> >
> > Wade
> >
> > "Gntlhnds" wrote:
> >
> >> I am trying to do something similar.  I have a spreadsheet that I import, 
> >> but
> >> I would like to change the data in one of the fields and update it to
> >> another.  I've modified the function you created, but it isn't working.
> >> What it is supposed to do is check the string in the field "OldReligion", 
> >> and
> >> based on the word in the Case statment, update the field "Religion" to
> >> something else.  Here's my code:
> >>
> >> Public Function GetReligion(ByVal OldReligion As String) As String
> >>
> >>      Select Case OldReligion
> >>        Case "*catholic*"
> >>            GetReligion = "Roman Catholic"
> >>        Case "*baptist*"
> >>            GetReligion = "Baptist"
> >>        Case "*preference*"
> >>            GetReligion = "No Preference"
> >>        Case "*lutheran*"
> >>            GetReligion = "Lutheran"
> >>        Case Else
> >>            GetReligion = "0"
> >>      End Select
> >> End Function
> >>
> >> "Wade" wrote:
> >>
> >> > Rob,
> >> >
> >> > You will need to write a function and write the query as below
> >> >
> >> > update temp set temp.levy = GetTaxable_ac(taxable_ac,temp.zone)
> >> >
> >> > In the VBA editor you will need to add a module like below:
> >> >
> >> > Public Function GetTaxable_ac(ByVal Taxable_ac As Double, ByVal Zone As
> >> > String) As Double
> >> >
> >> >      Select Case Zone
> >> >        Case "1"
> >> >            GetTaxable_ac = Taxable_ac * 10
> >> >        Case "2"
> >> >            GetTaxable_ac = Taxable_ac * 20
> >> >        Case Else
> >> >             GetTaxable_ac = 0
> >> >      End Select
> >> > End Function
> >> >
> >> > I hope this helps.
> >> >
> >> > Wade
> >> >
> >> >
> >> > > I have 3 columns (taxable_ac, zone, levy) in a table named "temp".
> >> > > I would like to update levy with taxable_ac*10 where zone=1 and
> >> > > taxable_ac*20 where zone=2. I would prefer to avoid using multiple 
> >> > > imbedded
> >> > > if-statements if at all possible. Here is where I got stuck.
> >> > >
> >> > > UPDATE temp SET temp.levy = taxable_ac*"10"
> >> > > WHERE temp.zone="1";
> >> > >
> >> > > I tried the following, but it obviously doesn't work. It should give 
> >> > > you an
> >> > > idea of what i'm trying to do though.
> >> > >
> >> > > UPDATE temp SET temp.levy = taxable_ac*"10"
> >> > > WHERE temp.zone="1" AND
> >> > > UPDATE temp SET temp.levy = taxable_ac*"20"
> >> > > WHERE temp.zone="2";
> >> > >
> >> > > I just started using Access yesterday and am trying to get the hang 
> >> > > of it.
> >> > > Thanks for your help.
> >> > >
> >> > > 
> 
> 
> 
0
Utf
6/14/2007 4:47:01 PM
I get a zero in the field as a result.  But since John said that wildcards 
don't work, then I need to find some other way to do what I want.  John 
mentioned translation tables.  I don't know if that will work for me or not, 
but I am going to look into it.

"Wade" wrote:

> What are you getting for a result (or error)?  Seeing your code would be 
> helpful in determining the problem.  Thanks.
> 
> Wade
> 
> "Gntlhnds" wrote:
> 
> > I am trying to do something similar.  I have a spreadsheet that I import, but 
> > I would like to change the data in one of the fields and update it to 
> > another.  I've modified the function you created, but it isn't working.    
> > What it is supposed to do is check the string in the field "OldReligion", and 
> > based on the word in the Case statment, update the field "Religion" to 
> > something else.  Here's my code:
> > 
> > Public Function GetReligion(ByVal OldReligion As String) As String
> > 
> >      Select Case OldReligion
> >        Case "*catholic*"
> >            GetReligion = "Roman Catholic"
> >        Case "*baptist*"
> >            GetReligion = "Baptist"
> >        Case "*preference*"
> >            GetReligion = "No Preference"
> >        Case "*lutheran*"
> >            GetReligion = "Lutheran"
> >        Case Else
> >            GetReligion = "0"
> >      End Select
> > End Function
> > 
> > "Wade" wrote:
> > 
> > > Rob,
> > > 
> > > You will need to write a function and write the query as below
> > > 
> > > update temp set temp.levy = GetTaxable_ac(taxable_ac,temp.zone)
> > > 
> > > In the VBA editor you will need to add a module like below:
> > > 
> > > Public Function GetTaxable_ac(ByVal Taxable_ac As Double, ByVal Zone As 
> > > String) As Double
> > > 
> > >      Select Case Zone
> > >        Case "1"
> > >            GetTaxable_ac = Taxable_ac * 10
> > >        Case "2"
> > >            GetTaxable_ac = Taxable_ac * 20
> > >        Case Else
> > >             GetTaxable_ac = 0
> > >      End Select
> > > End Function
> > > 
> > > I hope this helps.  
> > > 
> > > Wade
> > > 
> > > 
> > > > I have 3 columns (taxable_ac, zone, levy) in a table named "temp".
> > > > I would like to update levy with taxable_ac*10 where zone=1 and 
> > > > taxable_ac*20 where zone=2. I would prefer to avoid using multiple imbedded 
> > > > if-statements if at all possible. Here is where I got stuck.
> > > > 
> > > > UPDATE temp SET temp.levy = taxable_ac*"10"
> > > > WHERE temp.zone="1";
> > > > 
> > > > I tried the following, but it obviously doesn't work. It should give you an 
> > > > idea of what i'm trying to do though.
> > > > 
> > > > UPDATE temp SET temp.levy = taxable_ac*"10"
> > > > WHERE temp.zone="1" AND
> > > > UPDATE temp SET temp.levy = taxable_ac*"20"
> > > > WHERE temp.zone="2";
> > > > 
> > > > I just started using Access yesterday and am trying to get the hang of it. 
> > > > Thanks for your help.
> > > > 
> > > > 
0
Utf
6/14/2007 6:37:02 PM
Well there are other ways to do this.  You can use string manipulation in a 
function or a like statment in SQL ( OldReligion like '%catholic%' ).  It 
really depends on how you are doing things.

"Gntlhnds" wrote:

> I get a zero in the field as a result.  But since John said that wildcards 
> don't work, then I need to find some other way to do what I want.  John 
> mentioned translation tables.  I don't know if that will work for me or not, 
> but I am going to look into it.
> 
> "Wade" wrote:
> 
> > What are you getting for a result (or error)?  Seeing your code would be 
> > helpful in determining the problem.  Thanks.
> > 
> > Wade
> > 
> > "Gntlhnds" wrote:
> > 
> > > I am trying to do something similar.  I have a spreadsheet that I import, but 
> > > I would like to change the data in one of the fields and update it to 
> > > another.  I've modified the function you created, but it isn't working.    
> > > What it is supposed to do is check the string in the field "OldReligion", and 
> > > based on the word in the Case statment, update the field "Religion" to 
> > > something else.  Here's my code:
> > > 
> > > Public Function GetReligion(ByVal OldReligion As String) As String
> > > 
> > >      Select Case OldReligion
> > >        Case "*catholic*"
> > >            GetReligion = "Roman Catholic"
> > >        Case "*baptist*"
> > >            GetReligion = "Baptist"
> > >        Case "*preference*"
> > >            GetReligion = "No Preference"
> > >        Case "*lutheran*"
> > >            GetReligion = "Lutheran"
> > >        Case Else
> > >            GetReligion = "0"
> > >      End Select
> > > End Function
> > > 
> > > "Wade" wrote:
> > > 
> > > > Rob,
> > > > 
> > > > You will need to write a function and write the query as below
> > > > 
> > > > update temp set temp.levy = GetTaxable_ac(taxable_ac,temp.zone)
> > > > 
> > > > In the VBA editor you will need to add a module like below:
> > > > 
> > > > Public Function GetTaxable_ac(ByVal Taxable_ac As Double, ByVal Zone As 
> > > > String) As Double
> > > > 
> > > >      Select Case Zone
> > > >        Case "1"
> > > >            GetTaxable_ac = Taxable_ac * 10
> > > >        Case "2"
> > > >            GetTaxable_ac = Taxable_ac * 20
> > > >        Case Else
> > > >             GetTaxable_ac = 0
> > > >      End Select
> > > > End Function
> > > > 
> > > > I hope this helps.  
> > > > 
> > > > Wade
> > > > 
> > > > 
> > > > > I have 3 columns (taxable_ac, zone, levy) in a table named "temp".
> > > > > I would like to update levy with taxable_ac*10 where zone=1 and 
> > > > > taxable_ac*20 where zone=2. I would prefer to avoid using multiple imbedded 
> > > > > if-statements if at all possible. Here is where I got stuck.
> > > > > 
> > > > > UPDATE temp SET temp.levy = taxable_ac*"10"
> > > > > WHERE temp.zone="1";
> > > > > 
> > > > > I tried the following, but it obviously doesn't work. It should give you an 
> > > > > idea of what i'm trying to do though.
> > > > > 
> > > > > UPDATE temp SET temp.levy = taxable_ac*"10"
> > > > > WHERE temp.zone="1" AND
> > > > > UPDATE temp SET temp.levy = taxable_ac*"20"
> > > > > WHERE temp.zone="2";
> > > > > 
> > > > > I just started using Access yesterday and am trying to get the hang of it. 
> > > > > Thanks for your help.
> > > > > 
> > > > > 
0
Utf
6/14/2007 6:43:01 PM
I figured out the Like statement in an update query, but unfortunately the 
field that contains the results can be one of 15 different results, and I 
can't figure out how to write the sql statement.  I had already posted my 
issue before, but then this post came up so I was trying to see if I could 
make work in my situation.  My original post has a more detailed explanation 
as to what I'm trying to do.  
http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.access.queries&mid=3d91e549-aca4-432e-afc0-a756b83c8265&sloc=en-us


"Wade" wrote:

> Well there are other ways to do this.  You can use string manipulation in a 
> function or a like statment in SQL ( OldReligion like '%catholic%' ).  It 
> really depends on how you are doing things.
> 
> "Gntlhnds" wrote:
> 
> > I get a zero in the field as a result.  But since John said that wildcards 
> > don't work, then I need to find some other way to do what I want.  John 
> > mentioned translation tables.  I don't know if that will work for me or not, 
> > but I am going to look into it.
> > 
> > "Wade" wrote:
> > 
> > > What are you getting for a result (or error)?  Seeing your code would be 
> > > helpful in determining the problem.  Thanks.
> > > 
> > > Wade
> > > 
> > > "Gntlhnds" wrote:
> > > 
> > > > I am trying to do something similar.  I have a spreadsheet that I import, but 
> > > > I would like to change the data in one of the fields and update it to 
> > > > another.  I've modified the function you created, but it isn't working.    
> > > > What it is supposed to do is check the string in the field "OldReligion", and 
> > > > based on the word in the Case statment, update the field "Religion" to 
> > > > something else.  Here's my code:
> > > > 
> > > > Public Function GetReligion(ByVal OldReligion As String) As String
> > > > 
> > > >      Select Case OldReligion
> > > >        Case "*catholic*"
> > > >            GetReligion = "Roman Catholic"
> > > >        Case "*baptist*"
> > > >            GetReligion = "Baptist"
> > > >        Case "*preference*"
> > > >            GetReligion = "No Preference"
> > > >        Case "*lutheran*"
> > > >            GetReligion = "Lutheran"
> > > >        Case Else
> > > >            GetReligion = "0"
> > > >      End Select
> > > > End Function
> > > > 
> > > > "Wade" wrote:
> > > > 
> > > > > Rob,
> > > > > 
> > > > > You will need to write a function and write the query as below
> > > > > 
> > > > > update temp set temp.levy = GetTaxable_ac(taxable_ac,temp.zone)
> > > > > 
> > > > > In the VBA editor you will need to add a module like below:
> > > > > 
> > > > > Public Function GetTaxable_ac(ByVal Taxable_ac As Double, ByVal Zone As 
> > > > > String) As Double
> > > > > 
> > > > >      Select Case Zone
> > > > >        Case "1"
> > > > >            GetTaxable_ac = Taxable_ac * 10
> > > > >        Case "2"
> > > > >            GetTaxable_ac = Taxable_ac * 20
> > > > >        Case Else
> > > > >             GetTaxable_ac = 0
> > > > >      End Select
> > > > > End Function
> > > > > 
> > > > > I hope this helps.  
> > > > > 
> > > > > Wade
> > > > > 
> > > > > 
> > > > > > I have 3 columns (taxable_ac, zone, levy) in a table named "temp".
> > > > > > I would like to update levy with taxable_ac*10 where zone=1 and 
> > > > > > taxable_ac*20 where zone=2. I would prefer to avoid using multiple imbedded 
> > > > > > if-statements if at all possible. Here is where I got stuck.
> > > > > > 
> > > > > > UPDATE temp SET temp.levy = taxable_ac*"10"
> > > > > > WHERE temp.zone="1";
> > > > > > 
> > > > > > I tried the following, but it obviously doesn't work. It should give you an 
> > > > > > idea of what i'm trying to do though.
> > > > > > 
> > > > > > UPDATE temp SET temp.levy = taxable_ac*"10"
> > > > > > WHERE temp.zone="1" AND
> > > > > > UPDATE temp SET temp.levy = taxable_ac*"20"
> > > > > > WHERE temp.zone="2";
> > > > > > 
> > > > > > I just started using Access yesterday and am trying to get the hang of it. 
> > > > > > Thanks for your help.
> > > > > > 
> > > > > > 
0
Utf
6/14/2007 6:53:07 PM
Well there are two things you can do.  one is to create the select statement 
in the function to be based on all of the 15 possible results as below:

select case OldReligion

case "Catholic","catholic", "Roman Catholic"

case "Anglican","Episcipalian"

so on and so forth

Now it will return the result you want.  This is a similar idea to the one 
offered dealing with a table.   The other is you will need to nest iif 
statements in the query, which is tedious, but it will allow you to use the 
like sql statement.

iiif(OldReligion like '%catholic%,'Roman Catholic',iif(OldReligion like 
'%baptist%','Baptist', etc...

Wade





"Gntlhnds" wrote:

> I figured out the Like statement in an update query, but unfortunately the 
> field that contains the results can be one of 15 different results, and I 
> can't figure out how to write the sql statement.  I had already posted my 
> issue before, but then this post came up so I was trying to see if I could 
> make work in my situation.  My original post has a more detailed explanation 
> as to what I'm trying to do.  
> http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.access.queries&mid=3d91e549-aca4-432e-afc0-a756b83c8265&sloc=en-us
> 
> 
> "Wade" wrote:
> 
> > Well there are other ways to do this.  You can use string manipulation in a 
> > function or a like statment in SQL ( OldReligion like '%catholic%' ).  It 
> > really depends on how you are doing things.
> > 
> > "Gntlhnds" wrote:
> > 
> > > I get a zero in the field as a result.  But since John said that wildcards 
> > > don't work, then I need to find some other way to do what I want.  John 
> > > mentioned translation tables.  I don't know if that will work for me or not, 
> > > but I am going to look into it.
> > > 
> > > "Wade" wrote:
> > > 
> > > > What are you getting for a result (or error)?  Seeing your code would be 
> > > > helpful in determining the problem.  Thanks.
> > > > 
> > > > Wade
> > > > 
> > > > "Gntlhnds" wrote:
> > > > 
> > > > > I am trying to do something similar.  I have a spreadsheet that I import, but 
> > > > > I would like to change the data in one of the fields and update it to 
> > > > > another.  I've modified the function you created, but it isn't working.    
> > > > > What it is supposed to do is check the string in the field "OldReligion", and 
> > > > > based on the word in the Case statment, update the field "Religion" to 
> > > > > something else.  Here's my code:
> > > > > 
> > > > > Public Function GetReligion(ByVal OldReligion As String) As String
> > > > > 
> > > > >      Select Case OldReligion
> > > > >        Case "*catholic*"
> > > > >            GetReligion = "Roman Catholic"
> > > > >        Case "*baptist*"
> > > > >            GetReligion = "Baptist"
> > > > >        Case "*preference*"
> > > > >            GetReligion = "No Preference"
> > > > >        Case "*lutheran*"
> > > > >            GetReligion = "Lutheran"
> > > > >        Case Else
> > > > >            GetReligion = "0"
> > > > >      End Select
> > > > > End Function
> > > > > 
> > > > > "Wade" wrote:
> > > > > 
> > > > > > Rob,
> > > > > > 
> > > > > > You will need to write a function and write the query as below
> > > > > > 
> > > > > > update temp set temp.levy = GetTaxable_ac(taxable_ac,temp.zone)
> > > > > > 
> > > > > > In the VBA editor you will need to add a module like below:
> > > > > > 
> > > > > > Public Function GetTaxable_ac(ByVal Taxable_ac As Double, ByVal Zone As 
> > > > > > String) As Double
> > > > > > 
> > > > > >      Select Case Zone
> > > > > >        Case "1"
> > > > > >            GetTaxable_ac = Taxable_ac * 10
> > > > > >        Case "2"
> > > > > >            GetTaxable_ac = Taxable_ac * 20
> > > > > >        Case Else
> > > > > >             GetTaxable_ac = 0
> > > > > >      End Select
> > > > > > End Function
> > > > > > 
> > > > > > I hope this helps.  
> > > > > > 
> > > > > > Wade
> > > > > > 
> > > > > > 
> > > > > > > I have 3 columns (taxable_ac, zone, levy) in a table named "temp".
> > > > > > > I would like to update levy with taxable_ac*10 where zone=1 and 
> > > > > > > taxable_ac*20 where zone=2. I would prefer to avoid using multiple imbedded 
> > > > > > > if-statements if at all possible. Here is where I got stuck.
> > > > > > > 
> > > > > > > UPDATE temp SET temp.levy = taxable_ac*"10"
> > > > > > > WHERE temp.zone="1";
> > > > > > > 
> > > > > > > I tried the following, but it obviously doesn't work. It should give you an 
> > > > > > > idea of what i'm trying to do though.
> > > > > > > 
> > > > > > > UPDATE temp SET temp.levy = taxable_ac*"10"
> > > > > > > WHERE temp.zone="1" AND
> > > > > > > UPDATE temp SET temp.levy = taxable_ac*"20"
> > > > > > > WHERE temp.zone="2";
> > > > > > > 
> > > > > > > I just started using Access yesterday and am trying to get the hang of it. 
> > > > > > > Thanks for your help.
> > > > > > > 
> > > > > > > 
0
Utf
6/15/2007 12:36:01 PM
I tried the IIF statement in the query:

UPDATE Contacts SET Contacts.Religion = IIf("OldReligion" Like 
'*catholic*','Roman Catholic',IIf(IIf("OldReligion" Like 
'*baptist*','Baptist'),IIf("OldReligion" Like '*lutheran*','Lutheran')));

but it didn't work.  I ended up with an empty field.  Now, contacts.religion 
is a combo box.  Could that be messing up my results?

"Wade" wrote:

> Well there are two things you can do.  one is to create the select statement 
> in the function to be based on all of the 15 possible results as below:
> 
> select case OldReligion
> 
> case "Catholic","catholic", "Roman Catholic"
> 
> case "Anglican","Episcipalian"
> 
> so on and so forth
> 
> Now it will return the result you want.  This is a similar idea to the one 
> offered dealing with a table.   The other is you will need to nest iif 
> statements in the query, which is tedious, but it will allow you to use the 
> like sql statement.
> 
> iiif(OldReligion like '%catholic%,'Roman Catholic',iif(OldReligion like 
> '%baptist%','Baptist', etc...
> 
> Wade
> 
> 
> 
> 
> 
> "Gntlhnds" wrote:
> 
> > I figured out the Like statement in an update query, but unfortunately the 
> > field that contains the results can be one of 15 different results, and I 
> > can't figure out how to write the sql statement.  I had already posted my 
> > issue before, but then this post came up so I was trying to see if I could 
> > make work in my situation.  My original post has a more detailed explanation 
> > as to what I'm trying to do.  
> > http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.access.queries&mid=3d91e549-aca4-432e-afc0-a756b83c8265&sloc=en-us
> > 
> > 
> > "Wade" wrote:
> > 
> > > Well there are other ways to do this.  You can use string manipulation in a 
> > > function or a like statment in SQL ( OldReligion like '%catholic%' ).  It 
> > > really depends on how you are doing things.
> > > 
> > > "Gntlhnds" wrote:
> > > 
> > > > I get a zero in the field as a result.  But since John said that wildcards 
> > > > don't work, then I need to find some other way to do what I want.  John 
> > > > mentioned translation tables.  I don't know if that will work for me or not, 
> > > > but I am going to look into it.
> > > > 
> > > > "Wade" wrote:
> > > > 
> > > > > What are you getting for a result (or error)?  Seeing your code would be 
> > > > > helpful in determining the problem.  Thanks.
> > > > > 
> > > > > Wade
> > > > > 
> > > > > "Gntlhnds" wrote:
> > > > > 
> > > > > > I am trying to do something similar.  I have a spreadsheet that I import, but 
> > > > > > I would like to change the data in one of the fields and update it to 
> > > > > > another.  I've modified the function you created, but it isn't working.    
> > > > > > What it is supposed to do is check the string in the field "OldReligion", and 
> > > > > > based on the word in the Case statment, update the field "Religion" to 
> > > > > > something else.  Here's my code:
> > > > > > 
> > > > > > Public Function GetReligion(ByVal OldReligion As String) As String
> > > > > > 
> > > > > >      Select Case OldReligion
> > > > > >        Case "*catholic*"
> > > > > >            GetReligion = "Roman Catholic"
> > > > > >        Case "*baptist*"
> > > > > >            GetReligion = "Baptist"
> > > > > >        Case "*preference*"
> > > > > >            GetReligion = "No Preference"
> > > > > >        Case "*lutheran*"
> > > > > >            GetReligion = "Lutheran"
> > > > > >        Case Else
> > > > > >            GetReligion = "0"
> > > > > >      End Select
> > > > > > End Function
> > > > > > 
> > > > > > "Wade" wrote:
> > > > > > 
> > > > > > > Rob,
> > > > > > > 
> > > > > > > You will need to write a function and write the query as below
> > > > > > > 
> > > > > > > update temp set temp.levy = GetTaxable_ac(taxable_ac,temp.zone)
> > > > > > > 
> > > > > > > In the VBA editor you will need to add a module like below:
> > > > > > > 
> > > > > > > Public Function GetTaxable_ac(ByVal Taxable_ac As Double, ByVal Zone As 
> > > > > > > String) As Double
> > > > > > > 
> > > > > > >      Select Case Zone
> > > > > > >        Case "1"
> > > > > > >            GetTaxable_ac = Taxable_ac * 10
> > > > > > >        Case "2"
> > > > > > >            GetTaxable_ac = Taxable_ac * 20
> > > > > > >        Case Else
> > > > > > >             GetTaxable_ac = 0
> > > > > > >      End Select
> > > > > > > End Function
> > > > > > > 
> > > > > > > I hope this helps.  
> > > > > > > 
> > > > > > > Wade
> > > > > > > 
> > > > > > > 
> > > > > > > > I have 3 columns (taxable_ac, zone, levy) in a table named "temp".
> > > > > > > > I would like to update levy with taxable_ac*10 where zone=1 and 
> > > > > > > > taxable_ac*20 where zone=2. I would prefer to avoid using multiple imbedded 
> > > > > > > > if-statements if at all possible. Here is where I got stuck.
> > > > > > > > 
> > > > > > > > UPDATE temp SET temp.levy = taxable_ac*"10"
> > > > > > > > WHERE temp.zone="1";
> > > > > > > > 
> > > > > > > > I tried the following, but it obviously doesn't work. It should give you an 
> > > > > > > > idea of what i'm trying to do though.
> > > > > > > > 
> > > > > > > > UPDATE temp SET temp.levy = taxable_ac*"10"
> > > > > > > > WHERE temp.zone="1" AND
> > > > > > > > UPDATE temp SET temp.levy = taxable_ac*"20"
> > > > > > > > WHERE temp.zone="2";
> > > > > > > > 
> > > > > > > > I just started using Access yesterday and am trying to get the hang of it. 
> > > > > > > > Thanks for your help.
> > > > > > > > 
> > > > > > > > 
0
Utf
6/20/2007 12:48:22 PM
First, I don't think you want to use double quotes around OldReligion.  The 
issue with the combobox depends, but right now I don't think it is the cause 
of the issue.

Wade

"Gntlhnds" wrote:

> I tried the IIF statement in the query:
> 
> UPDATE Contacts SET Contacts.Religion = IIf("OldReligion" Like 
> '*catholic*','Roman Catholic',IIf(IIf("OldReligion" Like 
> '*baptist*','Baptist'),IIf("OldReligion" Like '*lutheran*','Lutheran')));
> 
> but it didn't work.  I ended up with an empty field.  Now, contacts.religion 
> is a combo box.  Could that be messing up my results?
> 
> "Wade" wrote:
> 
> > Well there are two things you can do.  one is to create the select statement 
> > in the function to be based on all of the 15 possible results as below:
> > 
> > select case OldReligion
> > 
> > case "Catholic","catholic", "Roman Catholic"
> > 
> > case "Anglican","Episcipalian"
> > 
> > so on and so forth
> > 
> > Now it will return the result you want.  This is a similar idea to the one 
> > offered dealing with a table.   The other is you will need to nest iif 
> > statements in the query, which is tedious, but it will allow you to use the 
> > like sql statement.
> > 
> > iiif(OldReligion like '%catholic%,'Roman Catholic',iif(OldReligion like 
> > '%baptist%','Baptist', etc...
> > 
> > Wade
> > 
> > 
> > 
> > 
> > 
> > "Gntlhnds" wrote:
> > 
> > > I figured out the Like statement in an update query, but unfortunately the 
> > > field that contains the results can be one of 15 different results, and I 
> > > can't figure out how to write the sql statement.  I had already posted my 
> > > issue before, but then this post came up so I was trying to see if I could 
> > > make work in my situation.  My original post has a more detailed explanation 
> > > as to what I'm trying to do.  
> > > http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.access.queries&mid=3d91e549-aca4-432e-afc0-a756b83c8265&sloc=en-us
> > > 
> > > 
> > > "Wade" wrote:
> > > 
> > > > Well there are other ways to do this.  You can use string manipulation in a 
> > > > function or a like statment in SQL ( OldReligion like '%catholic%' ).  It 
> > > > really depends on how you are doing things.
> > > > 
> > > > "Gntlhnds" wrote:
> > > > 
> > > > > I get a zero in the field as a result.  But since John said that wildcards 
> > > > > don't work, then I need to find some other way to do what I want.  John 
> > > > > mentioned translation tables.  I don't know if that will work for me or not, 
> > > > > but I am going to look into it.
> > > > > 
> > > > > "Wade" wrote:
> > > > > 
> > > > > > What are you getting for a result (or error)?  Seeing your code would be 
> > > > > > helpful in determining the problem.  Thanks.
> > > > > > 
> > > > > > Wade
> > > > > > 
> > > > > > "Gntlhnds" wrote:
> > > > > > 
> > > > > > > I am trying to do something similar.  I have a spreadsheet that I import, but 
> > > > > > > I would like to change the data in one of the fields and update it to 
> > > > > > > another.  I've modified the function you created, but it isn't working.    
> > > > > > > What it is supposed to do is check the string in the field "OldReligion", and 
> > > > > > > based on the word in the Case statment, update the field "Religion" to 
> > > > > > > something else.  Here's my code:
> > > > > > > 
> > > > > > > Public Function GetReligion(ByVal OldReligion As String) As String
> > > > > > > 
> > > > > > >      Select Case OldReligion
> > > > > > >        Case "*catholic*"
> > > > > > >            GetReligion = "Roman Catholic"
> > > > > > >        Case "*baptist*"
> > > > > > >            GetReligion = "Baptist"
> > > > > > >        Case "*preference*"
> > > > > > >            GetReligion = "No Preference"
> > > > > > >        Case "*lutheran*"
> > > > > > >            GetReligion = "Lutheran"
> > > > > > >        Case Else
> > > > > > >            GetReligion = "0"
> > > > > > >      End Select
> > > > > > > End Function
> > > > > > > 
> > > > > > > "Wade" wrote:
> > > > > > > 
> > > > > > > > Rob,
> > > > > > > > 
> > > > > > > > You will need to write a function and write the query as below
> > > > > > > > 
> > > > > > > > update temp set temp.levy = GetTaxable_ac(taxable_ac,temp.zone)
> > > > > > > > 
> > > > > > > > In the VBA editor you will need to add a module like below:
> > > > > > > > 
> > > > > > > > Public Function GetTaxable_ac(ByVal Taxable_ac As Double, ByVal Zone As 
> > > > > > > > String) As Double
> > > > > > > > 
> > > > > > > >      Select Case Zone
> > > > > > > >        Case "1"
> > > > > > > >            GetTaxable_ac = Taxable_ac * 10
> > > > > > > >        Case "2"
> > > > > > > >            GetTaxable_ac = Taxable_ac * 20
> > > > > > > >        Case Else
> > > > > > > >             GetTaxable_ac = 0
> > > > > > > >      End Select
> > > > > > > > End Function
> > > > > > > > 
> > > > > > > > I hope this helps.  
> > > > > > > > 
> > > > > > > > Wade
> > > > > > > > 
> > > > > > > > 
> > > > > > > > > I have 3 columns (taxable_ac, zone, levy) in a table named "temp".
> > > > > > > > > I would like to update levy with taxable_ac*10 where zone=1 and 
> > > > > > > > > taxable_ac*20 where zone=2. I would prefer to avoid using multiple imbedded 
> > > > > > > > > if-statements if at all possible. Here is where I got stuck.
> > > > > > > > > 
> > > > > > > > > UPDATE temp SET temp.levy = taxable_ac*"10"
> > > > > > > > > WHERE temp.zone="1";
> > > > > > > > > 
> > > > > > > > > I tried the following, but it obviously doesn't work. It should give you an 
> > > > > > > > > idea of what i'm trying to do though.
> > > > > > > > > 
> > > > > > > > > UPDATE temp SET temp.levy = taxable_ac*"10"
> > > > > > > > > WHERE temp.zone="1" AND
> > > > > > > > > UPDATE temp SET temp.levy = taxable_ac*"20"
> > > > > > > > > WHERE temp.zone="2";
> > > > > > > > > 
> > > > > > > > > I just started using Access yesterday and am trying to get the hang of it. 
> > > > > > > > > Thanks for your help.
> > > > > > > > > 
> > > > > > > > > 
0
Utf
6/20/2007 1:22:00 PM
I tried using single quotes and I also tried using contacts.oldreligion, but 
neither worked as well.  Right now I'm trying to create a vba module that 
will do it.  I don't see anyway of being able to do it within a single query.

"Wade" wrote:

> First, I don't think you want to use double quotes around OldReligion.  The 
> issue with the combobox depends, but right now I don't think it is the cause 
> of the issue.
> 
> Wade
> 
> "Gntlhnds" wrote:
> 
> > I tried the IIF statement in the query:
> > 
> > UPDATE Contacts SET Contacts.Religion = IIf("OldReligion" Like 
> > '*catholic*','Roman Catholic',IIf(IIf("OldReligion" Like 
> > '*baptist*','Baptist'),IIf("OldReligion" Like '*lutheran*','Lutheran')));
> > 
> > but it didn't work.  I ended up with an empty field.  Now, contacts.religion 
> > is a combo box.  Could that be messing up my results?
> > 
> > "Wade" wrote:
> > 
> > > Well there are two things you can do.  one is to create the select statement 
> > > in the function to be based on all of the 15 possible results as below:
> > > 
> > > select case OldReligion
> > > 
> > > case "Catholic","catholic", "Roman Catholic"
> > > 
> > > case "Anglican","Episcipalian"
> > > 
> > > so on and so forth
> > > 
> > > Now it will return the result you want.  This is a similar idea to the one 
> > > offered dealing with a table.   The other is you will need to nest iif 
> > > statements in the query, which is tedious, but it will allow you to use the 
> > > like sql statement.
> > > 
> > > iiif(OldReligion like '%catholic%,'Roman Catholic',iif(OldReligion like 
> > > '%baptist%','Baptist', etc...
> > > 
> > > Wade
> > > 
> > > 
> > > 
> > > 
> > > 
> > > "Gntlhnds" wrote:
> > > 
> > > > I figured out the Like statement in an update query, but unfortunately the 
> > > > field that contains the results can be one of 15 different results, and I 
> > > > can't figure out how to write the sql statement.  I had already posted my 
> > > > issue before, but then this post came up so I was trying to see if I could 
> > > > make work in my situation.  My original post has a more detailed explanation 
> > > > as to what I'm trying to do.  
> > > > http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.access.queries&mid=3d91e549-aca4-432e-afc0-a756b83c8265&sloc=en-us
> > > > 
> > > > 
> > > > "Wade" wrote:
> > > > 
> > > > > Well there are other ways to do this.  You can use string manipulation in a 
> > > > > function or a like statment in SQL ( OldReligion like '%catholic%' ).  It 
> > > > > really depends on how you are doing things.
> > > > > 
> > > > > "Gntlhnds" wrote:
> > > > > 
> > > > > > I get a zero in the field as a result.  But since John said that wildcards 
> > > > > > don't work, then I need to find some other way to do what I want.  John 
> > > > > > mentioned translation tables.  I don't know if that will work for me or not, 
> > > > > > but I am going to look into it.
> > > > > > 
> > > > > > "Wade" wrote:
> > > > > > 
> > > > > > > What are you getting for a result (or error)?  Seeing your code would be 
> > > > > > > helpful in determining the problem.  Thanks.
> > > > > > > 
> > > > > > > Wade
> > > > > > > 
> > > > > > > "Gntlhnds" wrote:
> > > > > > > 
> > > > > > > > I am trying to do something similar.  I have a spreadsheet that I import, but 
> > > > > > > > I would like to change the data in one of the fields and update it to 
> > > > > > > > another.  I've modified the function you created, but it isn't working.    
> > > > > > > > What it is supposed to do is check the string in the field "OldReligion", and 
> > > > > > > > based on the word in the Case statment, update the field "Religion" to 
> > > > > > > > something else.  Here's my code:
> > > > > > > > 
> > > > > > > > Public Function GetReligion(ByVal OldReligion As String) As String
> > > > > > > > 
> > > > > > > >      Select Case OldReligion
> > > > > > > >        Case "*catholic*"
> > > > > > > >            GetReligion = "Roman Catholic"
> > > > > > > >        Case "*baptist*"
> > > > > > > >            GetReligion = "Baptist"
> > > > > > > >        Case "*preference*"
> > > > > > > >            GetReligion = "No Preference"
> > > > > > > >        Case "*lutheran*"
> > > > > > > >            GetReligion = "Lutheran"
> > > > > > > >        Case Else
> > > > > > > >            GetReligion = "0"
> > > > > > > >      End Select
> > > > > > > > End Function
> > > > > > > > 
> > > > > > > > "Wade" wrote:
> > > > > > > > 
> > > > > > > > > Rob,
> > > > > > > > > 
> > > > > > > > > You will need to write a function and write the query as below
> > > > > > > > > 
> > > > > > > > > update temp set temp.levy = GetTaxable_ac(taxable_ac,temp.zone)
> > > > > > > > > 
> > > > > > > > > In the VBA editor you will need to add a module like below:
> > > > > > > > > 
> > > > > > > > > Public Function GetTaxable_ac(ByVal Taxable_ac As Double, ByVal Zone As 
> > > > > > > > > String) As Double
> > > > > > > > > 
> > > > > > > > >      Select Case Zone
> > > > > > > > >        Case "1"
> > > > > > > > >            GetTaxable_ac = Taxable_ac * 10
> > > > > > > > >        Case "2"
> > > > > > > > >            GetTaxable_ac = Taxable_ac * 20
> > > > > > > > >        Case Else
> > > > > > > > >             GetTaxable_ac = 0
> > > > > > > > >      End Select
> > > > > > > > > End Function
> > > > > > > > > 
> > > > > > > > > I hope this helps.  
> > > > > > > > > 
> > > > > > > > > Wade
> > > > > > > > > 
> > > > > > > > > 
> > > > > > > > > > I have 3 columns (taxable_ac, zone, levy) in a table named "temp".
> > > > > > > > > > I would like to update levy with taxable_ac*10 where zone=1 and 
> > > > > > > > > > taxable_ac*20 where zone=2. I would prefer to avoid using multiple imbedded 
> > > > > > > > > > if-statements if at all possible. Here is where I got stuck.
> > > > > > > > > > 
> > > > > > > > > > UPDATE temp SET temp.levy = taxable_ac*"10"
> > > > > > > > > > WHERE temp.zone="1";
> > > > > > > > > > 
> > > > > > > > > > I tried the following, but it obviously doesn't work. It should give you an 
> > > > > > > > > > idea of what i'm trying to do though.
> > > > > > > > > > 
> > > > > > > > > > UPDATE temp SET temp.levy = taxable_ac*"10"
> > > > > > > > > > WHERE temp.zone="1" AND
> > > > > > > > > > UPDATE temp SET temp.levy = taxable_ac*"20"
> > > > > > > > > > WHERE temp.zone="2";
> > > > > > > > > > 
> > > > > > > > > > I just started using Access yesterday and am trying to get the hang of it. 
> > > > > > > > > > Thanks for your help.
> > > > > > > > > > 
> > > > > > > > > > 
0
Utf
6/20/2007 1:31:04 PM
Well, in this case I think the module is the best of the easy fixes.  

"Gntlhnds" wrote:

> I tried using single quotes and I also tried using contacts.oldreligion, but 
> neither worked as well.  Right now I'm trying to create a vba module that 
> will do it.  I don't see anyway of being able to do it within a single query.
> 
> "Wade" wrote:
> 
> > First, I don't think you want to use double quotes around OldReligion.  The 
> > issue with the combobox depends, but right now I don't think it is the cause 
> > of the issue.
> > 
> > Wade
> > 
> > "Gntlhnds" wrote:
> > 
> > > I tried the IIF statement in the query:
> > > 
> > > UPDATE Contacts SET Contacts.Religion = IIf("OldReligion" Like 
> > > '*catholic*','Roman Catholic',IIf(IIf("OldReligion" Like 
> > > '*baptist*','Baptist'),IIf("OldReligion" Like '*lutheran*','Lutheran')));
> > > 
> > > but it didn't work.  I ended up with an empty field.  Now, contacts.religion 
> > > is a combo box.  Could that be messing up my results?
> > > 
> > > "Wade" wrote:
> > > 
> > > > Well there are two things you can do.  one is to create the select statement 
> > > > in the function to be based on all of the 15 possible results as below:
> > > > 
> > > > select case OldReligion
> > > > 
> > > > case "Catholic","catholic", "Roman Catholic"
> > > > 
> > > > case "Anglican","Episcipalian"
> > > > 
> > > > so on and so forth
> > > > 
> > > > Now it will return the result you want.  This is a similar idea to the one 
> > > > offered dealing with a table.   The other is you will need to nest iif 
> > > > statements in the query, which is tedious, but it will allow you to use the 
> > > > like sql statement.
> > > > 
> > > > iiif(OldReligion like '%catholic%,'Roman Catholic',iif(OldReligion like 
> > > > '%baptist%','Baptist', etc...
> > > > 
> > > > Wade
> > > > 
> > > > 
> > > > 
> > > > 
> > > > 
> > > > "Gntlhnds" wrote:
> > > > 
> > > > > I figured out the Like statement in an update query, but unfortunately the 
> > > > > field that contains the results can be one of 15 different results, and I 
> > > > > can't figure out how to write the sql statement.  I had already posted my 
> > > > > issue before, but then this post came up so I was trying to see if I could 
> > > > > make work in my situation.  My original post has a more detailed explanation 
> > > > > as to what I'm trying to do.  
> > > > > http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.access.queries&mid=3d91e549-aca4-432e-afc0-a756b83c8265&sloc=en-us
> > > > > 
> > > > > 
> > > > > "Wade" wrote:
> > > > > 
> > > > > > Well there are other ways to do this.  You can use string manipulation in a 
> > > > > > function or a like statment in SQL ( OldReligion like '%catholic%' ).  It 
> > > > > > really depends on how you are doing things.
> > > > > > 
> > > > > > "Gntlhnds" wrote:
> > > > > > 
> > > > > > > I get a zero in the field as a result.  But since John said that wildcards 
> > > > > > > don't work, then I need to find some other way to do what I want.  John 
> > > > > > > mentioned translation tables.  I don't know if that will work for me or not, 
> > > > > > > but I am going to look into it.
> > > > > > > 
> > > > > > > "Wade" wrote:
> > > > > > > 
> > > > > > > > What are you getting for a result (or error)?  Seeing your code would be 
> > > > > > > > helpful in determining the problem.  Thanks.
> > > > > > > > 
> > > > > > > > Wade
> > > > > > > > 
> > > > > > > > "Gntlhnds" wrote:
> > > > > > > > 
> > > > > > > > > I am trying to do something similar.  I have a spreadsheet that I import, but 
> > > > > > > > > I would like to change the data in one of the fields and update it to 
> > > > > > > > > another.  I've modified the function you created, but it isn't working.    
> > > > > > > > > What it is supposed to do is check the string in the field "OldReligion", and 
> > > > > > > > > based on the word in the Case statment, update the field "Religion" to 
> > > > > > > > > something else.  Here's my code:
> > > > > > > > > 
> > > > > > > > > Public Function GetReligion(ByVal OldReligion As String) As String
> > > > > > > > > 
> > > > > > > > >      Select Case OldReligion
> > > > > > > > >        Case "*catholic*"
> > > > > > > > >            GetReligion = "Roman Catholic"
> > > > > > > > >        Case "*baptist*"
> > > > > > > > >            GetReligion = "Baptist"
> > > > > > > > >        Case "*preference*"
> > > > > > > > >            GetReligion = "No Preference"
> > > > > > > > >        Case "*lutheran*"
> > > > > > > > >            GetReligion = "Lutheran"
> > > > > > > > >        Case Else
> > > > > > > > >            GetReligion = "0"
> > > > > > > > >      End Select
> > > > > > > > > End Function
> > > > > > > > > 
> > > > > > > > > "Wade" wrote:
> > > > > > > > > 
> > > > > > > > > > Rob,
> > > > > > > > > > 
> > > > > > > > > > You will need to write a function and write the query as below
> > > > > > > > > > 
> > > > > > > > > > update temp set temp.levy = GetTaxable_ac(taxable_ac,temp.zone)
> > > > > > > > > > 
> > > > > > > > > > In the VBA editor you will need to add a module like below:
> > > > > > > > > > 
> > > > > > > > > > Public Function GetTaxable_ac(ByVal Taxable_ac As Double, ByVal Zone As 
> > > > > > > > > > String) As Double
> > > > > > > > > > 
> > > > > > > > > >      Select Case Zone
> > > > > > > > > >        Case "1"
> > > > > > > > > >            GetTaxable_ac = Taxable_ac * 10
> > > > > > > > > >        Case "2"
> > > > > > > > > >            GetTaxable_ac = Taxable_ac * 20
> > > > > > > > > >        Case Else
> > > > > > > > > >             GetTaxable_ac = 0
> > > > > > > > > >      End Select
> > > > > > > > > > End Function
> > > > > > > > > > 
> > > > > > > > > > I hope this helps.  
> > > > > > > > > > 
> > > > > > > > > > Wade
> > > > > > > > > > 
> > > > > > > > > > 
> > > > > > > > > > > I have 3 columns (taxable_ac, zone, levy) in a table named "temp".
> > > > > > > > > > > I would like to update levy with taxable_ac*10 where zone=1 and 
> > > > > > > > > > > taxable_ac*20 where zone=2. I would prefer to avoid using multiple imbedded 
> > > > > > > > > > > if-statements if at all possible. Here is where I got stuck.
> > > > > > > > > > > 
> > > > > > > > > > > UPDATE temp SET temp.levy = taxable_ac*"10"
> > > > > > > > > > > WHERE temp.zone="1";
> > > > > > > > > > > 
> > > > > > > > > > > I tried the following, but it obviously doesn't work. It should give you an 
> > > > > > > > > > > idea of what i'm trying to do though.
> > > > > > > > > > > 
> > > > > > > > > > > UPDATE temp SET temp.levy = taxable_ac*"10"
> > > > > > > > > > > WHERE temp.zone="1" AND
> > > > > > > > > > > UPDATE temp SET temp.levy = taxable_ac*"20"
> > > > > > > > > > > WHERE temp.zone="2";
> > > > > > > > > > > 
> > > > > > > > > > > I just started using Access yesterday and am trying to get the hang of it. 
> > > > > > > > > > > Thanks for your help.
> > > > > > > > > > > 
> > > > > > > > > > > 
0
Utf
6/20/2007 2:20:01 PM
Reply:

Similar Artilces:

Qn on Restricting a public function overriding in base classes ?
How do we achieve the following : Class Baseclass { public: int funXYZ(); ..... }; Now, How do I modify the definition of the above class to restrict any other Class which derives from Baseclass, from over riding public function funXYZ(); Note that, the function funXYZ() needs to be public. Is it possible ? "satlal" <satish.lalam@gmail.com> skrev i meddelandet news:dg942b$67e$1@news.mch.sbs.de... > How do we achieve the following : > > Class Baseclass > { > public: > int funXYZ(); > ..... > }; > > Now, H...

how to I block text not to break across columns or pages ?
I want to keep certain text together and not have it split apart over columns or on a new page: like several lines comprising an address in an address book -- I want to keep it all together. Either apply a style with the Keep With Next paragraph format option to all but the last of the paragraphs you wish to keep together or Use Shift+Enter to make a line break within the same paragraph -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MV...

How do I add column A data labels to a scatter of columns B and C?
I have made a scatter plot using columns B and C as my x and y coordinates. I would like to use the data in column A to label my points. I already have the XY chart labeler 97 installed and selected, but I don't know how to get the data labels to show up on my graph. Thank you! Hi Christina - There are a couple of well written Excel add-ins you can download to do this: Rob Bovey's Chart Labeler, http://appspro.com John Walkenbach's Chart Tools, http://j-walk.com Both are free downloads, they install easily, and they integrate into Excel's interface very nicely. They l...

update the same cell into another column forming a list on opening
hi there, i have made a spreadsheet to enter data and then view the resulting statistics that are generated, i have one cell that changes in value after entering the data, and i would like to keep a record of what values this cell has been, i was after a formula that would update that single cell's value into a column forming a list each time the spreadsheet was opened or the value in the cell changed, like a field perhaps, this would fill up the column with a list of results from which to generate more stats and a graph etc. any help would be great, thank you -- thank...

Is Version Updated after sp-1 is updated on Money 2007
After you get the automatic software downloaded is it necessary to click onto the sp-1 to install? -- Deb No it is automatic. If I recall, after the SP1 is downloaded, you are prompted to close Money and restart it. After you restart Help > About should give you a version number ending in .1024. -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. I do not respond to any emails that I have not specifically asked for. "Rebelleheart" <Rebelleheart@discussions.microsoft.com> wrote in messa...

Online Updates
When trying to complete online update, the computer states the Money has encountered an error and must shut down. Have upgraded to 1415. Help! BTW: I'm using Money Plus Deluxe and XP "Cheyne14" wrote: > When trying to complete online update, the computer states the Money has > encountered an error and must shut down. Have upgraded to 1415. Help! ...

Fixed Number of Columns
After manipulating data in Excel I would like to copy and paste the data in a text editor. The data should be in fixed format, for example integer numbers should end in columns with multiples of 5. The text is then used as input to an old fashioned program which requires that the data be aligned I changed the font in Excel to Courrier New and then selected a column width of 5. When I copy the data to a text editor it copies some tab marks and the columns do not align. How can I make things align in the text editor? Vino, You can use a macro: copy the code below, then paste into a module...

Locking a column. Excel, 2002.
I don't know if locking is the correct phraseology and perhaps that is why I can't find an answer. In column A are the numbers, 1, 2, 3, and so on and I want these numbers to remain stationary (they are the rank ordering) when I do a sort on another column (say H). -- Regards Ron Badour Select the range that you want to sort, but avoid column A. Then sort your data. If you let excel guess at the range, it'll think that you may want all the contiguous columns sorted. Personally, I would sort that column, too. But I'd fill it with a formula like: =row()-1 (headers...

Script for Query-Based DL's
Pardon me if posted in the wrong place. We are scripting a large amount of OU structures and administration groups with delegation through dsacls. This is something that is repeated many times. One item that is asked to create is a query-based DL. Since it would need to be ran so many times and for consistency I am trying to find if we can have the script create the Q-B DL's. I have found scripts for dsacls and the Q-B DL's but I'm having a hard time finding how to script the creation of the DL's. Does anyone know how this would be done? Thank you, Michael ...

Updating Money Deluxe 2007 question
Will Microsoft keep the Money updates available permanently, as they do with updates for old operating systems like Windows 98? The reason I ask is that, at least in my experience, Money updates have never been a manually downloadable file, but rather once you install the program, Money calls out on the Internet and looks for updates. If Microsoft doesn't keep the updates online, is there any way to download the update files now for permanent keeping? I did a test. I installed Money 2007 Deluxe on my Virtual PC. As soon as I went into the program after installation, it updated ...

Try that update which comes from the M$ Corp.
--pxulfresdind Content-Type: multipart/related; boundary="xepmincpbkqfdfbp"; type="multipart/alternative" --xepmincpbkqfdfbp Content-Type: multipart/alternative; boundary="zhaxriplmgq" --zhaxriplmgq Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Partner this is the latest version of security update, the "October 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to p...

Upgrading to newest money money updates... errors..
I've been using Money 2005 for a month or so... I decided to switch which computer I use it at at home. Just yesterday I think was the last update that asked me to back up my file. Now, I've installed the program on a new computer we got and everytime it tries to download the latest money update it crashes when it goes to install it. It says I dont have access to the file or that it may be 'read only' or not enough disk space (needs 100 megs). I can use the file perfect on the orginal computer, it's not marked read only, and there's over 40GB of space available....

How does one print the columns from a Visio ERD diagram?
I see them but can't print them. ...

Excel 2000... Missing row and column designators.
Ho do you mean 'Tools - Options - View' and check 'Row/Column headings' -- Regards Frank Kabel Frankfurt, Germany "Clarence" <carowe@cnyconnect.net> schrieb im Newsbeitrag news:2ee7401c46c21$a45aef40$a501280a@phx.gbl... > ...

Recordset Not Updateable
I have set up an access database to track technical support and training services. I'm pretty new to Access and Very new to VB programming. I have hit a wall with something and have found no solution in help or on these discussion groups. Here's what I'm attempting to do, from the user's perspective. This process works great up to the very end, then things fall apart. Let's say the user wants to look at all open support cases. The user presses a button called "All Open Cases." A very basic query runs, and the results are shown in a read-only (contin...

can you make one worksheet update another
I have two similar worksheets within the same workbook. One sheet is sorted by name and the other sheet is sorted by a date for a speicfic name. Is there a way to change the date on the sheet, sorted by name, and that will automatically find the name on the other sheet(sorted by date) and change the date to the second sheet? Jaime, The normal way is to have one table, and sort it as needed when needed. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Jaime S." <Jaime S.@discussions.microsoft.com> wrote in mess...

updating and underlying table field with a combo box selection
I want to use an ID value from a combo box on a subform to update the same ID value in the form's underlying table. My main form is: frm_Street_Joiner_Main My Subform is: frm_Street_Joiner_Sub My Subform table is: tbl_Street_Joiner My Mainform is: frm_Street_Joiner_Main The combo box on my subform is called: StreetName, with a column count of two but the bound column is the actual street name. SELECT QRY_Street_Names_Joiner_Master.Street_Names, QRY_Street_Names_Joiner_Master.StreetNameID FROM QRY_Street_Names_Joiner_Master ORDER BY QRY_Street_Names_Joiner_Master.Street_Names, QR...

Why are my columns and rows reversed when I open Excel
When I open my Excel spreadsheet the columns, rows and scroll bars are reversed?? What's happened? Tools|options|International Tab Uncheck that "view current sheet right-to-left" Deb Web wrote: > > When I open my Excel spreadsheet the columns, rows and scroll bars are > reversed?? What's happened? -- Dave Peterson ...

Multi based criteria
I have a report setup that prints a single record. I get to this report by having the record open through a form and a command button to print preview the report. I have various subforms (data only applying to that record) that are also outputted on the report and that's working fine. But now my issue is that I have subreport that has three separate criteria I need to specify. The three controls are BrandName (value), Type (text) and DropNo (value). I have a separate table called OfficeCopies. In this are various people that, depending on the brand, type and drop, are assign...

Data Validation Lists
I have been searching the web all day, and I am not sure how to approach this problem. Here is a basic worksheet: ID Name Serial # 1 Joe ck9033n2389d 2 Joe 349-283fjrjh55 3 Bob c90320j34n5kjdf 4 Joe kgho95injhs 5 Bob 2353gdf745 Step 1: I have a drop down list with only one instance of each of the "Name" variable. Step 2: Upon selecting that name, I would like another drop down box with all the serial numbers associated with that person. Step 3: Then, based on the serial number, display stats in the same row for that i...

Merging queries by column in access
Hi, I have a series of queries which get data aggregated by year, e.g. select year(date), min(var1), avg(var1), max(var1) from table where (ridiculously complicated set of conditions) group by year(date) select year(date), min(var2), avg(var2), max(var2) from table where (entirely different ridiculously complicated set of conditions) group by year(date) etc etc There are six queries like this so what I want is to amalgamate these columns together into one results table (and also into one query so users can just run one stored query rather than six!) as follows: year, m...

URGENT : MS06-29 Breaks EDK Based Fax Gateways
Hello, We are an ISV based in france selling EDK Based Fax Gateways for Exchange 2000/2003 Microsoft Released a patch for Exchange 2003 this week: http://support.microsoft.com/kb/912442/en-us Unfortunately, after installing this patch, the gateway becomes unable to deliver a new message to the Gateway's mailbox. After doing some research, it seems that this is related to the "Send As"/"Receive As" rights-granted to the service account running the gateway if this is not LocalSystem- on the mailbox. Any help appreciated. Yes that would almost certainly be true....

update shape
I want to use a consistent shape in many pages. Is there a way to create a master copy such that when I update the text on a master copy all copies are updated. I created a new stencil and new master but updating the master doesn't seem to update the copies (after i save the file). Amos You have to update the master in the local, or "Document Stencil" Every Visio drawing contains a "hidden" Document Stencil, that carries copies of masters from "external" stencil files. When you drag a new shape into a drawing, the master gets copied from the external .v...

windows update fail...... What now?
ok so I had a problem with windows update downloading but not installing.. I did go to the windows file in the C: drive and manually installed it myself. Thinking that it would fix my install problem.. I was wrong.. I next have found out that in my event logger that my IIS6 is not working properly or something. It doesn't look right and in my event logger it says my security assessment tool 4.0 error 1303 I am the administrator and logged in already so what the heck? And I also would like to say that my computer is old and it's a IBM think pad running windows xp perfessio...

Is there any way to use the MATCH function with more than 1 column
For example, =MATCH(215, Y3:Y51, 0) will search from Y3 to Y51 for the value 215, but my data set spans more than one column. My data set ranges from A3 to Z51, and the function =MATCH(215, A3:Z51, 0) returns an #N/A error. What can I do? Do you want both the row and column ? Two separate values? -- Gary's Student "Mr. Snrub" wrote: > For example, =MATCH(215, Y3:Y51, 0) will search from Y3 to Y51 for the value > 215, but my data set spans more than one column. > > My data set ranges from A3 to Z51, and the function =MATCH(215, A3:Z51, 0) > returns an #...