conditional formatting syntax

Hi,
I learned from a previous post that using conditional formatting on a control
in a subform won't work properly, if you are displaying more than one record
in the subform.

Thank you all for your kind assistance.

So now I don't know the syntax for conditional formatting - I have searched
the Access help files, my two programming books, I looked how I did it once
in Excel; I tried various things in the subform but havent gotten it correct.
Thank you for your patience.

I am using Expression Is

The two fields I want to compare are Oct and Nov.

If Nov is greater than Oct, I would like the cell red.

Thank you,  CarlaGay

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201003/1

0
hireagenius
3/30/2010 7:36:20 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

8 Replies
816 Views

Similar Articles

[PageSpeed] 14

I wasn't too clear in this message.  The first time I was using VBA code. Now
I am just trying to use the Conditional Formatting functionality available in
the Access Menu.

hireagenius wrote:
>Hi,
>I learned from a previous post that using conditional formatting on a control
>in a subform won't work properly, if you are displaying more than one record
>in the subform.
>
>Thank you all for your kind assistance.
>
>So now I don't know the syntax for conditional formatting - I have searched
>the Access help files, my two programming books, I looked how I did it once
>in Excel; I tried various things in the subform but havent gotten it correct.
>Thank you for your patience.
>
>I am using Expression Is
>
>The two fields I want to compare are Oct and Nov.
>
>If Nov is greater than Oct, I would like the cell red.
>
>Thank you,  CarlaGay

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201003/1

0
hireagenius
3/30/2010 7:40:10 PM
hireagenius via AccessMonster.com wrote:
>So now I don't know the syntax for conditional formatting - I have searched
>the Access help files, my two programming books, I looked how I did it once
>in Excel; I tried various things in the subform but havent gotten it correct.
>Thank you for your patience.
>
>I am using Expression Is
>
>The two fields I want to compare are Oct and Nov.
>
>If Nov is greater than Oct, I would like the cell red.
>

	[Nov] >[Oct]

For some strange reason I have never figured out, the
field/control names must be in [ ]

-- 
Marsh
MVP [MS Access]
0
Marshall
3/30/2010 10:17:54 PM
On Tue, 30 Mar 2010 16:18:58 -0600, Marshall Barton <marshbarton@wowway.com>
wrote:

>hireagenius via AccessMonster.com wrote:
>>So now I don't know the syntax for conditional formatting - I have searched
>>the Access help files, my two programming books, I looked how I did it once
>>in Excel; I tried various things in the subform but havent gotten it correct.
>>Thank you for your patience.
>>
>>I am using Expression Is
>>
>>The two fields I want to compare are Oct and Nov.
>>
>>If Nov is greater than Oct, I would like the cell red.
>>
>
>	[Nov] >[Oct]
>
>For some strange reason I have never figured out, the
>field/control names must be in [ ]

It's because without the brackets Access thinks you're talking about text
strings - and the text string "Nov" is in fact less than the text string
"Oct". It'll never even think to look in the fields in your table.

If you in fact have *TABLE FIELDS* (as opposed to, say, a crosstab query)
named Oct and Nov you've got lots worse problems than conditional formatting
syntax!
-- 

             John W. Vinson [MVP]
0
John
3/30/2010 11:18:29 PM
John W. Vinson wrote:

>On Tue, 30 Mar 2010 16:18:58 -0600, Marshall Barton <marshbarton@wowway.com>
>wrote:
>
>>hireagenius via AccessMonster.com wrote:
>>>So now I don't know the syntax for conditional formatting - I have searched
>>>the Access help files, my two programming books, I looked how I did it once
>>>in Excel; I tried various things in the subform but havent gotten it correct.
>>>Thank you for your patience.
>>>
>>>I am using Expression Is
>>>
>>>The two fields I want to compare are Oct and Nov.
>>>
>>>If Nov is greater than Oct, I would like the cell red.
>>>
>>
>>	[Nov] >[Oct]
>>
>>For some strange reason I have never figured out, the
>>field/control names must be in [ ]
>
>It's because without the brackets Access thinks you're talking about text
>strings - and the text string "Nov" is in fact less than the text string
>"Oct". It'll never even think to look in the fields in your table.


