Query to create dates

I want to create a query that returns the dates of the next five Fridays. 
Is this possible?
Simon


0
BigPondNewGroups
11/7/2007 12:19:43 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
578 Views

Similar Articles

[PageSpeed] 21

Next Friday is:
    Date() - Weekday(Date()) + 6

If you want a query that gives you 5 records for the next 5 Fridays:
1. Create a table with 1 field of type Number, named (say) CountID.
Mark the field as primary key.
Save the table as (say) tblCount.
Enter the records 0 to 4 (5 records all up.)

2. Create a query based on this table.
In the Field row, enter:
    TheDate: Date() - Weekday(Date()) + 6 + 7 * [CountID]

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BigPondNewGroups" <stafe@operamail.com> wrote in message
news:z3iYi.9643$CN4.8949@news-server.bigpond.net.au...
>I want to create a query that returns the dates of the next five Fridays. 
> Is this possible?
> Simon
0
Allen
11/7/2007 12:52:18 PM
Thank youi very much
That worked brilliantly
Simon

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message 
news:OJWvJ0TIIHA.6068@TK2MSFTNGP05.phx.gbl...
> Next Friday is:
>    Date() - Weekday(Date()) + 6
>
> If you want a query that gives you 5 records for the next 5 Fridays:
> 1. Create a table with 1 field of type Number, named (say) CountID.
> Mark the field as primary key.
> Save the table as (say) tblCount.
> Enter the records 0 to 4 (5 records all up.)
>
> 2. Create a query based on this table.
> In the Field row, enter:
>    TheDate: Date() - Weekday(Date()) + 6 + 7 * [CountID]
>
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "BigPondNewGroups" <stafe@operamail.com> wrote in message
> news:z3iYi.9643$CN4.8949@news-server.bigpond.net.au...
>>I want to create a query that returns the dates of the next five Fridays. 
>>Is this possible?
>> Simon
> 


0
Simon
11/7/2007 1:12:09 PM
Reply:

Similar Artilces:

What controls default date when entering date fragment?
I know there has got to be a simple answer to this, but I just can't find it... What controls the date that defaults into a cell when you type in just a day, e.g. when I type in "23", what causes "23-Jan-00" to default in? I want it to default to "23-May-04", but since I got my new PC, I have to specify both the day and the month to get the correct date. Hi if the cell is formated as date the numeric value 23 is interpreted as date: 23 - Jan - 1900 No chance without macros for other defaults -- Regards Frank Kabel Frankfurt, Germany "Sharon C&...

Subtraction Query
I have two worksheets in the same document with a long list of Email addresses. I want to use a Query to create a new list that has only the Email addresses that are not present in both lists (i.e. subtraction). In Excel 2003 I tried going to Data, Import External Data, New Database Query and it made me select another worksheet. When I selected the one I was working on it said there are no tables in the worksheet. (Whats the difference between a column of data and a table?) Anyway, am I going about this the proper way to perform a Query? Thanks for any assistance you can offer, Bria...

