Multiple Parameters on the same field in a querytable

[Wyverrn]

Hello,

I am trying to write a query macro in Excel to query off of an SQL database.

What I cannot seem to figure out is how to have a parameter to return a range of values(like between 2 dates)

MSQuery will not work for me as I will need multiple tables and left outer joins, something MSQuery does not support.

My simplified test query:

[tt]Sub Test()

Dim QT As QueryTable
Dim CS As String

CS = "ODBC;DSN=TMDataMart;UID=sybase;PWD=******;DataBase=TMDataMart"

Set QT = ActiveSheet.QueryTables.Add(CS, Range("A10"), "SELECT Badge, First_name, Last_name, Department FROM Operator WHERE Badge BETWEEN ?[/color red] AND ?[/color red]")

With QT
.RefreshStyle = xlOverwriteCells
.Refresh

End With

End Sub[/tt]

In this case I need to have a parameters for the BETWEEN statement for BADGE. The query works fine the way it is, but gives a prompt for the 2 parameters. I need those parameters to populated from variables.


I apprecaite any assistance anyone can offer

 

[SkipVought]

Hi,

sSQL = "SELECT Badge, First_name, Last_name, Department "
sSQL = sSQL & "FROM Operator 
sSQL = sSQL & "WHERE Badge BETWEEN '" & sBadge1 & "' AND '" & sBadge2 & "'"

Set QT = ActiveSheet.QueryTables.Add(CS, Range("A10"), sSQL)

Skip,

_[red][/red]

 

[JerryKlmns]

I think Badge is a date field and sSQL should be

Dim sBadge1 As Date
Dim sBadge2 As Date
sBadge1="2006-09-01"
sBadge2="2006-10-01"

sSQL = "SELECT Badge, First_name, Last_name, Department "
sSQL = sSQL & "FROM Operator "
sSQL = sSQL & "WHERE Badge BETWEEN #" & sBadge1 & "# AND #" & sBadge2 & "#

 

[PHV]

You may also right click the data range and modify the parameters to get their value from cells.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Jerry,

If the variables are defined as DATE then they are DATES

Dim sBadge1 As Date
Dim sBadge2 As Date
sBadge1=#2006-09-01#   'this is a DATE not a string
sBadge2=#2006-10-01#   'this is a DATE not a string

sSQL = "SELECT Badge, First_name, Last_name, Department "
sSQL = sSQL & "FROM Operator "
sSQL = sSQL & "WHERE Badge BETWEEN " & sBadge1 & " AND " & sBadge2

Skip,

_[red][/red]

 

[Wyverrn]

So should concatenate the variable directly into the SQL Statement rather that creating parameter objects.

Seems easy enough.

Is there any purpose to the parameter object then? It seems to be needlessly complex, especially considering the method you suggested seems so much easier.

Thanks a lot for your help!

 

[JerryKlmns]

Skip

This is what it shows in immediate from your last post
SELECT Badge, First_name, Last_name, Department FROM Operator WHERE Badge BETWEEN 01/09/2006 AND 01/10/2006

And this is from mine
SELECT Badge, First_name, Last_name, Department FROM Operator WHERE Badge BETWEEN #01/09/2006# AND #01/10/2006#

And also ISDate(sBadge1) where sBadge1 = "2006-09-01", returns true and so does for sBadge1 = #9/1/2006#

The date formatting is due to my locals