Right, but isn't it odd that a string does not need to be
quoted?  Try that in a control source expression or SQL and
it will presume the unquoted string is a name.  Seems to me
it would have been more consistent to have used the
Expression Service instead of whatever it is that adds
quotes.  The query design grid's criteria is the only other
place I can think of that does that and it only does it when
the string is not the name of a field.

-- 
Marsh
MVP [MS Access]
0
Marshall
3/31/2010 4:03:12 AM
On Tue, 30 Mar 2010 22:03:12 -0600, Marshall Barton <marshbarton@wowway.com>
wrote:

>Right, but isn't it odd that a string does not need to be
>quoted?  Try that in a control source expression or SQL and
>it will presume the unquoted string is a name.  Seems to me
>it would have been more consistent to have used the
>Expression Service instead of whatever it is that adds
>quotes.  The query design grid's criteria is the only other
>place I can think of that does that and it only does it when
>the string is not the name of a field.

It is odd, but then nobody's ever accused Microsoft's programmers of being
consistant!
-- 

             John W. Vinson [MVP]
0
John
3/31/2010 5:24:25 AM
Oh, duh!  I was way over complicating it with If statements, etc.  Thank you
for your patience.  
Marshall Barton wrote:
>>So now I don't know the syntax for conditional formatting - I have searched
>>the Access help files, my two programming books, I looked how I did it once
>[quoted text clipped - 6 lines]
>>
>>If Nov is greater than Oct, I would like the cell red.
>
>	[Nov] >[Oct]
>
>For some strange reason I have never figured out, the
>field/control names must be in [ ]
>

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201003/1

0
hireagenius
3/31/2010 1:53:15 PM
high quality Soccer jerseys NBA Jersey tracksuit and jackets, GHD 
hairstraightener supplier from www.willpa.com

Are you a Retail businessman who bother by the purchase price?  China 
Cheapest TOP wholesale website can help you

we are specialize in replica sport goods manufacturing in china, we can 
offer you all kinds of soccer jersey, NBA jersey,shoes and so on. they are 
the best brand replica goods whih are look the same as the original goods. 
excellent quality and steady supply for them. we have been marketed in Europe 
and American for 3 year. all the goods we offer are AAA quality.  our soccer 
jersey are Thailand style. If any goods you buy from my company have problem, 
we will refund or resend them again. Most of ourProducts have no minimum 
order requirements,soyou can shop retail goods at wholesale prices. if you 
can buy more than 300usd. We offer free shipping. The more you buy the more 
discount for you.

National soccer jerseys: http://www.willpa.com
Club soccer jerseys: http://www.willpa.com
NBA Jerseys: http://www.willpa.com
T-shirt and shirt: http://www.willpa.com
Tracksuit: http://www.willpa.com
Hoody & Jackets: http://www.willpa.com
UGG boots: http://www.willpa.com
Hair style: http://www.willpa.com
shopping Index: http://www.willpa.com

EMS shipping.  7days arrive, paypal accept

want more information pls contact us or check our website: www.willpa.com
0
Utf
3/31/2010 8:36:01 PM
high quality Soccer jerseys NBA Jersey tracksuit and jackets, GHD 
hairstraightener supplier from www.willpa.com

Are you a Retail businessman who bother by the purchase price?  China 
Cheapest TOP wholesale website can help you

we are specialize in replica sport goods manufacturing in china, we can 
offer you all kinds of soccer jersey, NBA jersey,shoes and so on. they are 
the best brand replica goods whih are look the same as the original goods. 
excellent quality and steady supply for them. we have been marketed in Europe 
and American for 3 year. all the goods we offer are AAA quality.  our soccer 
jersey are Thailand style. If any goods you buy from my company have problem, 
we will refund or resend them again. Most of ourProducts have no minimum 
order requirements,soyou can shop retail goods at wholesale prices. if you 
can buy more than 300usd. We offer free shipping. The more you buy the more 
discount for you.

National soccer jerseys: http://www.willpa.com
Club soccer jerseys: http://www.willpa.com
NBA Jerseys: http://www.willpa.com
T-shirt and shirt: http://www.willpa.com
Tracksuit: http://www.willpa.com
Hoody & Jackets: http://www.willpa.com
UGG boots: http://www.willpa.com
Hair style: http://www.willpa.com
shopping Index: http://www.willpa.com

