Interesting calculated formula

[vanguardaccess]

I have a field, called [Mark], that can be anywhere from 0 to 100. I would
like a query field, called [vangrade], to operate the following on the number:

If [Mark] is from 0 to 64, change to 0.5
If [Mark] is from 65 to 95, apply the formula ([Mark]-55)/10
If [Mark] is from 96-100, change to 4.0

As a secondary question, besides being anywhere from 1-100, [Mark] can also
be a P or an F. Is it possible to have the P or F just stay as they are in
[vangrade] while performing the calculation above? If so, how?

Thanks,

--vanguardaccessman

 

[vanguardaccess]

perhaps we are lost in translation, but its still not clear to me. please
help.
--
--vanguardaccessman

I have a field, called [Mark], that can be anywhere from 0 to 100. I would
like a query field, called [vangrade], to operate the following on the
number:

If [Mark] is from 0 to 64, change to 0.5
If [Mark] is from 65 to 95, apply the formula ([Mark]-55)/10
If [Mark] is from 96-100, change to 4.0

As a secondary question, besides being anywhere from 1-100, [Mark] can
also
be a P or an F. Is it possible to have the P or F just stay as they are
in
[vangrade] while performing the calculation above? If so, how?

Thanks,

--vanguardaccessman

fa portugues?????? se sabe fala

 

[Tom Wickerath]

Hi vanguardaccessman,

Try this to see if it works for you. First, create a new stand-alone module
and paste the code shown below into it:
___________________________

Option Compare Database
Option Explicit

Public Function DetermineResult(Mark As Variant) As Variant
On Error Resume Next

If Len(Mark & "") > 0 Then

Select Case Mark
Case "P", "F"
DetermineResult = Mark
Case "0"
DetermineResult = 0.5
Case IsNumeric(Mark) = True
Mark = CCur(Mark)
Select Case Mark
Case 0 To 64
DetermineResult = 0.5
Case 65 To 95
DetermineResult = Format(([Mark] - 55) / 10, "0.0")
Case 96 To 100
DetermineResult = Format(4, "0.0")
Case Else
DetermineResult = "Out of range."
End Select
Case Else
DetermineResult = "Out of range."
End Select
End If

End Function

___________________________

Name this module something like basCalculateResult (the important point
being to give your module a unique name that is not already the name of an
existing subroutine or function, and is not a reserved word). Compile the
code by clicking on

Debug > Compile {ProjectName}

where {ProjectName} is the name of your VBA project. Your code should
compile without any errors.

Create a new query. Dismiss the Add Tables dialog without adding any tables.
In query design view, click on View > SQL View. You should see the word
SELECT highlighted. Delete this default keyword. Copy the following SQL
statement (Ctrl C) and paste it into the SQL view (Ctrl V), replacing the
SELECT keyword:

SELECT [Mark], DetermineResult([Mark]) AS vangrade
FROM [tblMarks];

Note: You didn't give the name of your table, so I used a name of
"tblMarks". Make the appropriate substitution in the above SQL statement. You
can then switch back to the more familiar design view, if you wish, by
clicking on View > Design View. Run the query. Does this provide the results
you were looking for?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[vanguardaccess]

Tom,

Many thanks to you. I created and debugged the module with the code you
wrote, then created the query also as you wrote. When I run the query, I get
the result "Unidentified function 'determine result' in expression".

As I don't know VBA, I don't know what to do. Your follow up is greatly
appreciated.
--
--vanguardaccessman

Hi vanguardaccessman,

Try this to see if it works for you. First, create a new stand-alone module
and paste the code shown below into it:
___________________________

Option Compare Database
Option Explicit

Public Function DetermineResult(Mark As Variant) As Variant
On Error Resume Next

If Len(Mark & "") > 0 Then

Select Case Mark
Case "P", "F"
DetermineResult = Mark
Case "0"
DetermineResult = 0.5
Case IsNumeric(Mark) = True
Mark = CCur(Mark)
Select Case Mark
Case 0 To 64
DetermineResult = 0.5
Case 65 To 95
DetermineResult = Format(([Mark] - 55) / 10, "0.0")
Case 96 To 100
DetermineResult = Format(4, "0.0")
Case Else
DetermineResult = "Out of range."
End Select
Case Else
DetermineResult = "Out of range."
End Select
End If

End Function

___________________________

Name this module something like basCalculateResult (the important point
being to give your module a unique name that is not already the name of an
existing subroutine or function, and is not a reserved word). Compile the
code by clicking on

Debug > Compile {ProjectName}

where {ProjectName} is the name of your VBA project. Your code should
compile without any errors.

Create a new query. Dismiss the Add Tables dialog without adding any tables.
In query design view, click on View > SQL View. You should see the word
SELECT highlighted. Delete this default keyword. Copy the following SQL
statement (Ctrl C) and paste it into the SQL view (Ctrl V), replacing the
SELECT keyword:

