I call a stored procedure with a datetime parameter from the ADP Access 2003 project, it gives an error

Incorrect syntax around '-'

I tried different date formats, but the same type of error is repeated, only the constructions are different.

Call from code

Me.items.Form.RecordSource = "exec usp_pct_ItemForTree " & cur & "," & usl & ",-1,1,'" & Nz(fltr, "") & "'" & "," & Abs(Nz(inet, 0)) & "," & idb & "," & DueDate

Stored procedure

 alter procedure [dbo].[usp_pct_ItemForTree] @group_id int=21 ,@instoreonly int=1 ,@idbrand int=-1 ,@hotim bit=1 ,@fltr varchar(150)='' ,@inet bit = 0 ,@idbill int=null ,@dueDate datetime = null

Date example

 2015-09-22

String after concatenation

 "exec usp_pct_ItemForTree 0,6,-1,1,'',0,5425157,2015-09-22"
  • Give at least an example of what the value in DueDate is. And even better is an example of which string at the output is obtained after concatenation. - Aries

2 answers 2

The problem is in the locale settings. A different locale is installed on the client and server. The client sends the date in some regional settings, while the server expects to receive in others.

From here and there is an error converting the string varchar to datetime . Need to look at the server settings. In what format it takes datetime type parameters. Or use the set dateformat statement before performing an update in a stored procedure.

Well, be sure to use single quotes, as Aries correctly noted. Without this, nowhere. The date should be passed to hp as well as string parameters.

    Try this:

     Me.items.Form.RecordSource = "exec usp_pct_ItemForTree " & cur & "," & usl & ",-1,1,'" & Nz(fltr, "") & "'" & "," & Abs(Nz(inet, 0)) & "," & idb & ",'" & DueDate & "'"
    • Error converting varchar data type to datetime - Anatol
    • @Anatol, I don’t have Access at hand now, so I can only guess. But such a format did not try 09/22/2015? And plus note that it’s generally not a good practice to use datetime for a date if you don’t need to store time. There is just a date type. - Aries