Help With Date Calculation Query

I have not used access in awhile.  I have dumped a lot of data into an access 
database and I need to compare two date fields that would essentially need to 
measure the number of days between the invoice date and the payment date.  I 
created the following expression, but cannot determind what I am doing wrong.

[AP-Payment History Report]![CheckDate] "Date ()"  -  [AP-Payment History 
Report]![InvoiceDate] "Date ()" < "30"

I guess I want the query results to show the invoices that were paid in less 
then thirty days.  Am I saying that right.

I have tried to use the wizard, but was not successful.  Thank you for your 
help, Ed Podowski
0
Utf
11/26/2007 10:03:01 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
786 Views

Similar Articles

[PageSpeed] 13

Add a computed field to your query

DateDiff("d", [InvoiceDate], [CheckDate])

and put <30 as the criteria under that computed field.


-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Ed Podowski" <EdPodowski@discussions.microsoft.com> wrote in message 
news:3478A443-1C2A-4EC2-8CFF-A02C6925DFA1@microsoft.com...
>I have not used access in awhile.  I have dumped a lot of data into an 
>access
> database and I need to compare two date fields that would essentially need 
> to
> measure the number of days between the invoice date and the payment date. 
> I
> created the following expression, but cannot determind what I am doing 
> wrong.
>
> [AP-Payment History Report]![CheckDate] "Date ()"  -  [AP-Payment History
> Report]![InvoiceDate] "Date ()" < "30"
>
> I guess I want the query results to show the invoices that were paid in 
> less
> then thirty days.  Am I saying that right.
>
> I have tried to use the wizard, but was not successful.  Thank you for 
> your
> help, Ed Podowski 


0
Douglas
11/26/2007 10:26:33 PM
You can use the DateDiff function to return the different between two dates

As a new field in the query write:

DateDiff("d",[AP-Payment History Report]![CheckDate], [AP-Payment History 
Report]![InvoiceDate])

And in the criteria section of that field write:
<30


-- 
Good Luck
BS"D


"Ed Podowski" wrote:

> I have not used access in awhile.  I have dumped a lot of data into an access 
> database and I need to compare two date fields that would essentially need to 
> measure the number of days between the invoice date and the payment date.  I 
> created the following expression, but cannot determind what I am doing wrong.
> 
> [AP-Payment History Report]![CheckDate] "Date ()"  -  [AP-Payment History 
> Report]![InvoiceDate] "Date ()" < "30"
> 
> I guess I want the query results to show the invoices that were paid in less 
> then thirty days.  Am I saying that right.
> 
> I have tried to use the wizard, but was not successful.  Thank you for your 
> help, Ed Podowski
0
Utf
11/26/2007 10:27:01 PM
Hello Ofer and Douglas,
	
Thank you both for your reply.

It took me awhile to figure out how to add a computed field.  I found a help 
topic named “Create a calculated field in a query (MDB)” which spelled out 
that it goes in the query [as you both have stated] and not in the table as a 
field.

In the total box I selected expression.

I tried both variations of your expression and the query actually runs, but 
before it runs the results it ask me two questions.

Enter The Parameter Value AP-Payment History Report!CheckDate  - I click ok 

Enter The Parameter Value AP-Payment History Report!InvoiceDate  - I click ok

I am not sure what to put here.  I want all the invoices in the table which 
were paid in less than thirty days from the invoice date.

If I put in nothing the query is empty.  When I put in 1 it returns 63,000+ 
records which is all the records in the table for that query and when I 
manually calculate the date difference some are greater than 30 days and some 
are less than 30 days.  So I know I still have something wrong.

I don’t think this is a conflict, but in the check date field in the query 
under criteria I have  - Between #1/1/2005# And #12/31/2005# so I will just 
get the checks which have been dated in 2005.  There may be a better way to 
write this, but the results are accurate.  I have checks in the table written 
before 01/01/2005 and after 12/31/2005 which do not show in my query results.