Formatting query
I have a spreadsheet that has a lot of formulae in it. Colleagues enter a formula into one cell as part of the necessary calculation. This is needed to be shown when printed. If I select view formulae in the normal manner (through options) then all the formulae is shown whereas, I just want the formula in the particular cell to be shown. Any Ideas/ advice!!!!? One way with a macro Sub testing() Dim cell As Range With Sheets("Sheet1").Range("C5:E10") For Each cell In .SpecialCells(xlCellTypeFormulas) cell.Value = "'" & cell.Formula Next Sheets("She...

Querying cells across cells
Hi all, I'm wanting to create a basic kind of calculator for tracking my finances. What I am wanting to do (and apologies if it's really simple, but I'm just missing it), is : I have 4 columns one of which is named : Payments This column is set to calculate the total at the end of the file. I also have another column named 'Paid?' What I'm looking to do is find a formulae that will add the payments column together (easy enough), but when I add the word 'Yes' into the Paid column, that amount is deducted from the total at the end. Does that make sense?...

Create shortcut button to activity
Hello Sorry if this is a newbie question but I havent found an answer so far. I'm trying to find a way of creating a custom button on the Lead update screen to create an activity eg a phone call as a shortcut instead of having to go to the Actions menu, then PhoneCall, ie it's just a click of a button instead of using the menus. I've added a custom button to "/activities/email/edit.aspx" and have PassParams = 1 set. However, when I click the button I get a new email activity with oID passed but the Recipient field is not populated, so therefore is not saved with the lead...

how do I do an if then query in excel from 2 sheets?
I have 2 sheets in excel... I need to do a query that says IF a number is input into a cell (in a large array) in sheet two, THEN the name that corresponds in that row will be input into a cell in sheet one. ARGH PLEASE HELP!!! Thanks, KAM You can use a vlookup. Instructions: http://www.officearticles.com/excel/vlookup_formulas_in_microsoft_excel.htm Tutorial: http://www.officearticles.com/tutorials/order_form_or_invoice_form_in_microsoft_excel.htm ************ Anne Troy www.OfficeArticles.com "Kerry" <Kerry@discussions.microsoft.com> wrote in message news:3F012692-2196-...

Creating custom Callback ...
Hi, How can I create custom callback functions and make them be called in the target application? Thanks, Hugo If I understand what you are getting at you will need to look at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnwui/html/ msdn_hooks32.asp Lots of sample code around if you search on hooks32 on google Andy Mortimer [MS] Please do not send email directly to this alias. This alias is for newsgroup purposes only ...

Create a Drop Down Link to another sheet?
I've created a fairly large spreadsheet with visible and hidden sheets. To make things simpler, I would like to put a drop-down list of pages in the top "navigation" area that I have created over each sheet. I'd like the user to choose from a list of available options and be carried to the appropriate page that corresponds to that choice. I'm not very literate in VBA, so if that's the answer, tell me the simple version. Any ideas? I can do this in HTML, CGI, and ACCESS just fine.. I'm just in the beginning stages of Excel-Geekdom. Thanks ...

where can i create my own word search?
Where can i create a personal word find? Please re-post with more detail and a clearer description of what you would like to do. Gord Dibben MS Excel MVP On Mon, 19 Jun 2006 14:25:02 -0700, Tameika Allen-Shepard <Tameika Allen-Shepard@discussions.microsoft.com> wrote: >Where can i create a personal word find? Now I think I got it. You want to create a word search puzzle grid. I don't know of any sites with an example, but hang in. I'm sure someone can help. Gord On Mon, 19 Jun 2006 14:25:02 -0700, Tameika Allen-Shepard <Tameika Allen-Shepard@discussions.micros...

Trouble Creating Organization Forms In Exchange 2003
I've built a new Exchange 2003 server on a new AD domain. I'm trying to create a Organization Forms Library but when I right click on EFORMS REGISTRY in the System Manager I do not see the New Organization Form option. Am I in the wrong place or should I see it? Mark Figured it out. I didn't have a "New" option because we limit people from creating Public Folders and have it restricted to "everyone". After removing the restriction temporarily it brought back the "new" option. "Mark McCurdy" <stuff@noreply.com> wrote in me...

Todays date
Hi All I have a spreadsheet with a date field. I want to calculate how many days have passed since that date. So I need to minus todays date from the original date. Does anyone know how I get Excel to display todays date and update for each new day. Any help greatly appreciated Kind regards Rexmann Rexmann, =TODAY() -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "rexmann" <rexmann@discussions.microsoft.com> wr...

Consolidate Multiple Records Query
Good Afternoon, I have been pulling my hair out with this trying to get it to work on Excel and Access, but I just don't have enough knowledge of Access to know if it can work. I have two fields in a table. In field one I have a list of variables, call them City, County, State. In field 2 I have the potential values for each variable. They are listed vertically, so Record 1 shows the value City in Field 1 and Apache Junction in Field 2. Record 2 shows a blank cell in Field 1 and Buckeye in Field 2. Record three shows a blank in Field 1 and Carefree in Field 2, etc. ...

Conditional Formatting
I have read & tried several CF solutions, but they are not working for me. I am working with Today() date, Due_Date, & days_late. I have a formula in the days_late column =IF(OR(TODAY()<Due_Date,J9="COMPLETED")," ",TODAY()-Due_Date). This formula gives me days_late or leaves that column blank (" "). I want 5 cells to turn red if days_late>1. All my attempts at CF have also turned the blank (" ") cells red also. I have tried ISBLANK and NOT(ISBLANK). -- pls Your problem is that " " is not blank. I know it looks...

Sorting Crosstab Query Values
I have a crosstab query that I would like to sort in Descending order based on the query's values. I can't seem to figure out an effective way. Below is my current SQL syntax: TRANSFORM Sum(qryTest.CountOfMembership_ID) AS SumOfCountOfMembership_ID SELECT qryTest.Region FROM qryTest GROUP BY qryTest.Region PIVOT qryTest.Club; I would like to sort the CountOfMembership_ID in Descending order. If this can be done any way through this query, please let me know. Thanks in advance! Chad Chad, I don't understand what you are expecting. The CountOfmembership is spread across...

Queries in BP
Hello: Is it possible to create queries in Business Portal that pull columns from views that users create in SQL Server 2005? I have created a few SQL views based on data from payroll historical tables, and I have created some SmartLists in SmartList Builder based on these views. I was hoping to be able to pull in some columns from these views into BP queries. Is it possible to do this? Thanks! childofthe1980s ...

Business Portal deployment and queries #2
Hi, I would like to know how to transfer our customized queries in a BP deployement. When we deployed BP from our test environnement to production it worked smooltly for the sharepoint part but the problem is that the queries didn't follow... We don't want to recreate all customized queries on each deployement ! I pretty sure that the queries are in the Dynamics database. I tried to backup the test version of that DB and restore it in prod but after that BP refused to start-up... So, is the solution to export only certain Dynamics tables data ? If yes, which tables ? M...

Update dynamicly a query field formula
Hi All,Is it possible to update a query formula by vba code?for now i found a solution by just building a dynamic query,but iprefer to have an option to update just one field with new formulafrom time to time,is it posible?...

Query 02-19-08
Ost Ocity Dstate Dcity Carrier Price Rank Diff A B C D X 1200 1 100 A B C D Y 1300 2 100 A B C D Z 1350 3 100 A B C D W 1789 4 100 A1 B1 C1 D1 X1 785 1 A1 B1 C1 D1 Y1 789 2 The rank for every carrier is based on the price . If rank1 carrier is not a pariticular carrier(say if it is not X1 or Y1 or Z1), then i want to calculate the difference be...

Help with crosstab query 01-25-10
Sorry but I’m new using Access I have a crosstab query (from the table name Paddle) with Diss Unit as Row Heading and Position as Column Heading. The data is grouped by Diss Unit. The cross tab query works fine when I use Last in Total Row and Value in Crosstab row under Serial No. The crosstab query work well if the data is entered in date sequence but I need sure that the value that I get is from the last date in the case that the data is not entered in sequence. Each instrument will have six positions with paddles assigned to each position so I need to document the ser...

Date conversions
I have to import data from an ASCII text file and use INSERT INTO ... to create records in an existing table. There are a couple of fields with dates in the form Mar 05,10 I know that I can set the 9 characters into a string and manipulate the string using format(... to massage the data into a form suitable for the SQL string using #s. I was just wondering if that technique is the most efficient or is there a better approach? -- Len ______________________________________________________ remove nothing for valid email address. On Wed, 10 Mar 2010 11:18:19 +1000, &qu...

How do I reference a Web Query on another sheet?
I am taking a Microsoft Office college course. I have a Web Query on worksheet one and I reference it in worksheet two, but when I hit enter it displays a 0. Do you want to reference the results of the query, or the quert itsekf? -- AP "ReAnna" <ReAnna@discussions.microsoft.com> a �crit dans le message de news:986F7D26-54D3-4105-8352-0475492EC7DE@microsoft.com... > I am taking a Microsoft Office college course. I have a Web Query on > worksheet one and I reference it in worksheet two, but when I hit enter it > displays a 0. I want to reference the results of ...

CRM 3 queries / software shortcomings? Help please.
We are looking to implement a CRM system at present, with emphasis on marketing. Having looked at the Microsoft offering there are certain areas that seem very weak. Would anyone with any experience of CRM 3 be able to make any suggestions regards the following: 1] According to an online Microsoft seminar the maximum number of emails that can be sent in one go is c.2,000. Or that is what they said it had been tested to (and passed). We need to send up to 20,000. 2] The whole email tracking seems weak. I understand that sent emails are recorded against the account/contact. B...