SELECT [Mark], DetermineResult([Mark]) AS vangrade
FROM [tblMarks];

Note: You didn't give the name of your table, so I used a name of
"tblMarks". Make the appropriate substitution in the above SQL statement. You
can then switch back to the more familiar design view, if you wish, by
clicking on View > Design View. Run the query. Does this provide the results
you were looking for?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

I have a field, called [Mark], that can be anywhere from 0 to 100. I would
like a query field, called [vangrade], to operate the following on the number:

If [Mark] is from 0 to 64, change to 0.5
If [Mark] is from 65 to 95, apply the formula ([Mark]-55)/10
If [Mark] is from 96-100, change to 4.0

As a secondary question, besides being anywhere from 1-100, [Mark] can also
be a P or an F. Is it possible to have the P or F just stay as they are in
[vangrade] while performing the calculation above? If so, how?

Thanks,

--vanguardaccessman

 

[vanguardaccess]

Tom,

I was able to get the query to run without the determine range error (as
stated in my previous reply). However, all the vangrade fields return the
result "out of range"
--
--vanguardaccessman

Hi vanguardaccessman,

Try this to see if it works for you. First, create a new stand-alone module
and paste the code shown below into it:
___________________________

Option Compare Database
Option Explicit

Public Function DetermineResult(Mark As Variant) As Variant
On Error Resume Next

If Len(Mark & "") > 0 Then

Select Case Mark
Case "P", "F"
DetermineResult = Mark
Case "0"
DetermineResult = 0.5
Case IsNumeric(Mark) = True
Mark = CCur(Mark)
Select Case Mark
Case 0 To 64
DetermineResult = 0.5
Case 65 To 95
DetermineResult = Format(([Mark] - 55) / 10, "0.0")
Case 96 To 100
DetermineResult = Format(4, "0.0")
Case Else
DetermineResult = "Out of range."
End Select
Case Else
DetermineResult = "Out of range."
End Select
End If

End Function

___________________________

Name this module something like basCalculateResult (the important point
being to give your module a unique name that is not already the name of an
existing subroutine or function, and is not a reserved word). Compile the
code by clicking on

Debug > Compile {ProjectName}

where {ProjectName} is the name of your VBA project. Your code should
compile without any errors.

Create a new query. Dismiss the Add Tables dialog without adding any tables.
In query design view, click on View > SQL View. You should see the word
SELECT highlighted. Delete this default keyword. Copy the following SQL
statement (Ctrl C) and paste it into the SQL view (Ctrl V), replacing the
SELECT keyword:

SELECT [Mark], DetermineResult([Mark]) AS vangrade
FROM [tblMarks];

Note: You didn't give the name of your table, so I used a name of
"tblMarks". Make the appropriate substitution in the above SQL statement. You
can then switch back to the more familiar design view, if you wish, by
clicking on View > Design View. Run the query. Does this provide the results
you were looking for?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

I have a field, called [Mark], that can be anywhere from 0 to 100. I would
like a query field, called [vangrade], to operate the following on the number:

If [Mark] is from 0 to 64, change to 0.5
If [Mark] is from 65 to 95, apply the formula ([Mark]-55)/10
If [Mark] is from 96-100, change to 4.0

As a secondary question, besides being anywhere from 1-100, [Mark] can also
be a P or an F. Is it possible to have the P or F just stay as they are in
[vangrade] while performing the calculation above? If so, how?

Thanks,

--vanguardaccessman

 

[Tom Wickerath]

Hi vanguardaccessman,

