How to pass date parameter into Oracle SQL string in VBA

[Doogster]

Hi folks

This is a bit of a long shot, but does anyone know how to pass a date variable into an Oralce SQL string in VBA. Here's an example of the string:

SQLCommand.CommandText = "SELECT T104F005_EMPLOYEE_NO, " & _
"to_char(T104F025_DATE_EFFECTIVE, 'DD/MM/YYYY'), " & _
"from DATABASE.T104_EMPLOYMENT_HISTORY " & _
"where T104F025_DATE_EFFECTIVE > '1-JUL-07' " & _
"order by T104F005_EMPLOYEE_NO "

The statement "where T104F025_DATE_EFFECTIVE > '1-JUL-07' " needs to be a variable date which is captured in another step in the code, then passed into the SQL string. Does anyone know how this is done? I've tried declaring a parameter (eg :DateEffective) and using that, but the SQL string wouldn't recognise it. Perhaps it has something to do with the fact that the SQL string is text surrounded by quotes, thereby treating the parameter as text rather than a true parameter? Any suggestions?

Cheers

[Doogster]

I just managed to figure out the way to pass a parameter to the SQL statement via VBA. It doesn't involve the standard Oracle parameter indicator, which is what was driving me mad. The secret is to use a question mark for each parameter, then set the parameter value in order of appearance in the SQL statement.

The following is the amended code:

.CommandText = "SELECT T104F005_EMPLOYEE_NO, " & _
"to_char(T104F025_DATE_EFFECTIVE, 'DD/MM/YYYY'), " & _
"from DATABASE.T104_EMPLOYMENT_HISTORY " & _
"where T104F025_DATE_EFFECTIVE BETWEEN ? AND ? " & _
"order by T104F005_EMPLOYEE_NO "
Set MyParameter = .CreateParameter("StartDate", adDBDate, dParamInput, , StartDate)
.Parameters.Append MyParameter ' this sets the value for the 1st parameter
Set MyParameter = .CreateParameter("EndDate", adDBDate, dParamInput, , EndDate)
.Parameters.Append MyParameter ' this sets the value for the 2nd parameter

StartDate and EndDate are date variables which have been set to certain dates (using the DD-MMM-YYYY format). MyParameter is a ADODB.Parameter variable.

Cheers