Option Group 1

[lars7]

Can anyone see what is wrong with the select case option group that I've got here? I added this to a query that was working fine and it still does but completely ignores the option buttons when clicked.

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 strPayno As String
Dim strSQL As String
' Identify the database and assign it to the variable
Set db = CurrentDb
' 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 payno='" & Me!cbodate & "' "
Select Case Me.frajdcodeno.Value
Case 1
strjdcodeno = "='is null'"
Case 2
strjdcodeno = "='is not null'"
Case 3
strjdcodeno = "Like '*'"
End Select
End If
If Len(strwhere) Then strwhere = "WHERE" & Mid(strwhere, 4)
' Build the SQL string
strSQL = "SELECT * FROM tbltrustStaff " & _
strwhere
' 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
' 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 = ""
' 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 at a glance
strjdcodeno = "='is null'"
strjdcodeno = "='is not null'"
Should be
strjdcodeno = " is null"
strjdcodeno = " is not null"
I think.

 

[lars7]

Hi Remou,
I tried what you suggested but it is still ignoring the option group

 

[Remou]

Ah, well, at a second glance, you have not added it to strWhere ...

 

[lars7]

Hi again,
Strwhere was a part that PHV added for me I don't fully understand it, is it here.

If Len(strwhere) Then strwhere = "WHERE" & Mid(strwhere, 4)
' Build the SQL string
strSQL = "SELECT * FROM tbltrustStaff " & _
strwhere

 

[Remou]

What is it you want to be, for example, Null? Lets just say it is a field called jdcodeno. You need to insert another strWhere in here:

    If Trim(Me!cbodate & "") <> "" Then
        strwhere = strwhere & "AND payno='" & Me!cbodate & "' "
    Select Case Me.frajdcodeno.Value
        Case 1
            strjdcodeno = "jdcodeno is null "
        Case 2
            strjdcodeno = "jdcodeno is not null "
        Case 3 
            strjdcodeno = "jdcodeno Like '*' "
    End Select
    [blue]strWhere = strWhere  & strjdcodeno[/blue] 
End If

Because you have the select case after an if:
If Trim(Me!cbodate & "") <> ""
It will only be added if cbodate has a value. Is that what you intend?

 

[lars7]

Hi Remou,
It's working now but not the way I want it to. I didn't know about the Select Case after If, thanks for that, so it’s not going to plan. "jdcodeno" is a field on its own and I would like to call on all the records that have a code number or not whether there is a value in the three combo boxes or not. At the moment the "is Null" or "Is Not Null" is going into the "cbodate" field as I'm sure you know.

 

[Remou]

It is just a matter of moving the End If, so that Select Case is no longer enclosed in the If statement and adding jdcodeno to each Case. Try changing it like this, starting from the If for cboDate and ending at the End If for cboDate:

    If Trim(Me!cbodate & "") <> "" Then
        strwhere = strwhere & "AND payno='" & Me!cbodate & "' "
    End If

    Select Case Me.frajdcodeno.Value
        Case 1
            strjdcodeno = "jdcodeno is null "
        Case 2
            strjdcodeno = "jdcodeno is not null "
        Case 3 
            strjdcodeno = "jdcodeno Like '*' "
    End Select
    strWhere = strWhere  & strjdcodeno

 

[lars7]

Hi Remou,
I'm getting a message “syntax error (missing operator) in query expression 'div='add'jdcodeno like '*". “But the same is happening for "is null" or "is not null" too. If I only select an option it gives me "Syntax error in from clause"

 

[Remou]

Hmm, I'm not paying enough attention, it is jcodeno, a number, right? So it will not be Like anything. It can only be null, not null, equal to, or greater or less than something. I also missed an And, and a check for null:

If not isnull(Me.frajdcodeno.Value)
    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
End If

 

[lars7]

Sorry Remou I should have told you that jdcodeno was a text field they can look like:

pmhac01
ppcnm12
pssanc56

Here is the code with the additions so you know where we have got to.

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 strpayno 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 payno='" & Me!cbodate & "' "
End If

Select Case Me.frajdcodeno.Value
Case 1
strjdcodeno = "jdcodeno is null "
Case 2
strjdcodeno = "jdcodeno is not null "
Case 3
strjdcodeno = "jdcodeno Like '*' "
End Select
strWhere = strWhere & strjdcodeno
If Len(strwhere) Then strwhere = "WHERE" & Mid(strwhere, 4)
' Build the SQL string
strSQL = "SELECT * FROM tbltrustStaff " & _
strwhere
' 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
' 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 = ""
' 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]

Still, Like '*' is the same as Is Not Null, so you only have the two choices. So, as i was saying before, I think something like this:

'If frajdcode has an option selected
If not isnull(Me.frajdcodeno.Value)
    Select Case Me.frajdcodeno.Value
        Case 1
            strjdcodeno = "AND jdcodeno is null "
        Case 2
            strjdcodeno = "AND jdcodeno is not null "
        'Case 3 commented out
        'Case 3 
        '    strjdcodeno = "AND jdcodeno Like '*' "
    End Select
End If

However, if you have a textbox in addition to the option group, where the user can fill in a few letters, we will have to change the above.

 

[lars7]

Hi,
It dosn't like the first line:

If not isnull(Me.frajdcodeno.Value)

It goes red right away with:

compile error:
expected: Then or Go To

 

[Remou]

I always forget then!
If not isnull(Me.frajdcodeno.Value)[blue] Then[/blue]

 

[lars7]

This is what i have now and the query isn't responding:



'If frajdcode has an option selected
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 commented out
'Case 3
'strjdcodeno = "AND jdcodeno Like '*' "'
End Select
End If

 

[Remou]

So this this what you have, in full:

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 strpayno 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 payno='" & Me!cbodate & "' "
    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 " & _
             strwhere
             
    'Comment and uncomment 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
    ' 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 = ""
    ' 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

You will notice a debug.print line, you can cut the print from the immediate window to the query design screen to check if it looks ok there.

 

[lars7]

Hi Remou,
Could you tell me what you did because I don't really see any difference, other than the "Debug.print strsql", but it is almost there? It is filtering for "is Null" and "is not null" but the like'*' is giving the same result as the "is not null" I will need to find a way to run the query without any of the option buttons being pressed, after they are pressed they can't go to depressed unless the form is close, as some of the fields I would like to add to this query don't need that criteria.

 

[Remou]

Like '*' is the same as Not Is Null, I meant to comment out Case 3, as I have done in previous posts. What did you want to happen?

 

[Remou]

All those Ifs should mean that you will get a Where string with almost nothing in it if no options are selected. In the case of jdcodeno, the line If Not IsNull(Me.frajdcodeno.Value) Then means that nothing is added to the where string if no option is selected, which means that all jdcodes, both null and not null, will be included in the query as nothing has been excluded in the where string.

 

[lars7]

That's it now. I commented out case 3 and I have called the options:

1) is null
2) Is not null
3) All

And that's what I get.
Thanks Remou for all your time and patience today you have been great.

I will be looking to add two more option buttons tomorrow to out put the query in word or excel hopefully I can work it out myself now that I’ve had this one to learn from.
Thanks again Remou and Merry Christmas.