Need Help!

M

mwaleedgul

Registered User.
Local time
Today, 08:13
Joined
Oct 20, 2017
Messages
18
I am facing an error. Using MS Access 2013

Module Code:
Public Function CalcVal(pvVal1, pvVal2, pvVal3)
Select Case True
Case pvVal3 >= 9
'add up in Value_2 and the Value_3 should be <9
If (pvVal2 + pvVal3) < 9 Then
CalcVal = pvVal2 + pvVal3
Else
CalcVal = 0
End If

Case pvVal2 >= 20
' add up in Value_1 and the Value_2 should be <20
If (pvVal1 + pvVal2) < 20 Then
CalcVal = pvVal1 + pvVal2
Else
CalcVal = 0
End If

Case Else
CalcVal = 0
End Select
End Function

When I call it in a query it give error: Undefined function CalcVal in expression.

select calcVal([field1],[field2],[field3]) from table

Please Help......
 
here, this is a wild guess.
call it in your Query:

SELECT [Field1], [Field2], [Field3], CalcVal([Field1], [Field2], [Field3], 1) As Value1, CalcVal([Field1], [Field2], [Field3], 2) As Value2, CalcVal([Field1], [Field2], [Field3], 3) As Value3 FROM yourTable;


Code: 
Public Function CalcVal(pVal1, pVal2, pVal3, pReturn As Byte) As Double
    Static strVal As String
    Static arrVal(1 To 3) As Double
    Dim dblValue As Double
    

    pVal1 = CDbl("0" & pVal1)
    pVal2 = CDbl("0" & pVal2)
    pVal3 = CDbl("0" & pVal3)

    If strVal <> pVal1 & "/" & pVal2 & "/" & pVal3 Then
        strVal = pVal1 & "/" & pVal2 & "/" & pVal3
        While pVal3 >= 9
            dblValue = pVal3 - 9
            If dblValue > 9 Then dblValue = 9
            pVal2 = pVal2 + dblValue
            pVal3 = pVal3 - 9
        Wend
        While pVal2 >= 20
            dblValue = pVal2 - 20
            If dblValue > 20 Then dblValue = 20
            pVal1 = pVal1 + dblValue
            pVal2 = pVal2 - 20
        Wend
        arrVal(1) = pVal1
        arrVal(2) = pVal2
        arrVal(3) = pVal3
    End If
    CalcVal = arrVal(pReturn)
End Function
 
Last edited:
Sir facing the same error...

Please see the pics below

open



open


open


Please help...
 
Did you name the module the same as the function?
Access won't recognize the function if it is the same.
 
I test the functions and it's running fine, no errors.
 
I test the functions and it's running fine, no errors.
 
First question: In the table, what are the data types for fields pvVal1, pvVal2, and pvVal3?

Second question: When you defined the function, did you define it in a general module or a class module?
 
Data tyoe of these values are numbers and 2nd i defined a general module sir... As I am not very much familiar with ms access... So if it is possible to send me the db file? I will be grateful to you... Thanks for reply sir...
 
mwaleedgul said:
Yes I did the same
Click to expand...

You can't do that, as Peter said...it drives the Access Gnomes crazy and they don't know what you mean! Rename the Module to something other than the name of the Function.

Linq ;0)>
 
OK, ArnelGP's suggestion includes some things that wont work correctly for numeric values. You are using SELECT CASE in a questionable way here... I offer the function with changes made in GREEN.

Code: 
Public Function CalcVal(pvVal1, pvVal2, pvVal3) [COLOR="Red"]As ????[/COLOR]

  [COLOR="seagreen"]If [/COLOR]pvVal3 >= 9 Then    'add up in Value_2 and the Value_3 should be <9

    If (pvVal2 + pvVal3) < 9 Then
      CalcVal = pvVal2 + pvVal3
    Else
      CalcVal = 0
    End If

  [COLOR="seagreen"]ElseIf[/COLOR] pvVal2 >= 20 [COLOR="seagreen"]Then[/COLOR]     ' add up in Value_1 and the Value_2 should be <20
    If (pvVal1 + pvVal2) < 20 Then
      CalcVal = pvVal1 + pvVal2
    Else
      CalcVal = 0
    End If

  Else
    CalcVal = 0
  End If

