No results using DateDiff

I am working with DateDiff . I get no error messages but also do not get any 
results in my calculated field. I have two dates, START_DATE and END_DATE and 
I need to know the number of days between them. START_DATE and END_DATE are 
both Date/Time type with a Short Date format.

I am working in the QBE grid to write the query. I select an empty column 
and put my DateDiff calculation on the line below Or: in the Criteria area. I 
have tried:

[END_DATE]-[START_DATE]
   and
DateDiff("d",[START_DATE],[STOP_DATE])

Both approaches ask for parameter values for Time and STOP_DATE. If I don't 
enter anything the query runs and displays data from all other columns, 
displays the calculated field, Time, but the cells are empty.

Help is appreciated, and I expect to feel pretty stupid after I get it!
0
Utf
1/7/2008 10:05:01 PM
access.queries 6343 articles. 1 followers. Follow

5 Replies
1291 Views

Similar Articles

[PageSpeed] 21

Martha

Is there any significance to your use of both [END_DATE] and [STOP_DATE]?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Martha" <Martha@discussions.microsoft.com> wrote in message 
news:026A5D8C-FAC6-4F29-AAD1-01E68AE7F554@microsoft.com...
>I am working with DateDiff . I get no error messages but also do not get 
>any
> results in my calculated field. I have two dates, START_DATE and END_DATE 
> and
> I need to know the number of days between them. START_DATE and END_DATE 
> are
> both Date/Time type with a Short Date format.
>
> I am working in the QBE grid to write the query. I select an empty column
> and put my DateDiff calculation on the line below Or: in the Criteria 
> area. I
> have tried:
>
> [END_DATE]-[START_DATE]
>   and
> DateDiff("d",[START_DATE],[STOP_DATE])
>
> Both approaches ask for parameter values for Time and STOP_DATE. If I 
> don't
> enter anything the query runs and displays data from all other columns,
> displays the calculated field, Time, but the cells are empty.
>
> Help is appreciated, and I expect to feel pretty stupid after I get it! 


0
Jeff
1/7/2008 11:01:27 PM
Create a calculated field in your query:

TotDays: DateDiff("d",[START_DATE], [END_DATE])

Not in the criteria rows.
-- 
Dave Hargis, Microsoft Access MVP


"Martha" wrote:

> I am working with DateDiff . I get no error messages but also do not get any 
> results in my calculated field. I have two dates, START_DATE and END_DATE and 
> I need to know the number of days between them. START_DATE and END_DATE are 
> both Date/Time type with a Short Date format.
> 
> I am working in the QBE grid to write the query. I select an empty column 
> and put my DateDiff calculation on the line below Or: in the Criteria area. I 
> have tried:
> 
> [END_DATE]-[START_DATE]
>    and
> DateDiff("d",[START_DATE],[STOP_DATE])
> 
> Both approaches ask for parameter values for Time and STOP_DATE. If I don't 
> enter anything the query runs and displays data from all other columns, 
> displays the calculated field, Time, but the cells are empty.
> 
> Help is appreciated, and I expect to feel pretty stupid after I get it!
0
Utf
1/7/2008 11:04:01 PM
Thanks, Klatuu. As this is my first post, could you let me know how to mark 
this thread as answered?

Martha

"Klatuu" wrote:

> Create a calculated field in your query:
> 
> TotDays: DateDiff("d",[START_DATE], [END_DATE])
> 
> Not in the criteria rows.
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "Martha" wrote:
> 
> > I am working with DateDiff . I get no error messages but also do not get any 
> > results in my calculated field. I have two dates, START_DATE and END_DATE and 
> > I need to know the number of days between them. START_DATE and END_DATE are 
> > both Date/Time type with a Short Date format.
> > 
> > I am working in the QBE grid to write the query. I select an empty column 
> > and put my DateDiff calculation on the line below Or: in the Criteria area. I 
> > have tried:
> > 
> > [END_DATE]-[START_DATE]
> >    and
> > DateDiff("d",[START_DATE],[STOP_DATE])
> > 
> > Both approaches ask for parameter values for Time and STOP_DATE. If I don't 
> > enter anything the query runs and displays data from all other columns, 
> > displays the calculated field, Time, but the cells are empty.
> > 
> > Help is appreciated, and I expect to feel pretty stupid after I get it!
0
Utf
1/8/2008 3:45:01 AM
It is enough, already, that you simply mentioned it !   You would be 
surprised to see how many posters don't even take time to give even that 
kind of simple feed-back ! Thanks to you to do it.


