Query Parameters that may be blank

[WelshyWizard]

Hi all,

I've got the following code which should out put some information into Excel according to some parameters set by the user.

strShift = Range("B3").Value
strName = Range("E3").Value
StartDate = Range("H3").Value
EndDate = Range("J3").Value

If Range("H3").Value = "" Or Range("J3").Value = "" Then

    Beep

    MsgBox "You must enter two dates", 0 + 48, "Information"

    End

End If

    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("B5"))
        .CommandText = Array( _
        "SELECT EMPLOYEE_HOURS_SHIFT.NAME, EMPLOYEE_HOURS_SHIFT.SHIFT_ID, EMPLOYEE_HOURS_SHIFT.TRANSACTION_DATE, Sum(EMPLOYEE_HOURS_SHIFT.HOURS_WORKED) AS 'Sum of HOURS_WORKED', EMPLOYEE_HOURS_SHIFT.RESOURCE_I" _
        , _
        "D, EMPLOYEE_HOURS_SHIFT.CONTRACT" & Chr(13) & "" & Chr(10) & "FROM FMDB.dbo.EMPLOYEE_HOURS_SHIFT EMPLOYEE_HOURS_SHIFT" & Chr(13) & "" & Chr(10) & "GROUP BY EMPLOYEE_HOURS_SHIFT.NAME, EMPLOYEE_HOURS_SHIFT.SHIFT_ID, EMPLOYEE_HOURS_SHIFT.TRANSACTION_DATE, EMP" _
        , _
        "LOYEE_HOURS_SHIFT.RESOURCE_ID, EMPLOYEE_HOURS_SHIFT.CONTRACT" & Chr(13) & "" & Chr(10) & "HAVING (EMPLOYEE_HOURS_SHIFT.TRANSACTION_DATE Between '" & Format(StartDate, "yyyy-mm-dd") & " 00:00:00' And '" & Format(EndDate, "yyyy-mm-dd") & " 23:59:59') AND (EMPLOYEE_HOURS_SHIFT" _
        , _
        ".NAME = '" & strName & "') AND (EMPLOYEE_HOURS_SHIFT.SHIFT_ID='" & strShift & "')" & Chr(13) & "" & Chr(10) & "ORDER BY EMPLOYEE_HOURS_SHIFT.SHIFT_ID, EMPLOYEE_HOURS_SHIFT.NAME" _
        )
        Debug.Print .CommandText
        .Name = "Query from SQLSvr"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With

This works fine. However, either, or both strName and strShift could both be left blank by the user.
If they are I want to output all records and ignore the particular parameter.... Is there an easy way of doing this?

I could right a few if statements containig several different queries (depending upon what info is put in) but I'm sure there must be some other way.

Cheers.

Today is the tomorrow you worried about yesterday - and all is well.....

 

[xlbo]

general way to build this is to use a set of IFs or SELECT CASE to build the HAVING/WHERE clause. Test for a valid string and if there isn't one, either remove the parameter or pass a wildcard (% or *) to it...

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