locked
Need advice on udf RRS feed

  • Question

  • Hi to all,
    I would like to have some advice on how to convert a VBA function to udf without using loop if possible:
    This is what I am currently using
    [code]
    Private Sub PageNumGenerator()
    'génération de numéro de page "East to West and in Alphabetical"
     Set rst = CurrentDb.OpenRecordset("Q-TableContents", dbOpenSnapshot, Options:=dbFailOnError + dbSeeChanges)
        ' Pour remplir le RecordSet
        rst.MoveLast
        rst.MoveFirst

        i = 1
        Do Until rst.EOF
            With rst
                .Edit
                ![NumPage] = i
                .Update
                .MoveNext
                If Not (rst.EOF) Then
                    .Edit
                    ![NumPage] = i
                    .Update
                    .MoveNext
                End If
                i = i + 1
            End With
        Loop

    rst.Close
    Set rst = Nothing

    End Sub
    [/code]

    This little function is to genetqate a page number for a specific location, the report on where the information is being displayed can onoly contain 2 location only, meaning if I have 60 location, the report will end up with 30 pages only, the cover page of thereport will show only the location and th3e page number attached to it, meaning location A page number 1, location b page number 1, location c page number 2 ect......
    I have tried  rank(), row_number but I cannot see a solution unless I start using a cursor whioch I do not want  but only if I really really really have to

    So any input on this will be great

    Thanks

     

    Wednesday, March 23, 2011 7:09 PM

Answers

  • Assuming that the source data is MyTable, will the following work for you?

    SELECT Location, PageNo = (ROW_NUMBER() OVER (ORDER BY Location)+1)/2
    FROM MyTable

     


    --Brad (My Blog)
    • Marked as answer by Alabil Wednesday, March 23, 2011 7:45 PM
    Wednesday, March 23, 2011 7:18 PM

All replies

  • Assuming that the source data is MyTable, will the following work for you?

    SELECT Location, PageNo = (ROW_NUMBER() OVER (ORDER BY Location)+1)/2
    FROM MyTable

     


    --Brad (My Blog)
    • Marked as answer by Alabil Wednesday, March 23, 2011 7:45 PM
    Wednesday, March 23, 2011 7:18 PM
  • I will try it and let you know
    works great, did not think about the row_number properly, one more thing learned today

    Thanks

    Wednesday, March 23, 2011 7:39 PM
  • >> I would like to have some advice on how to convert a VBA function to UDF without using loop if possible: <<

    The goal in a declarative language is that we NEVER have UDFs and loops. That was procedure code and we look at it like cannibalism to good SQL mprogrammers

    You also assume that we can read your strange non-SQL language. Why didn't you give specs that anyone could understand instead of the local version of ancient Japanese you posted? 

    You are violating THE basic principle of any tiered architecture; we do not format data for display in the DB; we do that in the front end. If you do not understand this, please stop programming until you do understand the basics.
    >> I would like to have some advice on how to convert a VBA function to UDF without using loop if possible: <<

    The goal in a declarative language is that we NEVER have UDFs. That was proceural code and we look at it like cannibalism. 

    You also assume that we can read your strange non-SQL language. Why didn't you give specs that anyone could understand instead of the local version of ancient Japanese you posted? 

    You are violating THE basic principle of any tiered architecture; we do not format data for display in the DB; we do that in the front end. If you do not understand this, please stop programming until you do understand the basics.
    >> I would like to have some advice on how to convert a VBA function to UDF without using loop if possible: <<

    The goal in a declarative language is that we NEVER have UDFs. That was proceural code and we look at it like cannibalism. 

    You also assume that we can read your strange non-SQL language. Why didn't you give specs that anyone could understand instead of the local version of ancient Japanese you posted? 

    Yo  are violating THE basic principle of any tiered architecture; we do not format data for display in the backend; we do that in the front end. If you do not understand this, please stop programming until you do understand the basics.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    Wednesday, March 23, 2011 8:48 PM