Vanderghast, Access MVP


"Martha" <Martha@discussions.microsoft.com> wrote in message 
news:6F794EBC-3E54-4BC8-B58C-CAE0ED35D8A8@microsoft.com...
> Thanks, Klatuu. As this is my first post, could you let me know how to 
> mark
> this thread as answered?
>
> Martha
>


0
Michel
1/8/2008 7:04:05 PM
Jeff,
No significance except to demonstrate that my head is not always directing 
my fingers. They should have been the same.
Martha

"Jeff Boyce" wrote:

> Martha
> 
> Is there any significance to your use of both [END_DATE] and [STOP_DATE]?
> 
> Regards
> 
> Jeff Boyce
> Microsoft Office/Access MVP
> 
> "Martha" <Martha@discussions.microsoft.com> wrote in message 
> news:026A5D8C-FAC6-4F29-AAD1-01E68AE7F554@microsoft.com...
> >I am working with DateDiff . I get no error messages but also do not get 
> >any
> > results in my calculated field. I have two dates, START_DATE and END_DATE 
> > and
> > I need to know the number of days between them. START_DATE and END_DATE 
> > are
> > both Date/Time type with a Short Date format.
> >
> > I am working in the QBE grid to write the query. I select an empty column
> > and put my DateDiff calculation on the line below Or: in the Criteria 
> > area. I
> > have tried:
> >
> > [END_DATE]-[START_DATE]
> >   and
> > DateDiff("d",[START_DATE],[STOP_DATE])
> >
> > Both approaches ask for parameter values for Time and STOP_DATE. If I 
> > don't
> > enter anything the query runs and displays data from all other columns,
> > displays the calculated field, Time, but the cells are empty.
> >
> > Help is appreciated, and I expect to feel pretty stupid after I get it! 
> 
> 
> 
0
Utf
1/8/2008 7:48:02 PM
Reply:

Similar Artilces:

How do I calculate escalation rates using dates and a set value
If I have a projected that needs to be escalated using start and completion dates by a certain percentage per year, how do I write the formula. For example if my project starts May 26, 2010 and lasts until may 26 2014 I want to add escalation to midpoint at 4% per year. ...

Setup Outlook 2003 to reply using Quotes
I have read some posts already but I am not clear on the quoting/replying method. I have set outlook 2003 as follows: Tools/options/mail - preferences tab - mail options, and then set to prefix with a > for replys and forwards. I get a whole bunch of > on some messages and mostly blue lines on the side for the rest, so I can't do the proper quoting. How is this supposed to be setup? Each time you reply should I cut and paste the text I don't want in the message or is there something that helps do that? If you can reply to this message and use the quoting method as the other g...

Using XSD stored in assembly resource with includes
I am trying to validate an XML file against an XSD that is stored in the assembly as an embedded resource. I can get it to work as long as the XSD does not include other XSDs. After a fair amount of searching, I have found 3 possible solutions but none have worked for me. They are: 1. Use the Includes property of XmlSchema to add included XSDs, then call Compile XmlSchema mainSchema = XmlSchema.Read( stream1, null ); XmlSchema includeSchema1 = XmlSchema.Read( stream2, null ); XmlSchema includeSchema2 = XmlSchema.Read( stream3, null ); mainSchema.Includes.Add( includeSchema1 ...

Limited User can't use Money
I've installed Money 2004 in hopes to clear this same problem I had with 2001, to no avail. I installed Money 04 with my System Administrator acct. It works fine for me. But when my wife tries with her Limited Account, she cannot get access. How do I get her access to this program and data? I've tried installing it in the All Users dir, but no help. I really don't want to give her Sys Admin priveleges just to use Money. Thanks to anyone that can help. MS Support has no info on this, nor does a Google search turn anything up. If she creates a new Money file, can you b...

How can i use a command button to validate date and time
I have two command buttons, each on a different sheet. i need both of them to put the current date in one cell on its sheet and current time in another on its sheet. i also need these cells to be locked so the user can only edit these cells by clicking the command button. Any answers? ...

Inconsistent autocomplete results
Hi everyone, Does anyone know the logic why the email address that appears in autocomplete in my last composed mail didn't appear in the current one? It is just few minutes ago & I didn't delete it.. -- CL Aloha CL, Not offhand. What version of Outlook are you using? If you start a new e-mail and carefully type the first few characters of the mysterious e-mail address does it appear? -Ben- Ben M. Schorr - MVP Roland Schorr & Tower http://www.rolandschorr.com Microsoft OneNote FAQ: http://www.factplace.com/onenotefaq.htm > Hi everyone, > Does anyone know the logi...

using common control version 6 in VC6
Hi all, I'm trying to use the common control version 6 in my MFC project, I've imported the manifest.xml adn specified 6 as the platform, but I still can't use the version 6 members of the NMLVCUSTOMDRAW structure in my custom drawn controls. for instance, when I tried to use clrFace of the structure, it would say 'clrFace' : is not a member of 'tagNMLVCUSTOMDRAW', and I've traced the problem to the definition of the strucfure itself, typedef struct tagNMLVCUSTOMDRAW { NMCUSTOMDRAW nmcd; COLORREF clrText; COLORREF clrTextBk; #if (_WIN32_IE...

how can I use my e-mail addresses on excel xp with microsoft outlo
I need to use my addresses on Excel xp with Microsoft outlook. I want to use my excel file as a database that I can change and improve everyday. I need to send mails to more than 1 contact, so creating an e.mail link is not enough for me: in fact, if I click on it, I send my mail only to 1 address... Txs See http://www.rondebruin.nl/sendmail.htm -- HTH RP (remove nothere from the email address if mailing direct) "Cat" <Cat@discussions.microsoft.com> wrote in message news:C7A9A930-6846-4071-8714-ED9CC50CD79F@microsoft.com... > > I need to use my addresses on E...

using A5 paper as a booklet in Publisher 2007 i.e 4 A6 sides
In 2003 I made greeting cards on A5 card by using the booklet I cannot make this work in 2007 -- mumbles star Use one of the Avery A4/A5 templates. C9355 works... If you use the A5 folded down to A6 it makes a small publication. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "mumbles" <mumbles@discussions.microsoft.com> wrote in message news:AA578767-48DF-4896-8E8C-FD17ECAEB500@microsoft.com... > In 2003 I made greeting cards on A5 card by using the booklet I cannot make > this work in 2007 > -- > mum...

I'm using the mileage log., How do I total all the mileage?
This is the first time that I'm using the Mileage Log, How do I get the total> Insert a row at bottom of existing log sheet below row 34. In Mileage column J35 enter =SUM(J7:J34) Gord Dibben MS Excel MVP On Mon, 2 Apr 2007 15:14:09 -0700, ricaislandgirl <ricaislandgirl@discussions.microsoft.com> wrote: >This is the first time that I'm using the Mileage Log, How do I get the total> Or look in E4 for the formula =SUM(J7:J34) If you are using the mileage log found here. http://office.microsoft.com/en-us/templates/TC060884581033.aspx?pid=CT101440991033&AxIns...

Using arrow keys to move between cells
Hey guys, Sorry for dumb question, but how do I move between cells w/o using my mouse and rather using arrow keys? Do you have use any of shift, alt, ctrl keys at same time? Thanks. If pressing the arrow keys are not moving you between cells, check and see if scroll lock is on on the key board, here is some info from help Arrow keys Move one cell up, down, left, or right. CTRL+arrow key Move to the edge of the current data region. HOME Move to the beginning of the row. CTRL+HOME Move to the beginning of the worksheet. CTRL+END Move to the last cell on the worksheet, in the bottom-most us...

I want to send autom fax using a distribution list
I live in USA. I create a distribution list with 125 fax numbers in Honduras. All the fax numbers are +011 (504) number, but the fax in Windows XP don't recognize that list. What can I do? You can start by not multiposting. -- Russ Valentine [MVP-Outlook] "JLG" <JLG@discussions.microsoft.com> wrote in message news:ED7BFC16-71A5-4800-A36C-000E85163913@microsoft.com... >I live in USA. I create a distribution list with 125 fax numbers in >Honduras. > All the fax numbers are +011 (504) number, but the fax in Windows XP don't > recognize that list. What can...

Calculations using empty cells
I have a spreadsheet with three columns: Current balance, withdrawals, and deposits. Current balance is determined by subtracting withdrawals and adding deposits to the balance in the line above. Suddenly I have started getting a cell entry #VALUE! in the current balance cell. A typical entry in the cell is for G43 would be: =G42-E43+F43 and the sudden new result is #VALUE. All three columns have cells formatted as numbers. HELP! On Thu, 6 Mar 2008 16:22:02 -0600, Hank in KC wrote: > I have a spreadsheet with three columns: Current balance, withdrawals, and > deposit...

Using Function to call Woorbook Sheet
I would like to use WEEKDAY() to call a particular Sheet in a Wookbook. The Sheets are called Sunday - Saturday and I have an IF statement that needs to check for particular information on that sheet depending on what day it is. Have it currently functioning but it is taking up way to much space. I am unable to update my code with other functions that I need it to calculate until I can figure this part out. Code =IF(OR(AND((TEXT(TODAY(),"dddd")="Monday"),(HLOOKUP(MOD(NOW(),1),Monday!$B$1:$BA$40,2))>0),AND((TEXT(TODAY(),"dddd")="Tuesday"),(HLOOKU...

Con't use capplication object
Hi everyone. I have developed an application.This application has a excel file list control.When i double click in the excel file list control,the excel application will run and open the corresponding excel file, and another window which has another list control is opened. When I selected a cell in the excel window and then double click in the list control,the selected list control item value will be put into the selected cell.I use the following code : CRange cr=m_ExcelApplication.get_ActiveCell(); cr.put_Value2(COleVariant(value)); m_ExcelApplication is the excel application instance...

Sorting a column by using formula #3
I am trying to use sort function just to delete blank cells in between Sort order doesn't matter actually. Data is coming by the use of simple cell reference of "another sheet -- Prais ----------------------------------------------------------------------- Praise's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1558 View this thread: http://www.excelforum.com/showthread.php?threadid=27144 Hi you may use the following addin to filter out blank cells: http://www.rondebruin.nl/easyfilter.htm -- Regards Frank Kabel Frankfurt, Germany Praise wrote: &g...

use a time value in a bar graph
i have values in a series like c5 9:01 d5 21:01 e5 =(d5-c5) I would like to use e5 in a bar graph it gives values which would make sense only to excel If the axis is formatted as time, it shouldn't be a problem. If it doesn't choose the scales to give clean divisions, you can choose an appropriate unit on the axis, such as 03:00. -- David Biddulph "pdfrone" <pdfrone@discussions.microsoft.com> wrote in message news:A8C14E9F-104A-464A-81B6-39DA2C679E71@microsoft.com... >i have values in a series > > like > > c5 9:01 > d5 21:01 > e5 =(d5-c5...

Using Mail Merge with Criteria in a Access query
Hi; In access 2000-2003 it was possible to use the Mail Merge function in Word to access a query that has criteria required for input. You can select the Word document and then the database is opened and the criteria box is opened for input (Enter Parameter Value). This then produces the data for the letters. It used a DDE connection between Word and Access. In Office 2007 the mail merge help document says to go to the Access database and select the query and lists how to link to an existing or new letter. The DDE connection is listed but does not attach to the query? It alway...

XSLTransform & using XML Resolver to get images
I am transforming xml using XSLTransform which works fine. The xsl file is stored on eg. C:\temp1 and the xml is loaded from c:\temp2. All image files referenced in the stylesheet file are stored on the same path as the stylesheet and are not used in the transform. How do I use an XMLResolver to transform using the images without having to move the files, is it possible??? Images are referenced as eg. <img src="image1.gif"/> in the stylesheet. Thanks * Pat Buxton wrote in microsoft.public.dotnet.xml: >I am transforming xml using XSLTransform which works fine. The xsl >...

Using Macros to test GP changes
I was discussing ways to test GP with my supervisor recently, and it was suggested that it may be possible to record a macro inside of GP to test common tasks in GP, and then use those macros whenever a server change, customization change or GP version change is made to verify that everything is working. Has anyone done this before? If so, what were the pitfalls you came across? If this is a possibility, we would probably be building a set of testing macros to supliment our current testing model. Thanks! Hi Jason. This is very possible though it may be cumbersome sometimes especial...

Moving SQL Databases to another drive results in error
I am trying to move the CRM SQL Databases to another drive that has more space and I get a replication error. Is there any documentation to help me move these successfully?? McKenzie This is a multi-part message in MIME format. ------=_NextPart_000_005D_01C5A44F.29F39C90 Content-Type: text/plain; charset="Utf-8" Content-Transfer-Encoding: quoted-printable you can do this but you need to disable replication and re-enable = afterwards Use SQL Enterprise manager, select the database you want to move and = then=20 choose the option (after right click) to detach the database,then ...

Using the same cell
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel If i have values in a column and want to copy those values over and over again how do I do it? Say that cell e6 has a value of cell e2 (it actually has "e2" in the cell) and the value in cell e2 is $100. I wish to copy the value of e6 to columns f thru z, but want the value copied to be e6 (otherwise they would change to f6, g6, etc). That way if I change the value of e2, every cell with e6 in it will change too. It seems to me that I used to enclose the value in quotes or parenthesis and that would copy over ...

Using PSTs between Outlook 2003 and Outlook 2007
Hi Is it possible to use the same PST file between an installation of 2003 and an installation of 2007 (bearing in mind that the file is a PST(97-2002) format file? Any issues to be aware of? Comments on any issues, in particular the ones described on http://support.microsoft.com/kb/913114/en-us and http://support.microsoft.com/kb/927537/en-us would be appreciated. If you don't care about those two issues (in respect to their privacy concerns), then yes. However, you can't install 2003 and 2007 on the same Windows, so your scenario is only possible if you are dual-booting. ...

can't use print area function Excel 2002
Hi, I have a table document that has [group] beside the name. I did not put that there when I saved it. It will not let me set or clear the print area, which is greyed out. And in page break view, I set the document to 5 pages and I do the same in "page setup," and it consistently shows that there are 72 pages in the document. "Troubleshooting printing" has nothing about this problem. Help! thanks, Bonnie Right click on one of the tabs for the file and select Ungroup "Bonnie" <bshepard@hsph.harvard.edu> wrote in message news:027301c381ea$b...

two mail accounts one uses spf problem
I have two email accounts I check with Outlook. One is a DSL account at home and the other a work email account. My work email changed mail servers to one that uses the SPF extensions. This included changing our email account settings accordingly. When I changed the work email settings on the home machine the home account stopped working (Receiving error, pop server timing out). At work (connected to the work Ethernet connection not my DSL connection) both accounts work fine. I tried deleting the work account on the home machine in Outlook but the home email still does not work. I checked eve...