EMS shipping.  7days arrive, paypal accept

want more information pls contact us or check our website: www.willpa.com
0
Utf
3/31/2010 8:36:13 PM
Reply:

Similar Artilces:

How do I save a file to PDF format in Word 2010
When I try to save the file in PDF format I get the message that the file is being used by another user. How do I save it to a PDF.file Do you have Adobe Acrobat full version or just the reader? If you only have the reader, you can't convert a document to pdf. If this is the case, you can search the internet for some free software to do the conversions. "Willyfin" wrote: > When I try to save the file in PDF format I get the message that the file is > being used by another user. > > How do I save it to a PDF.file > By chance, are you usin...

PDF Format for Excel
Can Excel spreadsheets be save in PDF format? Thom, Excel can't save files directly to PDF files. You need a third party product like Adobe's Acrobat (full version, not just the free Reader), or something like PDF995 (www.pdf995.com) . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Thom" <thancox@lgbcpa.com> wrote in message news:090401c3cfb4$a5eef630$a501280a@phx.gbl... > Can Excel spreadsheets be save in PDF format? Thom, Yes, but you need a program capable of doing so... A popular one is Adobe Acrob...

How do I clear the formating of the area OUTSIDE my chart on my w.
...

Conditional format on adjacent cells
Hi All, what am I doing wrong?? I want cells A1 to G1 to conditionally format (to red) if they are ALL empty, but if ANY one (or more) of them contain data (text) then NONE of them should format. I tried the formula =ISBLANK(A1:G1) then asigned a format, but for some reason A1 only responds to the data in A1 and seems to ignore the rest of the range, I've also tried similar range with formula ="" and cell value is equal to "". I am sure this must be possible but just can't find it. I'v been at this for three weeks now. Please put me out of my miss...

Joining formatted cells
I need to join (concatenate) two cells that have been custom formatted. Both cells contain numbers. The second cell is a number ranging fro 0001 to 2211. When joining the cells, Excel drops the leading zeroe from the result which I need. I have tried forcing the format on Cell2 to ####...text, etc...nothin has worked thus far. Example: Cell1: 123456 Cell2: 0034 Needed result: 123456-0034 Actual result: 123456-34 Anyone help me keep my leading zeroes in the result?? : -- tonywa ----------------------------------------------------------------------- tonywar's Profile: http://www.e...

Workflow
Hi I am creating a basic workflow that, on creation, assigns accounts to a sales territory based upon the first couple of characters in the post code. For instance, if the post code begins with CH, CV, CW etc they should be assigned to the Central territory. My question is if I can add multiple values into the condition or do I need to add a seperate condition for each entry (I have over 200 so I hope I can do the former!). I have tried to seperate the possible values using comma's and semi-colons in the condition but it seems that the workflow see's these as part of the value. Any...

brochure, booklet, pamphlet format
I just imported a Lotus 123 file into Excel. In Lotus it was in a format which exact name escapes me now but it was booklet or brochure or pamphlet. It subdided a regular sheet of paper into two in landscape, giving two pages at 8.5" tall by 5.5" wide. The pages would print side by side with proper headers and footers over each page. Then I simply cut the pages in two. In Excel I cannot figure out how to do this. it just prints the page in landscape with header and footers going right across the page and the page not subdivided ...

Date Formats-Weeks in Date No
Good morning, I have a graph that runs from a query. Recipients of a group wanted data shown in weeks so I entered: Week No: (Format([DateIR],"ww"" '""yy")) Results were: 1 '07; 2 '07; 3 '07 etc. They would like to see the 'week period' i.e. 'Jan 1-Jan 6' say, rather than the 'week number'. Can someone please advise me how you enter it to get the this information. The graph runs by week for the whole year. Thank you, IEJ Good morning, Thank you. I'll give it a try and see if it works. Regards, IEJ "KA...

Formating a column of numbers
I have a column of 17 digits (it is a vin number for vehicles) I need to be able to peel off all numbers (and letters) in this column except for the last eight digits. Once that is done I can then do the sort that I need. What's the easiest way to do this? Thanks! Greg Scarff Scarff Auto Center Buick Pontiac GMC 1212 South Main St. Kalispell, MT 59901 (406) 755-7777 salesdept@scarffauto.com Hi Greg, If your VIN is in cell A1 then in B1 have the formula =RIGHT(A1,8) and copy this down. Note that before sorting this new list you will have to copy the 8 character values and paste ...

Finding all the Formats used in an Excel Sheet
Hi, I want to find all the formats which have been used in excel sheet. Can you please provide some VBA/Code to retrieve the same? Regards, Brijesh Hi Brijesh.. This may not be a direct solution�.but still.. Create a short cut ke for this macro. You may go to each cell and press shortcut key t display the format..Expand � If� clause, for other formats also� Sub ContentChk() If Application.IsText(ActiveCell) = True Then MsgBox "Text" Else If ActiveCell = "" Then MsgBox "Blank cell� Else End If If ActiveCell.HasFormula Then MsgBox "formula"...

Disabling tables formatting features in Word2000 and Word2003
How can I disable the automatic table numbering and indenting features in MS Word? Jim Hawkins By asking in a Word newsgroup, not Windows XP. "Jim Hawkins" <jimhawkins@manx.net> wrote in message news:uRjHU6eELHA.588@TK2MSFTNGP06.phx.gbl... : : How can I disable the automatic table numbering and indenting : features in MS Word? : : Jim Hawkins : : : Bickford Shmeckler wrote: > By asking in a Word newsgroup, not Windows XP. > Such as ? I could only find 3 newsgroups about Word, one in hebrew, one in arabic and a third in a l...

How do I format text as a bulleted item in Excel? Is it possible?
It's a manual effort. You can use Insert|Symbol (if you're using xl2002+) or copy the bullet you want from CharMap. (Windows start button|charmap). You'll have to select that character in the formulabar and change the font to what you want. Chip Pearson has an addin that may help (if you're using xl2k or lower). http://www.cpearson.com/excel/download.htm (look for Symbolizer and make sure you get the correct version) DRB wrote: -- Dave Peterson Dave Peterson <petersod@verizonXSPAM.net> wrote in news:43062E62.984DE524@verizonXSPAM.net: > Or with: http://...

