Display fields between given dates/years

[Guest]

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??

 

[Allen Browne]

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

 

[Guest]

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!


--
Thanks, Jorgen

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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??

 

[Allen Browne]

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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!


--
Thanks, Jorgen

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

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??