Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Getting error because query contains a function

Discussion in 'Information Technology' started by Domac, Jul 28, 2006.

  1. Domac

    Domac
    Expand Collapse
    Guest

    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
     
  2. Loading...


  3. Danny J. Lesandrini

    Danny J. Lesandrini
    Expand Collapse
    Guest

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

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


    "Domac" <dd.cc@cc.cc> 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 & "));"
     
  4. Domac

    Domac
    Expand Collapse
    Guest

    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));
     
  5. Danny J. Lesandrini

    Danny J. Lesandrini
    Expand Collapse
    Guest

    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
    dlesandrini@hotmail.com
    http://amazecreations.com/datafast
     
  6. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    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" <dd.cc@cc.cc> 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 " & _
    > "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
    >
    >
    >
     
  7. SusanV

    SusanV
    Expand Collapse
    Guest

    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" <dd.cc@cc.cc> 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])
    > 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));
    >
    >
    >
    >
    >
     

Share This Page