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
> Excel.adodb.connection.execute -2147217900 Undefined Function 'nz' In Expression., Access 2013    
 
   
pacala_ba
post Mar 12 2018, 08:13 AM
Post#1



Posts: 631
Joined: 19-March 09
From: Europe, SLOVAKIA, Bratislava


Hi
i am unable to use NZ in SQL command... pullhair.gif
I want to update MyTable in TMP.ACCDB through Excel.ADODB.Connection.Execute
CODE
' EXCEL.MODULE
Set cnn0 = CreateObject("ADODB.Connection")
cnn0.Open "Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database Password=***;Data Source=C:\TMP.ACCDB;" & _
"Mode=16;Extended Properties='';Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1"
cSQL = "UPDATE [MyTable] SET [field1]= NZ([field1]);"
cnn0.Execute cSQL, i_EXEC, 1    ' adCmdText=1
....ERROR


-2147217900 Undefined function 'NZ' in expression. iconfused.gif

I am EXCEL.2010 and ACCESS.2013 confused.gif
CODE
cSQL = "UPDATE [" & gkc_CON_AOS_T37 & "] SET [SB0]= Trim0([SB0]);"
cnn0.Execute cSQL, i_EXEC, 1    ' adCmdText=1
....ERROR
...

-2147217900 Undefined function 'Trim0' in expression. iconfused.gif

TRIM0...
CODE
Private Function Trim0(cString As String) As String
On Error Resume Next
Dim lcXUser As String, lnXUAsk As Long, lnPos As Long
Const lcXName = "Trim0"
lnPos = InStr(1, cString, vbNullChar)
If (lnPos > 1) Then Trim0 = Mid$(cString, 1, lnPos - 1)
End Function

...
the same for udf function pullhair.gif
CODE
My_func( a1 as string, a2 as string)
cSQL = "UPDATE myTable] SET [SB0]= My_func([SB0],[SB1]  );

This post has been edited by pacala_ba: Mar 12 2018, 08:27 AM

--------------------
Go to the top of the page
 
HairyBob
post Mar 12 2018, 08:26 AM
Post#2



Posts: 987
Joined: 26-March 08
From: London, UK


Hi,

Apparently, the second parameter of the Nz() function must be passed if used in a query:

https://support.office.com/en-ie/article/nz...0a-7fd9f4c69b6c

As far as your Trim0() function is concerned, it's declared as Private - you need to declare it as Public if you're calling it from an SQL statement; however, I'm not sure what you're trying to achieve with it (you can use the built-in Trim() function in an SQL statement).

HTH...

Hairy.
This post has been edited by HairyBob: Mar 12 2018, 08:26 AM
Go to the top of the page
 
pacala_ba
post Mar 12 2018, 08:34 AM
Post#3



Posts: 631
Joined: 19-March 09
From: Europe, SLOVAKIA, Bratislava


Hi, thx
Public or Private ...the same wrong as before.... pullhair.gif

i tried separate NZ outside of string , but no success...
many variants does not work...
cSQL = "UPDATE [tbl1] SET [SB0]= " & NZ(" & [SB0] & " ) ;"

I want to use................ My_Func(a,b,c) lightbulb.gif
This post has been edited by pacala_ba: Mar 12 2018, 08:36 AM

--------------------
Go to the top of the page
 
LPurvis
post Mar 12 2018, 09:49 AM
Post#4


UtterAccess Editor
Posts: 16,271
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

A couple of things.
Firstly, Nz isn't a VBA function, it's a member of the Access application model. You are running this code in Access? (I know the forum says Access 2013, but you also refer to: Excel.ADODB.Connection

Secondly, you can't use functions in expressions that aren't part of the shared library. (Jet/ACE's expression service differs depending upon from where it's created. The Access OM and external VBA differs.)
In an OLEDB query, you can't use Nz or other non-included functions.

You can switch it out for an Immediate If statement though. That's actually a better option really anyway. (It preserves data type.)

Cheers

--------------------
Go to the top of the page
 
pacala_ba
post Mar 12 2018, 10:06 AM
Post#5



Posts: 631
Joined: 19-March 09
From: Europe, SLOVAKIA, Bratislava


Hi LPurvis, thx
You are running this code in Access? ....NO
...in EXCEL.2010, Excel.ADODB.Connection
I am updating access table in tmp.accdb ...through...EXCEL.ADODB

In an OLEDB query, you can't use Nz or other non-included functions.
....seems to be true cryhard.gif
i am now updating table with ADODB.RECORDSET.update command... yayhandclap.gif , but needs more code then SQL

QUOTE
You can switch it out for an Immediate If statement though. That's actually a better option really anyway. (It preserves data type.)

...?, what do you mean

--------------------
Go to the top of the page
 
LPurvis
post Mar 12 2018, 10:35 AM
Post#6


UtterAccess Editor
Posts: 16,271
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

>> You are running this code in Access? ....NO
>> ...in EXCEL.2010, Excel.ADODB.Connection
>> I am updating access table in tmp.accdb ...through...EXCEL.ADODB

As I'd expected. That you're connecting to an Access database file, won't change that you're running Excel VBA. So Nz is completely off the table. But it wouldn't actually be available anyway, the OLEDB provider doesn't support it.


>>>> You can switch it out for an Immediate If statement though. That's actually a better option really anyway. (It preserves data type.)
>> ...?, what do you mean

Well, you were trying to execute the following:
cSQL = "UPDATE [MyTable] SET [field1]= NZ([field1])"
That's functionally equivalent to:
cSQL = "UPDATE [MyTable] SET [field1]= IIF([field1] Is Null, "", [field1])"

That's a guess though, as you were using the default function of Nz. (I feel it's much better practice to always provide the second parameter for self-documentation and disambiguation of execution.)
If you were operating on a number field and wanted a zero entered, then
cSQL = "UPDATE [MyTable] SET [field1]= IIF([field1] Is Null, 0, [field1])"

That said, the query itself looks to me as if it should be written differently.
You're wanting to change any field1 value where it is currently Null to zero (or zero length string)
That's just:
cSQL = "UPDATE [MyTable] SET [field1]= 0 WHERE [field1] Is Null"

Cheers

--------------------
Go to the top of the page
 
pacala_ba
post Mar 13 2018, 04:16 AM
Post#7



Posts: 631
Joined: 19-March 09
From: Europe, SLOVAKIA, Bratislava


Hi, thx
ok, the BEST would be to use MY_NZ function, but not possible... cryhard.gif
thanks.gif

--------------------
Go to the top of the page
 
LPurvis
post Mar 13 2018, 05:03 AM
Post#8


UtterAccess Editor
Posts: 16,271
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

Yes, you can't use custom functions for the same reason, they're not exposed to the expression service as called externally. (Whereas they can be if called from Access - but not necessarily.)
Glad you're off and running though.

Cheers

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    15th July 2018 - 10:08 PM