Not Sure How to Do This!

  • Thread starter Thread starter Andy97
  • Start date Start date
A

Andy97

Hi

I have a form with several [month] form fields that contain numeric
values for each month.

(e.g) Jan = 121, Feb= 150, Mar=210

I also have a [Warehouse Stock] and am using conditional formating on
the [month] fields similar to this:

[jan_stock]+[feb_stock]<=[Warehouse Stock] then fomat the cell GREEN
(meaning that there is enough stock in [Warehouse Stock] to accommodate
Jan and Feb.

I also have an unbound field that displays a medium date and what I
want to do is to somehow use the month part of the date now() to be a
starting point for my conditional formatting. In other words if the
month is now Feb - then I don't want the figure in [jan_stock] to be
counted in the conditional formating, etc,..etc.

I'm not sure if I have explained that as clearly as I could so please
post back any comments. Of course I may be doing this completely wrong
anyway so if there is a better way please let me know.
 
Well you are using alpha text for the months currently and the Now() function
is numeric in nature... to isolate just the month value out of Now() do this:

The Val function returns the numbers contained in a specified string as a
numeric value. And the Left function will identify the characters of the
Now() that are the month since they appear first on the left....

i.e.

=Val(Left([Text18],2))

where Text18 is the arbitrary property name I use of where the Now() info is
showing on my form or report currently.

In this case I selected the 2 most left characters....even though it is Feb
and only a single character - it should still work and return you a 2

But you are only half way there still....either you convert your month
abbreviations to a number value or convert these number values to a month
abbreviation to finish things up.....
 
I think the idea is that the numbers represent the quantity in inventory, or
something to that effect.

To Andy, more details are needed. What is the database structure? What is
its purpose? Why is some stock January stock and other February stock,
etc.? You speak of formatting cells. Do you mean you want to format text
boxes or other controls on the form? If so, you could use something like
this code in the form's Current event:

If Me.JanStock + Me.FebStock > Me.WarehouseStock then
Me.txtYourTextBox.Backcolor = vbGreen
End If

For looking at the month in the date field, see Visual Basic Help for the
DatePart function, although you may need to use DateSerial to specify the
year too, in order to limit it to the current year.

These brief answers do not address possible concerns with the database
structure.

NetworkTrade said:
Well you are using alpha text for the months currently and the Now()
function
is numeric in nature... to isolate just the month value out of Now() do
this:

The Val function returns the numbers contained in a specified string as a
numeric value. And the Left function will identify the characters of the
Now() that are the month since they appear first on the left....

i.e.

=Val(Left([Text18],2))

where Text18 is the arbitrary property name I use of where the Now() info
is
showing on my form or report currently.

In this case I selected the 2 most left characters....even though it is
Feb
and only a single character - it should still work and return you a 2

But you are only half way there still....either you convert your month
abbreviations to a number value or convert these number values to a month
abbreviation to finish things up.....
--
NTC


Andy97 said:
Hi

I have a form with several [month] form fields that contain numeric
values for each month.

(e.g) Jan = 121, Feb= 150, Mar=210

I also have a [Warehouse Stock] and am using conditional formating on
the [month] fields similar to this:

[jan_stock]+[feb_stock]<=[Warehouse Stock] then fomat the cell GREEN
(meaning that there is enough stock in [Warehouse Stock] to accommodate
Jan and Feb.

I also have an unbound field that displays a medium date and what I
want to do is to somehow use the month part of the date now() to be a
starting point for my conditional formatting. In other words if the
month is now Feb - then I don't want the figure in [jan_stock] to be
counted in the conditional formating, etc,..etc.

I'm not sure if I have explained that as clearly as I could so please
post back any comments. Of course I may be doing this completely wrong
anyway so if there is a better way please let me know.
Click to expand...
Click to expand...
 
Hi Bruce

The figures for [Jan], [Feb], [Mar] etc... are stock usage figures from
the previous years which are simply entered. The only figure that is
dynamic on the form is the [Warehouse Stock] which is pulled from the
actual stock for a particular component.

I can already format the cells with conditional formatting to change
the cells green or red so that the user can quickly see if there is
enough stock against their monthly usage figure so I just need to be
able to somehow tell the different month cells when to be included in
the conditional formatting...
I have an unbound field that displays a medium date and what I want to
do is to somehow use the month part of the date now() to be a starting
point for my conditional formatting. In other words if the month is
now Feb - then I don't want the figure in [jan_stock] to be
counted in the conditional formating, etc,..etc.
<<<

How would I extract the month out of the current date and insert it as
a variable into my conditional formatting on the cells. I am currently
doing something like this:
[jan_stock]+[feb_stock]<=[Warehouse Stock]
 
It would be a great help if when you reply you retain the text of the
previous part of the thread, especially if you reply after several days have
passed. Flipping back and forth between messages certainly discourages me
from replying. I doubt I am alone in that.
A field is where a data item is stored in the table, or it can be either a
table field or a calculated field in a query. A field cannot be unbound. I
will assume you mean a text box or some other control on your form.
Similarly, I will assume that when you say you are formatting cells you mean
that you are formatting controls in a datasheet view of a form.
As I said in my previous post, DatePart and/or DateSerial will help you
accomplish what you need. I cannot tell you how to "extract the month out
of the current date and insert it as a variable into my conditional
formatting" without knowing more about what you are trying to do. I know
that if the month is February you don't want January stock to be included in
the formatting, but what DO you want included in the formatting? Also, how
did the date get into the unbound text box? The format of the date is
irrelevant, by the way. The date is stored as a number; the formatting is
just how you choose to view that number.
= DatePart("m",Now()) as the control source for a text box will return the
number for the current month. It can also be used in a query expression, or
in VBA (with some minor modifications). That's just intended to give you a
sense of how it works. If I knew how you are performing the conditional
formatting I could probably help you to get the desired result.

Andy97 said:
Hi Bruce

The figures for [Jan], [Feb], [Mar] etc... are stock usage figures from
the previous years which are simply entered. The only figure that is
dynamic on the form is the [Warehouse Stock] which is pulled from the
actual stock for a particular component.

I can already format the cells with conditional formatting to change
the cells green or red so that the user can quickly see if there is
enough stock against their monthly usage figure so I just need to be
able to somehow tell the different month cells when to be included in
the conditional formatting...
I have an unbound field that displays a medium date and what I want to
do is to somehow use the month part of the date now() to be a starting
point for my conditional formatting. In other words if the month is
now Feb - then I don't want the figure in [jan_stock] to be
counted in the conditional formating, etc,..etc.
<<<

How would I extract the month out of the current date and insert it as
a variable into my conditional formatting on the cells. I am currently
doing something like this:
[jan_stock]+[feb_stock]<=[Warehouse Stock]
Click to expand...
 
Bruce

Apologies! I'm new to using these groups and do not know the
etiquette.
Your correct - I am using a text box formatted as a general number for
my monthly figures. I am accomplishing my conditional formatting by
right clicking on the text box whilst in design mode and selecting the
'conditional formatting' option. What I would like to do is pass the
current month somehow into this formatting unless there is another way
to achieve this.
 
If you are using Outlook Express as your newsreader, click Tools > Options.
Click the Send tab, and chck the box that has to do with including the
message in the reply. In another newsreader there is probably a similar
option.
What is the formatting you are using? In other words, what condition
triggers the format change?
The trouble here is that I still don't know just what you are trying to do.
I realize that you are formatting a text box, but what text box? The
WarehouseTotal text box? What numbers do you want to add together to
produce a total for comparison?
 
Hi Bruce

I'm not using Outlook Express - I am using the group directly from the
web.

In the conditional formatting for say [Feb] I have two conditions: I
set them as expressions from the condition dropdown.

1) [feb_min_stock]<[Available Stock] (i then choose green as one of the
colour options)
2) [feb_min_stock]>=[Available Stock] (i then choose green as one of
the colour options)

I'm not sure how to do this other than from the 'Conditional
Formatting' option. I know you mentioned earlier of using some VBA to
achieve this but where does the code go? Under what event?
 
Mistake above:

1) [feb_min_stock]<[Available Stock] (i then choose green as one of the

colour options)
2) [feb_min_stock]>=[Available Stock] (i then choose red as one of
the colour options)
 
Hi Bruce,
with the help of that earlier code snipet I have manged to figure out
how to do the formating int eh code itself instead of using the
'conditional formatting' menu option.
 
You could just set the backcolor to green and change it if
[feb_min_stock]>=[Available Stock]. It's going to be either red or green,
so it's simplest to set one as the default.
You would probably use the form's Current event if you were using VBA, but
you could also use a Control's After Update event, or whatever suits your
needs. I can only guess which one would be best, since I still don't know
how your database is put together.
 
Hi Bruce

Not solved! Now I either need to pass the month into the conditional
formatting so that the previous months are not included or zero out the
previous months so that they are not included.

Any pointers on doing this?
 
For the [Mar] text box I am using the following as an expression:

[jan_min_stock]+[feb_min_stock]+[mar_min_stock]<=[Available Stock]
 
Back
Top