469,323 Members | 1,493 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,323 developers. It's quick & easy.

VB code help for Select Case

I having been using Access '97/2002 for about 4 years now and have
never really had the need or the time to learn visual basic. Well, I
think the time has finally come.

I need help with Visual Basic code that will examine numeric value for
a particular field in a query, and assign a new numeric vaule to that
field. There are over 21 possible values and I am told that IIF
statement will only handle 9 of the possibilities and that I need to
do it in Visual Basic.

I have done a little research on the net to get the basics of a Select
Case statement, but have no idea how to transform that in to a code
for my database.

For simplicity sake, here are the basics of the query I am stumped
on...

Type: Select query
Name: Percentagetbl
Fields: Propertycode, Identifier, Percentage

The propertycode and Identifier fields are known. The percentage
field is what I need created via code based on the value in
Identifier.

Below is a partial list of the "Cases" for the code (I did not think
there was a need to list all 21)

If Identifier = 11 then Percentage = .0517
If Identifier = 21 then Percentage = .0722
If Identifier = 12 then Percentage = .0217
The final "case" would be a case else. In other words If Identifier =
to anything besides the previous 20 items the Percentage = 0

Can anyone please assist me with formalizing this code?

Your help is greatly appreciated. I am enrolled in a Visual Basic
class in 2 weeks, but need to complete this before then.

Regards,

Terencetrent
Nov 13 '05 #1
4 2612
On 27 Jul 2004 13:14:27 -0700, Terencetrent wrote:
I having been using Access '97/2002 for about 4 years now and have
never really had the need or the time to learn visual basic. Well, I
think the time has finally come.

I need help with Visual Basic code that will examine numeric value for
a particular field in a query, and assign a new numeric vaule to that
field. There are over 21 possible values and I am told that IIF
statement will only handle 9 of the possibilities and that I need to
do it in Visual Basic.

I have done a little research on the net to get the basics of a Select
Case statement, but have no idea how to transform that in to a code
for my database.

For simplicity sake, here are the basics of the query I am stumped
on...

Type: Select query
Name: Percentagetbl
Fields: Propertycode, Identifier, Percentage

The propertycode and Identifier fields are known. The percentage
field is what I need created via code based on the value in
Identifier.

Below is a partial list of the "Cases" for the code (I did not think
there was a need to list all 21)

If Identifier = 11 then Percentage = .0517
If Identifier = 21 then Percentage = .0722
If Identifier = 12 then Percentage = .0217
The final "case" would be a case else. In other words If Identifier =
to anything besides the previous 20 items the Percentage = 0

Can anyone please assist me with formalizing this code?

Your help is greatly appreciated. I am enrolled in a Visual Basic
class in 2 weeks, but need to complete this before then.

Regards,

Terencetrent


Look up Select Case in VBA help.

Create a new User Defined function in a Module.
On the Database main folder, click Modules + New.

Click Insert + Procedure

Select function from the dialog box and give the function a name:
GetPercent

The Code window will then show:
Function GetPercent()

End Function

Add the code below, so that when you are done it looks like this:

Function GetPercent(NumberIn as Integer) as Double
Dim NewValue as Double
Select Case NumberIn
Case is = 11
NewValue = .0517
Case is = 12
NewValue = .0217
Case is = 21
NewValue = .0722
etc.
Case Else
NewValue = 0
End Select
GetPercent = NewValue
End Function
===========

You can call it from a query using:
Percentage:GetPercent([Identifier])

As criteria in the query use:
Where [Identifier] is not null

Or you can call it from a form or report's unbound control:
=GetPercent([Identifier])

[Percentage] is not a field in the table and the result need not be
stored in any table. As long as [Identifier] is stored in the table,
[Percentage] can be displayed where ever the result is needed, in a
form, or in a report by calling the above function.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
Nov 13 '05 #2
fredg <fg******@example.invalid> wrote in message news:<1k****************************@40tude.net>.. .
On 27 Jul 2004 13:14:27 -0700, Terencetrent wrote:
I having been using Access '97/2002 for about 4 years now and have
never really had the need or the time to learn visual basic. Well, I
think the time has finally come.

I need help with Visual Basic code that will examine numeric value for
a particular field in a query, and assign a new numeric vaule to that
field. There are over 21 possible values and I am told that IIF
statement will only handle 9 of the possibilities and that I need to
do it in Visual Basic.

I have done a little research on the net to get the basics of a Select
Case statement, but have no idea how to transform that in to a code
for my database.

For simplicity sake, here are the basics of the query I am stumped
on...

Type: Select query
Name: Percentagetbl
Fields: Propertycode, Identifier, Percentage

The propertycode and Identifier fields are known. The percentage
field is what I need created via code based on the value in
Identifier.

Below is a partial list of the "Cases" for the code (I did not think
there was a need to list all 21)

If Identifier = 11 then Percentage = .0517
If Identifier = 21 then Percentage = .0722
If Identifier = 12 then Percentage = .0217
The final "case" would be a case else. In other words If Identifier =
to anything besides the previous 20 items the Percentage = 0

Can anyone please assist me with formalizing this code?

Your help is greatly appreciated. I am enrolled in a Visual Basic
class in 2 weeks, but need to complete this before then.

Regards,

Terencetrent


Look up Select Case in VBA help.

Create a new User Defined function in a Module.
On the Database main folder, click Modules + New.

Click Insert + Procedure

Select function from the dialog box and give the function a name:
GetPercent

The Code window will then show:
Function GetPercent()

End Function

Add the code below, so that when you are done it looks like this:

Function GetPercent(NumberIn as Integer) as Double
Dim NewValue as Double
Select Case NumberIn
Case is = 11
NewValue = .0517
Case is = 12
NewValue = .0217
Case is = 21
NewValue = .0722
etc.
Case Else
NewValue = 0
End Select
GetPercent = NewValue
End Function
===========

You can call it from a query using:
Percentage:GetPercent([Identifier])

As criteria in the query use:
Where [Identifier] is not null

Or you can call it from a form or report's unbound control:
=GetPercent([Identifier])

[Percentage] is not a field in the table and the result need not be
stored in any table. As long as [Identifier] is stored in the table,
[Percentage] can be displayed where ever the result is needed, in a
form, or in a report by calling the above function.


Thanks, fredg.

I have it all typed in. I need to save the module. Does it matter
what name I give the module?
Nov 13 '05 #3
On 28 Jul 2004 06:39:56 -0700, Terencetrent wrote:
fredg <fg******@example.invalid> wrote in message news:<1k****************************@40tude.net>.. .
On 27 Jul 2004 13:14:27 -0700, Terencetrent wrote:
I having been using Access '97/2002 for about 4 years now and have
never really had the need or the time to learn visual basic. Well, I
think the time has finally come.

I need help with Visual Basic code that will examine numeric value for
a particular field in a query, and assign a new numeric vaule to that
field. There are over 21 possible values and I am told that IIF
statement will only handle 9 of the possibilities and that I need to
do it in Visual Basic.

I have done a little research on the net to get the basics of a Select
Case statement, but have no idea how to transform that in to a code
for my database.

For simplicity sake, here are the basics of the query I am stumped
on...

Type: Select query
Name: Percentagetbl
Fields: Propertycode, Identifier, Percentage

The propertycode and Identifier fields are known. The percentage
field is what I need created via code based on the value in
Identifier.

Below is a partial list of the "Cases" for the code (I did not think
there was a need to list all 21)

If Identifier = 11 then Percentage = .0517
If Identifier = 21 then Percentage = .0722
If Identifier = 12 then Percentage = .0217
The final "case" would be a case else. In other words If Identifier =
to anything besides the previous 20 items the Percentage = 0

Can anyone please assist me with formalizing this code?

Your help is greatly appreciated. I am enrolled in a Visual Basic
class in 2 weeks, but need to complete this before then.

Regards,

Terencetrent


Look up Select Case in VBA help.

Create a new User Defined function in a Module.
On the Database main folder, click Modules + New.

Click Insert + Procedure

Select function from the dialog box and give the function a name:
GetPercent

The Code window will then show:
Function GetPercent()

End Function

Add the code below, so that when you are done it looks like this:

Function GetPercent(NumberIn as Integer) as Double
Dim NewValue as Double
Select Case NumberIn
Case is = 11
NewValue = .0517
Case is = 12
NewValue = .0217
Case is = 21
NewValue = .0722
etc.
Case Else
NewValue = 0
End Select
GetPercent = NewValue
End Function
===========

You can call it from a query using:
Percentage:GetPercent([Identifier])

As criteria in the query use:
Where [Identifier] is not null

Or you can call it from a form or report's unbound control:
=GetPercent([Identifier])

[Percentage] is not a field in the table and the result need not be
stored in any table. As long as [Identifier] is stored in the table,
[Percentage] can be displayed where ever the result is needed, in a
form, or in a report by calling the above function.


Thanks, fredg.

I have it all typed in. I need to save the module. Does it matter
what name I give the module?


You can name the module whatever you wish, except not the same as the
function name, i.e. not "GetPercent", used within the module.
Why not just add a prefix to the function name .... "mdlGetPercent"?
Or something more descriptive .... "mdlFindPercentageFromTable".
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
Nov 13 '05 #4
fredg <fg******@example.invalid> wrote in message news:<82***************************@40tude.net>...
On 28 Jul 2004 06:39:56 -0700, Terencetrent wrote:
fredg <fg******@example.invalid> wrote in message news:<1k****************************@40tude.net>.. .
On 27 Jul 2004 13:14:27 -0700, Terencetrent wrote:

I having been using Access '97/2002 for about 4 years now and have
never really had the need or the time to learn visual basic. Well, I
think the time has finally come.

I need help with Visual Basic code that will examine numeric value for
a particular field in a query, and assign a new numeric vaule to that
field. There are over 21 possible values and I am told that IIF
statement will only handle 9 of the possibilities and that I need to
do it in Visual Basic.

I have done a little research on the net to get the basics of a Select
Case statement, but have no idea how to transform that in to a code
for my database.

For simplicity sake, here are the basics of the query I am stumped
on...

Type: Select query
Name: Percentagetbl
Fields: Propertycode, Identifier, Percentage

The propertycode and Identifier fields are known. The percentage
field is what I need created via code based on the value in
Identifier.

Below is a partial list of the "Cases" for the code (I did not think
there was a need to list all 21)

If Identifier = 11 then Percentage = .0517
If Identifier = 21 then Percentage = .0722
If Identifier = 12 then Percentage = .0217
The final "case" would be a case else. In other words If Identifier =
to anything besides the previous 20 items the Percentage = 0

Can anyone please assist me with formalizing this code?

Your help is greatly appreciated. I am enrolled in a Visual Basic
class in 2 weeks, but need to complete this before then.

Regards,

Terencetrent

Look up Select Case in VBA help.

Create a new User Defined function in a Module.
On the Database main folder, click Modules + New.

Click Insert + Procedure

Select function from the dialog box and give the function a name:
GetPercent

The Code window will then show:
Function GetPercent()

End Function

Add the code below, so that when you are done it looks like this:

Function GetPercent(NumberIn as Integer) as Double
Dim NewValue as Double
Select Case NumberIn
Case is = 11
NewValue = .0517
Case is = 12
NewValue = .0217
Case is = 21
NewValue = .0722
etc.
Case Else
NewValue = 0
End Select
GetPercent = NewValue
End Function
===========

You can call it from a query using:
Percentage:GetPercent([Identifier])

As criteria in the query use:
Where [Identifier] is not null

Or you can call it from a form or report's unbound control:
=GetPercent([Identifier])

[Percentage] is not a field in the table and the result need not be
stored in any table. As long as [Identifier] is stored in the table,
[Percentage] can be displayed where ever the result is needed, in a
form, or in a report by calling the above function.


Thanks, fredg.

I have it all typed in. I need to save the module. Does it matter
what name I give the module?


You can name the module whatever you wish, except not the same as the
function name, i.e. not "GetPercent", used within the module.
Why not just add a prefix to the function name .... "mdlGetPercent"?
Or something more descriptive .... "mdlFindPercentageFromTable".


That did It!!

I had the module named GetPercent and was getting an error message.

Thanks for all of your help.

Regards,

Terence
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by jason | last post: by
2 posts views Thread by rked | last post: by
11 posts views Thread by Jozef | last post: by
2 posts views Thread by Jusan | last post: by
2 posts views Thread by chrisw_14 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Gurmeet2796 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.