Don't show a record if field is empty 1

[cooldisk2005]

All,
I have the following code:

Private Sub cmdSearch_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'Constant Select statement for the Query definition
'Constant Select statement for the Query definition
strSQL = "SELECT tbl_showmoney.regID, tbl_showmoney.dateEntered, tbl_showmoney.name, tbl_showmoney.company, tbl_showmoney.mailingAddress, tbl_showmoney.city, tbl_showmoney.stateCan, tbl_showmoney.zipCode, tbl_showmoney.country, tbl_showmoney.telephoneNo, tbl_showmoney.faxNo, tbl_showmoney.email, tbl_showmoney.contype, tbl_showmoney.totalCamp, tbl_showmoney.idMtg, tbl_showmoney.day1Con, tbl_showmoney.day2Con, tbl_showmoney.day3Con, tbl_showmoney.day4Con, tbl_showmoney.day5Con, tbl_showmoney.day6Con, tbl_showmoney.day7Con, tbl_showmoney.bkstCount, tbl_showmoney.lunchTickets, tbl_showmoney.dinnerTickets, tbl_showmoney.drinkTickets " & _
"FROM tbl_showmoney"

strWhere = "WHERE"

strOrder = " ORDER BY tbl_showmoney.regID;"


'Set the WHERE clause for the QueryDef if information has been entered into a field on the form
If Not IsNull(Me.name1) Then '<--If the textbox txtFName contains no data THEN do nothing
strWhere = strWhere & " (tbl_showmoney.name) Like '*" & Me.name1 & "*'  AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.company) Then
strWhere = strWhere & " (tbl_showmoney.company) Like '*" & Me.company & "*'  AND"
End If

If Not IsNull(Me.contype) Then
strWhere = strWhere & " (tbl_showmoney.contype) Like '*" & Me.contype & "*'  AND"
End If

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 4)

'Pass the QueryDef to the query
Set qryDef = dbNm.QueryDefs("qry_showmoney")
   qryDef.SQL = strSQL & " " & strWhere & "" & strOrder
   
'Open the Query
    
    Dim dbs As DAO.Database, rst As DAO.Recordset
Dim rsCnt As Integer 'the counter
 
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("qry_showmoney", dbOpenDynaset)
 
With rst
  If .RecordCount > 0 Then 'What you want done
    DoCmd.OpenForm ("frm_registrationDeskSearch"), acNormal, "[regID]"
    DoCmd.Close acForm, "frm_search"
    
    
  Else
    MsgBox "There is no data that meets your criteria.  Please try again.", vbOKOnly, "No Data Found" 'What you want done
    name1.Value = ""
    company.Value = ""
    contype.Value = ""
    name1.SetFocus
  End If
End With

Set dbs = Nothing
Set rst = Nothing

   

End Sub

How do I modify the code to not show a record in the results if the dateEntered field has nothing in it?

 

[PHV]

Replace this:
strWhere = "WHERE"
with this:
strWhere = "WHERE dateEntered Is Not Null AND"

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

 

[cooldisk2005]

PHV,
I put your suggestion in, but the records that don't have anything in the dateEntered field is still coming up in the results.

Any other suggestions?

 

[PHV]

What is the RecordSource of frm_registrationDeskSearch ?

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

 

[cooldisk2005]

It is: qry_showmoney, so it is going to the right query, but I don't know what it is not working.

 

[PHV]

And what about opening your form like this ?
DoCmd.OpenForm "frm_registrationDeskSearch", acNormal

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

 

[cooldisk2005]

It still opens the records where if the dateEntered field is empty the record still shows.

I even opened just the query and it still does this (argggg)

 

[cooldisk2005]

Is there a way just to put some If/Then statement on the OnOpen event of the form to not show the records that don't have anything in the dateEntered field?

 

[PHV]

what is the data type of tbl_showmoney.dateEntered ?
I guess it isn't DateTime as a DateTime can't be empty ...
You may try this:[tt]
strWhere = "WHERE Trim(dateEntered & '')>'' AND"[/tt]

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

 

[cooldisk2005]

PHV,
That did the TRICK!!!!!

The data type of that field is Text, because if I use Date or DateTime, the results that go into the database is zeros, and I didn't want to see the zeros.

The database is a MySQL database, and I have a Microsoft Access database linking to that MySQL database.

That was my fault that I didn't give you the data type and I am really sorry about that!!!!

Thanks so much and I will give you a STAR!!!