By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,829 Members | 673 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,829 IT Pros & Developers. It's quick & easy.

How to pass date parameter into Oracle SQL string in VBA

P: 2
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
Jan 9 '08 #1
Share this Question
Share on Google+
1 Reply


P: 2
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
Jan 9 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.