If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Not Sure How to Do This!
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. |
#2
|
|||
|
|||
Not Sure How to Do This!
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" wrote: 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. |
#3
|
|||
|
|||
Not Sure How to Do This!
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" wrote in message ... 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" wrote: 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. |
#4
|
|||
|
|||
Not Sure How to Do This!
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] |
#5
|
|||
|
|||
Not Sure How to Do This!
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" wrote in message ups.com... 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] |
#6
|
|||
|
|||
Not Sure How to Do This!
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. |
#7
|
|||
|
|||
Not Sure How to Do 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? "Andy97" wrote in message oups.com... 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. |
#8
|
|||
|
|||
Not Sure How to Do This!
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? |
#9
|
|||
|
|||
Not Sure How to Do This!
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) |
#10
|
|||
|
|||
Not Sure How to Do This!
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. |
|
Thread Tools | |
Display Modes | |
|
|