Unable to add Query-based Distribution Group to Public Folders Permissions
Hello. I follow the following process to add a query-based distribution group to the ACLs of a public folder: Right Click on the folder -> Properties Permissions Tab -> Client Permissions Button Whenever I add a group, I press OK. As soon as I go back to check the permissions, they have reverted to defaults. I can add individual users and simple distribution groups, but not query-based ones. Any ideas? Rob Vella rob@absnetworks.com Query-based distribution groups can not be security principals. http://support.microsoft.com/kb/822897 Mariah <robvella@gmail.com> skrev i med...

create chart from external application
Hi, I would like to know if this is possible.... I have an application that contains an hierarchical chart. I would like to export this data into a format that Visio can *understand*, and then import it into Visio and have it display in a hierarchical manner. Does any body know if this is possible to do. .....or, if there is another way of getting my data into Visio, can you please let me know. Thanks. Here is an article that describes how to automate the org chart wizard, and how to format your data: http://office.microsoft.com/assistance/2002/articles/spotlight5_July00.aspx Hope ...

Worksheet Revision Date only once that day
I put a revision date in a cell (today's date) when I change a worksheet. If I merely print, view or something without actually changing the worksheet, I do not update the revision date even if I happen to save it again. I need the worksheet to automatically update the worksheet revision date only once for that day and only when the worksheet is changed (or only once after it's opened & changed will work just as well). I am trying to avoid changing the date every time the worksheet is changed since it's not necessary to change the date to the same date every time a change ...