Between And dillemma

[JohnE]

I realize in looking back thru this newsgroup there are
other subjects that talk about between time periods. But,
the dillemma that I face is different so I need to make
this posting.
I recently inherited a db in which all fields are text
(except autonumber). The date fields are also labeled as
text. There are 3 fields for recording the date, Month,
Date, and Year. I need to do a report giving the info
between selected month. Now when I go to the query design
and fill in the following for the Month I get the
corresponding months alphabetically. Example is between
January and June, I get January, June, July. Right now I
am at a loss as how to do the query so it works.

Between ([Forms]![usrfrmReportModule]![cboStartMonth]) And
([Forms]![usrfrmReportModule]![cboEndMonth])

If anyone can assist, I say thanks.
*** John

 

[John Spencer (MVP)]

You could try constructing a calculated date field and then applying date
criteria against that.

Field: RealDate: DateValue([Month] & " " & [Day] & " "& [Year])

One problem is that the above will error if the construct is not a valid date,
so you can use the IsDate Function to check that first.

Field: RealDate: IIF(IsDate([Month] & " " & [Day] & " "& [Year]),
DateValue([Month] & " " & [Day] & " "& [Year]),Null)

 

[JohnE]

John, I am rethinking the way to do this. Would a
multiselect listbox with the months in it work better? My
first thought is it would. But it has been a long time
since I've done something like that to pass the selections
to the query to show the selected months. Can you get me
started or point me to where there are examples?
Thanks.
*** John

-----Original Message-----
You could try constructing a calculated date field and then applying date
criteria against that.

Field: RealDate: DateValue([Month] & " " & [Day] & " "& [Year])

One problem is that the above will error if the construct is not a valid date,
so you can use the IsDate Function to check that first.

Field: RealDate: IIF(IsDate([Month] & " " & [Day] & " "& [Year]),
DateValue([Month] & " " & [Day] & " "& [Year]),Null)

I realize in looking back thru this newsgroup there are
other subjects that talk about between time periods. But,
the dillemma that I face is different so I need to make
this posting.
I recently inherited a db in which all fields are text
(except autonumber). The date fields are also labeled as
text. There are 3 fields for recording the date, Month,
Date, and Year. I need to do a report giving the info
between selected month. Now when I go to the query design
and fill in the following for the Month I get the
corresponding months alphabetically. Example is between
January and June, I get January, June, July. Right now I
am at a loss as how to do the query so it works.

Between ([Forms]![usrfrmReportModule]![cboStartMonth]) And
([Forms]![usrfrmReportModule]![cboEndMonth])

If anyone can assist, I say thanks.
*** John

.

 

[John Spencer (MVP)]

Well the problem I see is that you are always going to have problems as long as
the date field isn't a date, but is text. That's why I suggested you calculate
a date field. That way, you can specify a date range at any time against the
calculated field.

A multiselect listbox can be used, HOWEVER, it will require you to build the SQL
Where clause with VBA. A multi-select list box doesn't really have A single
value to pass to the query.

What if you want December 2000 to February 2001 or some other range that
overlaps years?

If you really feel the need to use multi-select listbox. Check out

http://www.mvps.org/access/forms/frm0007.htm
http://www.mvps.org/access/reports/rpt0005.htm

John, I am rethinking the way to do this. Would a
multiselect listbox with the months in it work better? My
first thought is it would. But it has been a long time
since I've done something like that to pass the selections
to the query to show the selected months. Can you get me
started or point me to where there are examples?
Thanks.
*** John

-----Original Message-----
You could try constructing a calculated date field and then applying date
criteria against that.

Field: RealDate: DateValue([Month] & " " & [Day] & " "& [Year])

One problem is that the above will error if the construct is not a valid date,
so you can use the IsDate Function to check that first.

Field: RealDate: IIF(IsDate([Month] & " " & [Day] & " "& [Year]),
DateValue([Month] & " " & [Day] & " "& [Year]),Null)

I realize in looking back thru this newsgroup there are
other subjects that talk about between time periods. But,
the dillemma that I face is different so I need to make
this posting.
I recently inherited a db in which all fields are text
(except autonumber). The date fields are also labeled as
text. There are 3 fields for recording the date, Month,
Date, and Year. I need to do a report giving the info
between selected month. Now when I go to the query design
and fill in the following for the Month I get the
corresponding months alphabetically. Example is between
January and June, I get January, June, July. Right now I
am at a loss as how to do the query so it works.

Between ([Forms]![usrfrmReportModule]![cboStartMonth]) And
([Forms]![usrfrmReportModule]![cboEndMonth])

If anyone can assist, I say thanks.
*** John

.