Placing Dates in a Query

[woodyinoz]

Hi all,

I am looking to fill in the dates in the following query by taking values entered into a text box.

My query is:

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=SQLSvr;Description=Connection to SQL Server (SQLSVR1);APP=Microsoft Office 2003;WSID=SWOODWARD;DATABASE=FMDB;Network=DBMSSO" _
        ), Array("CN;Trusted_Connection=Yes")), Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT LABOR_TICKET.TRANSACTION_DATE, LABOR_TICKET.WORKORDER_BASE_ID, LABOR_TICKET.RESOURCE_ID, LABOR_TICKET.EMPLOYEE_ID, LABOR_TICKET.HOURS_WORKED, SHOP_RESOURCE.BUR_PER_HR_RUN, EMPLOYEE.FIRST_NAME, " _
        , _
        "EMPLOYEE.LAST_NAME" & Chr(13) & "" & Chr(10) & "FROM FMDB.dbo.EMPLOYEE EMPLOYEE, FMDB.dbo.LABOR_TICKET LABOR_TICKET, FMDB.dbo.SHOP_RESOURCE SHOP_RESOURCE" & Chr(13) & "" & Chr(10) & "WHERE LABOR_TICKET.RESOURCE_ID = SHOP_RESOURCE.ID AND LABOR_TICKET.EMPLOY" _
        , _
        "EE_ID = EMPLOYEE.ID AND ((LABOR_TICKET.WORKORDER_LOT_ID='SO1') AND (LABOR_TICKET.CREATE_DATE Between {ts '2005-09-01 00:00:00'} And {ts '2005-09-30 00:00:00'}) OR (LABOR_TICKET.WORKORDER_LOT_ID='FO1')" _
        , _
        " AND (LABOR_TICKET.CREATE_DATE Between {ts '2005-09-01 00:00:00'} And {ts '2005-09-30 00:00:00'}))" _
        )

I want to replace the dates hardcoded in with the dates entered by a user on a form... Any ideas anyone?

Thanks,

Woody

 

[PHV]

You may try to replace this:
Between {ts '2005-09-01 00:00:00'}
By something like this:
Between {ts '" & Format(textbox reference here, "yyyy-mm-dd hh:nn:ss") & "'}

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

 

[woodyinoz]

That looks like it would work... I have put it into my code but my code crashes and highlights the following line as the issue

.Refresh BackgroundQuery:=False

This code is held after the query...

Any ideas?