Newbie: Adding Control to Filter Form View Data by Date

[RWRinn]

Hi,

I'm using Access 2003, and have only done a couple of small things with it thus far so not that familiar with it (or similar programs) yet.

I have created a simple table with several fields (mostly text), one of which is a date. I've set the Data Type:Format for that field to Short Date in the Tableesign View.

Then I created a form that just displays my fields in tabular layout.

When I look at the form in Form View mode, it displays the entire contents of the table (i.e. all the data) in rows. I can use Filter By Form and Apply Filter Sort to make it show only rows with a specific date. However, what I would like to be able to do is have the facility to type in a date in a box up at the header area (nothing fancy, no calendar entry required or anything), and it automatically filters the data you see below just to show entries with that date.

Have been looking in the help, google, and in these forums for something similar that I could adapt, but everyone else's problems seem a lot more complex. Help!

Many thanks,

Richard.

 

[RWRinn]

Two days and no responses... Did I describe the problem badly? Is it a silly problem? HAILP!!!

 

[DCrake]

If you have an unbound field in your form header set the the input mask to 99/99/000;0;_

set the format to dd/mm/yyyy

Then on the after update event of the control

Me.Filter = "[DateField]=#" & Me.Txtdate & "#"
Me.FilterOn = True


Where DateField is the name of the field in the table.

David

 

[RWRinn]

Thanks David.

Having a bit of trouble.

I've created the form from scratch again, using the new form wizard and making a tabular form from the ManagerData table where my data is.

Up in the header section, I've inserted a 'text box' (is this correct?).

In the properties for the text box (Text24), I've gone into the Data tab, and the Input Mask setting. If I click the ... at the right hand side of this it lets me select the format, so I've selected Short Date, and now the box contains the details: 00/00/0000;0;_ The format setting in the format tab is set to Short Date also.

In the Event tab, in the After Update setting, I have selected [Event Procedure] from the drop down list, and then clicked on the ... at the right hand side to build the event. This opens Visual Basic. I've pasted in your code and changed the DateField to Despatch, which is the name of the date field in my ManagerData table.

When I type a date into the text box (it comes up with the input mask okay), upon hitting enter, Visual Basic pops up with a Compile Error saying 'Method or data member not found', with '.Txtdate' highlighted. Was I supposed to change that to something else?!

Apologies for my lack of familiarity!


Many thanks

Richard.

 

[DCrake]

You say you named your field Text24 but refer to .txtDate? anyway you need Me.TxtDate

Or you can use Me.ActiveControl

David

 

[RWRinn]

Thanks David, I'm getting somewhere now!

I gathered from your reply that instead of .txtDate I should have the name of my field (Text24) so changed it accordingly, and now it is sort of working:

Me.Filter = "[Despatch]=#" & Me.Text24 & "#"
Me.FilterOn = True

When I enter a date into the text box, it is definitely applying a filter. However, for some reason that I can't fathom, when I type some dates it is filtering correctly and as expected, but for other dates no data is showing up, even though I have info in my table with those dates in the required field. Any ideas why that would happen?

Apologies for not really knowing what I'm doing in Visual Basic (never used it before). The last time I programmed anything was about 20 years ago in assembly language on a Commodore 64!

Thanks for your patience and help!



Richard.

 

[RWRinn]

Not sure why the filter was working when I typed some dates in, but not for others, so I've deleted all the records in my table and entered new info from scratch.

Ran the form again and it was looking good, all the records I entered were showing up when I typed in the relevant date to my entry field which applies a filter.

Then when I got to the first one with a February 2010 date nothing showed up. All the January ones fine, but none of the February information was coming up, although the information was there in the table and entered in the same way as the info with January dates.

Just to recap, I have an unbound text field in the head of my form which has a short date input mask, and and After Update event procedure:

Private Sub Text24_AfterUpdate()

Me.Filter = "[Despatch]=#" & Me.Text24 & "#"
Me.FilterOn = True

End Sub

Text24 is the name of the input box, and Despatch is the name of the date field in the table ManagerData.

Any ideas?



Richard.

 

[vbaInet]

Not sure why the filter was working when I typed some dates in, but not for others, so I've deleted all the records in my table and entered new info from scratch.

Ran the form again and it was looking good, all the records I entered were showing up when I typed in the relevant date to my entry field which applies a filter.

Then when I got to the first one with a February 2010 date nothing showed up. All the January ones fine, but none of the February information was coming up, although the information was there in the table and entered in the same way as the info with January dates.

Just to recap, I have an unbound text field in the head of my form which has a short date input mask, and and After Update event procedure:

Private Sub Text24_AfterUpdate()

Me.Filter = "[Despatch]=#" & Me.Text24 & "#"
Me.FilterOn = True

End Sub

Text24 is the name of the input box, and Despatch is the name of the date field in the table ManagerData.

Any ideas?



Richard.

Are you sure your dates are saved in a date format such as 12 January 2010 (for example)? And not just January 2009?

Could be an idea also to trim your input:

Private Sub Text24_AfterUpdate()

Me.Filter = "[Despatch]=#" & Trim(Me.Text24) & "#"
Me.FilterOn = True

End Sub

 

[RWRinn]

Thanks for your reply!

The format for my Despatch (date) field is set to short date (as is the input mask on the input box), so they're all like 21/01/2010 02/02/2010 etc.

I tried the trim thing you suggested, but nothing different happened. I take it that was just to do with if I had a different date format than short date perhaps?

R.

 

[JANR]

Pretty sure you've hit the "regionalSetting-bug". Access VBA/SQL uses US-format on dates. Try a force the date to this format:

Private Sub Text24_AfterUpdate()
Me.Filter = "[Despatch]=" & Format(Me.text24, "\#mm\/dd\/yyyy\#")
Me.FilterOn = True
End Sub

JR

 

[RWRinn]

Excellent! The February entries are now showing up also! Thank you all for helping!