SQL Queries “On the Fly”

[gsandy]

I have used the following code in a command button, on a form, to build a query:

Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Set db = CurrentDb
    Set qdf = db.QueryDefs("JobSummaryQuery")
    strSQL = "SELECT tblHourEnter.* " & _
             "FROM tblHourEnter " & _
             "WHERE tblHourEnter.JobNumber=" & Me.cmbJobList.Value & " " & _
             "AND tblHourEnter.Staff_ID=" & Me.cmbStaff.Value & " " & _
             "ORDER BY tblHourEnter.JobNumber;"
    qdf.SQL = strSQL
    DoCmd.OpenQuery "JobSummaryQuery"
   ' DoCmd.Close acForm, Me.Name
    Set qdf = Nothing
    Set db = Nothing

Question 1 - The above works if I select a staff member from combo box (cmbStaff), but does not if I don't.
How can I get to work with a null entry?

Question 2 - The table (HourEnter) has a field (Hours_Worked) and the query lists all the hours, line by line for the selected job number in cmbJobList. How can I get the sum of all the hours for selected job number, in the combobx (cmbJobList), in one line?

Thanks Sandy

 

[gsandy]

I will always select a job number BUT want an hours summary for all staff members for that job OR select a staff member and get hours summary for job selected.

 

[namliam]

The best way to handle this kind of thing is with a simple If...
Something along the lines of

    strSQL = "SELECT tblHourEnter.* " & _
             "FROM tblHourEnter " & _
             "WHERE 1=1 " ' the 1=1 is just a dummy to make things a little easier and you can prefix everything with AND
    If not isnull(Me.cmbJobList.Value) then 
        ' Notice the space before this AND to make sure you do not get syntax errors
        strSQL = strSQL & " AND tblHourEnter.JobNumber=" & Me.cmbJobList.Value & " " 
    endif
    if not isnull(Me.cmbStaff.Value) then
        ' Notice the space before this AND to make sure you do not get syntax errors
        strSQL = strSQL & " AND tblHourEnter.Staff_ID=" & Me.cmbStaff.Value & " " 
    endif
    strSQL = strSQL & " Order by ..."

I hope you get the idea and can expand it to completely suite your needs.

 

[gsandy]

Thanks Namliam, the code worked great.

I will re-post my second question.

Cheers Sandy