How do I send RTF formated mail using VB.NET?
I am currently using a VB.NET program to send out e-mails with plain text bodies (plus one attachment). The emails are being received as Rich Text messages (probably just my personal Outlook default, because I didn't do this in the program), but there is no actual formatting (italics, color, etc.) in the message body, which is passed to from VB.NET to Outlook as an unformatted text String. I want to start applying formatting to the message body. In particular, my app has a RichTextBox, which has an Rtf property, which returns a String formatted as RTF, which contains whatever my users t...

Formatting (macros or templates)?
What is the best way to apply consistently apply formatting to excel spreadsheets? I have spreadsheets that dump from S-Plus. I need to format them in a Memo type format with the company logo at top and other header info. What's the best way to do this, a template in word or macros in excel? Thanks for any advice. ...

day:hour:minute:second format
I want to put data in a day: hour: minute: second format and be able to multiply it by a number. I’m doing this in order to create a calculator that will allow me to see if the memory with a standard memory card last 5 day 1 hour and 51 minutes how long will it last if the memory is increased by 4 Custom format: d:hh:mm:ss This assumes that you are correctly inputting the values in. Remember that days are stored as integers, and time is stores as a decimal. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Seth J" wr...

Default Text Formatting During Import
Is there a way to set the default format during text import from "General" to "Text" without having to select every column individually and change the value. I import bills of materials with long part numbers, sometimes the long part numbers have only digits 0-9. If these fields are not changed to "text" with the import wizard, I get scientific notation on the part numbers. I would like a way to just globally default to "text" format for all imports. If your text files are always the same layout, maybe you could record a macro when you do it once manua...

Conditonal Format based on two different criteria
Hi all. In column A I have a list of a bunch of different test names. In column D we have times associate with each test. I would like to make a conditional format that will check column A for a specific test name and then check to see if the time in column D meets a certain requirement. This is what I have tried but can't seem to get it to work. =AND($A$4="CBC",D$4$>.0416) Anyone got any ideas? It should work...make sure your time really is greater than 0.416, and not something like 0.41599999999 -- Best Regards, Luke M "Chad_Oglesbay" ...

Conditional Format Help
How do I write a conditional format that applies to "=$N$13:$N$22,$N$31:$N$40" where If $L13<$N13 fill the cell RED. This is what I have in the Format values where this formula is true box: "="$L13<$N13". But it doesn't work. Any ideas? Thanks Try getting rid of the quotation marks "Ayo" wrote: > How do I write a conditional format that applies to > "=$N$13:$N$22,$N$31:$N$40" where If $L13<$N13 fill the cell RED. This is > what I have in the Format values where this formula is true box: > "=&quo...

How to restore styles in a document to their original format
When a paragraph is based on a style, say "Heading 1", if somebody changes it then Word 2007 may update the list of styles with a new version of the style and gives it a new name, say "Heading 1,PRTM Heading 1" and applies all the user's changes to it. "Heading 1" is now no longer available in its original form even when show "All Styles" is selected from the style manager. How can one strip all the modifications and restore it back to the "Heading 1" as it was in the document template? To restore the styles of the attache...

Send the attachment without ZIP format compression.
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Where is the default setting to remove the .ZIP format when sending any type of attachment. I send a .doc, .jpeg, etc and Entourage AUTOMATICALLY ADDS THE .ZIP TO THE FILE EXTENSION. VERY ANNOYING AS MOST RECEIVERS CANNOT OPEN THE ATTACHMENTS. On 2010-04-12 19:52:34 -0400, sunsyl@officeformac.com said: > Where is the default setting to remove the .ZIP format when sending any > type of attachment. I send a .doc, .jpeg, etc and Entourage > AUTOMATICALLY ADDS THE .ZIP TO THE FILE EXTENSION. It's in the preferen...

help macro for formatting pie chart
Hi I've got a macro for formatting pie charts in a report that I produce. The idea is that any count under 3 is not shown in the pie chart. The macro seems to work for most tables, but doesn't work when there are blank cells at the end of a table or the final column has a blank cell - it puts 0% labels on the pie chart. Can anyone help please (my vba skills are very rusty....) - I've copied the code below.... Thanks! :) Sub CalloutsChartData1() ' declare variables Dim intCount As Integer Dim intlength As Integer Dim ChartValue As String Dim strStart As String Dim s...

= Format("h:m:s")
I have built a calculation in access that takes "time 2" - "time 1" and provides a result of the lasped times. The expression gives back the answer. It looks like Expr1 0.00106481481634546 Now if I export it to Excel and format the cell to Time and will provide me a time value that I can understand. What I would like to do is have the results of the querry do it for me. Once this is done I would ask for all values => 60 seconds Any help is always welcomed. -- Dinellest On Mon, 4 Feb 2008 09:59:01 -0800, dinellest <dinellest@discussions.microsoft.com>...

"conditional formatting" "#values"
I have a formula that works fine but... some of the cells show #values, what i would like to do is conditional formatting them to white font if = #values. Any pointers? many thanks Try this: Select the cells to be impacted (I'll assume A1:A10, with A1 as the active cell) From the Excel main menu: <format><conditional formatting> Condition_1 Formula is: =ISERROR(A1) Click the [Formatting] button.....set the White font....Click the [OK] buttons. Does that help? *********** Regards, Ron XL2002, WinXP "Dewi..." wrote: > I have a formula that works fine bu...

Conditional Formatting question...
Is there a way to have Excel look in a particular cell, and if the value in the cell meets a requirement (such as Complete), then shade the entire row a different color? I can use conditional formatting to get the one cell shaded, but I can't get the rest of the row to be shaded. Thanks! Dino Try using an absolute reference in your Conditional Format formula. For example, if the cell that contains the value "complete" is A1, then refer to it as $A$1. "Dino" wrote: > Is there a way to have Excel look in a particular cell, and if the value in > the ce...

Message Box Formating
Is there a procedure that can be used to change the font and background colors for message boxes? -- Enjoy the blessings of the day. jerry Jerry, No, you can't change the font or colors of a message box. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Gerald Evans" <gerald.j.evans@worldnet.att.net> wrote in message news:vkaod.950527$Gx4.387440@bgtnsc04-news.ops.worldnet.att.net... > Is there a procedure that can be used to change the font and > background > colors for message boxes? > > -- >...