Label to show IF

I want my label (Set to No Visible) to show on my form when I select a row 
from my Combo Box ,if the date in the 5 Column is between 30 -60 days Old
the Name of the field in my Combo box is [MaxOfBillDate]
My Attempt:
 Me.lblEmailAvailable.Visible = IIf(MaxOfBillDate 30 - 60 
Days(Nz(Me.cbOwnerName.Column(4), 0)) _
            And IsEmailOn, True, False)
Thanks if you can Help me.....................Bob 


0
Bob
2/20/2008 4:56:54 AM
access 16762 articles. 3 followers. Follow

6 Replies
646 Views

Similar Articles

[PageSpeed] 20

On Wed, 20 Feb 2008 17:56:54 +1300, "Bob V" <rjvance@ihug.co.nz> wrote:

>I want my label (Set to No Visible) to show on my form when I select a row 
>from my Combo Box ,if the date in the 5 Column is between 30 -60 days Old
>the Name of the field in my Combo box is [MaxOfBillDate]
>My Attempt:
> Me.lblEmailAvailable.Visible = IIf(MaxOfBillDate 30 - 60 
>Days(Nz(Me.cbOwnerName.Column(4), 0)) _
>            And IsEmailOn, True, False)
>Thanks if you can Help me.....................Bob 
>

Well, that's imaginative code... not sure I understand it, and the VBA
compiler certainly will not! 

The compiler has NO intelligence. It is a mindless text engine which only
recognizes valid expressions. Saying "MaxOfBillDate 30-60 Days" might make
sense to a  human, but the poor compiler will be baffled!

Assuming that [MasOfBillDate] is a Date/Time field, try

Me.lblEmailAvailable.Visible = IIF(DateDiff("d", Nz(Me.cbOwnerName.Column(4),
Date())) , Date()) <= 60 AND DateDiff("d", Nz(Me.cbOwnerName.Column(4),
Date()), Date()) > 30 AND IsEmailOn, True, False)

The fieldname MaxOfBillDate isn't available within the combo box, you need to
extract the value directly; you might even need to wrap it in a CDate()
function.
-- 
             John W. Vinson [MVP]
0
John
2/20/2008 5:30:51 AM
Oops Sorry John [MaxOfBillDate] is from a query that gives the last Date of 
the table (Max), I am getting an error "Argument not Optional " on DateDiff!
Thanks for the help..Bob

"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
news:paenr3104p7fdqvbu49oanro1o6m7holrp@4ax.com...
> On Wed, 20 Feb 2008 17:56:54 +1300, "Bob V" <rjvance@ihug.co.nz> wrote:
>
>>I want my label (Set to No Visible) to show on my form when I select a row
>>from my Combo Box ,if the date in the 5 Column is between 30 -60 days Old
>>the Name of the field in my Combo box is [MaxOfBillDate]
>>My Attempt:
>> Me.lblEmailAvailable.Visible = IIf(MaxOfBillDate 30 - 60
>>Days(Nz(Me.cbOwnerName.Column(4), 0)) _
>>            And IsEmailOn, True, False)
>>Thanks if you can Help me.....................Bob
>>
>
> Well, that's imaginative code... not sure I understand it, and the VBA
> compiler certainly will not!
>
> The compiler has NO intelligence. It is a mindless text engine which only
> recognizes valid expressions. Saying "MaxOfBillDate 30-60 Days" might make
> sense to a  human, but the poor compiler will be baffled!
>
> Assuming that [MasOfBillDate] is a Date/Time field, try
>
> Me.lblEmailAvailable.Visible = IIF(DateDiff("d", 
> Nz(Me.cbOwnerName.Column(4),
> Date())) , Date()) <= 60 AND DateDiff("d", Nz(Me.cbOwnerName.Column(4),
> Date()), Date()) > 30 AND IsEmailOn, True, False)
>
> The fieldname MaxOfBillDate isn't available within the combo box, you need 
> to
> extract the value directly; you might even need to wrap it in a CDate()
> function.
> -- 
>             John W. Vinson [MVP] 


0
Bob
2/20/2008 7:43:52 AM
I think there is one too many closing parentheses.  There are three after 
the first instance of Date.  Try removing one, so that it matches the format 
of the second DateDiff expression.

