Results 1 to 2 of 2
  1. #1
    alfred.lok is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Aug 2022
    Posts
    3

    SQL Server views with CONVERT VARCHAR Error, and SQL statement with Date Time

    I am converting an old ADP to ACCDB. I have already reconnected the SQL server.

    My development PC:
    1. Windows 10
    2. Local SQL Server Express 2019
    3. Office 2010
    4. ODBC 13 for SQL Server

    My Customer:
    1. Windows 11 or 10
    2. Windows 2019 Server with SQL Server Express 2019


    3. Office 2016
    4. ODBC 13 for SQL Server

    There is some errors that I cannot understand what is the cause and how to make a quick fix.

    1. [Microsoft] [ODC Driver 13 for SQL Server] Restricted data type attribute violation (#0)
    In my development PC, it runs without any error when I open a form. But in my customer PC, it encounters the error. It takes me some days to find out that it is due to the views in SQL Server.

    It is due to the view definition, if a form that record source is linked to a view that contains
    Code:
    CONVERT(varchar(12), datacolumn1)
    in the definition, it will encounter the error. But I do not encounter the error in my development PC.

    Why and is that a quick way to fix it?


    2. Report with Date Time
    After days of testing, I find that the ACCDB cannot execute SQL with data time column successfully.

    This cannot be execute:

    Code:
    SQL = "select * from table1 where expiredate > '" & DateFrom & "'"


    I do not understand why and so I rewrite the string with sub SQL string using OA Date number.

    Code:
    SubSQL = "(select *, CDBL(expireddate) as dateno from table1)"
    
    SQL = "select * from " & SubSQL & " where (expiredate is not null) and dateno=" & doubleDateFrom 
    The above SQL returns correct records in both my PC and my customer PC.


    However, the following SQL cannot return correct records in my customer PC. It returns empty (not null) but just no record. But it returns correct records in my PC.

    Code:
    SQL = "select * from " & SubSQL & " where (expiredate is not null) and ((dateno >=" & doubleDateFrom & " ) and (dateno <= " & doubleDateTo & " ))"
    Any suggestion?

    Thanks.

    Alfred

  2. #2
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    For the first question:
    ------------------------
    check the result of your convert function with the data in the view. Some results might not be what you expect. Some examples in my SQL environment (2017 - development version).
    query:
    Code:
    SELECT CONVERT(varchar(12), '2022-05-15 13:00') as Example1, CONVERT(varchar(12), getdate())  as example2
    Results:
    Example1: '2022-05-15 1'
    Example2: 'Oct 7 2022 '

    Question2:
    ------------
    When executing date expressions on SQL you need the ' to delimit dates, but for access you need # signs
    so correct SQL expression :
    Code:
    select @SQL = 'select * from table1 where expireDate > ''' + DateFrom + '''
    '' here are 2 single quotes, not 1 double)
    correct Access expression:
    Code:
    strSQL = "select * from table1 where expiredate > #" & DateFrom & "#"

Please reply to this thread with any new information or opinions.

Similar Threads

  1. SQL Server Views
    By RayMilhon in forum SQL Server
    Replies: 3
    Last Post: 04-18-2018, 09:19 AM
  2. SQL Server Views documentation
    By RayMilhon in forum SQL Server
    Replies: 0
    Last Post: 09-04-2013, 11:35 AM
  3. Including Quotation Marks in SQL Server Views
    By EddieN1 in forum SQL Server
    Replies: 0
    Last Post: 02-17-2013, 07:32 PM
  4. Updating SQL Server Views via MS Access front-end?
    By tzvi in forum Import/Export Data
    Replies: 1
    Last Post: 01-10-2013, 11:13 AM
  5. Convert Number to Date/Time
    By Jerseynjphillypa in forum Queries
    Replies: 10
    Last Post: 06-13-2012, 12:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums