Type Mismatch in function?

L'apprentis

Redcifer
Local time
Today, 05:26
Joined
Jun 22, 2005
Messages
177
Hello,
I am writting my first function and I am facing a mismatch error, I don't know why.
The function is quite long but I have reduced it to the following code and I am still getting the mismatch error? Is there any error in the following code? I have also check the format of tablconst where NortonFactor is set as a double,

Code:
Public Function FctCastPrice(Weight As Double) As Currency
'Code to get the Price of casting just by knowing its weight and material [low carbon (LC) or stainless Steel (SS)]

'list of variable that are going to be equal to the constant define in tableConst
Dim rs As ADODB.Recordset
Dim dNortonFactor As Double

' Set variable with value from tableconst
Set rs = CurrentDb.OpenRecordset("tblConst")
dNortonFactor = rs!NortonFactor
Set rs = Nothing

If Forms!frmFctBuilding.TxtMaterial = "Low Carbon" Then
FctCastPrice = dNortonFactor * Weight
End If

End Function
 
Last edited:
Maybe because you are trying to return currency, but dNortonFactor * Weight are doubles.
 
Yes, sorry...I have changed it and I still get mismatch error
Is it not possible to multiply a currency by a "constant"

Public Function FctCastPrice(Weight As Double) As Currency
'Code to get the Price of casting just by knowing its weight and material [low carbon (LC) or stainless Steel (SS)]

'list of variable that are going to be equal to the constant define in tableConst
Dim rs As ADODB.Recordset
Dim dNortonFactor As Double
Dim IniLCPrice As Currency

' Set variable with value from tableconst
Set rs = CurrentDb.OpenRecordset("tblConst")
dNortonFactor = rs!NortonFactor
Set rs = Nothing

IniLCPrice ="3"
If Forms!frmFctBuilding.TxtMaterial = "Low Carbon" Then
FctCastPrice = IniLCPrice*dNortonFactor * Weight
End If

End Function
 
IniLCPrice ="3"
That variable is currency.

I don't believe you need to have the 3 set in quotes. To my knowledge quotes are only used for strings, not int.

Perhaps removing the "" from around the 3.

If I am incorrect on that, I am sure someone will correct me.
 
Last edited:
try
Dim rs As dao.Recordset

You may need to set a reference to DAO though

Peter
 
Can you explain where you are calling this function from?
 
I am by no means an expert on record sets, but something doesn't look right. I am listing below how I was taught to set up record sets.

Dim cnCurrent as ADODB.Connection
Set cnCurrent = CurrentProject.Connection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

Then you need to open the record set, retrieve the data you want and then close it back.
 
To Selenau837:
Thanks for your replies,
This is how i call the function for the moment, using a command button,

Code:
Private Sub CmdCalculate_Click()
On Error GoTo Err_CmdCalculate_Click
Weight = TxtWeight
TxtCastingPrice = FctCastPrice(Weight)
Exit_CmdCalculate_Click:
    Exit Sub
Err_CmdCalculate_Click:
    MsgBox Err.Description
    Resume Exit_CmdCalculate_Click
End Sub

I am not the king of using recordset in vba as well, I am just starting to learn how to use vba and I am far of being confident but is your suggestion not use in the situation where you want to add a set of record in a table?

Thanks Bat 17,
How do you set a reference to DAO?
 
I do not see where Weight was Declared in your click event. I know it was declared in your fuction when it was passed, but why not try Declaring it in your click event, instead of letting it be a variant. Can't hurt to try.

AS for my record set, I am not sure. I just know that is why I have in my notes for record sets, and will now do further research. Thank you.

Secondly, Weight = TxtWeight--> That should be txtWeight.value if you are referencing a object on your form.
 
Last edited:
in any module Tools>references...... scroll dowm the list and look for Microsoft DAO 3.6 Libary, or something like that :)

Peter
 
Cheers Bat 17. Openind the DAO Library allowed my code to work.
Thanks for your reply as well Selenau 837.

Now that I have been able to implement my code, I am facing a new problem:
1 of the constant used for the calculation (CurYNickel) is deducted from an other constant CurXnickel as shown on the following code:

Code:
Public Function FctCastPrice(Weight As Double) As Currency
'Code to get the Price of casting just by knowing its weight and material [low carbon (LC) or stainless Steel (SS)]


'list of variable the are going to be equal to the constant define in tableConst
Dim rs As DAO.Recordset
Dim curNickelPriceTonne As Currency
Dim dExchangeRate As Double
Dim curLCScrap As Currency
Dim curSSScrap As Currency
Dim dNortonFactor As double
Dim IniLCPrice As Currency

' Set variable with value from tableconst
Set rs = CurrentDb.OpenRecordset("tblConst")
curNickelPriceTonne = rs!NickelPriceTonne
dExchangeRate = rs!ExchangeRate
curLCScrap = rs!LCScrapSUrcharge
curSSScrap = rs!SSScrapSurcharge
dNortonFactor = rs!NortonFactor
Set rs = Nothing

'Variable (Ynickel) that give the value added to each stainless steel casting
'Deducted from Variable (Xnickel):
Dim curMin As Currency
Dim curMax As Currency
Dim curXnickel As Currency
Dim curYnickel As Currency

[COLOR="Red"]curXnickel = curNickelPriceTonne / dExchangeRate

curMin = "6001": curMax = "7000"
If curMin < curXnickel < curMax Then
curYnickel = "0.12"
End If
curMin = "7001": curMax = "8000"
If curMin < curXnickel < curMax Then
curYnickel = "0.23"
End If
curMin = "8001": curMax = "9000"
If curMin < curXnickel < curMax Then
curYnickel = "0.35"
End If
curMin = "9001": curMax = "10000"
If curMin < curXnickel < curMax Then
curYnickel = "0.46"
End If
curMin = "10001": curMax = "11000"
If curMin < curXnickel < curMax Then
curYnickel = "0.58"
End If
curMin = "11001": curMax = "12000"
If curMin < curXnickel < curMax Then
curYnickel = "0.69"
End If[/COLOR]

'Results
If Forms!frmFctBuilding.TxtMaterial.Value = "Low Carbon" Then
FctCastPrice = Forms!frmFctBuilding!IniLCPrice * dNortonFactor * Weight
ElseIf Forms!frmFctBuilding.Material = "Stainless Steel" Then
FctCastPrice = (Forms!frmFctBuilding.TxtIniSSPrice * dNortonFactor * Weight) + 1 + curYnickel
End If

End Function

I am having some trouble with the part of the code highlited in red, I don't know if the code is written properly because only the last value seems to be selected (ie: curYnickel="0.69").
So my first question is: How should I rewrite that little bit of code to have the function working properly?
My second question which would cover the first question as well is a bit more complex: Would it be possible to get curYNickel calculated directly from values stored in a table. If I had a TblNickelPrice with the field NickelPriceID, curMin, curMax, curYnickel and each record filled with the data that you can see highlighted in red in the above code? By knowing CurXnickel, the record where curXNickel is included between curMin and curMax would give the correct value for curYNickel.
 
Code:
curXnickel = curNickelPriceTonne / dExchangeRate

curMin = "6001": curMax = "7000"
If curMin < curXnickel < curMax Then
curYnickel = "0.12"
End If
curMin = "7001": curMax = "8000"
If curMin < curXnickel < curMax Then
curYnickel = "0.23"
End If
curMin = "8001": curMax = "9000"
If curMin < curXnickel < curMax Then
curYnickel = "0.35"
End If
curMin = "9001": curMax = "10000"
If curMin < curXnickel < curMax Then
curYnickel = "0.46"
End If
curMin = "10001": curMax = "11000"
If curMin < curXnickel < curMax Then
curYnickel = "0.58"
End If
curMin = "11001": curMax = "12000"
If curMin < curXnickel < curMax Then
curYnickel = "0.69"
End If

It appears you are resetting each variable each time you complete one of the IF statements.

Here is the code as I understand it written Psuedo code



curMin = 6001 and cur Max 7000
IF curmin less than curXnickle and less than cur Max
then
curyNickel is .12
next If

curMin = "7001": curMax = "8000"
If curMin less than curXnickel and less than curMax Then
curYnickel = "0.23"
end If

Ok, go to next IF

curMin = "8001": curMax = "9000"
If curMin lexss than curXnickel and less than curMax Then
curYnickel = "0.35"
End If
Next IF

Continues all the way down to

curMin = "11001": curMax = "12000"
If curMin less than curXnickel and less than curMax Then
curYnickel = "0.69"
End If

Therefore, it will always pick the 0.69. Because it performs each IF statement all the way down, and the last IF statment ends with curYnickel equaling 0.69.

That is my udnerstanding of the code you have highlighed in red. Is that what you ment for it to do?

The best way to see how your IF statments are working and why you keep getitng the same result is to break your code on that IF statment and then step through it so you can see how it is working. That is how I do it when I can't figure out why a piece of my code isn't working properly.

Secondly,

If that is not your intent, perhpaps a Select Case or Nested If statments might work best.

HTH
 
Yes, I see what you are saying and I think that you are right. How could I apply the Select case structure to that situation? What do you think about using a table as I have suggested in my last post? Do you think it's possible?
 
With Select Case you have to becareful because it will select the 1st True reponse. Order is very important. I have listed below the structure for a Select Case statement

Select Case (value)
Case (possible value1)
Case (possible value 2)
Case (possible value 2)
End Select

Even an If..ElseIF statement might work better

