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 16:30.

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,468
    Provided Answers: 10
    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

    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-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

Posting Permissions

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