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

 

[Frink]

Hallo,

Consider how you want the user to be able to search.
What would be easiest, most logical for your users? Do they use any similar things at the moment? What do they think of them?
When you have discovered the best way, then work out how to program it.
Whatever you decide, you'll be able to code, Access is good like that, but don't just code an easy programming solution if users are going to hate it and curse you for it. No-one needs that.

To specify a Date Range, you need two dates, or a date and a 'before, or or after' operator, or both.
There are little popup forms you can get for free which allow easy entering of a date in a calendar view. I use one called MiniCalendar by Scott Cybak, although I couldn't find it on the web last time I looked.

Radio buttons to select which column to search would work, but is it obvious? Will people like it?
Maybe separate search fields for each column would be better?

Plenty to think about there,


- Frink

 

[SilFaz]

Hi Frink,
Good comments and I've given them a think. The database is used to track users who have been trained on particular systems. There would only be three people using the DB since we only have three trainers. Our current method of tracking who was trained is an Excel spreadsheet.

The columns in the main table that collect dates correspond to various modules that we are training users on. When users attend a training session, we enter the date that they attended. I have created a form for entering new users and dates, a form for searching particular users and dates, and a form to edit users.

Currently, I can only search by one specific date per column. What I need is to be able to display all of the users who were trained on one module between April 2005 and Dec 2005.

Since there are about 10 modules that we are training on, having individual start and end dates would make the form look cluttered. I want only two (or 4) start and end dates and then using Radio Buttons the users can decide which module they want to search.

How would I program radio buttons to look at specific columns and then do a date range search? My VB skills are poor.

Thanks, Silvia

 

[Frink]

Hallo,

You'd have to get the filter form to create the recordsource for the data form based on the data entered.

This is going to require a bit of VB as it will need to be tailored to your app, but.

If your filter form has an 'Apply' button, then in the OnClick event procedure put

dim strSQLQuery as String
dim strField as string
strSQLQuery = "SELECT * FROM tblData "
Select Case me!fraModule
  case me!optModule_First.OptionValue
    strField ="datColumn1"
  case me!optModule_Second.OptionValue
    strField ="datColumn2"
  case me!optModule_Last.OptionValue
    strField ="datColumn3"
  case else       
    strField =""
end select
if strField <>"" then
  If isdate(me!txtStartDate) then 
    if isdate(me!txtEndDate) then
      strSQLQuery =strSQLQuery & "WHERE " & strField & ">=#" & me!txtStartDate & "# AND " & strField & "<=#" & me!txtEndDate & "#"
    else    'Start date only specified
      strSQLQuery =strSQLQuery & "WHERE " & strField & ">=#" & me!txtStartDate & "#"
    endif
  else      'End date only specified
    if isdate(me!txtEndDate) then
      strSQLQuery =strSQLQuery & "WHERE " & strField & "<=#" & me!txtEndDate & "#"
    endif
  endif
endif
forms!frmData.Recordsource=strSQLQuery

optModule_First is the name of the first option button.
Most people don't name their option buttons and use the default optionvalue numbers. It is better to name any control referred to in code as it makes your program easier to understand. The only time I wouldn't do this was if the Modules were numbered 1, 2, 3, 4 etc. and you were sure there would never be a 1A etc.

- Frink

 

[SilFaz]

Hello Frink,
I am sorry for my late response... I am in Australia and the time difference doesn't work in my favour.

The code that you sent me looks very good and I have managed to change most of it to fit my db.

I have a few questions though.
1. In the following bet of code, what does the fraModule refer to?

strSQLQuery = "SELECT * FROM tblData "
Select Case me!fraModule
  ...

2. When selecting the module columns from my table, can I put it like this?:

case me!optPtList.OptionValue
    strField ="tblTraining.[Pt Lists]"

*my syntax is strField = "[Table Name].[Column Name]"

3. At the end you put the following code:

forms!frmData.Recordsource=strSQLQuery

Does Recordsource refer to anythig specific on my form or is it a reserve word for VB?
Should I just put forms!frm_Find.Recordsource=strSQLQuery?

4. Finally, I already have a cmdFind button that calls a query to find information. Should I put the code that you sent under that command or should I create a new button that does only the date search?

Thank you for all of you help so far!!!!!!
Silvia

 

[Frink]

Hallo,

1. fraModule is the Option Group (or frame) control which holds the radio button selectors

2. Yep, I never have spaces in table or field names so don't need the square brackets.

3. RecordSource is a form property. It holds the sql statement, or table/query name of the data displayed in records on the form.
It's the recordsource of the form displaying the data that you want to set, not the recordsource of the find form.

4. It depends on your user interface, but if you have a separate Find form, then I think one button which sets all criteria would probably make more sense to the user, so it can go under cmdFind.

Hope that helps. A lot depends on how you have structured your forms, which is notoriously difficult to put into words.
Any further questions, just ask,

- Frink

 

[SilFaz]

Hi Frink,
The info you sent was excellent and I think I have the code working. I already have a subform on my main Frm_Find search form. The results of the Name and Group query are working and the entries appear in the form in Tabular layout.

How do I link the new date range VB code to this subform and have the same info (Name, Group, various module dates) appear which falls between the dates that I require?

Right now, I *think* that the code is working, but I am not sure because I do not have a place for it to be returned to so I can't check.

Thanks again,
Silvia

 

[Frink]

Hallo,

It is best to have a consistent method for performing your filtering, so add what filtering you need to the above code.

As I understand it, you have a form which contains some controls used for filtering on it. It also has a subform to display the filtered results, and a button to perform the filtering.
Is this correct?

If you let us know your form layout, and the other filtering controls you have we can post better code.
Please include as much detail as you can to help us to help you.

- Frink

 

[SilFaz]

Hi Frink, I have created a Blog to make this easier. I have posted screen shots of my database. In it, I have my main table, my search query, and my main search form. I hope that helpe you get an idea of what I am doing and how.

So far, your help has been excellent. Thank you for it.

Here is the address of the blog:
trainingdbsilfaz *dot* blogspot *dot* com/

Silvia

 

[Frink]

Hallo,

That's made it clearer.

Would you prefer to have a single 'Find' button, rather than two? I would suggest it would be better as the layout of your form does not easily distinguish which fields are used in conjunction with each button.
A single find button would generally be more elegant, more obvious and more flexible. Of course there may be specific reasons why you want it done this way, and that's fine too.
You could then get rid of your Module Training date find fields.

As indicated earlier, a good way of implementing a Filter, is to use code to build up an SQL Query and set the subforms recordsource when the Find button is clicked.

Before I go any further I'll suggest that you use the standard-ish MS Access naming conventions, where you add a type prefix to all your controls, fields and variables.
ie. Training becomes tblTraining, Name becomes strName, MedChart datMedChart, Role intRole (or lngRole, dblRole etc.) and remove the spaces from your field names, Pt Lists becomes datPtLists, Password Allocated become ysnPasswordAllocated. This makes it much easier to see what's going on (when you get used to it) particularly when you come back to change your db in a years time.

Private Sub btnFind_Click()
  Dim strSQLQuery as String
  Dim strField as string
  strSQLQuery = ""
  if len(nz(me!txtName,""))>0 then strSQLQuery =strSQLQuery & " AND strName Like """ & me!txtName & "*"""
  if len(nz(me!txtGroup,""))>0 then strSQLQuery =strSQLQuery & " AND strGroup Like """ & me!txtGroup & "*"""
  Select Case me!fraModule
    case me!optModule_First.OptionValue
     strField ="datColumn1"
    case me!optModule_Second.OptionValue
      strField ="datColumn2"
    case me!optModule_Last.OptionValue
      strField ="datColumn3"
    case else       
      strField =""
  end select
  if strField <>"" then
    If isdate(me!txtStartDate) then 
      if isdate(me!txtEndDate) then
        strSQLQuery =strSQLQuery & " AND " & strField & ">=#" & me!txtStartDate & "# AND " & strField & "<=#" & me!txtEndDate & "#"
      else    'Start date only specified
        strSQLQuery =strSQLQuery & " AND " & strField & ">=#" & me!txtStartDate & "#"
      endif
    else      'End date only specified
      if isdate(me!txtEndDate) then
        strSQLQuery =strSQLQuery & " AND " & strField & "<=#" & me!txtEndDate & "#"
      endif
    endif
  endif
  if strSQLQuery like " AND *" then
    strSQLQuery="WHERE " & mid$(strSQLQuery,5)
  else
    strSQLQuery=""
  endif
  forms!frmData.Recordsource="SELECT * FROM tblTraining " & strSQLQuery
