ms query in excel

[CraigMan]

Trying to restrict the records returned by a query in excel. I would like to specify a from and to date in two excel cells and use these two dates in the query. Can this be done? Programatically?

Craig Meyers, BSNucE, PE

 

[xlbo]

yup - pretty easily

You can access the Querytable object and interrogate the .CommandText property. This holds the SQL statement as a string. You can therefore manipulate the string as with any strings to insert variables (ie your From & To dates).

FromDate = Format(Range("A1").value,"dd/mm/yyyy")
ToDate = Format(Range("A2").value,"dd/mm/yyyy")

With sheets("Sheetname").Querytables(1)
.CommandText = "SELECT Data from Table WHERE " & _
               "DateField >= '# " & FromDate & " #' AND " & _
               "DateField <= '# " & ToDate & " #'"
.Refresh(false)
End With

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[CraigMan]

Thanks for the help!!

Craig Meyers, BSNucE, PE