Getting error because query contains a function

#1 (**permalink**) 28-Jul-2006, 08:18 AM

Hi,

Query named "qdfProizvodiAnalitikaZadnje" is using function to define value
in a field ..

When I execute this query from code i get message "Undefined function
'FormatNumberAsString' in expression"!

Why ??

When I execute it whitin query designer everything works fine!

Here is part of code:

strSql = "SELECT qdfProizvodiAnalitikaZadnje.* " & _
"FROM qdfProizvodiAnalitikaZadnje " & _
"WHERE (((qdfProizvodiAnalitikaZadnje.VEZA)=" & VezaPodEntiteta &
"));"

Set rst = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot)

Here is SQL of query that I mention above:

SELECT ProizvodiSifreRangovi.VEZA,
ProizvodiSifreRangovi.ENTITET,
ProizvodiSifreRangovi.OPIS,
ProizvodiSifreRangovi.START,
ProizvodiSifreRangovi.END,
IIf(IsNull([qdfProizvodiSifreZadnje]![UpisanoSifri]),"0",[qdfProizvodiSifreZadnje]![UpisanoSifri])
AS UpisanoSifri,
IIf(IsNull([qdfProizvodiSifreZadnje]![SlijedeciIndex]),[ProizvodiSifreRangovi]![START],[qdfProizvodiSifreZadnje]![SlijedeciIndex])
AS SlijedeciIndex,
[ProizvodiSifreRangovi]![ENTITET] & FormatNumberAsString([SlijedeciIndex])
AS SlijedecaSifra,

Switch([SlijedeciIndex]<[ProizvodiSifreRangovi]![START],"Ne",[SlijedeciIndex]>[ProizvodiSifreRangovi]![END],"Ne",[SlijedeciIndex]>=[ProizvodiSifreRangovi]![START]
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/11122-getting-error-because-query-contains-function.html
And [SlijedeciIndex]<=[ProizvodiSifreRangovi]![END],"Da") AS UnutarRanga,

IIf([SlijedeciIndex]=([ProizvodiSifreRangovi]![START]+[UpisanoSifri]),"Da","Ne")
AS Konzistentan

FROM ProizvodiSifreRangovi LEFT JOIN qdfProizvodiSifreZadnje ON
ProizvodiSifreRangovi.VEZA = qdfProizvodiSifreZadnje.VEZA
GROUP BY ProizvodiSifreRangovi.VEZA, ProizvodiSifreRangovi.ENTITET,
ProizvodiSifreRangovi.OPIS, ProizvodiSifreRangovi.START,
ProizvodiSifreRangovi.END,
IIf(IsNull([qdfProizvodiSifreZadnje]![UpisanoSifri]),"0",[qdfProizvodiSifreZadnje]![UpisanoSifri]),
IIf(IsNull([qdfProizvodiSifreZadnje]![SlijedeciIndex]),[ProizvodiSifreRangovi]![START],[qdfProizvodiSifreZadnje]![SlijedeciIndex])
HAVING (((ProizvodiSifreRangovi.VEZA)<>0));

here is function FormatNumberAsString:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11122

Public Function FormatNumberAsString(number As Long, Optional lenght As
Long) As String

'Po zadnjem pravilniku o ¹ifriranju
Dim lngLenght As Long
Dim strNumber As String 'Broj æe se konvertirati u string i ovisno o duljini
kreirati string popunjen nulama na glavi
Dim lngNumberLen As Long
Dim lngZeroAddendum As Long

'Provjeri bitno!
If IsMissing(lenght) Or lenght = 0 Then 'ako lenght nije upisan
'Po pravilniku o ¹ifriranju!
lngLenght = 5
Else
'Ako je poslan (izvanrednost)
lngLenght = lenght
End If

strNumber = CStr(number)
lngNumberLen = Len(strNumber)

'Razlika duljine opæenito minus duljine broja daje broj 0 koje se dodaju na
glavu stringa!
lngZeroAddendum = lngLenght - lngNumberLen

If lngZeroAddendum < 0 Then lngZeroAddendum = 0

FormatNumberAsString = String(lngZeroAddendum, "0") & strNumber