lblExit:
  Exit Sub
lblErr:
  Msgbox Err.Description,vbExclamation,"Error in frmFind.btnFind"
   Resume lblExit
End Sub

Other things you might like to consider is putting the Find button at the end of the Find fields, as people (in the West) generally work from top to bottom, so you get the form title, form fields, find button, results as you look down the page. I'd keep Add User at the top as it is not part of the find, although it might be better on nother form? Close could go at the top or bottom, up to you.
You could also implement a field colour coding scheme, with a different colour background for writeable, read-only and Find fields, to aid the user.
One last thing, Radio buttons (the round ones) are generally used in an option group to indicate you can only select one of the options. Checkbox buttons (square) are used where more than one option can be selected.

Just some comments, take them or leave them. You know your users better than I do. With forms, I think looking at MS products is a good way to start. See how they do things in Wizards etc. They're not perfect, but they are usually well done and people are used to them.

Hope the code works,

- Frink

 

[SilFaz]

Hi Frink, I am still having a hard time...

In response to your last message, I have changed my DB to match the naming conventions. You are absolutley correct - when I look at it in the future, it will be easier to understand. I've also changed my radio buttons to the round ones, not the checkboxes.

On my form, for now I do have two buttons... it is just to do some testing. The top one calls the query which is the recordsource for my subform. I have put the code that you posted for the other button OnClick. I have changed the names to match my fields. When I click the button, the subform doesn't react at all. It does not look up based on the criteria that is input. (I'm aware this all has to do with the recordsource...)

How do I change the recordsource for the subform so that the VB date range search results will be displayed. At the moment, I can't figure out the SQL query to base the subform on... :-(

In the end, I hope to have only one search button.

Thank you! Silvia

 

[Frink]

Hallo,

OOps. Sorry about that.

Hope you've managed to fix the problem, it should have been quite obvious.
I forgot to change the line above the lblExit:
It should be:
Me!frmSubformName.Form.Recordsource="SELECT * FROM tblTraining " & strSQLQuery

ie. the recordsource you want to change is the subform.
Me is the currrent form,
Me!frmSubformName is the subform control on Me
Me!frmSubformName.Form is the actual subform itself

