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
2152 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:
How to get 2 Max values?
I have following 2 tables (T1, T2) and rows: T1 (Column: Id) ---- 1 2 3 4 T2 (Columns: Id, Value) ---- 1, 1 1, 2 1, 3 1, 4 2, 1 2, 2 2, 3 2, 4 3, 1 3, 2 3, 3 3, 4 4, 1 4, 2 4, 3 4, 4 The result-set I need is: --------------------------------------- 1, 3 1, 4 2, 3 2, 4 3, 3 3, 4 4, 3 4, 4 i.e for each Id from T1, I need 2 max values from T2. How do I write the query? Will this work? Select t1.Id, ( Select t2.Value From t2 Where t2.Id = t1.Id Order By Value Desc Limit 2 ) From t1 I keep getting subquery returns more than one...
Display fields Limited on Values
I'm pretty fluent in MySQL but I ran into a problem. I have a database that consists mainly of booleans. It tracks whether people did certain things and the layout is pretty simple. However, I cannot figure out how to write a query that will display the person's name and what requirements they still must meet. I have created a rather lengthy query to display everyone who has not, but I want to be able to generate a form that will show the people what they still need to do. Thanks, Paul Well if you want us to think about it give us something to look at (what do you have, ...
selected value is not saved in combo box
I have problem with saving a combo box selected values in excel file. I've created a simple excel file using Office 2003. I've added a combo-box to one of worksheets. After I am selecting any value in the combo-box, saving the excel file and closing it. After opening the file there is no value selected in the combo-box. What I need to do in order to be able to keep selected values in the combo-box after closing and opening the excel file. Thanks in advance! -- MarkDev ------------------------------------------------------------------------ MarkDev's Profile: http://www.excel...
Delete cell value but keep formula in cell.
After the formula in a cell has calculated a value I would like to reset the cell for the next use by deleting the value but keeping the formula in the cell. Is this possible in Excel 2007? You need to explain that a bit. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Lofred" <Lofred@discussions.microsoft.com> wrote in message news:D3DE79C2-C41E-46C7-8F6C-EAD9960EC528@microsoft.com... > After the formula in a cell has calculated a value I would like to reset > the > cell for the next use by deleting the value but...
Is Not Null?
I have a query where I want to use Is Not Null in the criteria to check whether there is data in a text field. However when I run the query I still get all the records. Anyone tell me why? Thanks tony "Tony Williams" <tw@invalid.com> wrote in message news:uLLUXloYIHA.984@TK2MSFTNGP06.phx.gbl... >I have a query where I want to use Is Not Null in the criteria to check >whether there is data in a text field. However when I run the query I still >get all the records. Anyone tell me why? > Thanks > tony > If the field contains an empty string (""...
dummy alert
i have this formula =SUM(E3:E11/B3:B11), i want to divide the sum of the E column by the sum of the B column but am obviously missing something that will seem hideously obvious when pointed out thanks Micayla, Nice name. =Sum(E3:E11)/Sum(B3:B11) would work if you are looking for a single output of the one column sum divided by the other. OTOH if you want to divide each row and then sum the results, you could put =E3/B3 in the C3 cell and copy down. Then sum the numbers in Column C. If you are doing the latter, you can do it in one formula that looks like the one you made, but when you bu...
insert an image based on a cell value
i am trying to create a model of genetics within a spreadsheet where the male and female genetic contribution to their offspring is displayed in a cell either as A or a and using " &" to conbine in the offspring cell , chromosmes are displayed as AA,Aa or aa is it possible ....ifor this resultant cell content to then control an image to be viewed to show the shape of the offspring with that resultant genetic code.....eg IF cell content =AA 2 antennae / Aa 1 antenna / and aa 0 antenna i have a very basic drawing created but i would like to import better , spe...
Summing LOOKUP values
I am pulling info from one sheet and putting it in another, via a macro I am using the lookup funtion to get three values of data from separat worksheets. I then put those three values on a new worksheet and woul like to sum them. Unfortunitely, the values do not sum. A way that works would be to do paste-special values only, but I do not know how to do that via marco. Any suggestions? Thanks, Mik -- Message posted from http://www.ExcelForum.com i got the following after recording a macro (the first place to look fo solutions): Selection.PasteSpecial Paste:=xlPasteValues, Operation:=x...
2nd Request
The two tck_201_ fields are text boxes on a form that are being set by to calculated text boxes on a different form!subform. As you can see by the debug statements and results, the values will just not transfer. I have set the format for both of the TCK _201_ fields to Fixed! Any Ideas? Me.TCK_201_Done = Forms!frmPTS_Edit!SFrm_Date_Used_E!TMS_Actual Me.TCK_201_Tot = Forms!frmPTS_Edit!SFrm_Date_Used_E!TMS_NumDates Debug.Print Me.TCK_201_Done Debug.Print Me.TCK_201_Tot Debug.Print Forms!frmPTS_Edit!SFrm_Date_Used_E!TMS_Actual Debug.Print Forms!frmPT...
Checking Date for Null
I'm passing a date from a form control to a public function. What is good technique for having the function check the date for Null? Simple example: Calling statement (from Control Source) = MyFunction([txbDate]) Function MyFunction(dateDate as Date) As String If IsNull(dateDate) Then MyFunction = "something" Else MyFunction = "something else" End If End Function This won't work, since a date variable cannot be Null. I've got it working by declaring the date variable as Variant, but can this really be the right answer? Thanks, ...
Macro to add value if cell has a color :-s
I don't know if this exists, but is it possible to check if a certain cell has a color? Normally a cell is blank, but someone asked me to see if it is possible to check if a cell is colored.. Any suggestions? Thanks!!! Is this sufficient? Sub ifcolor() If Range("b2").Interior.ColorIndex > 0 Then MsgBox "yep" End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com <test@testnospam.nl> wrote in message news:obht5456aq6ur5gbqmasacqke9p2g13o7i@4ax.com... >I don't know if this exists, but is it possible to check if a cer...
Using parameter in query to specify null/not null records
I have this query: Select Max(SessionID) FROM Session WHERE ObjectID = [Enter CatID] And LevelID = 2 And [EndTime] Is Null I want to be able to use a parameter to be able to switch at will from AND [EndTime] Is Null to AND [EndTime] Is NOT Null Obviously, this is to be able to return values from only closed sessions or from only open sessions. I haven't encountered this syntax before in my work with Access, so I am a bit unsure as to how to phrase this. Does anyone have any ideas? Thanks; Amy You M_I_G_H_T be able to do that with the following: Enter anything at all ...
Max of value from DataGroup2 within DataGroup1
Hello - I have a table with data as follows below. I am trying to build a query that will give me the record with MAX of specDiffMax [value] for each TestFreq [DataGroup 2] within each TestNum [DataGroup1]. ID TestNum TestFreq specDiffMax -------------------------------------------- 4889683 Test 1 1710 3.669998 5123289 Test 1 1710 2.882999 4817314 Test 1 1710 3.102001 5134007 Test 1 1710.2 3.573002 4896056 Test 1 1710.2 3.355 4914480 Test 1 1710.2 3.515999 4889685 Test 2 1710.4 3.333 4896057 Test 2 1710.4 3.450001 4914481 Test 2 ...
How do I filter rows based upon a column value
I have a spreadsheet that contains multiple agency id's in a column. When generating reports, I would like to filter per agency and display only the rows associated with that agency. Is there a tutorial or sample on how to do this? Hi It sounds like you are looking for Data / Filter / AutoFilter. Have a look here for some basics: http://www.contextures.com/xlautofilter01.html -- Andy. "Jack" <nfr@nospam.com> wrote in message news:eqiU08TVEHA.2988@TK2MSFTNGP10.phx.gbl... > I have a spreadsheet that contains multiple agency id's in a column. When > generati...
Wont show Values, just formula
Have some formulas like the following:- =IF(B10=0," ",LOOKUP(B10,Menu!$A$1:$A$146,Menu!$B$1:$B$146)) wanted to change the lookup range, but every time i change the formula it just displayes the formula, and not the resulting value. WHY?? I have checked the View Options and they are OK. Tried deleting an retyping the formula. Nothing helps Can some1 help me please ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Actually what you want to make sure you have on ...
returning zero for a null
I am performing a count function in a query to give me total accounts grouped by state, region, district. Problem is some districts do not have account #s in them and they are not on the results of the query. I've been trying to get the results to return a zero for the null districts, but I cannot structure it correctly. Can anyone give me more insight as to the best way to do this and how/where I should insert the function. On Feb 20, 2:29=A0pm, TheReallyWhiteRunner <donald.quint...@gmail.com> wrote: > I am performing a count function in a query to give me total accounts > ...
excel to send email when value manually put in
Hi, I would like to make a macro, i believe it is called, to send an email when A1 is below or equal to a certain value, say 3. I have looked at the site http://www.rondebruin.nl/ but this is complicated. I put in the text: Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("A1"), Target) Is Nothing Then If IsNumeric(Target.Value) And Target.Value > 200 Then YourMacroName End If Sub Mail_CDO() Dim iMsg As Object Dim iConf As Object ' Dim Flds As Variant Set iMsg = CreateObject("CDO....
Improperly nested XML when column contains NULL #2
I'm getting improperly nested xml when a column contains null. I modified the first row (customer ALFKI) to have a NULL Address column. I'm using the following XSD Annotated Schema against the NorthWind database: <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema"> <xs:element name="customer" sql:relation="Customers" sql:key-fields="CustomerID"> <xs:complexType> <xs:sequence> <xs:element name="name" sql:field="ContactN...
Print values dynamically on a form
I have a form that needs to display values from an array on the form; how do I achieve the following? If item count in array is < 20 then print Val1 Val2 Val3 Val4 Val5 Val6 and so on If items in array is > 20 then print Val1 Val2 Val3 Val4 Val5 Val6 Val7 Val8 Val9 and so on The following code works fine and prints values in two columns butI need to modified it to print in three columns if nItemsCount > 20 for i=1 to nItemsCount If i Mod 2 Then nX = nX * 25 nY = nY - 15 End If myVal.Name = "myVal&...
Add Records based on Value
Here is a tricky situation that I've run into. I have a table with about 100 records, and each record has an sku and the quantity on hand. I want to put one record into a new table for as many pieces of inventory I have. For example: SKU Quantity 001 2 002 4 I need the new table to have SKU 001 001 002 002 002 002 Any ideas? You've described 'how' you want to do something. If you describe a bit more about "why" you want to, and "what" you expect you'll be able to do as a result, the newsgroup readers may be able to offer ...
excel same value in two cells
How can 2 cells have the same values, such that when I edit either cell the change is reflected on both? It is not possible without using some sophisticated features of event listening and macros. But one should ask the question: what would be the benefits? You can refer the second cell from the first one -- something like A2: =A1, so A1 and A2 will both have the same value and each time you change A1, A2 will change. pls help wrote: >How can 2 cells have the same values, such that when I edit either cell the >change is reflected on both? This macro will do as you describe............
chart x-axis values appear as a series
The x-axis values do not appear on the x-axis but appear as another series on the chart. How do I specify the x-axis values for a chart? Existing chart: Chart menu > Source Data > Series tab. For a new chart, fix the data first. Put the X values in the first column and Y values in the columns to the right. Put a label (series name) in the row above the first row of data for each set of Y values, but keep the cell atop the X values blank. Select the entire range, including X values, series names, and blank cell, then start the chart wizard. The blank cell helps Excel interpret t...
Search and display based on cell value
On page one. I have various full names listed throughout the worksheet in various places. When a user clicks on a cell with a name in it, I want them to be directed to sheet 2 where the same name (cell values) is found in column A. (There will be profile info starting with their name in column A.) I think the following may do what you are looking for. Right click the tab at the bottom of Sheet1 (which is what I assumed you meant by "page one"), select View Code from the popup menu that appears and then copy/paste the following into the code window that appeared... ...
Pie Chart not to display 0 Values
Good day, I have an excel Table something like below. Column A Column B Item 1 24% Item 2 13% Item 3 0% Item 4 3% Item 5 0% The Values in Column B are being automatically updated from another table. There are 24 Items in the table and usually about 10 of them are 0, in a given period of time. Is there any way that the chart can be intelligent enough to skip the 0 Values and display only values greater than 0. This would clear the clutter on the chart. I am using a pie chart for the purpose. Thanks for Help _________________ Best Regards Khawar Andy Pope has instructions on his web sit...
Excel macro to insert rows if a cell does not equal the value above that cell
I have a spreadsheet listing columns of information for many different people. If more than one row exists for the same person, I'd like to insert 2 rows after the row so that the group is separated from the next group. For example, here's how the data appears now: LastName Account# Balance Martin 1 500.00 Martin 2 750.00 Smith 5 100.00 Thomas 9 900.00 Here's what I'd like it to look like after running the macro: LastName Account# Balance Martin 1 500.00 Martin 2 75...
I have following 2 tables (T1, T2) and rows: T1 (Column: Id) ---- 1 2 3 4 T2 (Columns: Id, Value) ---- 1, 1 1, 2 1, 3 1, 4 2, 1 2, 2 2, 3 2, 4 3, 1 3, 2 3, 3 3, 4 4, 1 4, 2 4, 3 4, 4 The result-set I need is: --------------------------------------- 1, 3 1, 4 2, 3 2, 4 3, 3 3, 4 4, 3 4, 4 i.e for each Id from T1, I need 2 max values from T2. How do I write the query? Will this work? Select t1.Id, ( Select t2.Value From t2 Where t2.Id = t1.Id Order By Value Desc Limit 2 ) From t1 I keep getting subquery returns more than one...
Display fields Limited on Values
I'm pretty fluent in MySQL but I ran into a problem. I have a database that consists mainly of booleans. It tracks whether people did certain things and the layout is pretty simple. However, I cannot figure out how to write a query that will display the person's name and what requirements they still must meet. I have created a rather lengthy query to display everyone who has not, but I want to be able to generate a form that will show the people what they still need to do. Thanks, Paul Well if you want us to think about it give us something to look at (what do you have, ...
selected value is not saved in combo box
I have problem with saving a combo box selected values in excel file. I've created a simple excel file using Office 2003. I've added a combo-box to one of worksheets. After I am selecting any value in the combo-box, saving the excel file and closing it. After opening the file there is no value selected in the combo-box. What I need to do in order to be able to keep selected values in the combo-box after closing and opening the excel file. Thanks in advance! -- MarkDev ------------------------------------------------------------------------ MarkDev's Profile: http://www.excel...
Delete cell value but keep formula in cell.
After the formula in a cell has calculated a value I would like to reset the cell for the next use by deleting the value but keeping the formula in the cell. Is this possible in Excel 2007? You need to explain that a bit. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Lofred" <Lofred@discussions.microsoft.com> wrote in message news:D3DE79C2-C41E-46C7-8F6C-EAD9960EC528@microsoft.com... > After the formula in a cell has calculated a value I would like to reset > the > cell for the next use by deleting the value but...
Is Not Null?
I have a query where I want to use Is Not Null in the criteria to check whether there is data in a text field. However when I run the query I still get all the records. Anyone tell me why? Thanks tony "Tony Williams" <tw@invalid.com> wrote in message news:uLLUXloYIHA.984@TK2MSFTNGP06.phx.gbl... >I have a query where I want to use Is Not Null in the criteria to check >whether there is data in a text field. However when I run the query I still >get all the records. Anyone tell me why? > Thanks > tony > If the field contains an empty string (""...
dummy alert
i have this formula =SUM(E3:E11/B3:B11), i want to divide the sum of the E column by the sum of the B column but am obviously missing something that will seem hideously obvious when pointed out thanks Micayla, Nice name. =Sum(E3:E11)/Sum(B3:B11) would work if you are looking for a single output of the one column sum divided by the other. OTOH if you want to divide each row and then sum the results, you could put =E3/B3 in the C3 cell and copy down. Then sum the numbers in Column C. If you are doing the latter, you can do it in one formula that looks like the one you made, but when you bu...
insert an image based on a cell value
i am trying to create a model of genetics within a spreadsheet where the male and female genetic contribution to their offspring is displayed in a cell either as A or a and using " &" to conbine in the offspring cell , chromosmes are displayed as AA,Aa or aa is it possible ....ifor this resultant cell content to then control an image to be viewed to show the shape of the offspring with that resultant genetic code.....eg IF cell content =AA 2 antennae / Aa 1 antenna / and aa 0 antenna i have a very basic drawing created but i would like to import better , spe...
Summing LOOKUP values
I am pulling info from one sheet and putting it in another, via a macro I am using the lookup funtion to get three values of data from separat worksheets. I then put those three values on a new worksheet and woul like to sum them. Unfortunitely, the values do not sum. A way that works would be to do paste-special values only, but I do not know how to do that via marco. Any suggestions? Thanks, Mik -- Message posted from http://www.ExcelForum.com i got the following after recording a macro (the first place to look fo solutions): Selection.PasteSpecial Paste:=xlPasteValues, Operation:=x...
2nd Request
The two tck_201_ fields are text boxes on a form that are being set by to calculated text boxes on a different form!subform. As you can see by the debug statements and results, the values will just not transfer. I have set the format for both of the TCK _201_ fields to Fixed! Any Ideas? Me.TCK_201_Done = Forms!frmPTS_Edit!SFrm_Date_Used_E!TMS_Actual Me.TCK_201_Tot = Forms!frmPTS_Edit!SFrm_Date_Used_E!TMS_NumDates Debug.Print Me.TCK_201_Done Debug.Print Me.TCK_201_Tot Debug.Print Forms!frmPTS_Edit!SFrm_Date_Used_E!TMS_Actual Debug.Print Forms!frmPT...
Checking Date for Null
I'm passing a date from a form control to a public function. What is good technique for having the function check the date for Null? Simple example: Calling statement (from Control Source) = MyFunction([txbDate]) Function MyFunction(dateDate as Date) As String If IsNull(dateDate) Then MyFunction = "something" Else MyFunction = "something else" End If End Function This won't work, since a date variable cannot be Null. I've got it working by declaring the date variable as Variant, but can this really be the right answer? Thanks, ...
Macro to add value if cell has a color :-s
I don't know if this exists, but is it possible to check if a certain cell has a color? Normally a cell is blank, but someone asked me to see if it is possible to check if a cell is colored.. Any suggestions? Thanks!!! Is this sufficient? Sub ifcolor() If Range("b2").Interior.ColorIndex > 0 Then MsgBox "yep" End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com <test@testnospam.nl> wrote in message news:obht5456aq6ur5gbqmasacqke9p2g13o7i@4ax.com... >I don't know if this exists, but is it possible to check if a cer...
Using parameter in query to specify null/not null records
I have this query: Select Max(SessionID) FROM Session WHERE ObjectID = [Enter CatID] And LevelID = 2 And [EndTime] Is Null I want to be able to use a parameter to be able to switch at will from AND [EndTime] Is Null to AND [EndTime] Is NOT Null Obviously, this is to be able to return values from only closed sessions or from only open sessions. I haven't encountered this syntax before in my work with Access, so I am a bit unsure as to how to phrase this. Does anyone have any ideas? Thanks; Amy You M_I_G_H_T be able to do that with the following: Enter anything at all ...
Max of value from DataGroup2 within DataGroup1
Hello - I have a table with data as follows below. I am trying to build a query that will give me the record with MAX of specDiffMax [value] for each TestFreq [DataGroup 2] within each TestNum [DataGroup1]. ID TestNum TestFreq specDiffMax -------------------------------------------- 4889683 Test 1 1710 3.669998 5123289 Test 1 1710 2.882999 4817314 Test 1 1710 3.102001 5134007 Test 1 1710.2 3.573002 4896056 Test 1 1710.2 3.355 4914480 Test 1 1710.2 3.515999 4889685 Test 2 1710.4 3.333 4896057 Test 2 1710.4 3.450001 4914481 Test 2 ...
How do I filter rows based upon a column value
I have a spreadsheet that contains multiple agency id's in a column. When generating reports, I would like to filter per agency and display only the rows associated with that agency. Is there a tutorial or sample on how to do this? Hi It sounds like you are looking for Data / Filter / AutoFilter. Have a look here for some basics: http://www.contextures.com/xlautofilter01.html -- Andy. "Jack" <nfr@nospam.com> wrote in message news:eqiU08TVEHA.2988@TK2MSFTNGP10.phx.gbl... > I have a spreadsheet that contains multiple agency id's in a column. When > generati...
Wont show Values, just formula
Have some formulas like the following:- =IF(B10=0," ",LOOKUP(B10,Menu!$A$1:$A$146,Menu!$B$1:$B$146)) wanted to change the lookup range, but every time i change the formula it just displayes the formula, and not the resulting value. WHY?? I have checked the View Options and they are OK. Tried deleting an retyping the formula. Nothing helps Can some1 help me please ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Actually what you want to make sure you have on ...
returning zero for a null
I am performing a count function in a query to give me total accounts grouped by state, region, district. Problem is some districts do not have account #s in them and they are not on the results of the query. I've been trying to get the results to return a zero for the null districts, but I cannot structure it correctly. Can anyone give me more insight as to the best way to do this and how/where I should insert the function. On Feb 20, 2:29=A0pm, TheReallyWhiteRunner <donald.quint...@gmail.com> wrote: > I am performing a count function in a query to give me total accounts > ...
excel to send email when value manually put in
Hi, I would like to make a macro, i believe it is called, to send an email when A1 is below or equal to a certain value, say 3. I have looked at the site http://www.rondebruin.nl/ but this is complicated. I put in the text: Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("A1"), Target) Is Nothing Then If IsNumeric(Target.Value) And Target.Value > 200 Then YourMacroName End If Sub Mail_CDO() Dim iMsg As Object Dim iConf As Object ' Dim Flds As Variant Set iMsg = CreateObject("CDO....
Improperly nested XML when column contains NULL #2
I'm getting improperly nested xml when a column contains null. I modified the first row (customer ALFKI) to have a NULL Address column. I'm using the following XSD Annotated Schema against the NorthWind database: <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema"> <xs:element name="customer" sql:relation="Customers" sql:key-fields="CustomerID"> <xs:complexType> <xs:sequence> <xs:element name="name" sql:field="ContactN...
Print values dynamically on a form
I have a form that needs to display values from an array on the form; how do I achieve the following? If item count in array is < 20 then print Val1 Val2 Val3 Val4 Val5 Val6 and so on If items in array is > 20 then print Val1 Val2 Val3 Val4 Val5 Val6 Val7 Val8 Val9 and so on The following code works fine and prints values in two columns butI need to modified it to print in three columns if nItemsCount > 20 for i=1 to nItemsCount If i Mod 2 Then nX = nX * 25 nY = nY - 15 End If myVal.Name = "myVal&...
Add Records based on Value
Here is a tricky situation that I've run into. I have a table with about 100 records, and each record has an sku and the quantity on hand. I want to put one record into a new table for as many pieces of inventory I have. For example: SKU Quantity 001 2 002 4 I need the new table to have SKU 001 001 002 002 002 002 Any ideas? You've described 'how' you want to do something. If you describe a bit more about "why" you want to, and "what" you expect you'll be able to do as a result, the newsgroup readers may be able to offer ...
excel same value in two cells
How can 2 cells have the same values, such that when I edit either cell the change is reflected on both? It is not possible without using some sophisticated features of event listening and macros. But one should ask the question: what would be the benefits? You can refer the second cell from the first one -- something like A2: =A1, so A1 and A2 will both have the same value and each time you change A1, A2 will change. pls help wrote: >How can 2 cells have the same values, such that when I edit either cell the >change is reflected on both? This macro will do as you describe............
chart x-axis values appear as a series
The x-axis values do not appear on the x-axis but appear as another series on the chart. How do I specify the x-axis values for a chart? Existing chart: Chart menu > Source Data > Series tab. For a new chart, fix the data first. Put the X values in the first column and Y values in the columns to the right. Put a label (series name) in the row above the first row of data for each set of Y values, but keep the cell atop the X values blank. Select the entire range, including X values, series names, and blank cell, then start the chart wizard. The blank cell helps Excel interpret t...
Search and display based on cell value
On page one. I have various full names listed throughout the worksheet in various places. When a user clicks on a cell with a name in it, I want them to be directed to sheet 2 where the same name (cell values) is found in column A. (There will be profile info starting with their name in column A.) I think the following may do what you are looking for. Right click the tab at the bottom of Sheet1 (which is what I assumed you meant by "page one"), select View Code from the popup menu that appears and then copy/paste the following into the code window that appeared... ...
Pie Chart not to display 0 Values
Good day, I have an excel Table something like below. Column A Column B Item 1 24% Item 2 13% Item 3 0% Item 4 3% Item 5 0% The Values in Column B are being automatically updated from another table. There are 24 Items in the table and usually about 10 of them are 0, in a given period of time. Is there any way that the chart can be intelligent enough to skip the 0 Values and display only values greater than 0. This would clear the clutter on the chart. I am using a pie chart for the purpose. Thanks for Help _________________ Best Regards Khawar Andy Pope has instructions on his web sit...
Excel macro to insert rows if a cell does not equal the value above that cell
I have a spreadsheet listing columns of information for many different people. If more than one row exists for the same person, I'd like to insert 2 rows after the row so that the group is separated from the next group. For example, here's how the data appears now: LastName Account# Balance Martin 1 500.00 Martin 2 750.00 Smith 5 100.00 Thomas 9 900.00 Here's what I'd like it to look like after running the macro: LastName Account# Balance Martin 1 500.00 Martin 2 75...