I'm glad to see that you've had some success (eliminating the "Unidentified
function" error). Hmmm.....off-hand, I'm not sure why the data in your [Mark]
field is not passing the appropriate tests. I actually created a quicky
sample last night and tested it with various inputs. If you are willing to
send me a compacted and preferably zipped copy of your database, I will look
into this for you. If interested, you can find my e-mail address at the
bottom of the Contributor's page indicated below in my signature. Whatever
your decision, please do not post your e-mail address (or mine) to a
newsgroup reply. Doing so will only attract the unwanted attention of
spammers.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[vanguardaccess]

Tom,

Once again thanks a lot. After hearing that you got it to work, I tried
repeatedly. Finally, it occurred to me that the field in the table should
perhaps be changed from Number to Text. That indeed solved the problem. The
module is now working.

Unfortunately, now there is a new problem. I'm going to post it on a new
thread, however, as it does not relate to your solution. So, thanks again,
and if you can help with the other one, that would be great!

(I'm calling it complicated elements in a table.)

Thanks
--
--vanguardaccessman

Hi vanguardaccessman,

I'm glad to see that you've had some success (eliminating the "Unidentified
function" error). Hmmm.....off-hand, I'm not sure why the data in your [Mark]
field is not passing the appropriate tests. I actually created a quicky
sample last night and tested it with various inputs. If you are willing to
send me a compacted and preferably zipped copy of your database, I will look
into this for you. If interested, you can find my e-mail address at the
bottom of the Contributor's page indicated below in my signature. Whatever
your decision, please do not post your e-mail address (or mine) to a
newsgroup reply. Doing so will only attract the unwanted attention of
spammers.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Tom,

I was able to get the query to run without the determine range error (as
stated in my previous reply). However, all the vangrade fields return the
result "out of range"

 

[Tom Wickerath]

Yes, indeed, I did specify the Mark field as text. The reason I did this is
that you had included this statement in your initial post:

"As a secondary question, besides being anywhere from 1-100, [Mark] can also
be a P or an F."

There's just no (practical) way to store a P or an F in a field with a
numeric data type, especially if you have other numeric data. (One could
store the corresponding ASCII codes for P and F, which are 80 and 70,
respectively, but how would you differentiate this from a regular Mark score?
I suppose one could add some value, positive or negative, to extend it beyond
the normal range for Mark scores (0 to 100), but that's not exactly
"practical".

Anyways, I'm glad you got it to work. I can modify the function for a
numeric Mark field, if you'd prefer this, but how would you store the P and F
values?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Wickerath]

Hi Again,

and if you can help with the other one, that would be great!

(I'm calling it complicated elements in a table.)

Did you follow-through and make this new post? If so, I sure can't find it!

http://groups.google.com/groups?as_...08&as_maxd=10&as_maxm=5&as_maxy=2008&safe=off


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[vanguardaccess]

My apologies... it's posted now. I called it "complicated elements in a table"

I know its not related to the module because the same problem occurs on an
earlier version of my database (before I added the module). After your
solution, I put in Ps and Fs to the table and it returned this error. So I
tried it in an earlier version to see if it was related to the module and the
same thing happens.

Thanks again

 

[vanguardaccess]

Tom, I apologize for poorly communicating. I'm struggling here to figure
this thing out, and sometimes reply before fully thinking through the
problem. In any case, in fact I realize now that the solution to the problem
I'm having is connected to the module. I posted this same question on the
other thread, but here it is also, as it belongs here. Sorry for the
confusion.

First, I have a table with the following fields:
[ID]---[Mark]
[Mark] needs to be a text field because it can either be a number (from
1-100) or letter (P or F). It is being imported from an outside excel
spreadsheet (which needs to happen regularly), and if it is numerical the
table excludes the P's or F's.

The query that I'm running refers to a module. It turns the mark column
into a different scale (from 0-64 to 0,5, from 65-95 it applies the formula
([mark]-55)/10, from 96-100 to 4.0, and P's and F's stay as are).

I see that I'm going to have to redesign elements of my database, which is
okay. But what I need to be able to do is calculate on the text field in
both queries and reports. Is that possible? I would like it to ignore the
P's and F's, treat the numbers (from 0.5 to 4.0) that result from the module
as numbers, then calculate averages, for example.

Any help would be greatly appreciated.

 

[Tom Wickerath]

Here's a possible solution to this quandary...

Import the Marks data into a temporary table. Then run a modified version of
the DetermineResult function, such that the result is determined, as before,
but the data is appended to a different (permanent) table. The permanent
table could include two fields for recording the marks. Let's call them
NumericMark and PassFailMark. The NumericMark field could be data type
Currency (with the currency formatting removed), or Number / Single. This
field that would store numeric results calculated by the original
DetermineResult function. The PassFailMark would be a text field. The
modified DetermineResult function would be designed to append the data to the
appropriate field. This way, you store numeric data as numbers and text data
as text. Any further calculations (Average, Sum) would be based on the
NumericMark field only. That avoids messy type conversion functions where you
have to continually convert strings that could be numbers to numbers.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[vanguardaccess]

Tom, thanks a lot. Before I even try your possible solution, I'm curious
about something else. Is it possible to modify the module such that a
numerical (instead of text) format is not out of range?

--
--vanguardaccessman

Here's a possible solution to this quandary...

Import the Marks data into a temporary table. Then run a modified version of
the DetermineResult function, such that the result is determined, as before,
but the data is appended to a different (permanent) table. The permanent
table could include two fields for recording the marks. Let's call them
NumericMark and PassFailMark. The NumericMark field could be data type
Currency (with the currency formatting removed), or Number / Single. This
field that would store numeric results calculated by the original
DetermineResult function. The PassFailMark would be a text field. The
modified DetermineResult function would be designed to append the data to the
appropriate field. This way, you store numeric data as numbers and text data
as text. Any further calculations (Average, Sum) would be based on the
NumericMark field only. That avoids messy type conversion functions where you
have to continually convert strings that could be numbers to numbers.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Tom, I apologize for poorly communicating. I'm struggling here to figure
this thing out, and sometimes reply before fully thinking through the
problem. In any case, in fact I realize now that the solution to the problem
I'm having is connected to the module. I posted this same question on the
other thread, but here it is also, as it belongs here. Sorry for the
confusion.

First, I have a table with the following fields:
[ID]---[Mark]
[Mark] needs to be a text field because it can either be a number (from
1-100) or letter (P or F). It is being imported from an outside excel
spreadsheet (which needs to happen regularly), and if it is numerical the
table excludes the P's or F's.

The query that I'm running refers to a module. It turns the mark column
into a different scale (from 0-64 to 0,5, from 65-95 it applies the formula
([mark]-55)/10, from 96-100 to 4.0, and P's and F's stay as are).

I see that I'm going to have to redesign elements of my database, which is
okay. But what I need to be able to do is calculate on the text field in
both queries and reports. Is that possible? I would like it to ignore the
P's and F's, treat the numbers (from 0.5 to 4.0) that result from the module
as numbers, then calculate averages, for example.

Any help would be greatly appreciated.

 

[Tom Wickerath]

Hi Vanguardaccess,

Is it possible to modify the module such that a
numerical (instead of text) format is not out of range?

If I understand you correctly (?), you are talking about using a numeric
data type instead of a text data type (as I suggested as a part of my last
reply). The answer is yes, and in fact the function is greatly simplified in
this case.

Change the data type in the underlying table from text to number, with a
field size of Long Integer. Remove the Default Value = 0. In query design
view, right-click on the Vangard field. Select a Fixed format with one
decimal place. Save the query and close it. Then open your module and modify
it as follows:

Public Function DetermineResult(Mark As Variant) As Currency
On Error Resume Next

If Not IsNull(Mark) = True Then

Select Case Mark
Case 0 To 64
DetermineResult = 0.5
Case 65 To 95
DetermineResult = ([Mark] - 55) / 10
Case 96 To 100
DetermineResult = 4
Case Else
DetermineResult = -1
End Select

Else
DetermineResult = Null
End If

End Function


Note: In this case, I am returning a value of negative 1 for "Out of Range"
data. You can handle this is a report, using a conditional IIF statement in
the expression for a text box. Or, perhaps easier, would be to simply add a
Validation Rule in table design view for the Mark field:

Validation Rule: Between 0 And 100
Validation Text: The value you entered is out of range.

Make sure to include something in the Validation Text property, so that you
are notified if the validation rule is violated.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[vanguardaccess]

Nevermind... I got it to work, and it works beautifully. I was able to do it
through an append query to a new table, based on IIF statements. Thanks a
lot for explaining the concept, anyway. You have been very helpful.
--
--vanguardaccessman

Here's a possible solution to this quandary...

Import the Marks data into a temporary table. Then run a modified version of
the DetermineResult function, such that the result is determined, as before,
but the data is appended to a different (permanent) table. The permanent
table could include two fields for recording the marks. Let's call them
NumericMark and PassFailMark. The NumericMark field could be data type
Currency (with the currency formatting removed), or Number / Single. This
field that would store numeric results calculated by the original
DetermineResult function. The PassFailMark would be a text field. The
modified DetermineResult function would be designed to append the data to the
appropriate field. This way, you store numeric data as numbers and text data
as text. Any further calculations (Average, Sum) would be based on the
NumericMark field only. That avoids messy type conversion functions where you
have to continually convert strings that could be numbers to numbers.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Tom, I apologize for poorly communicating. I'm struggling here to figure
this thing out, and sometimes reply before fully thinking through the
problem. In any case, in fact I realize now that the solution to the problem
I'm having is connected to the module. I posted this same question on the
other thread, but here it is also, as it belongs here. Sorry for the
confusion.

First, I have a table with the following fields:
[ID]---[Mark]
[Mark] needs to be a text field because it can either be a number (from
1-100) or letter (P or F). It is being imported from an outside excel
spreadsheet (which needs to happen regularly), and if it is numerical the
table excludes the P's or F's.

The query that I'm running refers to a module. It turns the mark column
into a different scale (from 0-64 to 0,5, from 65-95 it applies the formula
([mark]-55)/10, from 96-100 to 4.0, and P's and F's stay as are).

I see that I'm going to have to redesign elements of my database, which is
okay. But what I need to be able to do is calculate on the text field in
both queries and reports. Is that possible? I would like it to ignore the
P's and F's, treat the numbers (from 0.5 to 4.0) that result from the module
as numbers, then calculate averages, for example.

Any help would be greatly appreciated.