Guidance

  • Thread starter Thread starter JB
  • Start date Start date
J

JB

Would like some suggestions on how to apporach this problem

I have a three digit text field that represents a month,( ie JAN, FEB,
etc,)

What I would like to do with this field is create a criteria that would
show only current, furture, and previous months from the current month
when ran. (Year is not a concerned) If the report was ran in June, it
would populate information for May, June, and Jully.

I'm a novice asking to do some werid things, so would it be easier to
create a IIF statement or a subqueiry to convert Jan to 01 and then
build a criteria function around it.

The other question is how do you create a criteria that Access would
recognize the value as a date.

Any advice would be great.
 
JB said:
Would like some suggestions on how to apporach this problem

I have a three digit text field that represents a month,( ie JAN, FEB,
etc,)

What I would like to do with this field is create a criteria that
would show only current, furture, and previous months from the
current month when ran. (Year is not a concerned) If the report was
ran in June, it would populate information for May, June, and Jully.

I'm a novice asking to do some werid things, so would it be easier to
create a IIF statement or a subqueiry to convert Jan to 01 and then
build a criteria function around it.

The other question is how do you create a criteria that Access would
recognize the value as a date.

Any advice would be great.
Click to expand...

Either...
CDate([FieldName] & "-01-01")
or
DateValue([FieldName] & "-01-01")

....would take your field and make a real date out it. You could then apply
standard date criteria. It wouldn't be very efficient, but it would work.
 
Another approach - I have no idea whether it would be faster or slower
than what Rick proposes - is to have a little table with two fields
Month Num
JAN 1
FEB 2
...
DEC 12

and then join this to the other table, e.g.

SELECT A.ID, A.MONTH, B.Num, A.FIRST
FROM B INNER JOIN A ON A.Month = B.MONTH
WHERE Abs([Choose month] - Num) <= 1
OR ([Choose month] = 1 And Num = 12)
OR ([Choose month] = 12 And Num = 1);

This expects to be passed a month number as parameter, of course.
 
JB said:
Would like some suggestions on how to apporach this problem

I have a three digit text field that represents a month,( ie JAN, FEB,
etc,)

What I would like to do with this field is create a criteria that would
show only current, furture, and previous months from the current month
when ran. (Year is not a concerned) If the report was ran in June, it
would populate information for May, June, and Jully.

I'm a novice asking to do some werid things, so would it be easier to
create a IIF statement or a subqueiry to convert Jan to 01 and then
build a criteria function around it.

The other question is how do you create a criteria that Access would
recognize the value as a date.

Any advice would be great.
Click to expand...

Here is another, way using code.

Create a new form.
In the form header add 6 text boxes -
arrange the text boxes three over three and name them like this:

LastMth ThisMth NextMth
LastDate ThisDate NextDate

Also in the form header, add a combo box

Name the combo box -> cboMonthSelect

Change the following:

ROW SOURCE TYPE = Value List

ROW SOURCE (should be 1 line)=
;"Jan";2;"Feb";3;"Mar";4;"Apr";5;"May";6;"Jun";7;"Jul";8;"Aug";9;"Sep";10;"Oct";11;"Nov";12;"Dec"

COLUMN COUNT = 2
COLUMN WIDTH = 0;0.3

In the afterUpdate event for the combo box, paste the following:
Watch for line wrap...

'--------------------------------
Private Sub cboMonthSelect_AfterUpdate()


'for month names

' should be one line
Me.LastMth = Choose((Me.cboMonthSelect - 1) - ((Me.cboMonthSelect - 1 < 1) *
12), "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct",
"Nov", "Dec")

' should be one line
Me.ThisMth = Choose(Me.cboMonthSelect, "Jan", "Feb", "Mar", "Apr", "May",
"Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

' should be one line
Me.NextMth = Choose((Me.cboMonthSelect + 1) + ((Me.cboMonthSelect + 1 > 12)
* 12), "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct",
"Nov", "Dec")


' for dates

' should be one line
Me.LastDate = DateValue(Choose((Me.cboMonthSelect - 1) - ((Me.cboMonthSelect
- 1 < 1) * 12), "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug",
"Sep", "Oct", "Nov", "Dec") & " 1 " & CStr(IIf(LastMth = "Dec", Year(Date) -
1, "")))


' should be one line
Me.ThisDate = DateValue(Choose(Me.cboMonthSelect, "Jan", "Feb", "Mar",
"Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec") & " 1")

' should be one line
Me.NextDate = DateValue(Choose((Me.cboMonthSelect + 1) + ((Me.cboMonthSelect
+ 1 > 12) * 12), "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug",
"Sep", "Oct", "Nov", "Dec") & " 1 " & CStr(IIf(NextMth = "Jan", Year(Date) +
1, "")))

End Sub
'--------------------------------

Switch back to Form View and select a month from the combo box.

HTH
 
Back
Top