Display fields between given dates/years

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jorgen - 27 Jul 2006 08:11 GMT

Hello

I need some help!

I have made a form where the user should be able to extract data between
entered years, which can be chosen from two comboboxes giving the from and to
year parameter!

I have difficulties getting access to extract only years in this interval!

Any tips??

Signature

Thanks, Jorgen

Reply to this Message

Allen Browne - 27 Jul 2006 08:43 GMT

You want to filter a date/time field, for all dates between years?

Let's assume:
- The date/time field is named MyDateField.
- You have 2 text boxes on your form, called txtStartYear and txtEndYear.
- You have set their Format property to General Number so that only numeric
values will be accepted.

You could then apply a filter to your form like this:

Dim strWhere As String
If Me.txtStartYear > 1900 And Me.txtStartYear < 2999 And _
Me.txtEndYear > 1900 And Me.txtEndYear < 2999 Then
strWhere = "[MyDateField] Between #1/1/" & txtStartYear & _
"# And #30/12/" & txtEndYear & "#"
Me.Filter = strWhere
Me.FilterOn = True
Else
Msgbox "Enter 4-digit years in both boxes."
End If

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hello
>
[quoted text clipped - 8 lines]
>
> Any tips??

Reply to this Message

Jorgen - 27 Jul 2006 10:07 GMT

Thank you Allen!

But how to display this?

I have tried with an IIF sentence in the query, (as you did in ser-62.html,
which is great) but it doesn't work, as it displays all years anyway!!

Thank you!

Signature

Thanks, Jorgen

> You want to filter a date/time field, for all dates between years?
>
[quoted text clipped - 29 lines]
> >
> > Any tips??

Reply to this Message

Allen Browne - 27 Jul 2006 13:10 GMT

Okay, the suggestion was to use a form to display the results.
Instead of putting the criteria in the query, use the Filter of the form.

If you want to put it into the Criteria of your query, under the date/time
field, try:
Between DateSerial([StartYear], 1,1) And DateSerial([EndYear],12,31)

When you run the query it will pop up 2 boxes asking for the StartYear and
EndYear.

To ensure only Numbers can be entered as the years, choose Parameters on the
Query menu (in query design view). Access opens a dialog. Enter 2 rows,
using exactly the same spelling and specify the Long Integer type, i.e.:
[StartYear] Long
[EndYear] Long

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Thank you Allen!
>
[quoted text clipped - 43 lines]
>> >
>> > Any tips??

Reply to this Message