It works the same way, in that order is important. Is it possible for curXNickle to be below 6001 or above 12000?

If not then perhaps use

If curXNickle < = 7000 then
curYNickle = 0.12
ElseIF curXNickle < = 8000 then
curYNickle = 0.23
ElseIF curXNickle < = 9000 then
curYNickle = 0.35
ElseIF curXNickle < = 10000 then
curYNickle = 0.46
ElseIF curXNickle < = 11000 then
curYNickle = 0.58
else
curYNickle = 0.69
end IF

What the above code does it, it takes curXNickle and goes from top to bottom checking to see if it falls in that paramater. Once it hits the first true answer, it then selects that curYNickle and then leaves that IF statement.

Try using it and then step through the code that way you can see it work.


As for your question about the table, I am not too sure at this moment. I will have to review that a bit more.

HTH
selena
 
Cheers Selenau, it seems to be working,
Here is a sample with the code, I think I could still improve it by making the function work by using values taken from tables directly but I am not sure how to do it.
 

Attachments

Welcome,

Perhaps you can post in the tables portion of the forum, and maybe one of the Access gurus can help out.

I am still thinking about it, and if you don't have a response by the time I come up with something, I will post it here for you.

Good luck!
 
Hi L'Appentis,

From what I can see quickly looking at your code, your recordset is only getting the first value. You need to loop through your recordset to carry out an action for each record. You would do something like this;

Code:
Dim rs as ADODB.Recordset
Dim cnn as Connection
Set cnn = CurrentProject.Connection
rs.Open "SELECT * FROM tblTable"
Do While Not rs.EOF
   [Carry out your Actions for each record here]
   rs.MoveNext
Loop
rc.Close
Set rs = Nothing
Set cnn = Nothing

Please excuse me if I have got this wrong, brain has finished for Xmas already I think...
 
I have changed my code slighly by adding a looping funcion to check the record of 1 of my table as ReclusiveMonkey as suggested, I get the error :"object invalid or no longer set", did I forget something?

Code:
Public Function FctCastPrice(Weight As Double) As Currency
'Code to get the Price of casting just by knowing its weight and material [low carbon (LC) or stainless Steel (SS)]

Dim rs As DAO.Recordset
Dim cnn As Connection
'list of variable the are going to be equal to the constant define in tableConst
Dim curNickelPriceTonne As Currency
Dim dExchangeRate As Double
Dim curLCScrap As Currency
Dim curSSScrap As Currency
Dim dNortonFactor As Double
Dim IniLCPrice As Currency

' Set variable with value from tableconst
Set rs = CurrentDb.OpenRecordset("tblConst")
curNickelPriceTonne = rs!NickelPriceTonne
dExchangeRate = rs!ExchangeRate
curLCScrap = rs!LCScrapSUrcharge
curSSScrap = rs!SSScrapSurcharge
dNortonFactor = rs!NortonFactor
Set rs = Nothing

'Variable (Xnickel) that give the value added to each stainless steel casting
'Deducted from Variable (Ynickel):
Dim curMin As Currency
Dim curMax As Currency
Dim curXnickel As Currency
Dim curYnickel As Currency


[COLOR="Red"]curXnickel = curNickelPriceTonne / dExchangeRate

Set cnn = CurrentProject.Connection
Set rs = CurrentDb.OpenRecordset("tblNickelPrice")

rs.MoveFirst                                     'Locate first record
Do Until rs.EOF                                 'begin the loop

If rs!Min < curXnickel < rs!max Then     
curYnickel = rs!nickelprice
rs.Close

Else

rs.MoveNext

End If

Loop                   'End of loop

rs.Close               'Close Table

Set rs = Nothing
Set cnn = Nothing[/COLOR]

If Forms!frmFctBuilding.TxtMaterial.Value = "Low Carbon" Then
FctCastPrice = Forms!frmFctBuilding!IniLCPrice * dNortonFactor * Weight
ElseIf Forms!frmFctBuilding.Material = "Stainless Steel" Then
FctCastPrice = (Forms!frmFctBuilding.TxtIniSSPrice * dNortonFactor * Weight) + 1 + curYnickel
End If

End Function
 
The reason that you are getting the error is because in this section:

Code:
Do Until rs.EOF                                 'begin the loop

If rs!Min < curXnickel < rs!max Then     
  curYnickel = rs!nickelprice
  rs.Close

Else

  rs.MoveNext

End If

Loop

If your 'If' statement is true you are closing the rs, then looping through it again. change the rs.Close to 'Exit Do' so it looks like:

Code:
Do Until rs.EOF                                 'begin the loop

  If rs!Min < curXnickel < rs!max Then     
    curYnickel = rs!nickelprice
    Exit Do
  Else
    rs.MoveNext
  End If

Loop
 

Users who are viewing this thread

Back
Top Bottom