Where in a Query 1

[lars7]

Hi,
I need to add a couple of criteria to a query so that it doesn't give me any records for staff who have left or transfered. I have tried a few variations of the code below but I have had no luck can anyone see where I'm going wrong,

strSQL = "SELECT * FROM tbltrustStaff " & _
Where "tbltrustStaff,left is null" and "tbltrustStaff,transfered is null"
strwhere

 

[Remou]

A bit hard to say. Maybe:
[tt]strSQL = "SELECT * FROM tbltrustStaff " _
& "Where tbltrustStaff.

is null " _
& "tbltrustStaff.[transfered] is null"[/tt]

I think it is easier to build the query in the query design screen and then cut-and-paste the SQL.
​

 

[lars7]

Hi Remou,
I tried it but it's giving me a syntex error (missing operator)

 

[Remou]

Oops, forgot the 'and':
strSQL = "SELECT * FROM tbltrustStaff " _
& "Where tbltrustStaff.

is null AND " _
& "tbltrustStaff.[transfered] is null"
​

 

[lars7]

HI Remou,
I made a slight error too in that the transfer field was a check box so I replaced the "is null" with "= 0" and it worked fine. Once again you have my thanks.

strSQL = "SELECT * FROM tbltrustStaff " _
& "Where tbltrustStaff.

is null AND " _
& "tbltrustStaff.[transfer] = 0"

and it was transfer not transfered.​

 

[lars7]

Hi,
I spoke to soon. Once the query is built then I have to add further criteria to it and that's where the "strwhere" comes in but I can't make that work now. Here is the full code:

Private Sub cmdOK_Click()
' Pointer to error handler
On Error GoTo cmdOK_Click_err
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strwhere As String
Dim strdiv As String
Dim strjdcodeno As String
Dim strfwdformatch As String
Dim strdiscipline As String
Dim strlocation As String
Dim strleft As String
Dim strSQL As String
' Identify the database and assign it to the variable
Set db = CurrentDb
DoCmd.DeleteObject acTable, "tbltruststaff"
DoCmd.CopyObject "C:\Documents and Settings\Tam\Desktop\J.E.D new..mdb.", "tbltruststaff", acTable, "trust staff"
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Set db = CurrentDb
Set tdf = db.TableDefs("tbltruststaff")
For Each fld In tdf.Fields
fld.Name = Replace(fld.Name, " ", "")
Next
' Check for the existence of the query, create it if not found,
' and assign it to the variable
If Not QueryExists("qryStaffListQuery") Then
Set qdf = db.CreateQueryDef("qryStaffListQuery")
Else
Set qdf = db.QueryDefs("qryStaffListQuery")
End If
' Get the values from the combo boxes
strwhere = ""
If Trim(Me!cbotrust & "") <> "" Then
strwhere = strwhere & "AND div='" & Me!cbotrust & "' "
End If
If Trim(Me!Cbopaynumber & "") <> "" Then
strwhere = strwhere & "AND fwdformatch=#" & Format(Me!Cbopaynumber, "yyyy-mm-dd") & "# "
End If
If Trim(Me!cbodate & "") <> "" Then
strwhere = strwhere & "AND discipline='" & Me!cbodate & "' "
End If
If Trim(Me!Cbolocation & "") <> "" Then
strwhere = strwhere & "AND location='" & Me!Cbolocation & "' "
End If
If Not IsNull(Me.frajdcodeno.Value) Then
Select Case Me.frajdcodeno.Value
Case 1
strjdcodeno = "and jdcodeno is null "
Case 2
strjdcodeno = "and jdcodeno is not null "
'Case 3
'strjdcodeno = "and jdcodeno Like '*' "
End Select
strwhere = strwhere & strjdcodeno
End If
If Len(strwhere) Then strwhere = "WHERE" & Mid(strwhere, 4)
' Build the SQL string
strSQL = "SELECT * FROM tbltrustStaff " _
& "Where tbltrustStaff.

is null AND " _
& "tbltrustStaff.[transfer] = 0" _
& strwhere
'Comment and unomment the debug.print line for testing
Debug.Print strSQL
' Pass the SQL string to the query
qdf.SQL = strSQL
' Turn off screen updating
DoCmd.Echo False
' Check the state of the query and close it if it is open
If Application.SysCmd(acSysCmdGetObjectState, acQuery, "qryStaffListQuery") = acObjStateOpen Then
DoCmd.Close acQuery, "qryStaffListQuery"
End If
Select Case Me.Fraoutput.Value
Case 1
Fraoutput = 1
DoCmd.SendObject acQuery, "qryStaffListQuery", "MicrosoftExcelBiff8(*.xls)", "", "", "", "", "", False, ""
'Case 2
'strjdcodeno = "and jdcodeno is not null "
Case 3
Fraoutput = 3
DoCmd.SendObject acReport, "rptdivision", "SnapshotFormat(*.snp)", "", "", "", "", "", False, ""
End Select
' Open the query
DoCmd.OpenQuery "qryStaffListQuery"
cmdOK_Click_exit:
' Turn on screen updating
DoCmd.Echo True
Me.cbotrust.Value = ""
Me.Cbopaynumber.Value = ""
Me.cbodate.Value = ""
Me.Cbolocation.Value = ""
' Clear the object variables
Set qdf = Nothing
Set db = Nothing
Exit Sub
cmdOK_Click_err:
' Handle errors
MsgBox "An unexpected error has occurred." & _
vbCrLf & "Please note of the following details:" & _
vbCrLf & "Error Number: " & Err.Number & _
vbCrLf & "Description: " & Err.Description _
, vbCritical, "Error"
Resume cmdOK_Click_exit
End Sub​

 

[Remou]

Just comment out this line:
[tt]If Len(strwhere) Then strwhere = "WHERE" & Mid(strwhere, 4)[/tt]
I think that should work.

 

[lars7]

Thanks again Remou it worked and have a Happy New Year.