Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2014

    Exclamation Unanswered: Time Data Type, SQL Server Versus M Access, Access 2010

    Hello all!

    I have an Access (2010 version) front end linked to a SQL Server 2008R2 database. I am having a lot of trouble with the time field.

    I have a few time data type fields in a few tables. I have chosen the time(0) data type in SQL server, but whenever I attempt to add test data to the time fields via the Microsoft Access front end, I receive an error stating, "Invalid character value for cast specification." (I attached a screen shot of the error message.)

    Has anyone had a similar issue? Any advice?



  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    You don't say specifically so but I suppose that the problem consists in passing a Date/Time value to a stored procedure (by the way there is no attachment to your post).

    The esiest way consists in passing a String value wich is the formatted Date/Time value (yyyymmdd or yyyymmdd hhnnss) and perform the conversion at the stored procedure level, like this:
    CREATE PROCEDURE SomeProcedure
        @DateTimeValue NVARCHAR(20)
        UPDATE SomeTable
            SET SomeTable.DateTimeValue = CAST(@DateTimeValue AS SMALLDATETIME);
    In Access you use:
    Sub CallSomeProcedure(Byval DateTimeValue As Date)
        Const c_Connect As String = "ODBC;Driver={SQL Server};Server=ServerName;Database=DatabaseName;Trusted_Connection=Yes;"    
        Const c_SQL As String = "SomeProcedure @DateTimeValue = '@D'"
        Dim qdf As DAO.QueryDef
        SET qdf=CurrentDb.CreateQueryDef("")
        qdf.Connect = c_Connect
        qdf.SQL = Replace(c_SQL, "@D", Format(DateTimeValue, "yyymmdd"))
        qdf.ReturnsRecords = False
        Set qdf = Nothing
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    without seeing the data its virtually impossible to give a 'good' answer to this
    the message is telling you that you are sending characters that are not permissable for the datatype you have specified

    off hand I'd guess you are probably sending the time element with AM or PM suffixed, where as Time(0) infers its a 24 hour clock
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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