Date calculation on 2 fields

I have a query that calculates captures a date filed 20 days after the date 
which is entered. It then gathers all the dates after the 20 days has been 
calculated. I need to code another date field that doesnt grab info 45 days 
past the date.

Example: works now
user - puts in date 1/28/2010
query - subracts 20 days from that date = 1/08/10
query - then gathers all the dates after 1/8/10.

Need - To look at another date field and make sure no data is gathered 45 
days from the date in field.

0
Utf
2/2/2010 3:40:04 PM
access.queries 6343 articles. 1 followers. Follow

5 Replies
963 Views

Similar Articles

[PageSpeed] 29

Are you talking about two separate queries, or a single query with two 
criteria?

(if the latter, your selection criterion might be something like - untested:

    Between 20 And 45

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

-- 
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Business analyst williams" <eku.williams@53.com> wrote in message 
news:64E69CFE-2AD3-403E-B453-5D83A2EFA408@microsoft.com...
>I have a query that calculates captures a date filed 20 days after the date
> which is entered. It then gathers all the dates after the 20 days has been
> calculated. I need to code another date field that doesnt grab info 45 
> days
> past the date.
>
> Example: works now
> user - puts in date 1/28/2010
> query - subracts 20 days from that date = 1/08/10
> query - then gathers all the dates after 1/8/10.
>
> Need - To look at another date field and make sure no data is gathered 45
> days from the date in field.
> 


0
Jeff
2/2/2010 4:01:11 PM
Note that the selection criteria I offered would be applied to the 
difference between dates, not to the date itself.

Regards

Jeff Boyce
Microsoft Access MVP

-- 
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Business analyst williams" <eku.williams@53.com> wrote in message 
news:64E69CFE-2AD3-403E-B453-5D83A2EFA408@microsoft.com...
>I have a query that calculates captures a date filed 20 days after the date
> which is entered. It then gathers all the dates after the 20 days has been
> calculated. I need to code another date field that doesnt grab info 45 
> days
> past the date.
>
> Example: works now
> user - puts in date 1/28/2010
> query - subracts 20 days from that date = 1/08/10
> query - then gathers all the dates after 1/8/10.
>
> Need - To look at another date field and make sure no data is gathered 45
> days from the date in field.
> 


0
Jeff
2/2/2010 4:01:53 PM
Posting the SQL statement of the current query and the name of the other date 
field would really help us to help you.
I'll take a guess and suggest this in the criteria for the other date field.

<Date() + 45
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Business analyst williams" wrote:

> I have a query that calculates captures a date filed 20 days after the date 
> which is entered. It then gathers all the dates after the 20 days has been 
> calculated. I need to code another date field that doesnt grab info 45 days 
> past the date.
> 
> Example: works now
> user - puts in date 1/28/2010
> query - subracts 20 days from that date = 1/08/10
> query - then gathers all the dates after 1/8/10.
> 
> Need - To look at another date field and make sure no data is gathered 45 
> days from the date in field.
> 
0
Utf
2/2/2010 4:09:01 PM
Here is the current query:

SELECT [Customer Communication].[Provisional Credit Date], [Customer 
Communication].[Reference ID], [Customer Communication].[Card Number], 
[Customer Communication].[DDA/Sav Acct Number], [Customer 
Communication].[Customer contact date]
FROM [Customer Communication]
WHERE ((([Customer Communication].[Provisional Credit 
Date])<=DateAdd("d",-20,[Date])));

The date field that I do not want exceed 45 days is the Customer contact date

"Jerry Whittle" wrote:

> Posting the SQL statement of the current query and the name of the other date 
> field would really help us to help you.
> I'll take a guess and suggest this in the criteria for the other date field.
> 
> <Date() + 45
> -- 
> Jerry Whittle, Microsoft Access MVP 
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> 
> 
> "Business analyst williams" wrote:
> 
> > I have a query that calculates captures a date filed 20 days after the date 
> > which is entered. It then gathers all the dates after the 20 days has been 
> > calculated. I need to code another date field that doesnt grab info 45 days 
> > past the date.
> > 
> > Example: works now
> > user - puts in date 1/28/2010
> > query - subracts 20 days from that date = 1/08/10
> > query - then gathers all the dates after 1/8/10.
> > 
> > Need - To look at another date field and make sure no data is gathered 45 
> > days from the date in field.
> > 
0
Utf
2/2/2010 4:23:01 PM
SELECT CC.[Provisional Credit Date],
 CC.[Reference ID],
 CC.[Card Number], 
 CC.[DDA/Sav Acct Number],
 CC.[Customer contact date]
FROM [Customer Communication] as CC
WHERE CC.[Provisional Credit Date] <= DateAdd("d",-20,Date())
AND CC.[Customer contact date] < Date() + 45;

Noticed that I clean up things by using an alias for the table name.

One thing confused me is the square brackets [ ]  around the Date in your 
SQL statement. If you have a field named Date, it would be OK, but if you 
want to use the Date function, it should look like above.

DateAdd is very handy when doing thing like adding months, weeks, years, 
etc. However you can just put a number like I did above if adding up days. 
You may what to change to one or the other for consistancy.

Be careful to test this especially if your date fields would happen to hold 
date and times. You could miss part of a day especially for future dates.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Business analyst williams" wrote:

> Here is the current query:
> 
> SELECT [Customer Communication].[Provisional Credit Date], [Customer 
> Communication].[Reference ID], [Customer Communication].[Card Number], 
> [Customer Communication].[DDA/Sav Acct Number], [Customer 
> Communication].[Customer contact date]
> FROM [Customer Communication]
> WHERE ((([Customer Communication].[Provisional Credit 
> Date])<=DateAdd("d",-20,[Date])));
> 
> The date field that I do not want exceed 45 days is the Customer contact date
> 
> "Jerry Whittle" wrote:
> 
> > Posting the SQL statement of the current query and the name of the other date 
> > field would really help us to help you.
> > I'll take a guess and suggest this in the criteria for the other date field.
> > 
> > <Date() + 45
> > -- 
> > Jerry Whittle, Microsoft Access MVP 
> > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> > 
> > 
> > "Business analyst williams" wrote:
> > 
> > > I have a query that calculates captures a date filed 20 days after the date 
> > > which is entered. It then gathers all the dates after the 20 days has been 
> > > calculated. I need to code another date field that doesnt grab info 45 days 
> > > past the date.
> > > 
> > > Example: works now
> > > user - puts in date 1/28/2010
> > > query - subracts 20 days from that date = 1/08/10
> > > query - then gathers all the dates after 1/8/10.
> > > 
> > > Need - To look at another date field and make sure no data is gathered 45 
> > > days from the date in field.
> > > 
0
Utf
2/2/2010 4:42:01 PM
Reply:

Similar Artilces:

Display/Hide a Form Control/Field Based On Another Field Value?
Hi all, I have to Combo boxes on a form. The first one I would always like users to see. The second, however, I would like hidden if the first box has certain values selected. So, if ComboBox1 has values of 1 or 2, I would like ComboBox2 to show as normal. If ComboBox1 has a value of 3, I would like ComboBox2 hidden. Is there a relatively simple way of doing this, if at all? Thanks for your help as always! --Eric You have to take care of 2 things: (1) When you move from record to record and the combo box contents changes (2) When the user changes the contents of the combo box For (1), inse...

Help me #2
The outlook clients failed to respond and the exchange server was restarted. A day later there was a blue screen on the server with a message stating that one of the drives might be bad. A hard reset helped bring the machine up and running. The event viewer had a critical log which said that the file system on Drive G: is corrupted and unusable. The machine currently does not show any G: drive. There is a backup schedule in place Mon, Tue - Weekly Differential Backup Wed - Offsite back up ( A full back up with the tapes being taken off site) Thu - Weekly Differential Backup Sun - We...

I need to create a date/time search query but i cant figure it out!
Hi, I have a time-slot grid that I want to be able to click on and have access find out if there are any records based around that slot.. so basically, I have a varDate and a varTime and I need to use a query to query against my databases StartTime, EndTime, and AppDate variables.. in psuedocode i need to find the result of WHERE varDate = AppDate AND (StartTime <= varTime AND EndTime >= varTime) Ive never done a search using dates before. I was wondering if the formatting of the dates is important? I did do a basic query that seem to fail when just searching for dates.. the...

Update records from one spreadsheet to another #2
Thanks for your response, but I do not understand what a "VLOOKUP" is. Is there anyway that someone can answer my question without referrin me to another web site? I am not versed in the use of Excel, which i why I am here. It seems to me that what I am asking for should be ver simple for someone who is trained in Excel. Thanks agai -- homevestor ----------------------------------------------------------------------- homevestors's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1530 View this thread: http://www.excelforum.com/showthread.php?threadid=...

Compare 2 workbook(s) data and then go from there..
Sorry, I was unable to figure out a way on how to word my subject but here's what I'm trying to accomplish... I have a single worksheet with 4 workbooks inside of it. They are as follows. 1> MGR Sched 2> Emp Sched 3> MGR Avail. 4> Emp Avail. What I want to do is add the Managers Availability to workbook 3 and if there's an "N/A" or some other text/code in the cell on workbook 1 for the same day or cell then it won't let me enter anything into it. Same goes for workbook 2 and 4. I just don't want to bounce back and forth between workbooks. I&...

Delete Server from Site #2
I'm removing my Exchange 5.5 server so I can run my Exchange 2003 server in native mode. I complete the "remove all" steps from the 5.5 CD according to KB189286. In article 272314 it says to use Exchange 5.5 Manager (on my 2003 server) to delete the 5.5 server from the site. However, when I try to delete it I get the error: "You cannot delete the Microsoft Exchange Server "MAIL" because it is still active. Shut the server down, and try again." The server has been down for a day and I'm still getting that error. I even deleted the DNS references to t...

Enter Network Password keeps coming up #2
I just updated my MS Office 2000 to MS Office 2003. My outlook use to work fine but now it ask what my password is all the time. Every send, receive, etc... It doesn't keep the setting when I check the "Save this password in your password list". I tried using the Microsoft Knowledge Base Article - 290684 on Save password settings not retained in Outlook but it states that I need to use regedit32, which I don't have in my Windows XP (with service pack 2). Is there anything I could do? I have uninstalled and reinstalled MS Office and Outlook, but it doesn't se...

Help calculating totals
Hello! I am using Excel 2002. I have a spreadsheet that is about 300 rows long. Each row refers to a specific serial# of a copy machine. Column A defines what brand the copier is, and say Column B defines how many copies it made. Column B would be the "January" column, so then I would C,D,E. etc for the 12 months going across. I am looking for an easy way, so at the bottom of the spreadsheet, I can have a total for each of the months, for the # of copies that each of the different brands made. I am constantly adding and removing different serial#'s, so I'm not sure what the...

test #2
test ...

How can I do this? #2
Hi I am not a constant user of Excel. I had a need so I gave it a shot I just opened the program and started to mess around, then I read some then I started to ask some users some questions. Soon I realized ho powerful and extensive the program really is, and not really that har to learn with the right "tools". The point: I figured out how to d what I needed and it works really well for me. All but about .01%. I there some way I can send you folks the file and then ask you how t add this last detail to the file? I can try to explain my question now but not knowing the proper terms i...

2 taxes on invoice
How do you show both federal and state taxes on invoices. Please help. Thanks. ...

How do i copy columns of data in notepad into microsoft excel? #2
I have a several large columns of data stored in notepad. The columns are all next to each other. I need to put column 1 in column A in excel and column 2 into column B in excel etc. Copy and paste does not work. I can’t just highlight one column I end up highlighting all 6 columns! If a just copy a paste all the data, the entire data just goes into column A of excel, and I can not perform any excel functions on the data. HELP! This is extremely annoying, I am beginning to think the only way is to copy each bit of data by hand into excel, but there over 1000 items!!! HELP!!!!!!!!!!!!!!...

Cannot Print Z Reports #2
Hi All. I am new to RMS 2.0 and cannot print a Z report. The register appears to print but does not. I did a Blind Close and tried to do a Z from the manager and it said that either a printer was not defined or a Z report template had not been defned. We have been printing all day so I assume it is the template. I cannot find any info about the Z Report template in the system help or manual. -- Dennis at Pacific Solutions Dennis - in SO Manager go to database - registers - register list - click on properties for the register in question - in receipt printer 1 tab, not what receipt for...

Between dates
I have dates in 1 column and total amount (income) in another column. I need the total amounts say from 1 Jan 10 to 8 Jan 10. Logically: I want the sum total of income between 2 dates. I've been trying to figure this out for days. Assuming your dates are in column A and the amounts you want to sum are in column B, then put the start date of your range in, say, F1 and the end date in G1 and use this: =3DSUMPRODUCT((A1:A100>=3DF1)*(A1:A100<=3DG1),B1:B100) This treats the dates in F1 and G1 as inclusive. Hope this helps. Pete On Jan 13, 11:37=A0am, Vicki Leibowitz ...

How to share same email a/c on 2 pc
I have linked 2 pc via Windows Small Office network. How to let these 2 pc share the same email a/c in Microsoft Outlook. Plse provide solution in detailed steps as I am a novice. Operating syste=xp outlook version 2003 Thanks "Christine" <centrol@singnet.com.sg> wrote in message news:hop8fj$7la$1@reader01.singnet.com.sg... > I have linked 2 pc via Windows Small Office network. > > How to let these 2 pc share the same email a/c in Microsoft Outlook. > > Plse provide solution in detailed steps as I am a novice. > > Operating sys...

reporting tools #2
What reporting tools do you all like? I have looked at a couple but would like to know what you all recommend? I'd recommend Quest MessageStats. http://www.quest.com/messagestats/ Nue "Brandon" <whocares@you.com> wrote in message news:%23Y%231IlbeGHA.1436@TK2MSFTNGP05.phx.gbl... > What reporting tools do you all like? I have looked at a couple but would > like to know what you all recommend? > > ...

File recovery #2
Is there any shareware utility for recovering corrupt Excel files? Hi, None that I know of. Just in case: Options to try and open a corrupt file - Set calculation to manual - open the file, but disable macros (assuming you've set macro security to medium: Tools, Macro, security) - As soon as you've clicked the disable macros button, press control-pageup or control-pagedown, thus changing sheets. If that does not work, try creating a link to the file: ='c:\my documents\[MyFileName.xls]Sheet1'!A1 and copy right and down. This at least gets you the worksheets values...

Migration #2
Hello all, I have all my clients on outlook 2002.We are in the process of Migration to Postfix. I would like to know whether its possible to migrate all the PST files to Postfix and if yes I would like to know how can it be done. Thanks in advance. Regards, Rajagopal Iyengar. IT manager. Better ask in a Postfix newsgroup. We are more into migrating to Outlook ;-) Anyway, see if you can export to a format that Postfix is able to import. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Create an Office 2003 CD slipstreamed...

2 DATA RANGES IN CHARTS
I am trying to create a chart of stacked data The data I have is Class of cost Planned and Actual by month/year I want a chart that will show me actuals by class of cost and then side by side actual and planned I am not sure if i can create a chart that will range two data series. If you arrange your data properly, a lot of things in Excel are vastly simplified. To chart multiple series, put the category labels (or X values) into a column, put the Y values for one series into the column to the right, and put Y values for any additional series in the next columns after that. If the da...

Excel: How do I enter a date in one cell & automatically dates adj
In excel, I am trying to find a formula or funtion so that when I enter a date (1/10/05) in cell A1 for example, then B1 automatically changes to 1/11/05, C1 to 1/12/05, etc. Can anyone help? Thanks!! In cell B1 your formula is =A1+1 That will advance the date by one day. Copy that formula across, and you're good to go. Hi Dave O, Thank you so much! Here's another question that has developed after I entered that formula. Before I have a date in A1, B1 states "1/1/1900", C1 states "1/2/1900"; but it works perfectly once I enter a date in A1. I'm trying ...

Task Fields.
When I add a task the "Name" of the "Contact" is added to the task... but how do I add the "Company" name and/or "Telephone Number" to the task list? Widows 98SE Outlook 2002 Thanks for any advice of help or information you can give, Jim You can accomplish what you want with a little custom VBA code. For an = example, see http://www.outlookcode.com/codedetail.aspx?id=3D566 --=20 Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/ju...

Can the Description Field be change in Position Setup later?
Hello, My accounting department is wanted to change the description field from the Position Setup to another name than they 1st started out with. Located in Tools/Setup/Payroll/Position Setup. Do anyone know how this change will effect the current accounts establish the posting connection between Payroll and the General Ledger? I have read about the Departments and Positions are simple to setup and are critical to a successful payroll system, but non thing on making changes to them after they are setup. Any help would be appreciate. Ed ...

CD KEY office X doesn't work after update Office 10.1.2
Can someone please give the solution for the following: I installed Office X, after I updated this version with Office 10.1.2 my CD Key didn't work anymore. What do I have to do? Thanx! Franc Hogguer <f.hogguer@zonnet.nl> wrote: > Can someone please give the solution for the following: > > I installed Office X, after I updated this version with Office 10.1.2 > my CD Key didn't work anymore. > > What do I have to do? Carefully check whether you entered the key correctly (see <http://support.microsoft.com/default.aspx?scid=kb;en-us;332010>) If that ...

OWA logoff problem #2
Hi every one! When ever I click on Logoff button in OWA 2000 the error msg. appears. "The page cannot be displayed" Please give me any suggestion. Regards Please help me with this, or can I upgrade OWA 2000 to OWA 2003 without upgrading the exchange? or is there any alternate OWA type interface available for exchange 2000? "msnews.microsoft.com" <shahzad.asghar@netsolpk.com> wrote in message news:eAzTWdN4EHA.3596@TK2MSFTNGP12.phx.gbl... > Hi every one! > > When ever I click on Logoff button in OWA 2000 the error msg. appears. "The > page cann...

POP3 email in Exchange 2003 #2
We have Exchange 2003 set up and our internal email within our LAN is being sent and received well. Email from within our LAN to the outside world also goes. However, I am unable to get outside email in my Exchange mailboxes. We have an external POP3 email server (mail.something.com) ... how do I configure Exchange to receive email from this server? At present we have the cumbersome process of having two systems in Outlook clients. One exchange email and another POP3 email client set up. And it's driving me mad! Please help. Regards. Russel I don't beleive there is any way...