End Function

#2 (**permalink**) 28-Jul-2006, 08:18 AM

What do you mean when you say, "when I execute the query from code ..."?
Show the code you're using to execute the function and maybe that will help.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11122
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11122
--

Danny J. Lesandrini
dlesandrini@hotmail.com
http://amazecreations.com/datafast

"Domac" wrote ...
> Hi,
>
> Query named "qdfProizvodiAnalitikaZadnje" is using function to define value in a field ..
>
> When I execute this query from code i get message "Undefined function 'FormatNumberAsString' in expression"!
>
> Why ??
>
> When I execute it whitin query designer everything works fine!
>
>
> Here is part of code:
>
>
> strSql = "SELECT qdfProizvodiAnalitikaZadnje.* " & _
> "FROM qdfProizvodiAnalitikaZadnje " & _
> "WHERE (((qdfProizvodiAnalitikaZadnje.VEZA)=" & VezaPodEntiteta & "));"

#3 (**permalink**) 28-Jul-2006, 08:18 AM

Look inside SQL text at bottom of my root post!

Function is named 'FormatNumberAsString' and it is used to define value of
expression of field named 'SlijedecaSifra'.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11122

Here is sql again:

SELECT ProizvodiSifreRangovi.VEZA,
ProizvodiSifreRangovi.ENTITET,
ProizvodiSifreRangovi.OPIS,
ProizvodiSifreRangovi.START,
ProizvodiSifreRangovi.END,
IIf(IsNull([qdfProizvodiSifreZadnje]![UpisanoSifri]),"0",[qdfProizvodiSifreZadnje]![UpisanoSifri])
AS UpisanoSifri,
IIf(IsNull([qdfProizvodiSifreZadnje]![SlijedeciIndex]),[ProizvodiSifreRangovi]![START],[qdfProizvodiSifreZadnje]![SlijedeciIndex])
AS SlijedeciIndex,
[ProizvodiSifreRangovi]![ENTITET] & FormatNumberAsString([SlijedeciIndex])
AS SlijedecaSifra,

Switch([SlijedeciIndex]<[ProizvodiSifreRangovi]![START],"Ne",[SlijedeciIndex]>[ProizvodiSifreRangovi]![END],"Ne",[SlijedeciIndex]>=[ProizvodiSifreRangovi]![START]
And [SlijedeciIndex]<=[ProizvodiSifreRangovi]![END],"Da") AS UnutarRanga,

IIf([SlijedeciIndex]=([ProizvodiSifreRangovi]![START]+[UpisanoSifri]),"Da","Ne")
AS Konzistentan

FROM ProizvodiSifreRangovi LEFT JOIN qdfProizvodiSifreZadnje ON
ProizvodiSifreRangovi.VEZA = qdfProizvodiSifreZadnje.VEZA
GROUP BY ProizvodiSifreRangovi.VEZA, ProizvodiSifreRangovi.ENTITET,
ProizvodiSifreRangovi.OPIS, ProizvodiSifreRangovi.START,
ProizvodiSifreRangovi.END,
IIf(IsNull([qdfProizvodiSifreZadnje]![UpisanoSifri]),"0",[qdfProizvodiSifreZadnje]![UpisanoSifri]),
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11122
IIf(IsNull([qdfProizvodiSifreZadnje]![SlijedeciIndex]),[ProizvodiSifreRangovi]![START],[qdfProizvodiSifreZadnje]![SlijedeciIndex])
HAVING (((ProizvodiSifreRangovi.VEZA)<>0));

#4 (**permalink**) 28-Jul-2006, 08:18 AM

I'm sorry, I wasn't clear. You said that you can run the query from
the design window and it works. Great! So what's the problem?

The problem must have to do with how you're executing the query
when NOT in the QBE window. How are you trying to launch it?
--

Danny J. Lesandrini
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11122
dlesandrini@hotmail.com
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11122
http://amazecreations.com/datafast

#5 (**permalink**) 28-Jul-2006, 08:18 AM

If I understand the code and SQL you posted correctly, I'm not sure that you
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11122
actually need the function at all. I think you can achieve the same thing
with an expression in the query ...