"Bob V" <rjvance@ihug.co.nz> wrote in message 
news:eYroWR5cIHA.4968@TK2MSFTNGP02.phx.gbl...
> Oops Sorry John [MaxOfBillDate] is from a query that gives the last Date 
> of the table (Max), I am getting an error "Argument not Optional " on 
> DateDiff!
> Thanks for the help..Bob
>
> "John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
> news:paenr3104p7fdqvbu49oanro1o6m7holrp@4ax.com...
>> On Wed, 20 Feb 2008 17:56:54 +1300, "Bob V" <rjvance@ihug.co.nz> wrote:
>>
>>>I want my label (Set to No Visible) to show on my form when I select a 
>>>row
>>>from my Combo Box ,if the date in the 5 Column is between 30 -60 days Old
>>>the Name of the field in my Combo box is [MaxOfBillDate]
>>>My Attempt:
>>> Me.lblEmailAvailable.Visible = IIf(MaxOfBillDate 30 - 60
>>>Days(Nz(Me.cbOwnerName.Column(4), 0)) _
>>>            And IsEmailOn, True, False)
>>>Thanks if you can Help me.....................Bob
>>>
>>
>> Well, that's imaginative code... not sure I understand it, and the VBA
>> compiler certainly will not!
>>
>> The compiler has NO intelligence. It is a mindless text engine which only
>> recognizes valid expressions. Saying "MaxOfBillDate 30-60 Days" might 
>> make
>> sense to a  human, but the poor compiler will be baffled!
>>
>> Assuming that [MasOfBillDate] is a Date/Time field, try
>>
>> Me.lblEmailAvailable.Visible = IIF(DateDiff("d", 
>> Nz(Me.cbOwnerName.Column(4),
>> Date())) , Date()) <= 60 AND DateDiff("d", Nz(Me.cbOwnerName.Column(4),
>> Date()), Date()) > 30 AND IsEmailOn, True, False)
>>
>> The fieldname MaxOfBillDate isn't available within the combo box, you 
>> need to
>> extract the value directly; you might even need to wrap it in a CDate()
>> function.
>> -- 
>>             John W. Vinson [MVP]
>
> 

0
BruceM
2/20/2008 12:17:03 PM
On Wed, 20 Feb 2008 20:43:52 +1300, "Bob V" <rjvance@ihug.co.nz> wrote:

>Oops Sorry John [MaxOfBillDate] is from a query that gives the last Date of 
>the table (Max), I am getting an error "Argument not Optional " on DateDiff!
>Thanks for the help..Bob


Is [MaxOfBillDate] a field in the Form's recordsource query (in which case it
is available for your expression) or is it only in the Combo's recordsource
(in which case it isn't)?

The DateDiff expression might have unbalanced parentheses: let's see... yep,
missed one! Should be

Me.lblEmailAvailable.Visible = IIF(
  DateDiff("d", 
     Nz(Me.cbOwnerName.Column(4), Date())) , 
     Date()) 
<= 60 
AND 
DateDiff("d", 
    Nz(Me.cbOwnerName.Column(4), Date())),
    Date())
 > 30 AND IsEmailOn, True, False)

-- 
             John W. Vinson [MVP]
0
John
2/21/2008 5:00:56 AM
John it is just a row source from my query on my drop down combo box!
Thanks Bob
"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
news:ov0qr39r3g71jikgddk01cb10gh2591n35@4ax.com...
> On Wed, 20 Feb 2008 20:43:52 +1300, "Bob V" <rjvance@ihug.co.nz> wrote:
>
>>Oops Sorry John [MaxOfBillDate] is from a query that gives the last Date 
>>of
>>the table (Max), I am getting an error "Argument not Optional " on 
>>DateDiff!
>>Thanks for the help..Bob
>
>
> Is [MaxOfBillDate] a field in the Form's recordsource query (in which case 
> it
> is available for your expression) or is it only in the Combo's 
> recordsource
> (in which case it isn't)?
>
> The DateDiff expression might have unbalanced parentheses: let's see... 
> yep,
> missed one! Should be
>
> Me.lblEmailAvailable.Visible = IIF(
>  DateDiff("d",
>     Nz(Me.cbOwnerName.Column(4), Date())) ,
>     Date())
> <= 60
> AND
> DateDiff("d",
>    Nz(Me.cbOwnerName.Column(4), Date())),
>    Date())
> > 30 AND IsEmailOn, True, False)
>
> -- 
>             John W. Vinson [MVP] 


0
Bob
2/21/2008 5:42:16 AM
On Thu, 21 Feb 2008 18:42:16 +1300, "Bob V" <rjvance@ihug.co.nz> wrote:

>John it is just a row source from my query on my drop down combo box!

In that case the fieldname is not accessible within your code.
-- 
             John W. Vinson [MVP]
0
John
2/21/2008 5:10:33 PM
Reply:

Similar Artilces:

Problem setting BackStyle on Label
I have a problem with Properties on Label in particular, when added by a script. The sequence below works on CommandButton1 and Frame1 objects but behaves strangely with Label1 Public Sub addLabel() Sheet2.OLEObjects.Add "Forms.Label.1", _ Left:=100, Top:=75, Height:=15, Width:=200 End Sub Public Sub nameLabel() Sheet2.Label1.Name = "title1" End Sub Public Sub setLabel() Sheet2.title1.BackStyle = 0 Sheet2.title1.Caption = "My Text" Sheet2.title1.Font.Bold = 1 Sheet2.title1.Font.Italic = 1 Sheet2.title1.Font.Size = 12 End Sub The one property that doesn&...

Calendar showing deleted birthdays
Many months ago I changed the first names of two of my contacts...like Jack to John. Recently noticed that clendar is showing birthdays for both Jack and John even though Jack no longer exisits. Have tried several things. Deleted John and waited several days. John's birthday disappeared but not Jack's. Tried adding Jack back then his birthday shows twice...Once for the real Jack and one for the one that was changed to John months ago then deleted. It seems the calendar is reaching back to a very old sync and refusing to delete the info. Any ideas? Did you try del...

How to get Table Description Field Text to show on Forms? Hovering? Right-Click?
Can I get the fioeld description I enter in the Table Design View to show on Forms or Queries? When the mouse hovers over the field or field name?? When the user right clicks on the field or field name?? At the bottom of the screen when the mouse hovers? How? Thanks for any help. Mel Ken, I've done some programming but not much in Access. How do I (where do I click) to add a function to the "standard module" ??? And, am I correct in understanding that the first function would be called each time the db opened? And the 2nd function would be called each time the form op...

Inserting a picture on a label
I am trying to insert a picture on a name badge label and when I insert it it does not show up. I can insert into other publications but not a label. What is up with that? Do you have detailed display enabled? View, pictures... If you do read the third FAQ here http://ed.mvps.org/Static.aspx?=Publisher/FAQs -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "stew" <stew@discussions.microsoft.com> wrote in message news:23E0A05E-EBBE-4E0C-8B1E-A966EE185107@microsoft.com... >I am trying to insert a picture on a name ...

How can I convert data from excel to address labels?
I've been asked to convert a list of addresses that are in an excel spreadsheet into address labels. Would appreciate any help! Thanks. See Mail Merge in Microsoft Word. It has wonderful instructions "KC" <KC@discussions.microsoft.com> wrote in message news:4ECF5BD3-97C3-4978-838A-C6DA2D55FB4A@microsoft.com... : I've been asked to convert a list of addresses that are in an excel : spreadsheet into address labels. Would appreciate any help! Thanks. You may want to read some tips for mailmerge: http://www.mvps.org/dmcritchie/excel/mailmerg.htm http://www.mvps.org...

Showing list of range names available for formulas??
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I know this MUST be possible in Excel...but I can't seem to find it. I have named ranges. I am entering a formula and want to refer to a range name. I don't remember exactly what I called it, or it has a long name that I don't want to have to type. Can't I use some sort of function key to just show a list of the available named ranges, so that I can point to the one I want? In excel for windows, we used a function key (can't recall which one). <br> Thanks, <br> hjd Rev...

Broken links don't show old data. Help!
I have a spreadsheet in an email attachment that no longer shows old data from broken links after upgrading to Office XP. The data is there, and displays while the "Update links" dialog box is up, but no matter what I choose the data then disappears into a sea of #REF when I dismiss the dialog box. How can I get the old data to show again? Thanks. -Mark ...

Show all investments on home page
Money 2004 deluxe shows only the first 75 investments on the home page investment summary. How can I get it to show all of them ca. 250? Running XP home. Jonathan notice the word 'summary'........... >-----Original Message----- >Money 2004 deluxe shows only the first 75 investments on the home page >investment summary. How can I get it to show all of them ca. 250? Running >XP home. > >Jonathan > > >. > Me: NOT helpful. Money 2000 showed them all. Jonathan "me" <m@e.com> wrote in message news:0aba01c3822e$e145f270$a301280a@ph...

Report showing #Error on a valid SQL View
I have an Access app with an SQL Server backend. One report is based on a VIEW. When I run the view alone all the data is visible. But when I run the report, I get #Error on one field. The field is numeric. However, the RecordSource property of the textbox is: [MyNumericFieldFromSQLView]/32 What's strange is that when I remove the division by 32, the #Error disappears. What I need to do in the report is divide the value from the view by 32. I cannot modify the view because it is not an option. Any help? Thanks ! It is possible that the field is a text field that is...

combine text & date formula to show mmmm dd, yyyy?
How do I combine text and date formula to show the written date and not the Excel number date? I have text in a couple of cells and a date formula in another cell(input from a different worksheet). I have the formula =A3 & F3 & A4 in A1 and would like it to read: We went to the station on December 21, 2009 and took the train. However, it is reading We went to the station on 40168 and took the train. Thanks. Try something like this... A1 = Today is B1 = 12/21/2009 =A1&" "&TEXT(B1,"mmmm dd, yyyy") Returns: Today is December 21, 2009 ...

Address Labels
The situation: I work for a small business which has a large number of Excel wookboo files, each of which holds the details of a customer. Part of these details are - obviously - the name and address listin for that customer, with the name and each line of the address bein listed in seperate cells. The problem: The owner of the business has asked that I build up an Excel databas of every 2004-2005 customer (well over a thousand), which lists th name and address of each, in such a way that it can be printed directl onto sticky labels if necessary. Needless to say, trying to do this manua...

printing labels
I can print word and excel documents, but when I go to print a full page of labels I can't. My printer shows a meesage to add paper when I all ready have paper. have you tried to remove the paper from the printer before printing labels? then simply feed the printer with the label paper when it asks. -- db·´¯`·...¸><)))º> DatabaseBen, Retired Professional - Systems Analyst - Database Developer - Accountancy - Veteran of the Armed Forces - Microsoft Partner - @hotmail.com ~~~~~~~~~~"share the nirvana" - dbZen ...

Store Operation 2.0 In Item Property showing On Order 6 or so.Need
I my Store Operation 2.0 database in am showing some #s in on order. I alreddy all PO closed and I don't have any any back order and its still showing On Order Quantity (Different #s for different items) How do I make this On Order Quantity Zero "0" I am looking for SQL query Update Items Set OnOrder = 0 some thing like that but I dont know the Database field name for Onorder Please help me. ...

How to show a Dialog that is in maximed state initially
I am trying to show a dialog when a button is pressed. I know that call Dialog d; d.DoModal(); What else show be do be done in order to show the dialog in a maximized state. I added the properties Minimize/Maximize from the properties . Psl guide. Bhargavi You could use a ShowWindow() call in the OnInitDialog() function for the dialog to show it maximized. http://msdn2.microsoft.com/en-us/library/72szh9c7.aspx Tom "Bhargs" <bhargavi_ks2001@yahoo.com> wrote in message news:1150301229.668141.55560@y43g2000cwc.googlegroups.com... >I am trying to show a dialog when a bu...

Show Number of Unread Mail IN Taskbar/System Tray
does anyone know if there is a way (in Outlook 2003) to show the number of unread or new mail in the taskbar or in the system tray. I am talking when you have Outlook minimized, it says Inbox - Microsoft Outlook....I want it to say Inbox (1) - Microsoft Outlook. I want Outlook to behave just like GMail does. Thanks. ...

old email addys showing up
When I click on new mail and start typing an address the addy bar comes up with old addresses instead of the new one the contack has. I am wondering how I can expunge the old addresses from the auto fill part of outlook. Hopefully this makes sense. :0) XP, Outlook XP select the address using the arrow key and press Del. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Excha...