I think Me!frmSubformName.Recordsource might work, but only because MS Access corrects the syntax (which imho, it shouldn't as it makes us sloppy and makes code illogical)

Hope that helps,


- Frink

 

[SilFaz]

It seems like it should be so easy, and yet, this is giving me such a hard time...

I did as you said. I still have the second button on my form for testing purposes. OnClick for that button, I pasted your big VB code that you last sent and modified the line you mentioned in your last posed. My subform is called frmFindSubform. I am getting an error stating
"Database can't find the field 'frmFindSubform' referred to in your expression."

For my big form Frm_Find, the recordsource is set to tblTraining. For the subform, the recordsource is set to the query that I posted on the blog (this may be part of the problem). The SourceObject of my subform is frmFindSubform and the Link Child and Master Fields are strName.

I am very confused...
Thanks, Silvia

 

[Frink]

Hallo,

This is the sort of thing that is difficult to explain, but I'll have a go.
Try to think about it logically.

A forms RecordSource defines the records to be displayed on it.

Q. What records do you want displayed on Frm_Find?
A. None, you want them displayed on the subform, so clear the recordsource for Frm_Find.

The controls on Frm_Find are going to determine the record source of the subform when the find button is pressed, therefore the frmFindSubform recordsource should be blank too.

You are now controlling the recordsource for the subform, so you don't want access to do any filtering or linking, so set your forms to not allow filters and clear the master/child linking fields.

Now when you click the Find button you want it to run my code, so put that in the OnClick event procedure. The line I modified in the last post is the one that sets the recordsource in the subform, but the error message says it can't find 'frmFindSubform'. This probably means that your subform control is named something else. Either rename the subform control, or change the name in the modified line.
Remember, Me!frmSubformName is the subform CONTROL on the current form.

Access programming is quite tricky because access will do quite a lot for you, but if you want to do things yourself, you have to undo all it's 'helpful' features, like filtering and linked fields.

It's sometimes difficult to take a step back and look at your program from another perspective, especially when you get bogged down in code. Make sure you know what you intend (in this case a form to set up a filter which sets the recordsource for the subform to display the records)
before you start coding. If you change direction mid-coding, you have to be careful to remove what no longer applies (like the main form recordsource)

As you do more, you'll get more experience of what works and what doesn't, so stick with it, you'll be fine.
There are various articles on the web about good HCI design which can be applied to Access forms, but also use you r own experience. If you find a tool you like, think about how they have laid it out, what makes it easy to use? Conversely, if you find a tool you hate (and there's plenty of them) what's bad about it? You can use this experience to make better forms in the future.
Also you have to bear in mind your target user, or range of users.
As people are generally familliar with MS office, why not get ideas from that interface. The last paragraph in the PS below describes a filter system very similar to the basic Auto Filter in MS Excel.

- Frink

PS. A good idea is to put a call to btnFind_Click in the Open event procedure of Frm_Find. This way it will populate the subform with the filter, thereby making everything consistent at the start.
If you did this then you could set up any initial values as default values in the controls.
If you wanted some default records displayed, but not set up in the filter controls, then don't put the call into the Open Event and just set the subforms recordsource to what you want.
You might want to include a Clear Filter button to make all the filter controls blank (or their default value):
ie. me!txtName="" etc.
You could call btnFind_Click at the end of the clear filter procedure to implement the blank filter, but I probably wouldn't especially if you have a slow connection. If they want to apply the cleared filter then they can click the other button afterwards. The button is Clear Filter and not Clear and Apply Filter, after all.
You could do away with the Filter button completely and put calls to btnFind_Click in the After Update procedure of all filter controls. Probably not one for this app, as the use of the option group makes the filter more complex. This method is good if you have a filter field above each column, aligned with the column, but now I'm digressing.

 

[SilFaz]

Success!!!!! Frink, it works like a charm. I know it was a long and drawn out process but you were an excellent resource!

I am down to having one Search button and it lets me search seperate columns to find date ranges. It comes up beautifully. I could not have done it with out you.

Another question that I have is for selecting the Group. I have a predetermined list of groups that a person can belong to (and they can only belong to one). Right now, we are doing a Group filter like this:

 If Len(Nz(Me!txtGroup, "")) > 0 Then strSQLQuery = strSQLQuery & " AND strGroup Like """ & Me!txtGroup & "*"""

The users choose from a combo box. Can we set this filter to choose from the list? Eg. Right now I have a Medical and a Medical Records group. When I select Medical, people from both groups are shown...

Thank you so very much! :-D
(Also, I am in the process of applying your other layout and functionality tips.)
Silvia

 

[Frink]

Hallo,

Sorry, I couldn't determine your requirement from the information supplied.

'Can we set this filter to choose from the list?'
How do you mean?
What filter? What list?
Group is just a list of groups. If the groups are held in tblPracticeGroup then set the cboGroup Rowsource to "SELECT DISTINCT strGroup FROM tblPracticeGroup ORDER BY strGroup" (in form design view)

'When I select Medical, people from both groups are shown...'
Are you saying that's what you want to happen, or that is an error.
Try and explain what you are hoping to achieve, with what controls, and remember that I don't know your application.

- Frink

 

[SilFaz]

Sorry, I see that I was quite vague. Currently, when I select medical, it shows me everyone in Medical and in Medical Records. What I want is for those to not to be grouped together. This is happening because in the VB code for btnFind we have the code set to:

if len(nz(me!txtGroup,""))>0 then strSQLQuery =strSQLQuery & " AND strGroup Like """ & me!txtGroup & "*"""

This means that if Medical is selected, the vb code runs 'Medical *' (I'm talking myself through this to make sure I understand).

What I need to do is change the VB code to return records based on the static name, not the name*... This is where I have my problem - trying to figure out how to pass the full name. I know it's simple and has to do with taking out just a bit of text from above.

Oh, the above tip with a Rowsource query was great. I did have a dropdown box, but mine worked differently.
Silvia

 

[PHV]

Replace this:
if len(nz(me!txtGroup,""))>0 then strSQLQuery =strSQLQuery & " AND strGroup Like """ & me!txtGroup & "*"""
with this:
[tt]If Trim(Me!txtGroup & "") <> "" Then strSQLQuery = strSQLQuery & " AND strGroup='" & Me!txtGroup & "'"[/tt]

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

 

[SilFaz]

That worked exactly right! Thank you PHV.

I'm sorry, I have one more question... I promise this is it (I hope).

The date range search works for the most part. The problem is hard to explain because often it shows dates outside of the range. As I did some more testing, it became even more strange.

It seems to be getting the day and the month mixed up... If I am looking for who was trained in November, I enter StartDate: 01-Nov-05 and EndDate: 30-Nov-05 (I set everything to Medium Date) The results that I get are everything from Jan - November.

If I put in 06-Nov-05 to 30-Nov-05, I get everything from the 6th of June (the 6th month) to November. The same happens as I go up in the days... eg. 8-Nov = everything from Aug - Nov, 10-Nov = Oct and Nov. If I put 11-Nov, i get everything from 11-Nov to end of Nov. 12-Nov comes up blank. From 13-Month-05 it is fine.

10-Jun-05 to 30-Nov-05 returns Oct-Nov!!! But as soon as I use 13-Month-Year, they all work fine.

 Select Case Me!fraModule
    Case Me!optMedChart.OptionValue
     strField = "datMedChart"
    Case Me!optPtLists.OptionValue
      strField = "datPtLists"
    Case Me!optMedHist.OptionValue
      strField = "datMedHist"
    Case Else
      strField = ""
  End Select
  If strField <> "" Then
    If IsDate(Me!txtStartDate) Then
      If IsDate(Me!txtEndDate) Then
        strSQLQuery = strSQLQuery & " AND " & strField & ">=#" & Me!txtStartDate & "# AND " & strField & "<=#" & Me!txtEndDate & "#"
      Else    'Start date only specified
        strSQLQuery = strSQLQuery & " AND " & strField & ">=#" & Me!txtStartDate & "#"
      End If
    Else      'End date only specified
      If IsDate(Me!txtEndDate) Then
        strSQLQuery = strSQLQuery & " AND " & strField & "<=#" & Me!txtEndDate & "#"
      End If
    End If
  End If

So it seems that some how it is confusing the DAY as the MONTH even though the MONTH is there in text.

Oh, there is one more question. How can I get the results in the subform to return in order by Date based on the column searched (strField above) and then 'subsorted' in alphebetical order?

Thank you again, you have given excellent help so far.
Silvia

 

[Frink]

Hallo,

I'm afraid this is because Access doesn't handle UK dates properly.
dd/mm/yyyy
If dd > 12 then it knows that it must be a day, not a month, if it's 12 or less then it assumes its the US format mm/dd/yyyy.
At least that's how I think it works.

My solution would be to create a function in a module:

Public Function strDateStamp(byval pdatDate as Date) as string
  strDateStamp=format$(pdatDate,"yyyymmdd") 'Use "yyyymmddhhnnss" to include time
End Function

Then whenever you need to compare a date, or sort by a date, use the function on the date rather than the exact date itself.

ie. Replace:
" & strField & ">=#" & Me!txtStartDate & "#
with
strDateStamp(" & strField & ")>=" & strDateStamp(Me!txtStartDate) & "

To sort it how you want, change
Me!frmSubformName.Form.Recordsource="SELECT * FROM tblTraining " & strSQLQuery
to
Me!frmSubformName.Form.Recordsource="SELECT * FROM tblTraining " & strSQLQuery & " ORDER BY strDateStamp(" & strField & "), strName"

where strName is the name of the field you want to sort alphabetically on.

- Frink


PS. PHV:

Replace this:
if len(nz(me!txtGroup,""))>0 then strSQLQuery =strSQLQuery & " AND strGroup Like """ & me!txtGroup & "*"""
with this:
If Trim(Me!txtGroup & "") <> "" Then strSQLQuery = strSQLQuery & " AND strGroup='" & Me!txtGroup & "'"

I agree that your expression is better as it handles spaces, but I thought that comparing a string against "" was not as quick as comparing len() against 0. Is this true, or it it now optimised better?
I'm too lazy to run a test...