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 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.
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?
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.
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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
|
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...
|
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
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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...
| |