Parameter Query

[Dave Wysocki]

I keep getting an conversion error when the open statement executes in following code.

I can hard code the dates in the where clause and the code works fine. Whenever I try to pass the dates as a parameter, I get the conversion error message.

I'm trying to accept date input from a form in Excel 2007.

The query needs to access a SQL server db and select the records that exist within the date range.

I am selecting against a view, not sure if that makes any difference. It should not as I tested the query in SQL Server management studio.

Private Sub btnCancel_Click()
    Unload Me
End Sub
 
Private Sub btnOk_Click()
' Create a connection object.
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection
 
' Provide the connection string.
Dim strConn As String
 
'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"
 
'Connect to the Pubs database on the local server.
strConn = strConn & "DATA SOURCE=HDQTMAPSMIA02V;INITIAL CATALOG=config_f9;"
 
'Use an integrated login.
strConn = strConn & " INTEGRATED SECURITY=sspi;"
 
'Now open the connection.
cnPubs.Open strConn
 
    Sheet1.Range("D4").Value = dtpPayPeriodBeginDt.Value
    Sheet1.Range("D6").Value = dtpPayPeriodEndDt.Value
 
' Create a recordset object.
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset
 
Dim parm1 As ADODB.Parameter
Dim parm2 As ADODB.Parameter
 
Set parm1 = New ADODB.Parameter
Set parm2 = New ADODB.Parameter
 
Dim strBeginDt As String
Dim strEndDt As String
 
strBeginDt = dtpPayPeriodBeginDt.Value
strEndDt = dtpPayPeriodEndDt.Value
 
With rsPubs
    ' Assign the Connection object.
    .ActiveConnection = cnPubs
    ' Extract the required records.
    .Open "select extra_code_1, surname, forenames, posting_sdate, posting_edate,grade_sht_title from dbo.ncl_active_deck_crew where posting_sdate <= 'convert(datetime, strBeginDt)' and posting_edate >= 'convert(datetime, strEndDt)' order by grade_sht_title,surname,forenames"
' Copy the records into cell A1 on Sheet1.
    Sheet1.Range("b20:g1048576").Clear
    Sheet1.Range("B20").CopyFromRecordset rsPubs
 
    ' Tidy up
    .Close
End With
 
cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing
 
    Unload Me
End Sub

[NeoPa]

Dave:
I keep getting an conversion error when the open statement executes in following code.

If you give us a line number then we can tell which of the two 'the open statements' you are referring to.

If it's the connection string that's a problem then I can't help I don't think, but if it's line #48 with the SQL string then I may be able to.

[sierra7]

Hi Dave,
I looked at this this-morning but could not understand where the SQL was executing. I understand you reading Start and End dates from a spreadsheet, using them as parameters in a query in a SQL Server database then copying the data back into the spreadsheet (starting at cell B20 not A1 as indicated by the comment)?

Are you using Access VBA? The 'Unload Me' at line #2 sounds a bit like a VB form?, so you may be in the wrong forum for the best help.

One bit I am suspicious of is "strBeginDt = dtpPayPeriodBeginDt.Value", storing the date as a string. Personally I would try keeping it as a Date. I know you are trying to convert it to a DateTime in your SQL statement. But to my knowledge 'convert' is a SQL Server function (not Access)in which case the parameter would have to have been declared with an '@' prefix.

But the fact that you are trying to run against an ADO recordset indicates the SQL may be running against a JET database, in which case NeoPa is right and he can help with the correct syntax for your country.
S7

[NeoPa]

S7:
But the fact that you are trying to run against an ADO recordset indicates the SQL may be running against a JET database

It's actually DAO (Data Access Objects), rather than ADO or ADODB (ActiveX Data Objects Database), which would indicate a Jet reference. Actually, either can work with Jet, but DAO is certainly more of a native protocol for it. It's not hard to see why these two are often confused for each other TBF.

[sierra7]

NeoPa,
I am not even sure that Mr. Wysocki is using Access, as I indicated in my post. He is definitely using ADODB which is indicative but I believe not necessarily means JET. Until this is clear we cannot be certain which syntax to use.
S7

[NeoPa]

Sierra7:
... ADODB which is indicative but I believe not necessarily means JET

I don't believe that's right S7. Jet is one of many database engines that ADODB supports, as it's more 'general purpose' than DAO, which, while it can be used elsewhere, is designed to support Jet specifically.

IE. DAO could be taken to imply Jet, but ADODB is less indicative of anything specific.

In this case lines #13 & #14 indicate the engine in use is SQL Server. This would indicate T-SQL syntax rather than Jet SQL. I'm somewhat familiar with the former, but less so than the latter obviously.

PS. It seems the application is not Access, but Excel 2007. I'm hoping that the VBA syntax and object set available is similar to that of Access.

[sierra7]

NeoPa,
I totally agree and it came blindingly obvious when I started to swot-up on ADO ConnectionStrings. Nice observation that the application is run from within Excel though!

Dave,
Sorry to keep you waiting while NeoPa and I have been confiring amongst ourselves! On this forum we usually work in Access and although a few years ago Microsoft made an attempt to direct us down the ADO route most of us stuck with DAO. Frankly I have had more exposure to Polish than I have to ADO.

However, the code that you posted looks like it has been modelled on a Microsoft example (by virtue of it's reference to the Pubs database), so it would not be defining parameters without reason (Lines #32 & 33). Normally the problem is how to pass a parameter into a SQL command, so I suggest the following. Set the two parameters to the start and end dates then include the parameter variables in the sQL statement thus;

Set parm1 = New ADODB.Parameter 
Set parm2 = New ADODB.Parameter 
 
'Dim strBeginDt As String 
'Dim strEndDt As String 
 
param1 = dtpPayPeriodBeginDt.Value 
param2 = dtpPayPeriodEndDt.Value 
 
With rsPubs 
    ' Assign the Connection object. 
    .ActiveConnection = cnPubs 
    ' Extract the required records. 
    .Open "select extra_code_1, surname, forenames, posting_sdate, posting_edate,grade_sht_title from dbo.ncl_active_deck_crew where posting_sdate <= 'convert(datetime, param1)' and posting_edate >= 'convert(datetime, param2)' order by grade_sht_title,surname,forenames" 
' Copy the records into cell A1 on Sheet1. 
    Sheet1.Range("b20:g1048576").Clear 
    Sheet1.Range("B20").CopyFromRecordset rsPubs 
 
    ' Tidy up 
    .Close 
End With 
 

This is what you would do in Transact SQL which runs on the server. I have been trying to make time to test this before posting but have not been able to, so cannot guarantee it will work.

You will see that I have not made use of the two string variables. It may be necessary to write the values to strings then set the parameters equal to the strings, I don't know.

If still not working I would try removing the quotes around the Convert() functions.

Gook luck. I will try again next week.
S7

[NeoPa]

This is all much easier when the actual question is clear of course, but it's understandable that the OP might be away over the Christmas period, so my first question (post #2) still not being answered is no big deal.

However, if we assume for now that the problem is with the 2nd occurrence of the Open command in the posted code, then we are looking at the SQL string which is created by line #48 of the posted code. If that is the case then we are looking at a SQL problem so you (Dave) need to look first at When Posting (VBA or SQL) Code. I suspect when you actually look at the SQL string the fact that the T-SQL Convert calls are there as literal strings instead of converted values will ring bells and indicate where you need to focus your attention.