On another note…can I assume the “d” in your expression is to return the 
results in days?

Douglas, I visited your web site and saw where you have a “y” in your 
DateDiff explanation.  So I drew the conclusion that was to return results in 
years.

Thank you both again for your help, Ed Podowski

"Douglas J. Steele" wrote:

> Add a computed field to your query
> 
> DateDiff("d", [InvoiceDate], [CheckDate])
> 
> and put <30 as the criteria under that computed field.
> 
> 
> -- 
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
> 
> 
> "Ed Podowski" <EdPodowski@discussions.microsoft.com> wrote in message 
> news:3478A443-1C2A-4EC2-8CFF-A02C6925DFA1@microsoft.com...
> >I have not used access in awhile.  I have dumped a lot of data into an 
> >access
> > database and I need to compare two date fields that would essentially need 
> > to
> > measure the number of days between the invoice date and the payment date. 
> > I
> > created the following expression, but cannot determind what I am doing 
> > wrong.
> >
> > [AP-Payment History Report]![CheckDate] "Date ()"  -  [AP-Payment History
> > Report]![InvoiceDate] "Date ()" < "30"
> >
> > I guess I want the query results to show the invoices that were paid in 
> > less
> > then thirty days.  Am I saying that right.
> >
> > I have tried to use the wizard, but was not successful.  Thank you for 
> > your
> > help, Ed Podowski 
> 
> 
> 
0
Utf
11/27/2007 2:52:00 AM
Hello,

I found the issue with the query returning zero results and the request for 
parameters.  I had simplified the field names when writing my original post.  
I realized this just a few minutes ago [after a few hours of sleep].  The 
query worked perfectly when I changed the field names in your expression to 
the actual field names.

Thank you very much for you help.  It was a learning experience.

Ed Podowski

"Douglas J. Steele" wrote:

> Add a computed field to your query
> 
> DateDiff("d", [InvoiceDate], [CheckDate])
> 
> and put <30 as the criteria under that computed field.
> 
> 
> -- 
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
> 
> 
> "Ed Podowski" <EdPodowski@discussions.microsoft.com> wrote in message 
> news:3478A443-1C2A-4EC2-8CFF-A02C6925DFA1@microsoft.com...
> >I have not used access in awhile.  I have dumped a lot of data into an 
> >access
> > database and I need to compare two date fields that would essentially need 
> > to
> > measure the number of days between the invoice date and the payment date. 
> > I
> > created the following expression, but cannot determind what I am doing 
> > wrong.
> >
> > [AP-Payment History Report]![CheckDate] "Date ()"  -  [AP-Payment History
> > Report]![InvoiceDate] "Date ()" < "30"
> >
> > I guess I want the query results to show the invoices that were paid in 
> > less
> > then thirty days.  Am I saying that right.
> >
> > I have tried to use the wizard, but was not successful.  Thank you for 
> > your
> > help, Ed Podowski 
> 
> 
> 
0
Utf
11/27/2007 1:10:00 PM
Reply:

Similar Artilces:

query criteria
For a parameter for a report, I have a multiselect listbox to choose districts. Using a global variable, I form a string with all the codes of the districts that were chosen. I have checked the string and the lists of district codes are correct. In the district code field of the query for the report, I put in the criteria " In (getstrDistricts())". When I choose one district it works. When I choose more than one district, the report is blank. What am I doing wrong? Leah -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/2007...

Date display in Excel
Format column of cells as Date, display as mm/dd/yy. Date entered into cell, shows up correctly in the text entry field at the top of the screen, but the data on the worksheet displays as "33747", or similar number. Only happening on one workbook. Try tools|options|View tab|uncheck Formulas. Clark wrote: > > Format column of cells as Date, display as mm/dd/yy. Date > entered into cell, shows up correctly in the text entry > field at the top of the screen, but the data on the > worksheet displays as "33747", or similar number. Only > happening on one...

