Using date in a string

[craigg3]

Im trying to run a query and would like the user to not have to go through all the steps so im making it to where they can specifiy the date in a cell and then the query will pull based on that date.

Im having problems with:

((employees.date_last_worked>={ts '2018-06-15 00:00:00'}) AND (emp_taxes.tax_authority_type='F'))

The ts '2018-06-15 00:00:00' is the part im having problems with. I need it to look at Sheets("datego").range("Q1").value and grab the date that is within that cell.


I have tried:
GD=Sheets("datego").range("Q1").value
((employees.date_last_worked>=GD) AND (emp_taxes.tax_authority_type='F'))
((employees.date_last_worked>=& GD &) AND (emp_taxes.tax_authority_type='F'))

.... and a few other combinations but cant get it to work. Im sure im not entering things correctly.

Help would be appreciated. Thanks.

 

[jkpieterse]

Suppose your querystring is stored in a variable called sSQL and it already contains the SELECT part of the statement, then this appends the criteria to it:

sSQL = sSQL & " WHERE ((employees.date_last_worked>={ts '" & format(Sheets("datego").range("Q1").value, "yyyy-mm-dd") &" 00:00:00'}) AND (emp_taxes.tax_authority_type='F'))"

 

[John_w]

Try this SQL string as the query's CommandText:

    GD = Sheets("datego").Range("Q1").Value
    Dim SQL As String
    SQL = "SELECT xxxx FROM yyyy WHERE " & _
          "((employees.date_last_worked>={ts '" & Format(GD, "yyyy-mm-dd") & " 00:00:00'}) AND (emp_taxes.tax_authority_type='F'))"

 

[craigg3]

Works perfect, thanks for your help Jan!

 

[craigg3]

John, thanks for your help also.