Return Dates Based On Day And Month Only

Jan 3, 2006

I would like a query to return dates based upon the input of just the day and month. At the moment I have a parameter query which asks for 'start date' and 'end date' and this works fine, but I want the query to return all the records for all the years in the database and not just the current one (date format is dd/mm/yy)

So if I type <start date> 01/01 and <end date> 02/01 the query will return:

01/01/04
01/01/05
01/01/06
02/01/04
02/01/05
02/01/06

Does anyone know a solution - I have been searching all afternoon!!??

View Replies


ADVERTISEMENT

Modules & VBA :: Calculate Next Service Month Based On Dates

Jan 16, 2015

I've been trying to solve this problem for the past month and at the verge of destroying my PC! I've trawled through numerous web sites but just cant seem to figure this one out.I'm trying to get access to tell me what the next service moth is based on the start date, end date, and the frequency of service.

For example:

Start date: 01/01/2014
End date: 31/12/2015

service frequency: Every 4 months

Using the above information the service months are:
1 - April 2014
2 - August 2014
3 - December 2014
4 - April 2015
5 - August 2015
6 - December 2015

View 6 Replies View Related

Queries :: Return Records Between Dates Based On 2 Date Fields In A Table

Apr 24, 2013

I have a table which includes a start date field and completion date field for housebuilding.

I am trying to extract all records that have either a started date or a completed date between 2 dates supplied by the user. I have tried to use Between on both fields but that doesn't return results between the fields.

It workd if I just do it on EITHER the start date field OR the completion date field so that implies to me that I need to break it into 2 queries, one returning start date recrods and the other returning completion date records but then I would need to have somthing that removes records that appear in both the start date and the completion date results.

View 7 Replies View Related

Return 'month' For A Given Date

Sep 26, 2005

I have a table which has a column 'Date', which contains dates in the format dd/mm/yy. I wish to produce a quiery which will produce an extra column entitled 'month', which will match each date to the month of that date. I know there is a formula which does this (called 'month' i think), but i dont know how to use it to achieve what I want.

Any help would be greatly appreciated!

View 1 Replies View Related

Return Month And Year

Aug 14, 2007

Hey all, I have a form that has two textboxes, one box is for month and the other is for the year. After I enter the month and year I want, I click on a command button that previews a report. Well, the report is not giving me the information for that particular month and year, actually it gives me nothing.

The report gets its information from a query. In that queries criteria field is the below expression that I have.

Like [forms]![frmPEL]![cboMonth] & "/*/" & Right([forms]![frmPEL]![txtYear],2)

Can anyone see anything wrong with this? I should be able to enter the month and year and click on the report button I want and preview that report, but no joy.

Your help is greatly appreciated.

RR

View 5 Replies View Related

Return Last Month Value In A Text Box

Aug 26, 2006

I have used the following function to return the date of the previous Sunday in a text box when I open a form..

=Date()-Weekday(Date()-1)

In a different text box I would like return the pevious month based on the date in the text box.

For example the above function would return date 20/08/06. I would like to display JUL-06 in the second text box. Can anybody help?

View 2 Replies View Related

Query To Return All Birthdays For Next Month

Sep 28, 2015

I came up with the following string to do this but I keep getting an error...

Next_months_Birthday: DateSerial(Year(Date())+IIf(Format(Date()),"mmdd") >Format([Month],"00") & Format([Day],"00"),1,0),[Month],[Day])

The error I get is ....The expression you entered contains invalid syntax, or you need to enclose your text data in quotes.

I attached a screen shot ....

View 8 Replies View Related

Queries :: Return 0 Where Data Does Not Exist For A Particular Month

Mar 13, 2015

I'm trying to make a query to return payroll data, but I'm not sure how to get Access to return 0 where data doesn't exist for a particular month.

For example, imagine this table:

Name MonthHours
John 1 160
John 2 160
Dave 1 160
Dave 2 160
Matt 1 160

How would I get this ouput:

Name MonthHours
John 1 160
John 2 160
Dave 1 160
Dave 2 160
Matt 1 160
Matt 2 0

View 10 Replies View Related

Forms :: Filtering Records To Return Appointments For Month

Aug 4, 2015

I have an appointment database with, of course a Date field.I would like to be able to filter the records to show appointments for a particular month.I want to see what appointments I have for September, for example.Ideally I would like to populate a combo box with month names and filter the form based on the month selected.

View 4 Replies View Related

Queries :: Return Result When Enter Month As Search Criteria

Jun 15, 2015

I cannot get my query to return result when I enter the month of "June" as search criteria. June is listed in the table and query, every other month is returned except June.

I have attached two example for your reference.

View 9 Replies View Related

Please Help To Group Dates By Next Month

Aug 21, 2007

I have a table column with dates like

02/05/06
02/06/06
...........

I need to calculate the next date[Due Date] in 6 months, which is simple

[Patient's file].[Last Appointment Date]+180 as [Due Date]

and I have to group the Due Date by the next month. Lets say the due date is 08/25/07, so it has to be grouped as September group at the top or the list. It should be displayed as "September, 2007"

How should I assing let say 08/01/07 - 08/31/07 dates to September groups. I mean the statement should be universal for any day and any month of the year.

Thank you.

View 1 Replies View Related

Show All Dates In Any Given Month / Quarter

Aug 10, 2005

I need a query that lists each day of the month (or quarter) that is selected from a form. Meaning, if a user selects June 2005 on the form, the query will return to show every date from June 1, 2005 to June 30, 2005. Is this simple, tiny little thing possible?

The reason behind this is that on a report, I need to show data for every date. If there is not data entered for that date, it still needs to appear with the date listed, but with no entries represented.

Thanks!
Tess

View 3 Replies View Related

Queries :: Displaying All Dates Within A Month

Oct 14, 2014

I have created a query to limit the list in a combo box to display the months and years of the date field in a table. Below is the code that I am using. This works fine except this displays the years within the months - for example:

January 2012
January 2013
January 2014
February 2012
March 2012
March 2014
April 2012
May 2012
May 2014

I would much prefer it to sort by month and year:

January 2012
February 2012
March 2012
April 2012
May 2012
January 2013
January 2014
March 2014
May 2014

Code:
SELECT DISTINCT Month([QTDate]) AS MoNum, Format([QTDate],"mmmm yyyy") AS MoName
FROM MainTBL
ORDER BY Month([QTDate]);

How to change this code to make it sort on Month and Year, not just month?

View 3 Replies View Related

Filter All Dates Within Current Month

Mar 21, 2015

I am probably quite wrong but I am trying to apply a filter that returns all dates within the current month

The bulk of the filter is from Microsoft Access examples.

Private Sub MonthB_Click()
DoCmd.ApplyFilter , "DueDate Year([DueDate]) = Year(Now()) And Month([DueDate]) = Month(Now())"
End Sub

It is not working??

View 3 Replies View Related

Queries :: Totals Query - Return Data Set Between Two Dates

Jun 9, 2015

I am trying to create a Totals Query which returns a data set between two dates. So far I have managed to select the data I want (Please see attached screenshot). However, I only want to select records between a date range working on my field [DueDate]. If I add the due date field to the current query then it removes the grouping and all records are displayed.

View 7 Replies View Related

How To Get Beginning And Ending Dates Of Current Month

Mar 14, 2005

I know i have read this before, and i have been searching for a hour now.

can someone tell me how set a variable = the beginning date ofthe month and another variable2 = the ending date of the month?

Thanks for your help!

View 1 Replies View Related

Queries :: Matching Dates By Month And Year

Aug 27, 2013

Looking to match to date fields within a query by using the month and year only, is it possible and how.

I'm using Access 2003

View 13 Replies View Related

Queries :: Eliminate Records With Dates In Same Month

Jun 17, 2015

I am trying to figure out a logical WHERE condition. I have a Query that gives me data,

Code:
Name | Start | End
--------+-----------------+-------------
Paul | 30-May-15 |
Eugin | 21-May-15 | 28-May-15
Francis | 04-Mar-15 | 08-May-15
Samuel | 10-May-15 | 13-May-15

I want to have only Paul and Francis. As Eugin start is 21-05-2015 and End is the same month, so is Samuel's. So I want to eliminate those two records.I have checked pbaldy's web of overlapping records. But unlike that, I need something customised.

Begins and ends before range - we don't want - Yes
Begins before, ends during - we want this one - Yes
Begins and ends during - we want this one too - NO
Begins during and ends after - we also want this one - Yes
Begins and ends after - we don't want this one - Yes
Begins before and ends after - we want this one -NO

View 5 Replies View Related

Create Reports Showing All Start Dates By Month

Dec 8, 2014

I have a table that has the following:

ID
Program Year
Program
Start Date
Midpoint Date
Internship Date
End Date

I have successfully created queries to create reports showing all Start Dates by Month no matter what program and similar reports for all the other date queries. What I need it to do is list everything happening within a month and sort them by category. How do I do that? I've attached the database so you can see my queries as well as the main switchboard reports associated.

View 4 Replies View Related

Queries :: Parameter Query Criteria To Return Dates With 2 Years Remaining

Sep 17, 2013

I am trying to create a parameter query to return dates that have 2 years remaining.

For example I have dates for when mortgages expire, and I want to recognise the dates that have two years remaining using a parameter query but I can't figure out if I use DateAdd or DateDiff.

View 2 Replies View Related

Queries :: Expression To Show Years And Month Difference Between 2 Dates?

May 6, 2014

I am wanting to get an expression that will return the difference in years and months between 2 dates. Specifically, I want to know peoples ages in years and months based on a person's D.O.B and todays date. I have managed to do it in years:

Expr1: DateDiff("yyyy",[TBL_EmployeeDetails]![D_O_B],Date())

and in months but I would like to know how to return the difference in years and months.

View 1 Replies View Related

Queries :: Dates In A Query - Show Orders By Month For Last 12 Months

Apr 22, 2013

I want to create a simple query from a list of orders dating back over 12 months.

The fields I have is Ord_Date, Qty

I need to show the orders by month for the last 12 months.

The problem I am getting is that the orders for the month of April (as we are in April now) contain orders from 1st - 22nd April 2013 and orders from 23rd - 30th April 2012, therefore confusing the figures.

I would just like orders grouped by month with a total qty dating back 12 months, but without any old orders for the current month.

View 6 Replies View Related

Text Return Based On GPA

Nov 10, 2004

Dear All:

Thank you for all you help with the above title. Your suggestions were all implemented.

Code:
Private Sub GPA_AfterUpdate()
If DIVISION = GRADUATE Then 'Honors field is empty
HONORS = ""
End If
If UNDERGRADUATE = True Then 'Apply appropriate honors
End If
If GPA < 3.2 Then
HONORS = ""
ElseIf GPA >= 3.2 And GPA < 3.5 Then
HONORS = "CUM LAUDE"
ElseIf GPA >= 3.5 And GPA < 3.8 Then
HONORS = "MAGNA CUM LAUDE"
ElseIf GPA >= 3.8 Then
HONORS = "SUMMA CUM LAUDE"
End If
End Sub

Here is the issue: Form created with with a combo box called "DIVISION."The choices of the combo box are "GRADUATE" AND "UNDERGRADUATE." There is a text box called "GPA", where a gpa is entered. then, there is an "Honors" field. When a gpa is entered, the honors box returns the appropriate message.

When graduate is selected from the combobox, the honors box should be empty. (That part does not work.) When undergraduate is selected from the combo box, it works.

Please help,

Dion

View 5 Replies View Related

Return Info Based On Other Fields

May 3, 2006

Hi All

I have 2 formulas that work for me in excel. I hope to be able to replicate the result in Access.

1)
I have the following in a field called PROBLEM STORE NAME:
SALE (DAY 1 - HD)
Mt DRUITT (WOWPOS) (E)
THE MALL (WOWPOS)
TOWN HALL (RF)
LIVINGSTON

