Am posting this message (also on reports forum) as i dont know whether the problem is at report or query level. Hope you can help.
Hello all, this is probably simple but im new to all this!
I am trying to produce reports that show details for only the past year, at present my reports are bringing up details of all the previous years also. Is there any way you can put conditions on reports ( or should it be in a query) that tells the report to only include dates i.e. today - 365 days. At present i have to change the dates every day both a start and end date. Is this possible?
i have 2 tables.. 1 with bookings and 1 with a BonusPeriod range.
in the Bookings table there is a field called [ServiceDate]
in the bonus table i have a field called [Period] which is text i also have 2 dates, [StartDate] And [EndDate] i need to Dlookup the Bonus table to return the value of the period table based on where the [ServiceDate] falls.
i dont want to use VBA (i really need it to work within a query that inserts it's data into another table)
i've tried everything but had no joy
Expr1: DLookUp("[Period]","BonusPeriods","[ServiceDate]" Between "#[StartDate]#" And "#[EndDate]#")
Expr1: DLookUp("[Period]","BonusPeriods","[Staff_BookingsAndQuotes_Master].[ServiceDate]" Between "#[StartDate]#" And "#[EndDate]#")
Expr1: DLookUp("[Period]","BonusPeriods","(FormatDateTime([ServiceDate]),"yyyy-mm-dd") Between (FormatDateTime([BonusPeriods]![StartDate]),"yyyy-mm-dd") and (FormatDateTime([BonusPeriods]![EndDate],"yyyy-mm-dd"))))
I run various queries on a daily basis for multiple date ranges (previous day, week to date, month to date, and year to date). The below criteria allows me to get WTD numbers but it has a flaw. When I run the reports on Monday, this criteria gives me Sunday and Monday's data when I actually want the previous week's data. Does anyone know of an iif statement that basically, says, "If today is Monday, give me last week's data, else give me wtd of the current week?" Right now, I manually change the criteria on Monday morning to hard code the dates and then revert to the formula on Tuesdays but I need to have this completly automated because there are a large number of queries and I don't have time to change them all. Thanks for the help!
I received help on previous threads. Basically, I have built a query to group by course title and then select the number of students that were assigned a course during a specifc time frame. Is there a way to show multiple columns in the query. For example my query now shows, enrollment for March, I would like to column next to it to show enrollment for Feb and then another column for Jan.
This is my current sql view.
PHP Code: SELECT tblContent.Title, Count(*) AS [Students Assigned] FROM tblProfile INNER JOIN tblContent ON tblProfile.LoginName = tblContent.LoginName WHERE (((tblProfile.LastName)<>"Demo" And (tblProfile.LastName)<>"Care" And (tblProfile.LastName)<>"Robins") AND ((tblContent.Code) Like "NH*") AND ((tblProfile.Groups) Not Like "*Professional Development Center*" And (tblProfile.Groups)<>"All Users,Professional Development Center") AND ((tblProfile.Delete)<>Yes) AND ((tblContent.Type)="Learning Track")) GROUP BY tblContent.Title, tblContent.[Date Assigned] HAVING (((tblContent.[Date Assigned])>=#3/01/2004# And (tblContent.[Date Assigned])<=#3/31/2004#));
I have a table with employee names and there start time and stop time for their shift. There is also a start time and stop time which they cant work during their shift. So I have shiftstarttime and shiftstoptime and codestarttime and codestop time. I have to create a table with the employee names and generate a time called outboundtime. This outboundtime starts at a specified time (e.g. 9am) and runs for a specified time (60mins). This outboundtime has to be within the range of the employees shift and cant be within the range of the cant work time. If it was, then it would start after the codestoptime for that employee.
I have been trying "if then statements" but I can't seem to compare a range of time to another range of time. Only a specified time to a range which is making things confusing.
I am attempting to create a parameter query to show a specific range of dates of service for my customers. Example: I want to see data from my table from Jan. 1st, 2006 to Feb. 15th, 2006 for any customers serviced in that time frame. In the query I have under my Date field, I have the following in the criteria field: Between [Input Start Date] And [Input End Date]. When I go to the while in the design mode of creating the query on the main menu under Query then to parameters and get my Query Parameters what should be put into the Parameter column and the data type columns and will this allow the user to input any start date and end date and get the information from the master table?
I also need to create a parameter query that will allow viewing of only each months table data. Example: Jan. 1st, 2006 through Jan. 31st, 2006.
I must be having a bad day, as I can't get something really simple to work. I have a query, that amoungst other things, I want to select all records between two dates. For instance, the table has a 'ScheduledDate' field, and I have set up two parameters in the query that prompt for 'From' and 'To' parameters.
In design view, I have tried the following in the 'criteria' box for the 'ScheduledDate' field;
>=[From] <=[To]
This doesn't work ~ can anyone point me in the right direction. I've searched the forum, perhaps not using the correct keywords?
I've read alot of good things on here regarding the DateDiff function and some other things that have helped me out finding the difference between two dates within the same entry on a table. I have a question regarding finding the difference in days between two entries within the same table.
Example:
test table:
Date ID # Name 1/18/07 1234567 Smith 1/20/07 1234567 Smith 1/23/07 1234567 Smith
Is there a way to find the difference in days between the lowest and highest dates? I want to know how many days are between the first entry and the last entry? In this cae it would be five days. I can do it if the days are in the same entry with the datediff function, but having trouble finding it in this situation. Any pointers?
(Thanks for all the good information on this site, BTW. I've seen a few posts similar to this, but it didn't really answer my question)
I am trying to put together a quick database for my HR team. I need to be able to pull multiple date ranges to show which employees are up for an annual, 6 month and 90 day review.
Is it even possible to put multiple date ranges in one query, or is this going to get complicated? I have no real database background, and know absolutely no SQL so I'm flyin by the seat of my pants here...
I have a simple form where a user selects a query from a drop down list. I need to have the query apply a date range that has also been selected in the form.
Would I call to the date form fields from the query as a variable somehow?? If so, could someone show me the syntax? I am extremely new to access.
I have not found the solution to this looking elsewhere, but basically what I am trying to do is the following:
person date 1 1/1/2012 1 12/15/2012 2 6/12/2012 1 6/13/2012 2 6/14/2012 3 6/16/2012
I want a query which shows the count or records for which a person has a date within 30 days of each other. This is being queried from a large table in which records would may not be sequential by person. Thus in my example above, the third record for person 1 would be omitted as well as the record for person 3.
How do I count the number of records with values within specified ranges?
My database contains a field with values ranging from say 1 to 1000. I need to write a query to show count the number of records with values in ranges I specify in the query.
I'm looking for output such as
<100 - 50 records 101 - 500 - 30 records 501 -1000 - 50 records
I am using two combo boxes to filter a list box with the code below. The combo boxes work, but when the form opens, the list box is empty until it is filtered using the combo boxes.
I want the list box to return all records when no filter is applied.
Here is the code:
SELECT Q_Gender_Statistics.ParticipantID, Q_Gender_Statistics.Gender, Q_Gender_Statistics.Date, Q_Gender_Statistics.Year, Q_Gender_Statistics.[First Name], Q_Gender_Statistics.[Last Name], Q_Gender_Statistics.[Other Names], Q_Gender_Statistics.[Passport No], Q_Gender_Statistics.[Duty Station], Q_Gender_Statistics.[Contact Number] FROM Q_Gender_Statistics WHERE (((Q_Gender_Statistics.Year) = Forms!F_Gender_Statistics!cboYear) AND ((Q_Gender_Statistics.Gender) = Forms!F_Gender_Statistics!cboGender)) ORDER BY Q_Gender_Statistics.Date DESC;
Any way to use multiple count functions in a query with their own individual filters without affect the others?
For example:
SELECT [E&I Table].System, [E&I Table].DeleteRecord, Count([E&I Table].[Status#1]) AS [CountOfStatus#1], Count([E&I Table].[Status#2]) AS [CountOfStatus#2] FROM [E&I Table] WHERE ((([E&I Table].[Status#1]) Like "ITR Rcv'd by QA" Or ([E&I Table].[Status#1]) Like "Completed" Or ([E&I Table].[Status#1]) Like "Ready for T/O" Or ([E&I Table].[Status#1]) Like "Reviewed by JVV") AND (([E&I Table].[Status#2]) Like "ITR Rcv'd by QA" Or ([E&I Table].[Status#2]) Like "Completed" Or ([E&I Table].[Status#2]) Like "Ready for T/O" Or ([E&I Table].[Status#2]) Like "Reviewed by JVV")) GROUP BY [E&I Table].System, [E&I Table].DeleteRecord;
I am trying to count in each column of the E&I table with criteria using WHERE but the problem is when you have more than 1 WHERE it affects the other columns as well... tried a bunch of different ways and I am now having to create seperate Queries than combine them using another Query...
So I'm new to Access, and I am trying use a query that can be referred to by a chart. So the idea is that I use the query to select data only from the date range that the user chooses on the home screen of the database for their chart (using the command Between [Forms]![Home Screen]![From] And [Forms]![Home Screen]![to])..Although it has been working fine for charts that only have two parameters, when I attempted to make a line graph that sorts by 3 parameters (i.e. date and amount for different types of something), it stops. I get the message that "The Microsoft Office Access database does not recognize [Forms]![Home Screen]![From] as a valid field name or expression" or something to that effect.I'd rather not remove the whole specification created by using the dates from the home screen, as it has been working fine on all other aspects of my charts and reports.
Hi there. I'm just jumping into Access and have the following question. In my Purchase Order Table, I have a date field calcualated as text, i.e. "01/12/04". I need to convert this to a date format in my queries so I can do calculations, i.e. 01/12/04 - 01/05/04 = 7 days. Can someone help me with how to convert this text date to a date format. Sorry if this is an easy question. - AJS
Hi I would like to manipulate some date information. I have a start date field and an end date field and I would like to draw up a schedule for payment where the Acess takes the finished date and the start date to get total days appart then devides this by a value set by the operator. say If the operator wanted 6 paymes then my total days would be devided by 6 giving me the number of days to add on to the start date. Then with these dates a schedule is made up and the code I have tried looks something like this.
Dim valuation1 As Date Dim valuation2 As Date Dim valuation3 As Date Dim valuation4 As Date Dim valuation5 As Date Dim valuation6 As Date Dim valuation7 As Date Dim valuation8 As Date Dim valuation9 As Date Dim valuation10 As Date Dim valuation11 As Date Dim valuation12 As Date Dim startdate As Double Dim findate As Double
Dim valcounter As Double
'retrieve start date and end date startdate = CDbl(Me.Start_Date) findate = CDbl(Me.Completion_Date)
'calculate evaluation periods Dim valCount As Double Dim valDays As Double
Select Case valcounter Case "1" Me.valuation1 = startdate Case "2" Me.valuation2 = startdate Case "3" Me.valuation3 = startdate Case "4" Me.valuation4 = startdate Case "5" Me.valuation5 = startdate Case "6" Me.valuation6 = startdate Case "7" Me.valuation7 = startdate Case "8" Me.valuation8 = startdate Case "9" Me.valuation9 = startdate Case "10" Me.valuation10 = startdate Case "11" Me.valuation11 = startdate Case "12" Me.valuation12 = startdate
I was wondering if somebody could help with a problem I have come across. I am currently developing a database for a small charity that I work for. The aim of the database is to record when staff undertake counselling.
I have created a query that calculates the current age of a client in the database uing the following expresion:
Age: Date()-[Date of Birth]
The Date of Birth is formated as a Short Date
In the query I have set the format of the column in the query to 'yy'. This then just displays the age of a client. This work fine apart from ages which are less than 30. If the age is less than 30 e.g. 29 it displays the date as 1929.
Does anybody know how I might be able to solve this or an alternative way in which to calculate dates and display it as 'yy'. (Its been a few years since I last work with access so skills a little rusty)
The reason behind the yy is that the monthly & yearly stats have to group by age ranges.
I have a field for 'deadline' date and I need to show all records where the deadline date is within 7 days of todays date. (Obviously todays date will change daily...)
I have tried subtracting the date from todays date but it doesnt work.
Just a quick request for a bit of help. Tries a few things but didnt work.
I have a form which has an employee start date. What I need to do is work out the holiday entitlement of each emplyee. The math behine this is, if they have been here less than a year, we do the following. 20 Days holiday entitlement per year, Divided by 52, then multiplyed by the ammount of weeks remaining in the financial year. eg, (20 / 52 = 0.384615384) * 31 = 11.92 So they would have 12 Days holiday entitlement.
But if they have been here for 12 months or more, they are entitled to the full 20 days holiday.
How would I express this as an expression or query or VBA to calculate this. I just cant figure it out, especially the if been here 12 months or more.
I have the start date, in a tbl called tbl_employees if that helps. ( also in the form frm_employee_details ) Our financial Year runs from the 1st December - 31st November
Several questions here, trying to lead up to the easiest code...
First is I have a table with some dates, and I need to do some date calculations, but one of the dates I need will vary. So I was thinking of having a parameter ask for it. Is this the best method?
Second, then once I have this date in, I'll need to have it subtract from another dated reference in the table to figure out the numbers of days difference. e.g. 05/31/06 - 05/01/06 = 30 Can you get two dates to subtract like that and come up with a general number field? If so, then will the dates have to be text, converted to numbers, or can stay as dates?
I've tried several methods and have been quite unsuccessful in each attempt.