Excel and some disappeared sheets
Anybody has the same or similar experiences : - I worked with excel file / closed as usually - new day opened and unfortunatelly some very important sheets disappeared / why ? Don=B4t know - I had about 7-9 sheets just now only 3 ones are there but not so important as another ones - the file has original size / I quickly looked into file by normal text editor only for reading and all relevant data are there, however if I open file by Excel, NO original sheets or data are there. - this file is VERY IMPORTANT for me - why happened ? Don=B4t know since I ask anybody for help Thanks in advan...

office 2000 [excel] cannot open previously OK file. Help
When trying to open a Microsoft Excel Work Sheet that I had accessed modified and saved to only yesterday [Office 2000] a window pops up with: "name.xls cannot be accessed. The file may be resd-only, or you may be trying toaccess a read only location. Or, the server the document is stored on may not be responding." Older files stored on the same mediums ;HD, Flash memory or 3.5in. diskette are accessed with no problem. This is on a network drive? ...

Queries and Charts
Does anyone know why the expressions in queries work fine for reports but not charts? ...

Pass parameter from FORM to QUERY
I have an append query that I trying to call from a cmd btn...but it prompts me for the parameter(ie QuoteID)... How do I tye it into my call: Dim stQueName As String stQueName = "Quote Query" DoCmd.OpenQuery stQueName, acNormal, acEdit On Apr 13, 12:56 pm, jlt...@hotmail.com wrote: > I have an append query that I trying to call from a cmd btn...but it > prompts me for the parameter(ie QuoteID)... > How do I tye it into my call: > > Dim stQueName As String > stQueName = "Quote Query" > DoCmd.OpenQuery stQueName, acNormal, acEd...

C# Adding Days to a Date
Hello, I have 2 objects: objContract.activeon and objContract.expireson. I am trying to add 364 days to objContract.activeon and assign it to the value of objContractexpireson. ---------------------------------------------------------- // Contract Start Date DateTime dt = DateTime.Now; objContract.activeon = new CrmDateTime(); int iFound = 0; string sTemp = ""; if (objAccount.paymenttermscode.Value == 1) // due on receipt - use Todays Date { objContract.activeon = objInvoice.CFDinvoicedate; } else // ...

Separating Date and Time in a cell
I have a column of cells in the format "11/01/02 06:21". I would like to separate the text into 2 cells - one with the date and the other with the time. My attempts with LEFT and RIGHT have been unsuccesful. Thanks for your help Sameer --- Message posted from http://www.ExcelForum.com/ For the date use =INT(A1) replace A1 with the first cell of your range for time =MOD(A1,1) you probably have to reformat the first to mm/dd/yy (or whatever the setting is) and hh:mm Note that you can do this by just using format but if you want to compare to other cells with just pure d...

Changing query execution sequence
Hi all, I got a spreadsheet which would execute a bunch of queries. It's noted that the queries are executing in the sequence of when it was added to the spreadsheet. Does anyone out there know of a way to switch the order without deleting and recreating them? Thanks! Wing ...

OL2002 Problem : HELP, SOS, MAYDAY
Hi, I have an IMAP account and OL2002 as client. I have checked for my account ( tools, send/receive settings, define send/receive groups, <my_account>, edit) download item description only. It works only the first time I start OL2002, but it does not work anymore for subsequent automatic/manual send/receive operations. Please could someone really help me ? Thank you in advance, Patrick ...

Forumla to calculate a percentage
I have a column of figures that I need to calculate that if the figure in column A is Transport costs to us is �100 and column B is the price sold to the customer is �25.00, i need the third column to work out < 72% Theoretical Transport Recovery in other words if the value in column B is <72% mark it red Like wise for fuel is the value in column B is <132% mark it red Try as I might I cannot get it to work it out any ideas Julie, This formula gives the correct percentage... =(B1-A1)/A1 Format the formula cell with this custom number format... 0%;[Red]-0% If you don'...

