Searching Date Ranges in Several Columns 3

[SilFaz]

I have a form which is used to search my main table based on criteria like Name and Group and return the results in a subform. In addition to Name and Group columns, I currently have 3 columns in my table which represent modules of a system. When users are trained on the modules, I put the date that they were trained into that field. At this time, I can search for specific dates, but I also want to search by date ranges.

Is it possible to have date range Search Textboxes and a checkbox to select which Module column I want the search to apply to?

For a Date Range search, do I have to use separate textboxes for startMonth, endMonth, startYear and endYear, or can I do a startMnthYr, endMnthYr?

Especially if I have to do the months and years separately, I need to have a check box system for choosing which module column to search.

Any help is greatly appreciated!
Silvia

 

[SilFaz]

Hi guys, I'm sorry, I am still having another problem. I changed the code for date comparision as you told me to above. My code for that section is now

   If strField <> "" Then
    If IsDate(Me!txtStartDate) Then
      If IsDate(Me!txtEndDate) Then
        strSQLQuery = strSQLQuery & " AND strDateStamp(" & strField & ")>=" & strDateStamp(Me!txtStartDate) & " AND strDateStamp(" & strField & ")<=" & strDateStamp(Me!txtEndDate) & ""
      Else    'Start date only specified
        strSQLQuery = strSQLQuery & " AND strDateStamp(" & strField & ")>=" & strDateStamp(Me!txtStartDate) & ""
      End If
    Else      'End date only specified
      If IsDate(Me!txtEndDate) Then
        strSQLQuery = strSQLQuery & " AND strDateStamp(" & strField & ")<=" & strDateStamp(Me!txtEndDate) & ""
      End If
    End If
  End If
  If strSQLQuery Like " AND *" Then
    strSQLQuery = "WHERE " & Mid$(strSQLQuery, 5)
  Else
    strSQLQuery = ""
  End If
  Me!FindQ.Form.RecordSource = "SELECT * FROM tblTraining " & strSQLQuery & " ORDER BY " & strField & ", strName"
lblExit:
  Exit Sub

It is now giving me a run-time error (2001) "You cancelled a previous operation" and is highlighting the row:

Me!FindQ.Form.RecordSource = "SELECT * FROM tblTraining " & strSQLQuery & " ORDER BY " & strField & ", strName"

Any ideas as to what might be causing this? Could it be something within this Procedure or would it be within the Module itself?

The Public Function strDateStamp code above - should I put that into my existing Object Class module which runs the search or should I create a new Module and call it modDateStamp?

Thanks again :-S

 

[Frink]

Hallo,

I'm guessing, and it is just a guess, that maybe the record on display has been edited, then when you reset the recordsource it moans as it hasn't saved the record.

The only way to confirm this would be to try it out.
You shouldn't really try any fixes until you know what the problem is.

It may be that the code is writing to a bound control before it gets to this point. Look for any Me<something>=<somethingelse> statements.

It may be that the user can edit the record then click Find without saving.
If this is the case then Me.Dirty will be true if the record has been changed and Me.Undo or docmd.SaveRecord can be used (I think)
Something like
If Me.Dirty then
if Msgbox("Do you want to save your changes to this record before filtering",vbQuestion+vbYesNo,"Record Changed")=vbYes Then
DoCmd.SaveRecord
Else
Me.Undo
End If
End If

Have a little tinker in this area and see what you can find,

- Frink

 

[SilFaz]

I wasn't sure how to insert the Me.Dirty statement... I did however clean up my code quite a bit and got rid of prodecures that I didn't need. The good news is that the Date Range search works now!!!!

I think the problem was that a lot of people have already been trained, but we don't have the date that they were trained on. It was giving me the error because there were blanks in the specific column in the table.

Thank you again for all of you fantastic help. I'm glad that you hung in there.

I have one more question, but feel free not to answer as it is not mission critical at this point. How can I get it to return values even if there are blanks? For example, it was giving me the error when I was searching date ranges of Nursing training because I didn't have dates for all of them. I would like it to still return the specified dates even if not all of them have dates entered...

Silvia