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...Differnce between two dates
I want to dermine how mnay days there is between cell c6 and g6 if g6
contains a value, if not then i want thee code to do nothjing.
You didn't mention which is the Start Date and which one is end date (i.e.)
whether the start date is C6 Or G6.
If the Start Date is C6 and the End Date is G6 then use the below formula
If the Start Date is G6 and the End Date is C6 then use the below formula
Remember to Click Yes, if this post h...Graph displaying dates alphabetically on x axis, not by date
Title says it all - I want the x axis to show dates in DATE ORDER, not
I've read thru the forum, can't see how to fix it - your help will be
p.s. Here is the graph Row Source, should there be an ORDER BY statement in
SELECT (Format([Date Paid],"mmm"" '""yy")) AS Expr1, Sum([Tenant Payments].
Amount) AS SumOfAmount FROM [Tenant Payments] GROUP BY (Format([Date Paid],
"mmm"" '""yy")), [Tenant Payments].[Date Paid], (Year([Date Paid]...Dates
I am working on a spreadsheet that consists of a rolling 12 mont
period. Once a month has passed how do I delete it and add a new mont
to the 12 month period? Please help
~~ Message posted from http://www.ExcelForum.com
You have to provide more info on how your data is laid out. Tell us what
you have and tell us what you want to have when the month changes. HTH
"erodri02" <firstname.lastname@example.org> wrote in message
> I am working on a spreadsheet that consists of a rolling 12 month
> period. Once...Date Formula Help #2
I am tring to create a formuls that will allow me to do the following:
In cell A1 have a start date, let's say 02/01/05 and in cell B
02/02/05 and so on all the way to the end of the month.
But when I get to the end of the month the cell that would contain th
date of 02/29/05 would be blank if it is not a leap year and if it is
leap year then it would show the date of 02/29/05.
Thanks a million for any help
cummingse's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2561
View ...cannot save the date
I am at a loss why i cant save the todays date.On my form i have a control
called LastUpdated.It is set to day/time and to a short date.I want to save
the present date by the following:
Me!LastUpdated = Now
DoCmd.Close acForm, Me.Name
However the field lastUpdated stays blank and does not show the present date.
What may be the reason ?
Message posted via AccessMonster.com
On Wed, 29 Aug 2007 19:47:14 GMT, "peljo via AccessMonster.com" <u19312@uwe>
>I am at a los...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
...Unwanted automatic date format
I am doing some cut and past, and also some search and replace for numbers
with a date-like format ("2-5", "2/5"), and Excell keeps interpreting these
as dates, and inserts the date format. When I reformat the cell to text, the
data is like "33679", I have lost the original data.
I have repeatedly set the cell format to text before doing the search and
replace, but for some reason, Excel still re-sets the format to Date.
I have also tried reseting both the source and target spreadsheets to the
text format before I cut and past. In both cases the problem...IF the date in A1 is a holiday, cell B1 should say "HOLIDAY"
Here's what I want to do but I'm too dumb to it. Usually I Google
before asking here but my Internet is down though NGs and email work.
In cell A1 we enter a date, say, 07/22/04. If the date in A1 is a
holiday I want cell B1 to show the word HOLIDAY. If A1 is not a
holiday, B1 should be blank. I will list the holiday dates in cells D1
Part 2 is the same as 1 but instead of the word HOLIDAY appearing the
name of the holiday, NEWS YEARS DAY, will appear. I will list the
holiday names in cells E1 to E10 next to the dates in column D.
=IF(ISNUMBER(MATC...limit Criteria from a date field
I have a field that performs a calcualtion based on a date:
I need to add to the criteria >50
But the error says Arithmatic overflow error, guess because it was a date
value to begin with.
I tried creating anothe field from that one: Age at Panel: ([Age]>50) which
give me a -1 or 0 but I still cannot make the criteria = -1 or 0.
I tried another query but it just carries over the same issues.
How do I allow only >50 from this calculation????
Dan @BCBS <DanBCBS@discussions.microsoft.com> wrote:
>I have a field that performs a calcualtion base...Function to set Date
I would like to know how to set below equation:-
to set the 1-week (i.e Monday) prior to a specific date: e.g. 2009/11/24, i
would like to have the output = 2009/11/16
e.g. 2009/12/31, output = 2009/12/21
Try this. Cell B16 holds the specific date
Microsoft Excel MVP
"J" <J@discussions.microsoft.com> wrote in message
> I would like to know how to set below eq...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...Sum Where Date is less than certain date
I might be getting too technical for my level, but here is what I am
trying to do. I want to sum amounts from a query that is not the
source for my form. I have the sum working, but I want to break it
down. I am trying to sum the numbers that are less than the date in
the date field of my form. Here is what I have so far, courtesy of
help I received in this group:
=IIf([Car] Is Not Null,IIf([Car]="1",DSum("[Miles on Previous
Pumped]","AutosGasStats1"),DSum("[Miles on Previous
Tank]","A...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...How can I get an age using todays date and date of birth?
I'm using a spreadsheet where I need ages listed. Is there a formula that
can be used to compute the age if I use todays date and date of birth? So
far I am not having any luck. Thanks for any help you may provide. I am
using Excel 2000.
assuming the birthdate is in cell A1
> I'm using a spreadsheet where I need ages listed. Is there a formula that
> can be used to compute the age if I use todays date and date of birth? So
> far I am not having any luck. Thanks for any help you may provide. I am
> using Excel...Date formula #12
I have a spreadsheet that I need to have the dates for the month. On this
application I needed the First of the month in one cell and the last day of
the month listed in another. I inserted a calendar that allows me to put the
selected date into the active cell. (Formatted ddd mmmm dd yyyy) The I use
the formula =DATE(YEAR(C6),MONTH(C6)+1,DAY(C6)-1) to enter the last day of
the month into the appropriate cell. (Formatted mmmm dd yy) The problem I am
having is that this formula works on most of the dates in a month but gives
me a #NUM! Error when it is asked to give me the l...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 ...text is being interpreted as a date
I have the following value which I split into 4 columns using Text T
Columns (within a vba routine) :
(delimited on the ~ )
the problem is that the first column of data gets automatically (an
incorrectly) recognized as a date (Feb-08).
If I format it afterwards, it turns it into 2778797.
That value is supposed to be text (02-9508).
does anyone know how I can keep excel from incorrectly misinterprettin
the format of the data ???
Message posted from http://www.ExcelForum.com
format your columns as "Text" before you do your text to co...PrimaryContactID NULL
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...Formatting a text box for date entry only
Is there a way to format a text box on a user form to only allow date
entries ie: mm/dd/yy or mm/dd/yyyy?
Not really, but you can ensure it is a date, like so
Private Sub Worksheet_Change(ByVal Target As Range)
Dim fValid As Boolean
fValid = False
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
If IsNumeric(.Value) Then
If IsDate(.Value) Then
If Year(.Value) >= 1970 And Year(.Value) <= 2999 Then
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
My spreadsheet has a couple of date columns. Some of the rows would have no
date in it. However, after running the macros - which uses these cell value
as "blank", the date of "1/1/1900" appearred in those blank cells and I
could not make it disappear.
Do you have any suggestion to make that date disappear? Since I am using
VBA, I would prefer some command scripts that would turn those cells "off".
Thanks in advance.
Your code must be returning a 1 somehow which is converted to that date (Day
1 in xl = 1/1/1900, you must explicitly test for th...Using CORREL with arrays containing null values
I'm using the CORREL function, but one of the arrays I'm comparing has null
values (entered as #N/A), so my result is #N/A. How can I modify my formula
to correlate all the points in the 2 arrays, apart from the null values and
their corresponding entries in the other array?
You might incorporate an IF function, along the lines of
IF(ISNUMBER(your array function here),your array function here,0)
It seems redundant, but it evaluates the array function to determine if
it is numeric.
...Hide Sub Report(s) when records are null
I am running Access 2007 sp2 MSO. I have a report that has three (3) sub
reports. There are times when not all three sub reports have values to
display. Is there a way to code the given report to not be visible if there
are no records to report? The second part of this question would be if there
is a way to make the report invisible is it also possible to code so that the
space where the report would normally reside would not be taken with a blank
space (the foot print of where the report normally resides)?