Type Mismatch in function?

[reclusivemonkey]

Ok, firstly I've only used ADO, not DAO so I can't advise on that syntax. Its not the same, so if you use the DAO method, then syntax will be different. In the first recordset, again you don't seem to be moving through the recordset. Whether or not that makes a different if you only have one record in there (which I presume there is in tblConst) I don't know.

You can check this simply by entering

Debug.Print curNickelPriceTonne

Just before your red code

 

[Bat17]

I dont think this construct works in Access.
If rs!Min < curXnickel < rs!max Then
curYnickel = rs!nickelprice

Try
If rs!Min < curXnickel and curXnickel < rs!max Then
curYnickel = rs!nickelprice

HTH

Peter

 

[L'apprentis]

Thank you so much for you r patience and your time guys, I have done the modification and I get a different error which seems a bit odd to me: "Invalid use of Null", any ideas why?

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 curYnickel As Currency
Dim curXnickel As Currency

[COLOR="red"]Debug.Print curNickelPriceTonne[/COLOR]

curXnickel = curNickelPriceTonne / dExchangeRate

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

[COLOR="Red"]rs.MoveFirst       'set current index
Do Until rs.EOF    'begin the loop
If rs!Min < curXnickel And curXnickel < rs!max Then    'Carry out your Actions for each record here
   curYnickel = rs!nickelprice
   Exit Do
Else
   rs.MoveNext
End If
Loop          'End of loop[/COLOR]
Set rs = Nothing
Set cnn = Nothing

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

 

[Bat17]

I am not sure where the error is occuring. if NickelPriceTonne is null in the table you should get the error at
curNickelPriceTonne = rs!NickelPriceTonne
if NickelPriceTonne is a value in the table then
Debug.Print curNickelPriceTonne
should not give you an error.

Peter

 

[reclusivemonkey]

Thank you so much for you r patience and your time guys, I have done the modification and I get a different error which seems a bit odd to me: "Invalid use of Null", any ideas why?

No offence, but the more I look, the more I get confused. As I said I use ADO, so can't advise where your code is going wrong. However, I have recreated this with ADO, and I've at least got the first part working. I made a quick tblConst with just the five fields you list in the first part. I changed to a sub just so I could easily check it was working. I will get the later part in a minute or two. So far this works fine;

Sub TestFunctionCall()

[b]Dim rs As New ADODB.Recordset[/b]
Dim cnn As Connection
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 cnn = CurrentProject.Connection
' Set variable with value from tableconst
[b]rs.Open "SELECT * FROM tblConst", cnn, adOpenStatic, adLockOptimistic
Do While Not rs.EOF
    curNickelPriceTonne = rs(0)
    dExchangeRate = rs(1)
    curLCScrap = rs(2)
    curSSScrap = rs(3)
    dNortonFactor = rs(4)
    rs.MoveNext
Loop[/b]

Debug.Print curNickelPriceTonne

rs.Close
Set rs = Nothing
Set cnn = Nothing

Personally, I would split this into two functions to make this a little easier. I am looking at the second part now.

 

[reclusivemonkey]

As far as I can tell, there is no Min or Max property for a recordset. To find the minimum and maximum you are going to have to loop through, checking for values as you go. Can you zip this DB up and post it in the thread, might be easier for me to get it working for you...

 

[L'apprentis]

Reclusive Monkey, here is a sample with a form that shows all the data needed in the code and a calculation button. As you can see Min and Max are defined in tblNickelPrice. I am pretty sure that the first part of my code is working because if you go through the record and the item is made of Low Carbon, the code is working. The code doesn't work only if the item is in stainless steel where the bit of code highlighted in red is needed.

 

[reclusivemonkey]

Think its working

Try this; I seems to work and I get a number, but whether its the right answer, I have NO idea!

 

[L'apprentis]

Eh....You're not going to believe it but I was getting the same "Invalid use of Null" error. I then realised that I was only getting the error when the weight looked up in the initial price table was not a multiple of 0.5 or 1.0.Yes...I kept getting the error because the weight in the table was not set to double but single...I think the code is working now but I am still going to see if I can enhance it by getting the initial price directly from a third table.
Reclusivemonkey, thanks a lot for your time, your help is much apreciated, I'll get you a pint if one day I'll go down to west YorkShire...

 

[reclusivemonkey]

Eh....You're not going to believe it but I was getting the
same "Invalid use of Null" error.

I got this as well, but only when I went to the second record in your form. When I try to go past record 1 in any of the subforms or the main form, I get an error message. I'll leave that one to you ;-)

I then realised that I was only getting the error when the weight looked up in the initial price table was not a multiple of 0.5 or 1.0.Yes...I kept getting the error because the weight in the table was not set to double but single...I think the code is working now but I am still going to see if I can enhance it by getting the initial price directly from a third table.

I was a bit lost as to what you were doing on the form to be honest. If you have any trouble with variables, you could always set them to variant which I think is a generic holding type you can put anything in. I do think the code is OK now, so hopefully you can tinker with the form and it will hold together...

Reclusivemonkey, thanks a lot for your time, your help is much apreciated, I'll get you a pint if one day I'll go down to west YorkShire...

No problem. Pint of Hoegaarden for me thanks!

 

[L'apprentis]

I don't know if there is much point but here is my final code with a working demo. Only weight and material are needed, all the other different variable are taken from three different tables using DAO.

Public Function FctCastPrice(Weight As Double, Material As String) 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

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




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

curXnickel = curNickelPriceTonne / dExchangeRate
Set cnn = CurrentProject.Connection
Set rs = CurrentDb.OpenRecordset("tblNickelPrice")
rs.MoveFirst           'set current index
Do Until rs.EOF        'begin the loop
If rs!Min < curXnickel And curXnickel < rs!max Then    'Carry out your Actions for each record here
   curYnickel = rs!nickelprice
   Exit Do
Else
   rs.MoveNext
End If
Loop                   'End of loop
rs.Close
Set rs = Nothing
Set cnn = Nothing



'get the initial price of casting depending on material and weight from TblIniPrice
'Set value to InilCPrice or IniSSPrice
Dim CurIniLCPrice As Currency
Dim CurIniSSPrice As Currency
Set cnn = CurrentProject.Connection
Set rs = CurrentDb.OpenRecordset("tblIniPrice")
rs.MoveFirst    'Set current index
Do Until rs.EOF  'Begin the loop
   If Material = "Low Carbon" And Weight = rs!Weight Then
      CurIniLCPrice = rs!IniLCPrice
      Exit Do
   ElseIf Material = "Stainless steel" And Weight = rs!Weight Then
      CurIniSSPrice = rs!IniSSPrice
      Exit Do
   Else
      rs.MoveNext
   End If
Loop
rs.Close
Set rs = Nothing
Set cnn = Nothing

'CALCULATION/RESULTT:
' Give price is casting is Low Carbon
If Material = "Low Carbon" Then
FctCastPrice = CurIniLCPrice * dNortonFactor * Weight
' Else give price if casting is stainless steel
ElseIf Material = "Stainless Steel" Then
FctCastPrice = (CurIniSSPrice * dNortonFactor * Weight) + 1 + curYnickel
End If

End Function

Thanks again to Selenau 837, Bat 17, reclusivemonkey, nateobot

 

[Bat17]

A suggestion
Change TxtWeight to a combo box based on TblIniPrice so that only valid values can be used, similarly for other fields such as ‘Material’ which need a restricted choice.

Peter