End Function

One thing I see that could lead to an issue is that you don't have a data type for the function (see what I put in red letters). You need to have the same data type for the function as the data type of pvVal1, pvVal2, and pvVal3, whatever type they are. If you don't put the correct data type, then I believe the default is a Variant and I am not sure what SQL will do with that.

The other thing is that your SELECT CASE True is essentially a non-starter. That SELECT was not based on a variable so it would never work quite right. And the things on which you tried to base the CASE were not variables named in the SELECT CASE, so that code, whatever it did, would have been invariant (i.e. always return the same result regardless of inputs.)
 
Sir Many thinks for you help its work now but I have some other issues if you can solve please...

When pvVal3 increase from 9 then it should plus on in pvVal2 like below

pvVal3=10 and pvVal2=12 it should be like pvVal3=1 and pvVal2=13

same case for pvVal2 if should be (1) add up in pvVal1 i.e. if pvVal1=23 and pvVal2=22 and pvVal1 should be 24 and pvVal2 should be 2... Thanks...
 
Check your email i sent you.
 
Hi sir...
Need help here...
I need function in ms access is below...

This formula used here in our area for land measurements.
K = Any number no limite for K
M= 20 (20 M is equal to 1 K)
S= 9 (9 S is equal to 1 M)
K, M and S
If S is greater than and equal to 9 then 1 add up in M
For example.
K=10
M=12
S=10 (S will be always <9 if it is 9 then 1 add up in M and M will be 13 and S will be 0)
M will be always <20 if it is 20 then 1 add up in K and K will be 11 and M will always be less than 20...
 
It is clear that English is not your first language. Unfortunately, that makes your explanations not so clear. I do not wish to say that you can't get help, but I cannot yet understand your posts on the topic of what happens when your pvVal3 > 9 and the post involving K, M, and S. If I can't understand the requirement, I can't offer an answer.
 
Yes you are right sir... I am unable to convey my question properly... I am trying to ask my question in better way... Thanks.
 
Here, try this one. Check finalquery. Chk the module
 

Attachments

Sarsahi = 9

Marla = 20

Kanal = Any Digit (No limte) if we use the above mentioned Values then the answer will be like this.

Kanal = 1

Marla = 1

Sarsahi = 0

These are the Land measurement units used in our area.

For Example I need to add the values.

Kanal Marla Sarsahi
20 18 8
23 13 7
--------------------------------------
43 31 15 (In case we plus and sum the values the Answer will be this)

But This answer should be like this.

44 12 6 (Answer I need)


Note:
Sarsahi never be increased from 9 it will be always less then 9 and if It is 9 then we call it 1 Marla and we write it 1 Marla and 0 Sarsai.

Marla never be increased from 20 it will be always less then 20 and if It is 20 then we call it 1 Kanal and we write it 1 Kanal and 0 Marla

I hope you can understand now my question I am reffering you some websites where you can get better idea...

Websites are:

https://www.easycalculation.com/unit-conversion/Kanal-kanal-Marla-marla.html

https://en.wikipedia.org/wiki/Marla_(unit)

https://en.wikipedia.org/wiki/Kanal_(unit)
 
You must log in or register to reply here.

Similar threads

A
Filter Records
Replies
1
Views
244
June7
June7
C
control variable
2
Replies
30
Views
601
cheekybuddha
cheekybuddha
Mike Krailo
Solved Need help with before update code logic?
Replies
12
Views
379
Mike Krailo
Mike Krailo
mike60smart
Syntax
Replies
8
Views
288
Mark_
M
K
How can I add this code to the storage location of my file before scanning and then convert it to a PDF file?
2
Replies
25
Views
987
The_Doc_Man
The_Doc_Man

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom