The Val function

[drisconsult]

I have a Field [GCSESUB01]. It is an alphanumeric field. The user will enter either 9, or 8, or 7, or 6, or 5, or 4, or 2, or 1 into this field.

There are nine fields altogether to which this will apply up to [GCSESUB09]

At a later stage I need to convert these fields to numeric fields using the Val function, where a total and an average can be found.

My problem is I do not know how to use the Val function for an unknown variable, such as 9 to 1.

Is there a solution to this problem?
Terence

 

[isladogs]

Apply the Val to the field rather than the value

BTW - With this new and counterintuitive grading system, GCSE grade could also be 3

 

[Pat Hartman]

Val() will extract LEADING numbers from a string so:

Val("123abc") = 123
but
Val("abc123") = null

If only numbers 1-9 are valid, use a combo or make a validation rule to accept only those characters and if you need to use them in arithmetic, why are you storing them as text?

 

[plog]

1. Structure your tables properly. When you start numbering field names, its time for a new table. Instead of 9 fields to store these values, you need a new table to which you add a new record for every entry.

2. Why not just store them as numbers if that's what they are? Store numbers as numbers, no need for Val.

3. Don't let users enter values, make users select values. Make the input a drop down, that ensures only valid values go into your tables. You can count on not being able to count on users to do what you want.


#1 and #2 are the most important, especially since you will plan on using these numeric values in aggregate. Save yourself some big headaches in the future by addressing the small headaches now. Structure properly and store as numbers

 

[Mark_]

Please note, an issue that you WILL encounter is problems with math when your data has either a NULL or a non-numeric value. Plog's suggestion avoids this. If you do not do something similar, you will have issues later when you need to work around issues you are setting yourself up for.

 

[isladogs]

As an ex-teacher, I made some assumptions in my answer.
The new GCSE 'pass' grades are 9-1 with 9 as the top grade. But if students are absent or fail to even get grade 1 then letter grades such as X or U might be entered (which count as 0).
The field should I believe contain nothing but the single number or letter.

@Pat
I disagree with your results for Val though not with the usefulness of this or otherwise.
From the immediate window....

?val(sm-000412)
-412 
?Abs(Val(sm-000412))
 412 
?val(sm000412)
 0 
?("sm-00412") 
sm-00412
?("abc123") 
abc123

 

[Pat Hartman]

Perhaps MS has changed Val() but not keep up with the documentation:

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/val-function

 

[isladogs]

I'm not aware of any change in how Val works.
The documentation doesn't say what happens when the Val function has a non numeric value at the start.
For info Val(xyz) and Val("xyz") are both 0.

If you want to extract the number part of e.g. Xyz-0012568, then using Abs is the easiest ...as long as there are no quotes

 

[theDBguy]

@Pat
I disagree with your results for Val though not with the usefulness of this or otherwise.
From the immediate window....

?val(sm-000412)
-412 
?Abs(Val(sm-000412))
 412 
?val(sm000412)
 0 
?("sm-00412") 
sm-00412
?("abc123") 
abc123

Hi. I am not sure that is a fair example. To me, sm-000412 is neither a text nor a number but maybe a variable. However, I don't understand where Access is getting its value or how it is reading something like that. For example, here's the result of my Immediate Window:

?sm-000412
-412 
?Abs(sm-000412)
 412 
?sm000412

?IsNull(sm-000412)
False
?IsNull(sm000412)
False

What's going on here? Please notice the third print line produced a blank line. But, printing the same "variable" without using Val() produced the same result as with using Val(), except for the third line, which tells me ?sm000412 results in a ZLS. So, what is Val() evaluating in the other cases?

 

[gemma-the-husky]

val reads the number. From your examples, it must ignore leading minus signs.

note that there ARE a couple of alpha expressions that still get treated as numbers by val (eg leading e's will get treated as part of an exponential expression), and therefore treated as numbers. I think there might be some other special cases also.

I just checked what val does. I got the alpha exceptions wrong, but there are a couple.

See this
https://docs.microsoft.com/en-us/of...tFrameworkMoniker-Office.Version=v16)&rd=true

 

[isladogs]

I used those values as they came from another current thread related to part numbers. The main point I was making was that the result is zero rather than null if Val cannot extract number values from a string.

As for the output of e.g. ?sm-004112, does it indicate that it is being 'evaluated' in the immediate window without using Val?
However, Val gives 0 when there is nothing to evaluate but omitting it gives null

 

[theDBguy]

I used those values as they came from another current thread related to part numbers. The main point I was making was that the result is zero rather than null if Val cannot extract number values from a string.

As for the output of e.g. ?sm-004112, does it indicate that it is being 'evaluated' in the immediate window without using Val?
However, Val gives 0 when there is nothing to evaluate but omitting it gives null

Hi Colin. Good questions. Here's some more results from the Immediate Window.

?sm-000412
-412 
?sm000412

?sm000412=""
True
?Val(null)
--Runtime error: 94 'Invalid use of Null'

So, as I thought earlier, the result of ?sm000412 is a ZLS. If you use Val() with a null value, you'll get a runtime error for "invalid use of null." So, what do you think Access "thinks" something like sm-000412 represent? Since there's no quote around it, it's not a literal string. Since there's leading alpha characters in it, it is not a number. Yet somehow, if we put a minus sign in the middle, Access "sees something." Just looking at it, I would have thought it is a variable, but I am not sure exactly what it is.

 

[isladogs]

Ah yes. ZLS not null.
Some more oddities below. Sometimes Abs & Val give the same output but not always

?Abs(null)
Null
?val(sm+004112)
 4112 
?abs(sm+004112)
 4112

 

[Galaxiom]

Ah yes. ZLS not null.
Some more oddities below. Sometimes Abs & Val give the same output but not always

?Abs(null)
Null
?val(sm+004112)
 4112 
?abs(sm+004112)
 4112

Applying a function to Null will either return Null or fail with an error if the function argument is not Variant.

sm+004112 is adding the value of a variable called sm to a number.

Variables in the immediate window are Variants. As such they are Empty until assigned a value. If treated as a number it will be zero. If treated as a string it will be ZLS.

? IsEmpty(sm)

 

[drisconsult]

In reply to the DBGuy
I had written this DB over 20 years ago when we had "O" Levels. Now we have the new 9:1 score system.
So I have converted the old system into the new system. There is no requirement to provide an average of the 9:1 scores. For me it is an after thought and it would make a nice touch to have such a system.
I already have a system in place that counts all the various scores and places them in their respective columns. Please see the screenshot that I am sending you.
Regards Terence

 

[theDBguy]

sm+004112 is adding the value of a variable called sm to a number.

Variables in the immediate window are Variants. As such they are Empty until assigned a value. If treated as a number it will be zero. If treated as a string it will be ZLS.

Thanks! This explains it for me.

 

[Gasman]

Variables in the immediate window are Variants.

So that is why if I have in the immediate window

ss= "welcome to the pleasuredome.    this is my test.     hope you like it"

and try and use

tt= propersentence(ss)

where the function is

Function ProperSentence(pstrParagraph As String) As String

I get ByRef argument Type Mismatch error?

 

[Galaxiom]

So that is why if I have in the immediate window

ss= "welcome to the pleasuredome.    this is my test.     hope you like it"

and try and use

tt= propersentence(ss)

where the function is

Function ProperSentence(pstrParagraph As String) As String

I get ByRef argument Type Mismatch error?

It is because of ByRef parameters (the default when not explicitly declared) and their arguments must be exactly the same type since they become one and the same through the call.

Declare the parameter as ByVal in the function definition and it will work fine.

 

[drisconsult]

Gentleman, my apologies

I have not made myself clear on my wish for help regarding a count and average on part of my school database.

I am sending four screenshots that will identify my problem much clearer. In short, there can be either a 9, or 8, or 7, or 6, or 5, or 4, or 3, or 2, or 1, or U entered into anyone of the ten fields.

Please see the screenshots.

Screenshot 1 shows the menu that I use for all IGCSE/GCSE details. I have skipped the Mock examinations because if I can resolve the final printout and can resolve the mock printout, the final printout be the most important.

Screenshot 2 shows the allocation of subjects to teachers.
Screenshot 3 shows the comparison with the final IGCSE/GCSE results with the mock results.

Screenshot 4 shows the final printout. Here you can see the ten boxes in which any one of nine numbers plus a U can be inserted.

So my problem is, how do I calculate one box that can have up to ten variables?

You will observe that there are ten boxes, each with a calculated number. Each box can have any one of ten entries as I have outlined above. Because there is no official requirement to create a total for the entries and then an average, I made each single entry a text file.

When I converted the text files to a single number, Access gave me an error that stated that the query was too complex to compute.
Thank you for your help
Terence

 

[isladogs]

I thought we'd already answered this before going into a digression about Val, Abs and variants

Count shouldn't be an issue as you can count text values.
For the average, you need to convert the text values to numbers.
Either use Val or CInt. The only issue is with U grades which you need to convert to zero. Use Replace or Switch for that purpose
Use a query or user defined function to do the calculations.

Off topic but couldn't help noticing your class size!