UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> User Defined Function Works But Not When Called From A Query Or Through A SQL Statement., Access 2013    
 
   
Saintor
post Nov 29 2017, 08:52 AM
Post#1



Posts: 157
Joined: 7-November 07



The function is

CODE
Function MonthSupplyWh1(SC1 As String, Wh1 As String, SC2 As String, Wh2 As String) As Double

Dim rst1 As DAO.Recordset
Dim SC As String
Dim Wh As String



On Error Resume Next

Debug.Print SC1, Wh1, SC2, Wh2

If nz(SC2, "") > " " And nz(Wh2, "") > "0" Then

    SC = nz(SC2, "")
    Wh = nz(Wh2, "")
    
ElseIf Wh1 > "0" Then
    
    SC = nz(SC1, "")
    Wh = nz(Wh1, "")
    
Else
    
    MonthSupplyWh1 = 999.9
    Exit Function
    
End If


Set rst1 = CurrentDb.OpenRecordset("SELECT * FROM vWh_Q WHERE Stockcode='" & SC & "' AND Warehouse='" & Wh & "'")

If rst1.EOF Then
    rst1.Close
    Set rst1 = Nothing
    MonthSupplyWh1 = 999.9
    Exit Function
    
End If


rst1.MoveFirst

If rst1!Demand > 0 Then MonthSupplyWh1 = Format(rst1!QtyOnHand, "0.0") / rst1!Demand Else MonthSupplyWh1 = 999.9

rst1.Close
Set rst1 = Nothing

End Function


When fired from the debug window, it always work as expected.

I tried to include it as a calculated field in query. It says "#Error", that's it. I added nz() a few places but that didn't change a thing.

As I couldn't make it work, I changed the query to create a temporary table and after populated, I update the field (number, double) with the function. No luck. When I do it this way, it tells me that there is a key or type violation, no more detail. There is no restriction on the field (nulls are allowed).
Go to the top of the page
 
GroverParkGeorge
post Nov 29 2017, 09:04 AM
Post#2


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


I might be useful to see the SQL in that query where you are getting the error.

"When I do it this way, it tells me that there is a key or type violation, no more detail."

It would also be useful to see the datatypes in the table(s) used in that query because it is being reported as a datatype violation.

--------------------
Go to the top of the page
 
HairyBob
post Nov 29 2017, 10:22 AM
Post#3



Posts: 875
Joined: 26-March 08
From: London


You say that the field allows nulls, yet your parameters are declared as strings - a string data type cannot be assigned null.

Maybe you need to change your parameter declarations from 'As String' to 'As Variant'.

HTH...

Hairy.

--------------------
If it was easy, It wouldn't be fun!
Go to the top of the page
 
Saintor
post Nov 29 2017, 11:45 AM
Post#4



Posts: 157
Joined: 7-November 07



QUOTE
You say that the field allows nulls, yet your parameters are declared as strings - a string data type cannot be assigned null.

Maybe you need to change your parameter declarations from 'As String' to 'As Variant'.

HTH...

Hairy.


Problem so solved, many thanks!
Go to the top of the page
 
HairyBob
post Nov 29 2017, 11:54 AM
Post#5



Posts: 875
Joined: 26-March 08
From: London


yw.gif

--------------------
If it was easy, It wouldn't be fun!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 06:53 AM