DateTime Null Or Minimum Value
Hello,
I am working on a authentication system and I have the following:
create table dbo.Users
(
Id int identity not null,
LastLock datetime not null,
LastLogin datetime not null,
LastPasswordChange datetime not null,
LastReset datetime not null,
Username nvarchar(40) not null constraint Users_Username_U unique,
constraint Users_PK primary key clustered(Id)
) -- Users
When a user opens the account there was never a login, it was never
locked or password changed.
So should I insert in this fields a Null value or a "SQL Minimum
Date".
What is your opinion in relation to this?
Thanks,
Miguel
|
0 |
|
shapper
9/8/2010 11:48:54 PM
sqlserver.programming
1873 articles. 
0 followers.
7 Replies
1897 Views
On Wed, 8 Sep 2010 16:48:54 -0700 (PDT), shapper <mdmoura@gmail.com>
wrote:
>Hello,
>
>I am working on a authentication system and I have the following:
>
>create table dbo.Users
>(
> Id int identity not null,
> LastLock datetime not null,
> LastLogin datetime not null,
> LastPasswordChange datetime not null,
> LastReset datetime not null,
> Username nvarchar(40) not null constraint Users_Username_U unique,
> constraint Users_PK primary key clustered(Id)
>) -- Users
>
>When a user opens the account there was never a login, it was never
>locked or password changed.
>So should I insert in this fields a Null value or a "SQL Minimum
>Date".
>
>What is your opinion in relation to this?
If the LastLogin semantic matters, have it as a separate table
with optional 1:1 mapping.
Sincerely,
Gene Wirchenko
|
|
0 |
|
Gene
9/9/2010 12:08:26 AM
An important criteria in the design of anything is to go for what it's easier to understand not only for you but for the other people as well. If someone want to know the list of accounts where there was never a login, it's easy to make - and understand - a query searching for a list of records with a Null value but less easy to do the same with the SQL minimum date; especially when this date could change with the exact type of the data. Do you know that the minimum date is different on the SQL-Server for the datetime and smalldatetime and that on Access and other database systems, you have other minimum for the data as well? Furthermore, accessing these from a GUI or converting these values to between different databases or to another type of system could lead to some serious problems. Practically all systems will understand a Null value but it's not the same with a minimum date value. So, going with the Null value is definitely the way to go. -- Sylvain Lafontaine, ing. MVP - Access Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) "shapper" <mdmoura@gmail.com> wrote in message news:91df2fe9-f0ca-4945-9a8e-c9bb454e794f@k30g2000vbn.googlegroups.com... > Hello, > > I am working on a authentication system and I have the following: > > create table dbo.Users > ( > Id int identity not null, > LastLock datetime not null, > LastLogin datetime not null, > LastPasswordChange datetime not null, > LastReset datetime not null, > Username nvarchar(40) not null constraint Users_Username_U unique, > constraint Users_PK primary key clustered(Id) > ) -- Users > > When a user opens the account there was never a login, it was never > locked or password changed. > So should I insert in this fields a Null value or a "SQL Minimum > Date". > > What is your opinion in relation to this? > > Thanks, > Miguel
|
|
0 |
|
Sylvain
9/9/2010 4:06:49 AM
You are dealing with events that have durations, but do not model
durations in the table. I would change the schema to get a proper
temporal model. Not knowing your business rules, here is a guess.
CREATE TABLE dbo.UserHistory
(user_name NVARCHAR(40) NOT NULL,
lock_start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
lock_end_time DATETIME,
CHECK (lock_start_time < lock_end_time),
login_start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
login_end_time DATETIME,
CHECK (login_start_time < login_end_time),
user_password VARCHAR(16) NOT NULL
CHECK (..),
password_start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
password_end_time DATETIME,
CHECK (password_start_time < password_end_time),
PRIMARY KEY (user_name, lock_start_time));
Create VIEW on this to get the current situation based on the
CURRENT_TIMESTAMP and NULL columns in the end_time columns. Look at
DATETIME2(n) data types, if you need them.
|
|
0 |
|
CELKO
9/9/2010 7:36:37 PM
shapper (mdmoura@gmail.com) writes: > I am working on a authentication system and I have the following: > > create table dbo.Users > ( > Id int identity not null, > LastLock datetime not null, > LastLogin datetime not null, > LastPasswordChange datetime not null, > LastReset datetime not null, > Username nvarchar(40) not null constraint Users_Username_U unique, > constraint Users_PK primary key clustered(Id) > ) -- Users > > When a user opens the account there was never a login, it was never > locked or password changed. > So should I insert in this fields a Null value or a "SQL Minimum > Date". I think LastLock should be nullable, but you could argue that it should be the creation date, because it was locked before that. But that is quite stretched. LastLogin needs to be nullable, because else you need a magic value. And a magic value is just a more difficult way to spell N-U-L-L. LastPasswordChange could be set to the creation date, because the password was set at that time. LastReset could be NULL or be set to the creation date depending how you look at tie. And, yen, CreationDate should probably be a column of its own in the table. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|
|
0 |
|
Erland
9/10/2010 8:56:25 PM
On Fri, 10 Sep 2010 22:56:25 +0200, Erland Sommarskog
<esquel@sommarskog.se> wrote:
>shapper (mdmoura@gmail.com) writes:
>> I am working on a authentication system and I have the following:
>>
>> create table dbo.Users
>> (
>> Id int identity not null,
>> LastLock datetime not null,
>> LastLogin datetime not null,
>> LastPasswordChange datetime not null,
>> LastReset datetime not null,
>> Username nvarchar(40) not null constraint Users_Username_U unique,
>> constraint Users_PK primary key clustered(Id)
>> ) -- Users
>>
>> When a user opens the account there was never a login, it was never
>> locked or password changed.
>> So should I insert in this fields a Null value or a "SQL Minimum
>> Date".
>
>I think LastLock should be nullable, but you could argue that it should
>be the creation date, because it was locked before that. But that is
>quite stretched.
>
>LastLogin needs to be nullable, because else you need a magic value. And
>a magic value is just a more difficult way to spell N-U-L-L.
No, you do not. A table with login id and last login would do
it. If there has not been a login under that login id yet, then there
is no row in that table for that login id.
[snip]
Sincerely,
Gene Wirchenko
|
|
0 |
|
Gene
9/10/2010 9:14:06 PM
Gene Wirchenko (genew@ocis.net) writes: > On Fri, 10 Sep 2010 22:56:25 +0200, Erland Sommarskog ><esquel@sommarskog.se> wrote: >>LastLogin needs to be nullable, because else you need a magic value. And >>a magic value is just a more difficult way to spell N-U-L-L. > > No, you do not. A table with login id and last login would do > it. If there has not been a login under that login id yet, then there > is no row in that table for that login id. That's also a more difficult way to spell N-U-L-L. Such a table would make sense if you want to track all logins, not only the latest. But having a table for a single value? Makes sense if you have a number of columns that are NULL most of the times, and are non-NULL together. But for a single value that is non-NULL only in a special case? Nope. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|
|
0 |
|
Erland
9/11/2010 9:41:54 AM
The difference is a null in column space versus a null in row space! They are both null, but IMHO it is easier to code for a null in row space than it is for a null in column space (considering the insert / update race condition) and you get the fringe benefit of tracking all logins. The problem with this design is that the table will grow quickly and without a good set of indices, either insert or select performance will be a problem. These are solvable problems, just like the insert / update race is solvable with lock hints + transaction isolation ;) "Erland Sommarskog" <esquel@sommarskog.se> wrote in message news:Xns9DF0770097F1DYazorman@127.0.0.1... > Gene Wirchenko (genew@ocis.net) writes: >> On Fri, 10 Sep 2010 22:56:25 +0200, Erland Sommarskog >><esquel@sommarskog.se> wrote: >>>LastLogin needs to be nullable, because else you need a magic value. And >>>a magic value is just a more difficult way to spell N-U-L-L. >> >> No, you do not. A table with login id and last login would do >> it. If there has not been a login under that login id yet, then there >> is no row in that table for that login id. > > That's also a more difficult way to spell N-U-L-L. > > Such a table would make sense if you want to track all logins, not only > the > latest. But having a table for a single value? Makes sense if you have a > number of columns that are NULL most of the times, and are non-NULL > together. But for a single value that is non-NULL only in a special case? > Nope. > -- > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >
|
|
0 |
|
m
9/12/2010 1:37:10 AM
Similar Artilces:
Cell Value on Toolbar
Is it possible to show the value of a cell on a custom toolbar. Cell location will not change, but cell content will. -- HilcrRWise ------------------------------------------------------------------------ HilcrRWise's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=11699 View this thread: http://www.excelforum.com/showthread.php?threadid=494807 One way Option Explicit Const WS_RANGE As String = "B10" Private Sub Worksheet_Activate() Dim oCtl As CommandBarControl On Error Resume Next Set oCtl = Application.CommandBars.FindControl(Tag:=&qu...
Adding third lookup value to index/match
I understand how Index/Match works with double lookups... But I cant quite get the third lookup down. Looking to add a third lookup value and range to this formula (range z) =INDEX(table, MATCH(x,range x,0), MATCH(y,range y,1)) Data is laid out like so... (Vertical column range is x) A B C D E F G H 1 range yyyyyyyyyyyyyy -> 2 range zzzzzzzzzzzzzz -> x x x x x x any suggestions...? How does the 3rd range relate? Need more specific details. -- Biff Microsoft Excel MVP "J.W. Aldridge" <jeremy.w.aldridge@gmail.com> wrote in mess...
Subquery returned more than 1 Value in Manufacturing
Working in the Edit MO Status window. Can search by MO number, but if try to search by any ranges (ex: date, site, etc), get error: [Microsoft][ODBC SQL Server Driver][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <+, >, >+, or when the subquery is used as an expression. We are running GP v. 9.0. Any ideas? ...
datetime to datetime offset
We are in the process of upgrading our systems entirely. Making huge changes to the hardware and software. One change is going from SQL Server 2000 to 2008. 2008 has the new datetimeoffset data type, which for us could be a God-send. In reading online however, it seems that the date time is always passed in as a string. Is there no function that allows us to pass in a datetime and an offset or do we actually have to do as I have seen all over the the Internet and convert our dateTime values to a string, then concatonate the offset and pass it in that way? If we DON'T ...
Need PivotChart x axis to cross Y axis at minimum Y axis value
I see an option to have x cross y at max y, but how can I say min y? On Thu, 16 Aug 2007, in microsoft.public.excel.charting, Aaron <Aaron@discussions.microsoft.com> said: >I see an option to have x cross y at max y, but how can I say min y? Jon Peltier taught me this one when I grumbled about the absence of a "min y" check box. As it turns out, setting the x crossing to some absurdly low figure like -999, far below the y axis minimum, does not make the x axis disappear, or ignore the figure and stay at the default zero. Instead it makes the x axis cross at, not below ...
Comparing DateTime ?
i want to compare 2 DateTime. I use mktime and difftime for comparing. If I compare 2 DateTime of same year it works fine but if both dates are from different year , I get a wrong result. any idea ? >i want to compare 2 DateTime. I use mktime and difftime for comparing. > >If I compare 2 DateTime of same year it works fine but if both dates >are from different year , I get a wrong result. Wrong - how? Give us a short code snippet example of what you're doing, the values involved, and the results you're getting. Dave I get the date and time in the format ex : 10:45:4...
VLOOKUP Value in Another Formula
Help, I've almost given up on this. I'm using VLOOKUP in a certain cell. This formula returns a value from another worksheet. I have this part down pat. What I want to do is use the value returned from the cell having the VLOOKUP formula in another cell so I can label the other cell a certain way. My VLOOKUP formula is: =IF(ISNA(VLOOKUP($G$18,'DJC2 ARN DATA'!$A$5:$U$3005,8,FALSE))," ",VLOOKUP ($G$18,'DJC2 ARN DATA'!$A$5:$U$3005,8,FALSE)) Let's say that this VLOOKUP formula is in cell G21. I want to put "PRIMARY NUMBER" in cell G16 if t...
Join query
I have a table that has the list of parts that go on a shop order to create a part. On the table "Requirement_Details" I am selecting the Order, and the Current Due Date. The table Transaction_History tells me the dates something was Issued to the Order by the Transaction Code "I". My problem is that I am looking for the last day that something was issued to the shop order so if I select the MAX(dbo.Transaction_History.TNXDTE_15) as below and nothing has been issued yet the query does not return Null or for this field. I just don't get any records. ...
Reference to cell with text is returning #VALUE
I have a cell that references a different cell that that consists of H9:J9 that is formated for and contains text. The referenceing cell shows #VALUE instead of the actual text that is in the cell? Any ideas as to why this is happening? Any help would be appreciated. What does your formula look like? A simple link formula can only reference a single cell. Like this: =H9 You can't reference a range of cells like this: =H9:J9 That is why you're getting the error. -- Biff Microsoft Excel MVP "Jorist" <Jorist@discussions.microsoft.com> ...
#Value
I need to add 2 cells (E24+E44), how can I achieve this without getting #Value when zero value? Hi Margo, Check your data. The only way you should be getting #Value is if a number is actually being interpreted as text instead of a value. The data in either E24 or E44 has to be text instead of a value. >-----Original Message----- >I need to add 2 cells (E24+E44), how can I achieve this >without getting #Value when zero value? >. > ...
unknown xslt function "format-dateTime"
Iam using Beta2 of VS2005 and getting the error "Error 4 'format-dateTime()' is an unknown XSLT function" at the following line: <xsl:value-of select="format-dateTime($pubDate,'[FNn] [D] [MNn] [Y] [h]:[m01] [PN]')" /> how can I get around this? > > Iam using Beta2 of VS2005 and getting the error "Error 4 'format-dateTime()' > > is an unknown XSLT function" at the following line: > > format-dateTime() is XPath2.0/XSLT2.0/XQuery1.0 function. VS2005 only > supports XPath1.0/XSLT 1.0. Use ms:format-date() exten...
re: updating values
that works, but i'll need to add a lot of hidden feilds (20+/-)... Is there another way (perhaps more efficient -if not as simple?) ("there's more than one way to skin a cat") thanks inadvance, mark --------------------------------------------------------------------- "Daryl S" <DarylS@discussions.microsoft.com> wrote in message news:79CFD708-34B3-419A-A3F1-CF7050ACDE9F@microsoft.com... > Mark - > > Add the field [PresetOption] to the form. You can set the .visible > property > to FALSE so the user won't see it. Then the code...
xsd restriction on dateTime based on other dateTime
Hi All, i'm trying to define an xml schema for validation with a restriction on dateTime element based on another dateTime Element. in English: end (dateTime) should be greater than or equal to begin (dateTime) Is it possible to define such restrictions in XSD? regards Joris van Lier Joris van Lier wrote: > i'm trying to define an xml schema for validation with a restriction on > dateTime element based on another dateTime Element. > > in English: end (dateTime) should be greater than or equal to begin > (dateTime) > > Is it possible to define such rest...
Getting values from hyperlink
Does someone has an answer for the question below? I have a cell (say A1) with the formula: =HYPERLINK(“[d:\tmp\file.xls]sheet!B11") In the next cell, I would like to see the value that the cell pointed by the hyperlink has. I tried: =INDIRECT(A1) But Excel gives a #REF error in the INDIRECT function. Does someone have any idea on how can I solve this? Thanks a lot Antonio Duarte. =indirect() won't work with closed workbooks. Harlan Grove wrote a User Defined Function that can pull from a closed workbook: http://groups.google.co.uk/groups?selm=ASAuc.4700%24H4.12%40w...
log chart minor gridline values at tick marks
I have an XY scatter graph with log10 scales on both axes. How can I display the minor gridline values at the axes tick marks? Currently, the axes display only the power of 10 values (.1, 1, 10, etc...) Thanks Excel does not allow for minor tick labels. However, you can add a dummy XY series along each axis, with a point at each value where you want a label. Hide the points (no line or marker) and add a data label that shows the value. Position the label appropriately. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ ExcellerZero...
Paste Values in linked Cells Only
Goodmorning Everyone! I have a worksheet within a workbook that contains 1500 rows of data over 14 columns (the 12 months, plus to summary columns) - called the "Year to Date" sheet. The YTD sheet has links to other worksheets within the same workbook. This workbook is used as a roll forward workbook. The YTD sheet does not change, but all other worksheets in the workbook get updated with the current months data. In preparing for a new month, I have written a macro that cleans up a lot of the data from the last month. The problem is that I want the YTD worksheet...
C# DateTime -> XML DateTime
Is there a method available to convert a C# DateTime format to XML DateTime format? Thanks, Guy Have you looked at the XmlConvert class? --Dare -- This posting is provided "AS IS" with no warranties, and confers no rights. "Guy Dillen" <guy_dillen@hotmail.com> wrote in message news:3f5f632e$0$13863$ba620e4c@reader0.news.skynet.be... > Is there a method available to convert a C# DateTime format to XML DateTime > format? > > Thanks, > Guy > > yes i have looked at this lass, but does this also work in the direction C#->XML? "SQ...
OnCtlColorDlg & return NULL
The MS documentation about WM_CTLCOLORDLG says: "If an application processes this message, it must return the handle of a brush. The system uses the brush to paint the background of the dialog box." The documentation about "CWnd::OnCtlColor" says something quite similar. But then when the wizzard generates OnCtlColor the included comment says: "TODO: Return a different brush if the default is not desired". Now I wonder if really a brush MUST(!) be returned. Returning NULL works. Nevertheless, that doesn't mean it's valid to do so. Finally, if I can re...
How do I prevent values from been multiplied by two or three?
Hi, I created a query to add values from two different tables. However, some of the results are been multiplied by two and some of them by three. Is there anything I need to do on the query itself to solve this calculation issue? Thanks legacy wrote: > Hi, > I created a query to add values from two different tables. However, > some of the results are been multiplied by two and some of them by > three. Is there anything I need to do on the query itself to solve > this calculation issue? > Probably Show us the sql statement (switch your query to SQL View) an...
Data Migration
I'm trying to migrate Sales History information into CRM. Invoices and Invoice Lines are migrating and calculating the Extended value correctly. I'm having a problem with Credit Notes. Credit Note Lines (where Price per unit < 0) are not migrating. I'm getting a "CrmIncidentUnknownError" in the migration log for these lines. The same error occurs for lines where Price per Unit = 0. I've modified all the relevant value fields on the forms to allow minimum values of -100,000,000. I'm using Existing products and over-riding the Price each time. I previously ...
Value does not fill down (copy) properly.
I need to combine last name and first name fields, and am using this formula: +B2&","&C2 The B column is the last name, and C column is the first name. My first glitch is, when I enter that formula, the forumula RESULT does not display in the cell, only the formula itself. My second glitch is, when I try to copy that formula down the rest of the column of data, it should reflect the descending row numbers (ie., B3 & C3, then B4 & C4, etc.). But only the C column increases accordingly, the B column remains B2 and does not increase. What, praytell, have I don...
Timestamp value of GINA window presentation
I am currently troubleshooting slow logon times for a client with computer infrastructure spread across the globe. Before I start invesagating I want to create a baseline for logon times. I have modified the clients logon script to record when it starts and when it ends. I am using the computers tick count to record how long the computer has been on, but realize that there is a space of time between the user authenicating to the computer (control-alt-delete) and when the logon script starts, if the customer turns his computer on and then leave for an extended period of time I ...
Remove the Formula but leave the value alone?
How easy a macro can be developed to remove the formula but leave the value alone for a group of cells when a special condition matches like my example here? I started with the formula below but realized that I can’t get the result I wanted as to leave the value alone for F4 in the IF formula when the month moving forward. The F4 value originally returned from the Vlookup result. F4: =IF(F2=B1,VLOOKUP($C2,'Sales07'!$B:$Z,10,FALSE),IF(F2>B1,C4+D4+E4,F4)) G4: =IF(G2=B1,VLOOKUP($C2,'Sales07'!$B:$Z,10,FALSE),IF(G2>B1,D4+E4+F4,G4)) H4: =IF(H2=B1,VLOOKUP($C2,'Sale...
NULL DATE
This works fine if there is a date in the textbox, but if the textbox is empty or null, I get an error. This is part of an SQL statement. What can I do if it's Null so that it still works. Thanks DS Format(Forms!frmBSDiscountNames![TxtExpDate], "\#mm\/dd\/yyyy\#") Change you line to Nz(Format(Forms!frmBSDiscountNames![TxtExpDate], "\#mm\/dd\/yyyy\#")) This will return a zero legnth string instead of a null value, and will allow your code to process it. Note that if at any point you are comparing that field to Null, it will fail, and you will have to compar...
Null values and calculations
I have a null value that appears as a zero in a report as the control is "=nZ([PRS TAT],0)". Yet, I am unable to deduct this zero value from a second value. The field just comes out blank. Is this null zero a true zero or can is not be used for calculations. If not, any suggestions on what I can use instead? Thanks! What is the other calculation? Nz() returns a variant data type so you may need to wrap it in Val() =Val(Nz([PRS TAT],0)) -- Duane Hookom Microsoft Access MVP "Karina M ;)" wrote: > I have a null value that appears as a zero in a report as the cont...
Is it possible to show the value of a cell on a custom toolbar. Cell location will not change, but cell content will. -- HilcrRWise ------------------------------------------------------------------------ HilcrRWise's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=11699 View this thread: http://www.excelforum.com/showthread.php?threadid=494807 One way Option Explicit Const WS_RANGE As String = "B10" Private Sub Worksheet_Activate() Dim oCtl As CommandBarControl On Error Resume Next Set oCtl = Application.CommandBars.FindControl(Tag:=&qu...
Adding third lookup value to index/match
I understand how Index/Match works with double lookups... But I cant quite get the third lookup down. Looking to add a third lookup value and range to this formula (range z) =INDEX(table, MATCH(x,range x,0), MATCH(y,range y,1)) Data is laid out like so... (Vertical column range is x) A B C D E F G H 1 range yyyyyyyyyyyyyy -> 2 range zzzzzzzzzzzzzz -> x x x x x x any suggestions...? How does the 3rd range relate? Need more specific details. -- Biff Microsoft Excel MVP "J.W. Aldridge" <jeremy.w.aldridge@gmail.com> wrote in mess...
Subquery returned more than 1 Value in Manufacturing
Working in the Edit MO Status window. Can search by MO number, but if try to search by any ranges (ex: date, site, etc), get error: [Microsoft][ODBC SQL Server Driver][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <+, >, >+, or when the subquery is used as an expression. We are running GP v. 9.0. Any ideas? ...
datetime to datetime offset
We are in the process of upgrading our systems entirely. Making huge changes to the hardware and software. One change is going from SQL Server 2000 to 2008. 2008 has the new datetimeoffset data type, which for us could be a God-send. In reading online however, it seems that the date time is always passed in as a string. Is there no function that allows us to pass in a datetime and an offset or do we actually have to do as I have seen all over the the Internet and convert our dateTime values to a string, then concatonate the offset and pass it in that way? If we DON'T ...
Need PivotChart x axis to cross Y axis at minimum Y axis value
I see an option to have x cross y at max y, but how can I say min y? On Thu, 16 Aug 2007, in microsoft.public.excel.charting, Aaron <Aaron@discussions.microsoft.com> said: >I see an option to have x cross y at max y, but how can I say min y? Jon Peltier taught me this one when I grumbled about the absence of a "min y" check box. As it turns out, setting the x crossing to some absurdly low figure like -999, far below the y axis minimum, does not make the x axis disappear, or ignore the figure and stay at the default zero. Instead it makes the x axis cross at, not below ...
Comparing DateTime ?
i want to compare 2 DateTime. I use mktime and difftime for comparing. If I compare 2 DateTime of same year it works fine but if both dates are from different year , I get a wrong result. any idea ? >i want to compare 2 DateTime. I use mktime and difftime for comparing. > >If I compare 2 DateTime of same year it works fine but if both dates >are from different year , I get a wrong result. Wrong - how? Give us a short code snippet example of what you're doing, the values involved, and the results you're getting. Dave I get the date and time in the format ex : 10:45:4...
VLOOKUP Value in Another Formula
Help, I've almost given up on this. I'm using VLOOKUP in a certain cell. This formula returns a value from another worksheet. I have this part down pat. What I want to do is use the value returned from the cell having the VLOOKUP formula in another cell so I can label the other cell a certain way. My VLOOKUP formula is: =IF(ISNA(VLOOKUP($G$18,'DJC2 ARN DATA'!$A$5:$U$3005,8,FALSE))," ",VLOOKUP ($G$18,'DJC2 ARN DATA'!$A$5:$U$3005,8,FALSE)) Let's say that this VLOOKUP formula is in cell G21. I want to put "PRIMARY NUMBER" in cell G16 if t...
Join query
I have a table that has the list of parts that go on a shop order to create a part. On the table "Requirement_Details" I am selecting the Order, and the Current Due Date. The table Transaction_History tells me the dates something was Issued to the Order by the Transaction Code "I". My problem is that I am looking for the last day that something was issued to the shop order so if I select the MAX(dbo.Transaction_History.TNXDTE_15) as below and nothing has been issued yet the query does not return Null or for this field. I just don't get any records. ...
Reference to cell with text is returning #VALUE
I have a cell that references a different cell that that consists of H9:J9 that is formated for and contains text. The referenceing cell shows #VALUE instead of the actual text that is in the cell? Any ideas as to why this is happening? Any help would be appreciated. What does your formula look like? A simple link formula can only reference a single cell. Like this: =H9 You can't reference a range of cells like this: =H9:J9 That is why you're getting the error. -- Biff Microsoft Excel MVP "Jorist" <Jorist@discussions.microsoft.com> ...
#Value
I need to add 2 cells (E24+E44), how can I achieve this without getting #Value when zero value? Hi Margo, Check your data. The only way you should be getting #Value is if a number is actually being interpreted as text instead of a value. The data in either E24 or E44 has to be text instead of a value. >-----Original Message----- >I need to add 2 cells (E24+E44), how can I achieve this >without getting #Value when zero value? >. > ...
unknown xslt function "format-dateTime"
Iam using Beta2 of VS2005 and getting the error "Error 4 'format-dateTime()' is an unknown XSLT function" at the following line: <xsl:value-of select="format-dateTime($pubDate,'[FNn] [D] [MNn] [Y] [h]:[m01] [PN]')" /> how can I get around this? > > Iam using Beta2 of VS2005 and getting the error "Error 4 'format-dateTime()' > > is an unknown XSLT function" at the following line: > > format-dateTime() is XPath2.0/XSLT2.0/XQuery1.0 function. VS2005 only > supports XPath1.0/XSLT 1.0. Use ms:format-date() exten...
re: updating values
that works, but i'll need to add a lot of hidden feilds (20+/-)... Is there another way (perhaps more efficient -if not as simple?) ("there's more than one way to skin a cat") thanks inadvance, mark --------------------------------------------------------------------- "Daryl S" <DarylS@discussions.microsoft.com> wrote in message news:79CFD708-34B3-419A-A3F1-CF7050ACDE9F@microsoft.com... > Mark - > > Add the field [PresetOption] to the form. You can set the .visible > property > to FALSE so the user won't see it. Then the code...
xsd restriction on dateTime based on other dateTime
Hi All, i'm trying to define an xml schema for validation with a restriction on dateTime element based on another dateTime Element. in English: end (dateTime) should be greater than or equal to begin (dateTime) Is it possible to define such restrictions in XSD? regards Joris van Lier Joris van Lier wrote: > i'm trying to define an xml schema for validation with a restriction on > dateTime element based on another dateTime Element. > > in English: end (dateTime) should be greater than or equal to begin > (dateTime) > > Is it possible to define such rest...
Getting values from hyperlink
Does someone has an answer for the question below? I have a cell (say A1) with the formula: =HYPERLINK(“[d:\tmp\file.xls]sheet!B11") In the next cell, I would like to see the value that the cell pointed by the hyperlink has. I tried: =INDIRECT(A1) But Excel gives a #REF error in the INDIRECT function. Does someone have any idea on how can I solve this? Thanks a lot Antonio Duarte. =indirect() won't work with closed workbooks. Harlan Grove wrote a User Defined Function that can pull from a closed workbook: http://groups.google.co.uk/groups?selm=ASAuc.4700%24H4.12%40w...
log chart minor gridline values at tick marks
I have an XY scatter graph with log10 scales on both axes. How can I display the minor gridline values at the axes tick marks? Currently, the axes display only the power of 10 values (.1, 1, 10, etc...) Thanks Excel does not allow for minor tick labels. However, you can add a dummy XY series along each axis, with a point at each value where you want a label. Hide the points (no line or marker) and add a data label that shows the value. Position the label appropriately. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ ExcellerZero...
Paste Values in linked Cells Only
Goodmorning Everyone! I have a worksheet within a workbook that contains 1500 rows of data over 14 columns (the 12 months, plus to summary columns) - called the "Year to Date" sheet. The YTD sheet has links to other worksheets within the same workbook. This workbook is used as a roll forward workbook. The YTD sheet does not change, but all other worksheets in the workbook get updated with the current months data. In preparing for a new month, I have written a macro that cleans up a lot of the data from the last month. The problem is that I want the YTD worksheet...
C# DateTime -> XML DateTime
Is there a method available to convert a C# DateTime format to XML DateTime format? Thanks, Guy Have you looked at the XmlConvert class? --Dare -- This posting is provided "AS IS" with no warranties, and confers no rights. "Guy Dillen" <guy_dillen@hotmail.com> wrote in message news:3f5f632e$0$13863$ba620e4c@reader0.news.skynet.be... > Is there a method available to convert a C# DateTime format to XML DateTime > format? > > Thanks, > Guy > > yes i have looked at this lass, but does this also work in the direction C#->XML? "SQ...
OnCtlColorDlg & return NULL
The MS documentation about WM_CTLCOLORDLG says: "If an application processes this message, it must return the handle of a brush. The system uses the brush to paint the background of the dialog box." The documentation about "CWnd::OnCtlColor" says something quite similar. But then when the wizzard generates OnCtlColor the included comment says: "TODO: Return a different brush if the default is not desired". Now I wonder if really a brush MUST(!) be returned. Returning NULL works. Nevertheless, that doesn't mean it's valid to do so. Finally, if I can re...
How do I prevent values from been multiplied by two or three?
Hi, I created a query to add values from two different tables. However, some of the results are been multiplied by two and some of them by three. Is there anything I need to do on the query itself to solve this calculation issue? Thanks legacy wrote: > Hi, > I created a query to add values from two different tables. However, > some of the results are been multiplied by two and some of them by > three. Is there anything I need to do on the query itself to solve > this calculation issue? > Probably Show us the sql statement (switch your query to SQL View) an...
Data Migration
I'm trying to migrate Sales History information into CRM. Invoices and Invoice Lines are migrating and calculating the Extended value correctly. I'm having a problem with Credit Notes. Credit Note Lines (where Price per unit < 0) are not migrating. I'm getting a "CrmIncidentUnknownError" in the migration log for these lines. The same error occurs for lines where Price per Unit = 0. I've modified all the relevant value fields on the forms to allow minimum values of -100,000,000. I'm using Existing products and over-riding the Price each time. I previously ...
Value does not fill down (copy) properly.
I need to combine last name and first name fields, and am using this formula: +B2&","&C2 The B column is the last name, and C column is the first name. My first glitch is, when I enter that formula, the forumula RESULT does not display in the cell, only the formula itself. My second glitch is, when I try to copy that formula down the rest of the column of data, it should reflect the descending row numbers (ie., B3 & C3, then B4 & C4, etc.). But only the C column increases accordingly, the B column remains B2 and does not increase. What, praytell, have I don...
Timestamp value of GINA window presentation
I am currently troubleshooting slow logon times for a client with computer infrastructure spread across the globe. Before I start invesagating I want to create a baseline for logon times. I have modified the clients logon script to record when it starts and when it ends. I am using the computers tick count to record how long the computer has been on, but realize that there is a space of time between the user authenicating to the computer (control-alt-delete) and when the logon script starts, if the customer turns his computer on and then leave for an extended period of time I ...
Remove the Formula but leave the value alone?
How easy a macro can be developed to remove the formula but leave the value alone for a group of cells when a special condition matches like my example here? I started with the formula below but realized that I can’t get the result I wanted as to leave the value alone for F4 in the IF formula when the month moving forward. The F4 value originally returned from the Vlookup result. F4: =IF(F2=B1,VLOOKUP($C2,'Sales07'!$B:$Z,10,FALSE),IF(F2>B1,C4+D4+E4,F4)) G4: =IF(G2=B1,VLOOKUP($C2,'Sales07'!$B:$Z,10,FALSE),IF(G2>B1,D4+E4+F4,G4)) H4: =IF(H2=B1,VLOOKUP($C2,'Sale...
NULL DATE
This works fine if there is a date in the textbox, but if the textbox is empty or null, I get an error. This is part of an SQL statement. What can I do if it's Null so that it still works. Thanks DS Format(Forms!frmBSDiscountNames![TxtExpDate], "\#mm\/dd\/yyyy\#") Change you line to Nz(Format(Forms!frmBSDiscountNames![TxtExpDate], "\#mm\/dd\/yyyy\#")) This will return a zero legnth string instead of a null value, and will allow your code to process it. Note that if at any point you are comparing that field to Null, it will fail, and you will have to compar...
Null values and calculations
I have a null value that appears as a zero in a report as the control is "=nZ([PRS TAT],0)". Yet, I am unable to deduct this zero value from a second value. The field just comes out blank. Is this null zero a true zero or can is not be used for calculations. If not, any suggestions on what I can use instead? Thanks! What is the other calculation? Nz() returns a variant data type so you may need to wrap it in Val() =Val(Nz([PRS TAT],0)) -- Duane Hookom Microsoft Access MVP "Karina M ;)" wrote: > I have a null value that appears as a zero in a report as the cont...