Function to find expired information
I am trying to create a function which will let me know when a particular
date has passed or expired. For example, a person has insurance which
expires on 4/30/06 - I want something that will let me know on 4/31/06 that
that person's insurance is expired. Maybe also create a macro that will
automatically filter all the expired's. Is there anyway to do this? I
tried VLOOKUP, I tried an IF function, but I'm just not sure exactly how to
Thanks a million!
take a look at conditional formatting:
Select the column with the dates
...spin button value
Does the value in spinbutton properties have to be numeric or can it be a
day eg mon tues wed etc if so how do I dio this
A Forms toolbar spinner control returns an integer. You can have another
cell with a formula that references the cell linked to the spinner like
Here A1 is linked to the spinner. You'd want to limit the spinner's minimum
value to 1 and maximum value to 7 in this case.
"tina" <tina@...if A1=Null and B1=A1 why is result in B1=0 ??
if A1=Null and B1=A1 why is result in B1=0 ??
because of this I get wrong result for average calculations: average for
(6,8,0) <> average for (6,8,null)
(remove nothere from the email address if mailing direct)
"WGeorg" <WGeorg@discussions.microsoft.com> wrote in message
> if A1=Null and B1=A1 why is result in B1=0 ??
> because of this I get wrong result for average calculations: average for
> (6,8,0) <> average for (6,8,null)
Use this ...Null Is Null
I've got a text box in a report that's not returning properly. Here's
its Control Source:
This returns #Error. It's definitely my IIf statement as it returns
properly when removed however there are times when
[srptFGPKConfigsTPK_totalwt].[SumWtg] will be null. In fact, this
subreport will not appear if no records exist. I suspect that this is
How can I write this Control Source t...vlookup and if function
i'm trying to make 2 dropdown lists which when added with a figure in a 3rd
cell copies the cell into another column.
a1=(dropdown"january") b1=(dropdown"paid") c1=("manual input"100)
in g1 i want the total 100(c1) only if a1& b1 is set above.
but what i also want is if a1=jan,b1=paid,c1=200 and g1 is full(>1) then
enter 200(c1) into g2
On the face of it, this might suffice
In G1: =IF(AND(A1="january",B1="Paid"),C1,"")
In G2: =IF(G1=200,G1,"")
http...Syntax for Is Not Null ?
When one of our forms is opened (Current event) I want a message box fired by
certain conditions (that the Ref control does have a value, but the Country
control is empty). I have tried various bits of code along the lines shown
below, but keep getting runtime errors. Wot's wrong with the following,
If [Me.Ref] is not null and [Me.Country] = "" Then MsgBox "Please enter the
If (not isnull([Me.Ref])) AND IsNull([Me.Country]) Then MsgBox "Please enter
...When is a Null not a Null?
I have a form which is used for adding and entering data depending on
where it is called from. When called in add mode (acFormAdd) a blank
form is openned.
When the cursor enters the text box [Name] on the form field I am
trying test whether it is empty so that a search form can be openned
if a new name is to be added. (I don't want the search form to open in
cases when the form already has data in and I am editing rather than
adding data). The form is based on a query and the data displayed in
[Name] is the result of a calculated field in the query which
concatenates firstname and surn...IF Function Help, Please:)
I need the IF function to add up two cells and if the sum is greater than or
equal to 80, then the cell needs to read "80," if the sum is less than 80 I
just want it to show whatever number it calculates (79 and below).
This is for a timesheet with overtime.
Thank you for your help!!
The syntax: =IF(some result equals or is less than 80, TRUE: the result,
FALSE: show 80)
> I need the IF function to add up two cells and if the sum is greater than or
> equal to 80, then the cell needs to rea...Passing Qry as part of Rpt DoCmd.OpenReport
If the selected manufacturer = "_All" first value in list, then my query
sQry = "Year = " & ddlYear.Value
else: sQry = "Year = & ddlYear.Value & " and manufacturer = " & ddlManu.Value
.... also tried: "...=" & "'" & ddlManu.Value & "'"
DoCmd.OpenReport sReportName, acViewPreview, , sQry
Checking the value of sQry shows "Year = 2007 and manufactuer = 'TRMCO'" But
I still get the same data set as "Year = 2007" When I query by pasting the
same where clase into my dat...What is the Null character?
What is the character sequence that Excel uses to represent an empty cell? I
am using a formula and when it returns false, I would like for it to
represent the cell as if it was empty. I have tried using "" as an empty
cell, but excel recognizes that this is a value, not an empty cell.
You can't do what you want to do. A formula always returns
Microsoft MVP - Excel
Pearson Software Consulting, LLC
"Quinn Ramsey" <Quinn Ramsey@discussions.microsoft.com> wrote in
news:D67F6311-0CCA-48B5-A...retrieving the XmlEnumAttribute and the enum-values (2)
This post is a followup to the thread "retrieving the XmlEnumAttribute values
for an Enum" in this same forum earlier this month, since my last query in
that thread went unanswered.
I have an enum defined (DotNet v2) as
public enum velocityUom
ms = 21,
My original post was to ask how I could retrieve the XmlEnumAttribute values
for this Enum so that I could create a combo box with values like "m/s",
&qu...Passing Values between forms
I am trying to link to forms. I have a button on the main form
(frmCompanies) that needs to open another form when a button is clicked. The
new form that opens is frmScheduledActivities. When it open it needs to check
to see if there is an existing record (scheduled activity) for the company
and if there is no scheduled activity be set to add a new record.
When the button is clicked on the main company form (on the on click event)
this code runs:
Private Sub cmdCallBack_Click()
On Error GoTo Err_cmdCallBack_Click
Dim stDocName As String
Dim stLinkCriteria As String...more on VBA function name change
I thought I'd start a new thread since I haven't received any replies to my
I've declared a function in a module using mixed case:
Function TMDE_Category (FormName As Form)
I noticed recently that it appeared in the module as
Function tmde_category(FormName As Form)
I changed it back to the mixed case declaration, saved the module, exited
the app, reopened it and looked. The function had changed back to the all
Things I've tried since the original post:
Using the databse documenter, I selected all ob...Simple EXCEL worksheet function
I am creating an EXCEL worksheet of people's names, company and location. I
need to do a simple sort on name. When the list goes beyond 250, the sort
function stops working. Is this a limitation of EXCEL? If so, can I continue
the list on "Sheet 2", "Sheet 3" etc and still be able to sort through all
the lists as if they are one?
#1. Select the entire range you want to sort--don't let excel guess. I'm
guessing that you have an empty row in the real range to sort and excel is
#2. Nope. Excel doesn't support this type of...Charting null values
I have a chart that is plotting null values. I tried the formula
=if(C25="",N/A(),Sum(C22:C25)). I've also deleted the formula to create a
completely null cell.
I've been to Tools, Options, Chart and selected
Plot empty cells as
Not plotted, zero and Interpollated
As well as turning Plot Visible Cells only (turned it on and off)
Nothing seems to make a difference.
Stephanie, what kind of chart are you using? Can you give an example of how
your data table looks like?
> I have a chart that is plotting ...How to Link a cell in an Excel spread sheet to a text value in Visio 2007
I would like to have a text value in Visio take the value of a cell in an
Excell spread sheet. Is there a way to do this, at least on opening the
Thanks in advance,
UCO Lick Observatory
Laboratory for Adaptive Optics
visio 2007 professional
"Marco" <email@example.com> wrote in message
> I would like to have a text value in Visio take the value of a cell in an
> Excell spread sheet. Is there a way to do this, at leas...NULL values
Working with a downloaded table form a Lotus Notes contact manager. Now in
access call tblclients.
I query certain info. to update the records.
BUT...'Is Null' does not work. It comes back empty and there are definitely
empty contents in the fields I chose. I know how to do this as it works in
my other tables on other databases.
The only difference I can see in this table as opposed to tables created
directly in Access is the Field Design property called Unicode Compression
saying No instead of the default Yes.
I changed the Unicode to Yes in case this had something to do with it and
...How can I check a combo box content or value or something
Hi. I need to check if my combobox has something on it, what I mean is if my
users choose any value.
I need something looked like:
if combo1 then
msgbox "you must choose an option on Combo1"
How can I do something looked like?
In the AfterUpdate event of the combo type,
if IsNull(combo1.text) then
msgbox "you must choose an option on Combo1"
"Marco" <Marco@discussions.microsoft.com> wrote in message
> Hi. I need to check if my combobox h...How to MATCH value up a column
I need to match/find the first matching cell up a column. Ideas? TIA!
I don't know what you are looking for, but see:
Please click "yes" if this post helped you!
> I need to match/find the first matching cell up a column. Ideas? TIA!
Thankx for the reply. Here is my
I need to have IF(A11<A10, search up column A for 1st match, example A4,
display next column B4.
Clear as mu...Taking the Mode based on cell value
Could you provide a few more details about what you want to do?
Microsoft MVP - Excel
Pearson Software Consulting, LLC
"sip8316" <firstname.lastname@example.org> wrote in message
...VLOOKUP: Retrieving Values
I am trying to lookup a value ( SCOTLAND ) on a spreadsheet called (
Data ) in order to populate ceratin cells on my other spreadsheet (
If the value (SCOTLAND) is located in (Data) I want it to copy the
values of specific cells (not all of them ) from the same row into (
CHECK COLUMN N (CLASS) IN SPREADSHEET (DATA) FOR STRING (SCOTLAND)
IF THE STRING = SCOTLAND THEN
IN SPREADSHEET (Spreadone) POPULATE
CELL A7:A25 = ACCOUNT NO (from Data)
CELL B7:B25 = CLIENT (from Data)
CELL C7:C25 = CUSIP (from Data)
CELL D7:D25 = QTY (from Data)
CELL E7:E25...Using ISNA on more than one column within an If Function
I'm trying to construct a formula that looks at the following section of data:
A B C D
#N/A less than 12mnths Not On List
12 to 18mnths #N/A 1
Essentially I want to show in Column D a value based on the following:
Where the value in column C shows anything other than "not On List" is
If however, the value shown is Not On List then it needs to lookup the value
in Column A and return the corresponding value in the associa...pass control on photo swap
Do you have to pass control on photo swap for the other person to download photos? If that's the case it would explain a lot. I would appreciate your help. Thanks.
EggHeadCafe - Software Developer Portal of Choice
Silverlight 3 RIA Services: DataForm, Validation
I assume you mean the MSN Photo Swap activity. In that activity, you do need to pass control
for the other user to add pictures. There are also some instances where the 'Save' ...Can I set an audio alert that triggers as Excel cell value chgs?
I would like to add an audio alert to a spreadsheet cell whenever it changes
to a specific value? Can this be done?
....I'm not familiar with any audio alerts but, you can use conditional
formatting to the cell's format change when it value changes. Go to
Format...Conditional Formatting. You can then either set a value or use a
formula and set the format (what it will look like when the cell is the value
that you set).
> I would like to add an audio alert to a spreadsheet cell whenever it changes
> to a specific value? Can this be done?
i have discovered a strange behavior of CRM regarding the relationship
between accounts and contacts:
Some of my accounts show up a referenced contact but do not have a
PrimaryContactID GUID in the databasefield. Both where converted from a lead
which is refrenced with the correct GUID in the OriginatingLeadID Field in
Any idea how this can happen?
When you convert a lead into an account/contact the contact references the
account throught the parent customer field. The account does not reference
the contact (this would be the primary contact fi...