SELECT tblTest.TestNumber, Format$(NZ([TestNumber], 0), String$([How Many?],
"0")) AS Expr1
FROM tblTest;

Here the parameter '[How Many?]' performs the same function as the optional
argument in your function.

Here's an example of how you might use it ...

Public Sub TestQuery()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim fld As DAO.Field

Set db = CurrentDb
Set qdf = db.QueryDefs("qryTest")
qdf.Parameters("[How Many?]") = 5
Set rst = qdf.OpenRecordset()
Do Until rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value
Next fld
rst.MoveNext
Loop
rst.Close

End Sub

--
Brendan Reynolds
Access MVP

"Domac" wrote in message
news:uChTj67iGHA.4368@TK2MSFTNGP03.phx.gbl...
> Hi,
>
> Query named "qdfProizvodiAnalitikaZadnje" is using function to define
> value in a field ..
>
> When I execute this query from code i get message "Undefined function
> 'FormatNumberAsString' in expression"!
>
> Why ??
>
> When I execute it whitin query designer everything works fine!
>
>
> Here is part of code:
>
>
> strSql = "SELECT qdfProizvodiAnalitikaZadnje.* " & _
> "FROM qdfProizvodiAnalitikaZadnje " & _
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11122
> "WHERE (((qdfProizvodiAnalitikaZadnje.VEZA)=" & VezaPodEntiteta &
> "));"
>
>
>
> Set rst = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot)
>
>
>
> Here is SQL of query that I mention above:
>
> SELECT ProizvodiSifreRangovi.VEZA,
> ProizvodiSifreRangovi.ENTITET,
> ProizvodiSifreRangovi.OPIS,
> ProizvodiSifreRangovi.START,
> ProizvodiSifreRangovi.END,
> IIf(IsNull([qdfProizvodiSifreZadnje]![UpisanoSifri]),"0",[qdfProizvodiSifreZadnje]![UpisanoSifri])
> AS UpisanoSifri,
> IIf(IsNull([qdfProizvodiSifreZadnje]![SlijedeciIndex]),[ProizvodiSifreRangovi]![START],[qdfProizvodiSifreZadnje]![SlijedeciIndex])
> AS SlijedeciIndex,
> [ProizvodiSifreRangovi]![ENTITET] & FormatNumberAsString([SlijedeciIndex])
> AS SlijedecaSifra,
>
> Switch([SlijedeciIndex]<[ProizvodiSifreRangovi]![START],"Ne",[SlijedeciIndex]>[ProizvodiSifreRangovi]![END],"Ne",[SlijedeciIndex]>=[ProizvodiSifreRangovi]![START]
> And [SlijedeciIndex]<=[ProizvodiSifreRangovi]![END],"Da") AS UnutarRanga,
>
> IIf([SlijedeciIndex]=([ProizvodiSifreRangovi]![START]+[UpisanoSifri]),"Da","Ne")
> AS Konzistentan
>
> FROM ProizvodiSifreRangovi LEFT JOIN qdfProizvodiSifreZadnje ON
> ProizvodiSifreRangovi.VEZA = qdfProizvodiSifreZadnje.VEZA
> GROUP BY ProizvodiSifreRangovi.VEZA, ProizvodiSifreRangovi.ENTITET,
> ProizvodiSifreRangovi.OPIS, ProizvodiSifreRangovi.START,
> ProizvodiSifreRangovi.END,
> IIf(IsNull([qdfProizvodiSifreZadnje]![UpisanoSifri]),"0",[qdfProizvodiSifreZadnje]![UpisanoSifri]),
> IIf(IsNull([qdfProizvodiSifreZadnje]![SlijedeciIndex]),[ProizvodiSifreRangovi]![START],[qdfProizvodiSifreZadnje]![SlijedeciIndex])
> HAVING (((ProizvodiSifreRangovi.VEZA)<>0));
>
>
> here is function FormatNumberAsString:
>
> Public Function FormatNumberAsString(number As Long, Optional lenght As
> Long) As String
>
> 'Po zadnjem pravilniku o ¹ifriranju
> Dim lngLenght As Long
> Dim strNumber As String 'Broj æe se konvertirati u string i ovisno o
> duljini kreirati string popunjen nulama na glavi
> Dim lngNumberLen As Long
> Dim lngZeroAddendum As Long
>
>
> 'Provjeri bitno!
> If IsMissing(lenght) Or lenght = 0 Then 'ako lenght nije upisan
> 'Po pravilniku o ¹ifriranju!
> lngLenght = 5
> Else
> 'Ako je poslan (izvanrednost)
> lngLenght = lenght
> End If
>
> strNumber = CStr(number)
> lngNumberLen = Len(strNumber)
>
>
> 'Razlika duljine opæenito minus duljine broja daje broj 0 koje se dodaju
> na glavu stringa!
> lngZeroAddendum = lngLenght - lngNumberLen
>
> If lngZeroAddendum < 0 Then lngZeroAddendum = 0
>
>
>
>
> FormatNumberAsString = String(lngZeroAddendum, "0") & strNumber
>
>
>
>
>
> End Function
>
>
>