Help With Margin Formula
Hello, I need help with a margin forumla (calculated from retail). Say I have a cost of $10.00, and I need the formula to calculate a 40% margin from retail. So the retail should end up at $16.67. Not sure how to get from $10.00 to $16.66, I just know the cost and the margin I need to make. Thanks JR =A1/(100%-40%) -- Kind regards, Niek Otten "JR" <gaspower@aol.com> wrote in message news:eGszf.424$2O6.53@newssvr12.news.prodigy.com... > Hello, > I need help with a margin forumla (calculated from retail). Say I have a > cost of $10.00, and I need the formul...

GETPIVOTDATA data_field help
When I enter a reference to a cell, eg A2, as the value for the data_field item in the GETPIVOTDATA I get a #REF error. However, when I instead type the value of A2, eg 2003, manually into the GETPIVOTDATA formula, it works. Can anyone please help--I want this formula to vary based on what is in A2. Cheers This function is so variable in action, and difficult to handle, that have given it up in favour of VLOOKUP. In fact, having no problem i use with pivot tables -- Message posted from http://www.ExcelForum.com Try copying the headings from the pivot table, and pasting them into the ce...

Exchange server crashed, please help....! Need to restore two priv.edb and pub.edb files into one....!
Hi Guys, I was wondering if I could get some help with the following problem we are having on our company. Here is the scenario; Our Windows NT 4.0 SP4a server running Exchange 5.5 SP4 crashed (Server 1) due to the exchange database reaching its 16 Gig's max limit. I went ahead and moved some mailboxes' e-mails to a few .pst files in order to make some space. This worked ok. Then, I decided to build another exchange server (Server 2) to moved some mailboxes and alleviate the load. Once the server was ready and configured as part of the current exchange site, I went ahead and move...

date function #3
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C73005.0FA093A0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have a cell A1 with date 5/2/07. If in cell A2 I do month(A1), I get = 5. Is there a way to return May instead? either through a command or = formatting? I can reference cell A2 with the 5 with a if(cell =3D 5, = "May", ) in cell A3, but the date in cell A1 varies from Jan to Dec and = the 12 embedded if's in cell A3 are too long (that is the error I get = when trying to do so), plus i...

Help!!!!
I am running Exchange 5.5 on NT 4.0 sp6a. Norton Anti- virus deleted my edb.log file. I have done an on-line restore with Arcserve 2000 and now my directory service won't start. getting 1166 internal processing error. I have been to support and could find anything that helped. Thanks in advance You may want to run eseutil /mh <drive and path to your dir.edb and include the dir.edb > c:\edb.dmp. Find this file and see if your dir is also inconsistent. If so you will need to restore your dir. -- Nan Bennett (MSFT) Microsoft Exchange Support Please do not send e-mail...

Calculating on alphabetic cell content
Hi, A selection of 4 different letters in a column representing different values to be used in a formula shall be run through. The calculated result of each cell in the column shall be placed in the cell next to the read one that holds the letter. Thanks in advance. Hi i think you're after the COUNTIF function with your column of letters in A1:A100 and the letter you're interested in in C1 then in D1 =COUNTIF(A1:A100,C1) this will count the number of times the value in C1 occurs in your range. If this isn't what you're after, could you type out a few examples of your ...

Removing text from cells leaving numbers (help with function)
I need a function that will remove all text from a cell and just leav numbers. Formatting cells to number does not work. For example if I have: (Sired] Tennessee 37013 (herein I just want 37013 left. Anybody know a function to resolve this -- Message posted from http://www.ExcelForum.com The following will strip the text from the active cell and place the number in the adjcent cell one column to the left. If there are subsequent numbers in the original string you will get erroneous results. Put the cursor on the cell to be processed and run the macro. ********************************...

multiple iterations of same query
Running Access 2003 on Vista. I have a query that chooses 15 records at random. This works fine. What I want to do is run the same query for approx 110 different users, each with their own randomly selected 15 records. Obviously, I want to avoid manually running the same query that many times. How can I get around this? Post your query SQL. -- Build a little, test a little. "Nathan" wrote: > Running Access 2003 on Vista. > > I have a query that chooses 15 records at random. This works fine. > > What I want to do is run the same query fo...

how do I change date format in the header in Excel XP ?
I need to chage my date format, in the header to Day; Month DD, YYYY ie. Saturday; May 23, 2005. Woudl you please help me out ? Thank you. Regards, Hesam Shakourian Check this out. http://support.microsoft.com/?kbid=213742 but change the format to "dddd, MMMM dd, yyyy" "Hesam" <Hesam@discussions.microsoft.com> wrote in message news:93495F75-4196-4208-9C0D-E800BCAE3A89@microsoft.com... > I need to chage my date format, in the header to Day; Month DD, YYYY > ie. Saturday; May 23, 2005. > > Woudl you please help me out ? > > Thank you. > &g...

I really need help with a formview problem
I have a formview that I place a toolbar onto. In my OnInitialUpdate(), after I create the toolbar, I say GetParentFrame()->RecalcLayout(); ResizeParentToFit(FALSE); ResizeParentToFit(TRUE); The formview is still not sized properly. It looks like the problem is the toolbar on the top of the formview. It is not being taken into account in sizing the formview. The difference at the bottom of the form that is not displayed is the size of the toolbar. Any suggestions? Should work if your toolbar was created (and docked) back in your CMainFrame::OnCreate() method. ----- William Gower ...

Bank One Download is not working
We are trying to download transaction data directly from Bank One and the file is not being recognized by Money - i.e. the Bank One website states that the download is complete, but Money does not recognize it. Note that a file is being saved on our hard drive but we can't figure out how to read it. It is entitled mnyimprt.exe-(numerous numbers and letters follow). In microsoft.public.money, Miki wrote: >We are trying to download transaction data directly from >Bank One and the file is not being recognized by Money - >i.e. the Bank One website states that the downloa...

Calculating the 95% confidence inteval of a slope
As a low key user, I can calcualte the slope of a line from the x and y values, but I don't know how to determine how good the estimate is. Can someone help? -- Seeker 1) Using Analysis ToolPac Regression tool. See http://www.jeremymiles.co.uk/regressionbook/extras/appendix2/excel/ http://www.stat.wmich.edu/s216/book/node128.html 2) With LINEST and/or STEYX. See http://office.microsoft.com/en-us/excel/HA011119631033.aspx quote:The STEYX function calculates the standard error of a regression, a measure of the amount of error accrued in predicting a y for each given x. Th...

Option trades with past expiry dates not showing up in Portfolio M
Hello: Money 2006 Portfolio Manager does not show closed option trades that have expiry dates in the past, even when the "show closed positions" is checked. The transactions are still there in the investment accounts, just not visible in portfolio manager. I just called microsoft support and have notified them of the issue and hope that this issue is fixed as an update. If not, it pretty much makes the portfolio manager (and Microsoft Money 2006) useless for option trading investors. "MumbaiBabu" <MumbaiBabu@discussions.microsoft.com> wrote in message news:1...

help #2
Hi, I need help with the following: I need to plot a 2-D column graph comparing the profit margin (y-axis) for the 3 items (x-axis) for Malaysia and Singapore, using the data below; Malaysia Item Mfr's Agent's Retailer's Selling GST Profit Total Cost Charge Markup Price Margin Lipstick 9.85 15.76 12.81 38.42 1.54 27.03 105.40 Toner 11.90 19.04 15.47 46.41 1.86 32.65 127.33 Cleanser 15.90 25.44 20.67 62.01 2.48 43.63 170.13 Total 37.65 60.24 48.95 146.84 5.87 103.31 402.86 Singapore Item Mfr's Agent's Retailer's Selling GST...