I want these to be grouped as WOWPOS or ISS460 (if not WOWPOS), so return in field STORE TYPE as:
PROBLEM STORE NAME.............................STORE TYPE
SALE (DAY 1 - HD)............................................... ..ISS
Mt DRUITT (WOWPOS) (E)......................................WOWPOS
THE MALL (WOWPOS).......................................... ...WOWPOS
TOWN HALL (RF).............................................. ......ISS
LIVINGSTON........................................ ..................ISS
I use the following formula in Excel to achieve this when I export query results from Access:
=IF(ISNUMBER(SEARCH("wowpos",K2)),"WOW","ISS460")

2)
I have a field VENDOR ID that may or may not contain ANY detail. If the cell is empty I need it to return N/A. If populated I need to return VENDOR CALL. The following formula achieves that for me in Excel:
=IF(ISBLANK(X2),"NO","VENDOR CALL")

Any suggestions would be greatly appreciated

cheers

sallee

View 1 Replies View Related

Honors Text Return Based On GPA

Nov 9, 2004

Dear Access Wizards:

I have created a form based on a query with students' gpa in the form. The gpa is in a specific field.
There is another field for honors with is for summa cum laude, magna cum laude, cum laude.

Here is the issue: If the students' gpa is 3.8, return "summa cum laude"
If the students' gpa is 3.5, return "magna cum laude"
if the students' gpa is 3.2, return "cum laude"

This applies to undergraduate students only, which there is a field for.

Any ideas on how to resolve this issue?

Many thanks,

Dion

View 3 Replies View Related

General :: How To Return A Value Based On Another With Subforms

Mar 14, 2015

I have a form with a field within it called ID number, another table contains the ID number with a person's name next to it. I was wondering how I can make it so that in the form, when the ID number is entered, the name of the person also shows next to it and does so automatically for each different record. I am pretty sure that a subform is needed however it doesn't seem to work for me so I must be doing it wrong. How would I do this?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved