473,327 Members | 2,074 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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 2806
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Mohammed Mazid | last post by:
Hi folks! Can anyone please help me with this? I am developing a Quiz program but I am stuck with "multiple answers". Basically I need some sort of code that would select multiple answers...
4
by: jason | last post by:
Hello. Newbie on SQL and suffering through this. I have two tables created as such: drop table table1; go drop table table2; go
2
by: rked | last post by:
I get nameSPAN1 is undefined when I place cursor in comments box.. <%@ LANGUAGE="VBScript" %> <% DIM ipAddress ipAddress=Request.Servervariables("REMOTE_HOST") %> <html> <head> <meta...
3
by: D | last post by:
Does anyone have a VBA code that converts numbers to Spelled out numbers? Example 100 to One Hundred. I need it for a check writing program and I don't feel like reinventing the wheel. Thanks
11
by: Jozef | last post by:
I have some old code that I use from the Access 95 Developers handbook. The code works very well, with the exception that it doesn't seem to recognize wide screens, and sizes tab controls so that...
3
by: kathyk | last post by:
Hi All, I am using Access 2003 on machines with windows 2000 and XP. The problem I'm having started only after we got a new image for our PC's. This database app has been around for awhile and...
2
by: Jusan | last post by:
hi, I have written a cheque writing utility in vb.net and would like to invite comments on the code. can this be done in a better / faster / shorter way? thnx Public Class...
2
by: chrisw_14 | last post by:
I've created an Access 2000 database. I want to have a Scripts/Macro section to automate certain tasks for users. If a user has a specific task they want done periodically, they can run a...
3
by: Hazza | last post by:
Hi, I am using PHP and mysql to create a website. I am fairly new to PHP, and thus am grateful to anyone who helps! Firstly I am running a homepage, that displays additional content if a user...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.