Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2009
    Posts
    73

    Question Unanswered: Access functions to T-sql conversion

    I have tp convert access function to t-sql code
    how to convert it
    Function NextPHolderNo() As Long

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim sqlString As String


    sqlString = "SELECT * FROM tblCounter WHERE ([CountKey]=3);"
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(sqlString, DB_OPEN_DYNASET, dbSeeChanges)

    rst.MoveFirst
    rst.Edit
    NextPHolderNo = rst![CValue]
    rst![CValue] = rst![CValue] + 1

    rst.Update ' Save changes.
    rst.Close ' Close table.

    End Function

  2. #2
    Join Date
    Jun 2003
    Posts
    4
    You cant do an update in a SQL Server function unless it is on a table variable within the function, so your next best bet is to use a stored procedure. Here you go:

    -- ************************************************** *********
    -- Increments CValue in tblCounter and returns the next CValue
    -- ************************************************** *********

    CREATE PROCEDURE [dbo].[spGetNextCountKeyNo] (@CountKey int)
    AS

    BEGIN

    DECLARE @NextCountKey int

    SELECT @NextCountKey = CValue
    FROM tblCounter
    WHERE CountKey = @CountKey

    UPDATE tblCounter
    SET CValue = (CValue + 1)
    WHERE CountKey = @CountKey

    SELECT @NextCountKey

    END

    GO

    -- Test
    EXEC [dbo].[spGetNextCountKeyNo] 3

  3. #3
    Join Date
    Aug 2009
    Posts
    73
    Thanks A Lot
    but how to format date

    Function NextClaimNo() As Long


    Dim MinClaimNo As Long
    MinClaimNo = (Format(Date, "yyyy") - 1930) * 10000 + 1
    If NextClaimNo < MinClaimNo Then
    NextClaimNo = MinClaimNo
    rst![CValue] = MinClaimNo + 1
    End If
    Last edited by amitwadhawan123; 12-21-09 at 17:30.

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    Quote Originally Posted by amitwadhawan123 View Post
    Thanks A Lot
    but how to format date

    Function NextClaimNo() As Long


    Dim MinClaimNo As Long
    MinClaimNo = (Format(Date, "yyyy") - 1930) * 10000 + 1
    If NextClaimNo < MinClaimNo Then
    NextClaimNo = MinClaimNo
    rst![CValue] = MinClaimNo + 1
    End If
    google "Convert"
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •