AfterUpdate for date

[Guest]

I have a combo box called "cboPromoStartDate" with a calendar control that
displays a pop-up calendar. When the entry person clicks their date
(7/14/2005), that date format of numbers and slashes displays in the combox
box and is stored in the underlying table.

There is another field in the same table called "Month" where I would like
to store "JUL" after the entry person clicks on 7/14/2005 in the pop up
calendar.

Can someone help me with the code for the AfterUpdate in the
cboPromoStartDate?

Also, I have tried typing "=[cboPromoStartDate]" in the ControlSource of the
Month text box, but doesn't seem to do anything. Please help.

Sincerely,
Access Code Rookie

 

[Rick B]

You should not do that. You would be storing the same data in two fields.
What if the date is changed without using your form? Then the data is out
of synch.

When you want to use that month in a form, report, or query, simply pull it
out of the date using the ?month" function, or the format.


=Month([YourFieldName])

=Format([YourFieldName],"mmm")

 

[Guest]

I understand that it would be bad to duplicate the data in my table, but
here's why I was doing it. Perhaps you can offer a workaround to this report
situation:

In a form, I have cascading combo boxes where users can select (combobox1)
Region, (combobox2) Customer, (combobox3) Product Segment, (combobox4)
Product Fgroup, (combobox5) Month, and (combobox6) Year. The user makes
their selections, or leaves blank, if they want ALL, and then clicks on an
ApplyFilter button at the bottom of the form. A report is generated based on
their selections. Now, doing it your way and leaving the ShortDates (i.e.
7/14/2005), I can no longer use a combox box for the user to pick JAN, FEB,
MAR, etc. When I click the combobox, the months are repeated for every
record. So now instead of 1 "JAN", I'm getting dozens.

I didn't want to have to create a "Between" set of dates for my users to
generate their report, because it is easier for them to pick a month. Any
suggestions for me?

You should not do that. You would be storing the same data in two fields.
What if the date is changed without using your form? Then the data is out
of synch.

When you want to use that month in a form, report, or query, simply pull it
out of the date using the ?month" function, or the format.


=Month([YourFieldName])

=Format([YourFieldName],"mmm")




--
Rick B

I have a combo box called "cboPromoStartDate" with a calendar control that
displays a pop-up calendar. When the entry person clicks their date
(7/14/2005), that date format of numbers and slashes displays in the combox
box and is stored in the underlying table.

There is another field in the same table called "Month" where I would like
to store "JUL" after the entry person clicks on 7/14/2005 in the pop up
calendar.

Can someone help me with the code for the AfterUpdate in the
cboPromoStartDate?

Also, I have tried typing "=[cboPromoStartDate]" in the ControlSource of the
Month text box, but doesn't seem to do anything. Please help.

Sincerely,
Access Code Rookie

 

[Guest]

You don't have to (and shouldn't) store the month in a seperate field in the
table. You can "create" the "field" whenever you need it in a query.

Do you also have a field for the year?

(BTW, "MONTH" and "YEAR" are reserved words and shouldn't be use as object
names)

As for the month, there are two ways to have the month names in the combo
box.

---
The first is to use a value list where you type in the name of the months.
Set the ROW SOURCE TYPE to "Value List", then for the ROW SOURCE, enter:
;JAN;FEB;MAR;APR;MAY;JUN,....,DEC

The leading semi-colon puts a blank (null) as the first selection.

---
The second way is to use a query for the RECORD SOURCE. I don't know what
the table name or field name is, so change them to your names. Create an
unbound combo box and paste the following into the Row Source:

SELECT DISTINCT UCase(Format([PromoStartDate],"mmm")) AS P_Month,
Month([PromoStartDate]) AS exp2 FROM Table4 ORDER BY Month([PromoStartDate]);


What are the Row Sources for Combo5 and Combo6 right now?


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

I understand that it would be bad to duplicate the data in my table, but
here's why I was doing it. Perhaps you can offer a workaround to this report
situation:

In a form, I have cascading combo boxes where users can select (combobox1)
Region, (combobox2) Customer, (combobox3) Product Segment, (combobox4)
Product Fgroup, (combobox5) Month, and (combobox6) Year. The user makes
their selections, or leaves blank, if they want ALL, and then clicks on an
ApplyFilter button at the bottom of the form. A report is generated based on
their selections. Now, doing it your way and leaving the ShortDates (i.e.
7/14/2005), I can no longer use a combox box for the user to pick JAN, FEB,
MAR, etc. When I click the combobox, the months are repeated for every
record. So now instead of 1 "JAN", I'm getting dozens.

I didn't want to have to create a "Between" set of dates for my users to
generate their report, because it is easier for them to pick a month. Any
suggestions for me?

You should not do that. You would be storing the same data in two fields.
What if the date is changed without using your form? Then the data is out
of synch.

When you want to use that month in a form, report, or query, simply pull it
out of the date using the ?month" function, or the format.


=Month([YourFieldName])

=Format([YourFieldName],"mmm")




--
Rick B

I have a combo box called "cboPromoStartDate" with a calendar control that
displays a pop-up calendar. When the entry person clicks their date
(7/14/2005), that date format of numbers and slashes displays in the combox
box and is stored in the underlying table.

There is another field in the same table called "Month" where I would like
to store "JUL" after the entry person clicks on 7/14/2005 in the pop up
calendar.

Can someone help me with the code for the AfterUpdate in the
cboPromoStartDate?

Also, I have tried typing "=[cboPromoStartDate]" in the ControlSource of the
Month text box, but doesn't seem to do anything. Please help.

Sincerely,
Access Code Rookie