#6 (**permalink**) 28-Jul-2006, 08:18 AM

Are you trying to run this using DoCmd.RunSQL? Because you can only run
action queries via DoCmd (Insert, Append, etc, not SELECT). Instead of using
docmd, use a recordset instead, with your query as the datasource.
--
hth,
SusanV

"Domac" wrote in message
news:uHDuiG8iGHA.2188@TK2MSFTNGP04.phx.gbl...
> Look inside SQL text at bottom of my root post!
>
> Function is named 'FormatNumberAsString' and it is used to define value
> of expression of field named 'SlijedecaSifra'.
>
>
> Here is sql again:
>
> SELECT ProizvodiSifreRangovi.VEZA,
> ProizvodiSifreRangovi.ENTITET,
> ProizvodiSifreRangovi.OPIS,
> ProizvodiSifreRangovi.START,
> ProizvodiSifreRangovi.END,
> IIf(IsNull([qdfProizvodiSifreZadnje]![UpisanoSifri]),"0",[qdfProizvodiSifreZadnje]![UpisanoSifri])
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11122
> AS UpisanoSifri,
> IIf(IsNull([qdfProizvodiSifreZadnje]![SlijedeciIndex]),[ProizvodiSifreRangovi]![START],[qdfProizvodiSifreZadnje]![SlijedeciIndex])
> AS SlijedeciIndex,
> [ProizvodiSifreRangovi]![ENTITET] & FormatNumberAsString([SlijedeciIndex])
> AS SlijedecaSifra,
>
> Switch([SlijedeciIndex]<[ProizvodiSifreRangovi]![START],"Ne",[SlijedeciIndex]>[ProizvodiSifreRangovi]![END],"Ne",[SlijedeciIndex]>=[ProizvodiSifreRangovi]![START]
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11122
> And [SlijedeciIndex]<=[ProizvodiSifreRangovi]![END],"Da") AS UnutarRanga,
>
> IIf([SlijedeciIndex]=([ProizvodiSifreRangovi]![START]+[UpisanoSifri]),"Da","Ne")
> AS Konzistentan
>
> FROM ProizvodiSifreRangovi LEFT JOIN qdfProizvodiSifreZadnje ON
> ProizvodiSifreRangovi.VEZA = qdfProizvodiSifreZadnje.VEZA
> GROUP BY ProizvodiSifreRangovi.VEZA, ProizvodiSifreRangovi.ENTITET,
> ProizvodiSifreRangovi.OPIS, ProizvodiSifreRangovi.START,
> ProizvodiSifreRangovi.END,
> IIf(IsNull([qdfProizvodiSifreZadnje]![UpisanoSifri]),"0",[qdfProizvodiSifreZadnje]![UpisanoSifri]),
> IIf(IsNull([qdfProizvodiSifreZadnje]![SlijedeciIndex]),[ProizvodiSifreRangovi]![START],[qdfProizvodiSifreZadnje]![SlijedeciIndex])
> HAVING (((ProizvodiSifreRangovi.VEZA)<>0));
>
>
>
>
>