Labels leaving a blank line where addr2 is missing
Some of my addresses have 1 address line and some have 2. In order to include all of them in the same label, I'm getting a blank line when address 2 is missing. Why won't it float if there is a blank line? -- Brenda First, check that the Section that contains your Address fields is set to Grow and Shrink. To do this, click inside that section or on the grey bar just above it but ensure that you are not selecting any controls. In the Properties box next to Can Grow and Can Shrink (like Alice In Wonderland) click Yes. 2. Click in the left margin so that you are selecting all your tex...

How do I label a field on a report in Access?
I thought when you added field to a report there was an attached label. I am not seeing this. I really need to label my fields, how can I get this done? Alisa If the "default" control definition was modified to leave off the label, you won't have one for new (control) objects. To add one, first add a (bare-naked) label, then "cut" it, then highlight the control to which you want to attach it, then "paste". The label will be attached to the (highlighted) control. Regards Jeff Boyce Microsoft Office/Access MVP "Alisa" <Alisa@discussions....

Showing Annual Events/Restrictions
Is there a manner in which you can show annual events, such as spring break-up, or environmental constraints, on a Gantt chart in MS Project? Can you shade period of time in a year that will repeat every year? Hello Liz, The background shading on the Gantt Chart view comes from a calendar. You can create a custom calendar (tools > change working time) and set the event as non-working time. Then through Format > Timescale, Nonworking time tab, set the custom calendar as the nonworking time display. I hope this helps. Let us know how you get along. Julie Project ...

XY Labeler and Excel 2007
I'm hooked on the XY labeler add-in http://www.appspro.com/Utilities/ChartLabeler.htm, and have recently upgraded to 2007... BUT now I can't figure out how to get to the add-in. I've loaded it (under the microsoft office button) but don't see it in the ribbon under add-ins or in any of the chart options... What am I missing?! Hi, Did you download the latest version. It works fine on my machine, its on the Add-ins ribbon. Is it showing in the Add-in area under Active Application Add-ins in the Excel Options dialog box? Cheers, Shane Devenshire "Megan" wr...

MSPOS: Unable to print the Serial Number on a Price Label
Feature in RMS but not in MSPOS ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=fa08fa3a-4508-4511-9cb0-ced7bfa77393&dg=microsoft.public.pos ...

Emails to a Public Folder shows as Posts
I have a public folder which is email enabled. Howevere all emails do not display as Email message type but a Post message type. Any idea how to fix this? Regards Chris "Chris Kudla" <chris@orbitsa.co.za> wrote: >I have a public folder which is email enabled. Howevere >all emails do not display as Email message type but a Post >message type. Any idea how to fix this? > >Regards > >Chris See: http://www.outlook-tips.net/howto/post_or_note.htm for description and links to resolve, Mark Arnold MCSA MCSE+M MVP, FAQ: http://www.swinc.com/resource/exch...

Always show the content of a specific cell in the frame of Excel
Is it possible always to show the content of a specific cell in the frame of Excel (so you always can see it). Then it is possible always, for examble, to see the sum of some cells, when you are filling the underlying cells with numbers. In QuattroPro ver. 8.0 this was possible. In Danish this cell was called "Lyncelle" - perhaps "Quick-cell" in English. Best wishes Niels Danstrup Excel 2002 and 2003 have a Watch Window toolbar that displays the value of one or more cells. That may be what you want. -- Jim "Niels Danstrup" <NielsDanstrup@discussions...

? Make entire sheet of same-name labels using mailmerge
My art organization would like to make each member their own 'custom' sheet of artwork identification labels as a thank you gift. I am setting the project up in Publisher 2003, using Avery 5163 labels (10 labels per sheet). I have everything working great (mail-merging from an Excel file), but no matter what I have tried, I can't seem to make 'one' name print "10" times on one page of labels. I am sure this can be done somehow, because several times a year I receive personalized address labels from various organizations asking for donations. Any help is ...

To show a message box if the mouse is over a particular cell??
Is there a particular way to display a message box,(or run any other sub) if the user puts the mouse over it lets say for cell A1. Like it display comments for cells that have comments in them. Since, there is a property of in excel to response to mousehover, I expect that this can be done. I don't think you'll find a solution in excel for that mouse over a cell problem. But if you could rely on change of selection, you could tap into the worksheet_selectionchange event. That'll fire if you use the mouse, arrow keys, page up/down, ... Subodh wrote: > > Is ...