Getting error because query contains a function

D

Domac

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]
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
 
D

Danny J. Lesandrini

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.
 
D

Domac

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])
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));
 
D

Danny J. Lesandrini

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?
 
B

Brendan Reynolds

If I understand the code and SQL you posted correctly, I'm not sure that you
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 said:
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]
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
Click to expand...
 
S

SusanV

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.
 
Top