Calculation Criteria On Max(date)

Jan 16, 2006

My query selects all those who donated to a fund.The max on the date restricts all but their last donation, working fine.But now I want to list all those who have not donated anything in the last x days (user defined). I want to use Date()- dateField>30 but now it does not let me peform the query.
Any way I can do this in just one query? Thanks!

SELECT main.dgTitle, main.dgInit, main.dgSurname, main.dgNo, main.dgStreet, main.dgShul, Max(main.dgDate) AS maxdate
FROM main
WHERE (((Date()-[main].[dgdate])>30))
GROUP BY main.dgTitle, main.dgInit, main.dgSurname, main.dgNo, main.dgStreet, main.dgShul;

View Replies


ADVERTISEMENT

Problem With Calculation From Date Criteria

Aug 13, 2006

Hi, I have a membership database I'm working on and the problem I'm tearing what's left of my hair out over is this:

If the date of joining the club is <= 15th of that month then the renewal date is 1st of that same month in the following year.

However, if the date of joining the club is >15th of that month then the renewal date is 1st of the following month in the following year.

I hope that makes sense/ Example:

JoinDate = 03/08/06 then RenewalDate = 01/08/07

BUT IF

JoinDate = 16/08/06 then RenewalDate = 01/09/07

Any ideas on how I would go about doing this would be greatly appreciated.

I realise that I could simply use the DatAdd() function to add an entire year to the JoinDate, but as you see the criteria I have to work to is unfortunately not that simple.:(

Thanks hopefully.

View 3 Replies View Related

Queries :: How To Set Criteria For Percentage Calculation In SQL

Oct 15, 2013

I have a query that calculates a percentage that works fine. The sql is like so:

SELECT Table1.cost, Table1.sales, Table1.location, [sales]-[cost] AS GrossProfit, [GrossProfit]/[sales] AS GrossProfitPC
FROM Table1;

If I try to set criteria to select records based on the calculated value of the expression (eg; only select records with a GrossProfit percentage > 50) when I run the query it asks for a parameter as if the field [GrossProfit ] were a parameter.
Is there a way i can set a criteria based on the value of the percentage field?

View 2 Replies View Related

Calculation Between Start Date & End Date

Jul 18, 2005

I have a form which has a start date field & end date field
i have a query that works out how many days from start date to end date that excludes weekends now my boss wants it to exclude any holidays we may have such as bank holidays etc
can any help me with this
i think i need a module and a new table with all the holidays in it
i am right or iam i going crazy?

View 3 Replies View Related

Date Calculation

Feb 11, 2007

I want to resolve date calculation problem.

I have one form field with users total sum of lenght of service (format of field looks like yy/mm/ddd) in another form field I have data with users date of employment in a firm.
In the third field I want to show total sum of lenght of service (previouse lenght of service and lenght of service in our firm) in format like in first field yy/mm/ddd.

What is a easiest way to calculate a third field in a form.

Thanks in advance !!

View 3 Replies View Related

Date Calculation Help!

Nov 4, 2005

Hi, I have a table that has a start date field, a duration field and an end date field. I was wondering what I need to do so that the end date is calculated and entered when the duration is added onto the start date to produce the end date?

Should the date fields be set as a date, data type?

Sorry if this does not make sense ;)

Many thanks, Tobi

View 1 Replies View Related

Date Calculation

May 31, 2005

Hi Every1

I need sum quick help
I want to pick out the date from my table that is 2 weeks old?
Any ideas how to do this?

thanks

View 11 Replies View Related

Date Calculation

Nov 27, 2007

In my tables i currently have a start date for each employee, in my query i need to single out those who fall after 3rd september 2007, providing a yes/no answer.

View 3 Replies View Related

Date Calculation

Sep 9, 2005

Here is the scenerio:
I have two tables:
tblDate: Contains on Months (i.e.Jan, Feb, Mar, etc)
tblActivities: Contains 4 fields Name, date, activity and hours

I have greated a form that allows me to select what month I want to see. It has a simple combo box in it that point back to tblDate.date. I setup a macro that is doing a OpenReport, there Where Clause is as follows:
[Forms]![frmMnthSelect]![Combo0] Like [tblVolunteers]![Date]

Of course this is not working, and I am at a loss.

I have a Query that is setup that is pulling all records for the current month, but I need to be able to select individual months, because I often have to pull reports for past and future projects.

Can some one start me out in the right direction......

Thanks,
Chuck

View 5 Replies View Related

Excel Date Calculation

Nov 21, 2005

Hrmm, im sure there used to be an Excel forum, i seem to have lost it :/ Hope its ok posting here:

What im trying to do is, for a range of dates from 1/1/2005 to 31/12/2020 get the Qtrly Financial Period before...

So, for instance:

A date of 1/1/2005 makes the period before 1/10/2004 to 31/12/2005
A date of 1/5/2005 makes the period before 1/1/2005 to 31/3/2005
A date of 1/8/2010 makes the period before 1/4/2010 to 30/6/2010

Fiscal Period (for those that dont work with them) are, 1st April to 30th June, 1st July to 30th Sept, 1st Oct to 31st Dec and 1st Jan to 31st March of any year.

So what i want is 3 colums in excel, Column 1 = Period Start, Column 2 = Period End, Column 3 = Date

Can anyone figure the forumla out? Doesnt matter where its done in, just as long as the end result can be in Excel....

View 4 Replies View Related

Date Time Calculation

Apr 9, 2006

Hello friends,

I have a table for "Production Orders" ie. work orders.

there are feilds : start date , start time

based on these feilds i want to calculate the remaining time to start production.

but what i want is

if remaining time is > 1 day then
give results in the format " 1 day and 2 hrs"

elseif remaining time < 1 day then
give result in the format " 2 hrs and 24 mins"

and also current time - remaining time > 0 else result = "N/A"



how can i do this simply

View 2 Replies View Related

Date Across Month Calculation

Jun 21, 2006

Good Morning,

I have a table where I record period of time:
Example:

ID1 from 25-apr-06 to 06-may-06 Holiday
ID1 from 06-may-06 to 02-jun-06 Work

How do I automatically calculate how many days ID1 was on Holiday and how many days was workign in may?

Any help appreciated

Enrico

View 3 Replies View Related

Date / Time Calculation Help

Jul 13, 2006

I'm having trouble getting my head around this.
I have a database that collects stats of patients admitted to ICU.
I have a table PtDataTable (pk = PtID) with a one to many relationship with PtVisitTable (pk = VisitID, fk = PtID). The patient visit table has the fields AdmissionDate and AdmissionTime, and DischargeDate and DischargeTime.
I need to calculate if the patient is readmitted to ICU in less than or equal to 48hrs. In other words calculate the time elapsed between DischargedDate and DischargedTime in one record and the AdmissionDate and AdmissionTime for other records in the PtVisitTable for the same patient.
I'm not sure if I stated this clearly.
Any help is appreciated as always.

View 2 Replies View Related

Date Calculation In Query

Apr 20, 2007

YearsOfService: DateDiff("d",[Employee]![SIV Date],Date())/365

First I'm calculating the current Years of Service. SIV Date = Hired Date

Then I would like to calculate the years of service as of 12/31/"Current Year", where the Current Year would not require updating every year and 12/31 would be constant.

I'm just not sure how to specify the Month/Day and use Current Year not hard coded. I could set up a user defined date for that half of the calc but my users are not really bright and allowing them to simply click a button is my only option there.

Ultimately I will compare the 2 years of Service numbers to determine if there will be a change in the level of vacation accrual an individual receives during the year. The only part I have issue with is coding that date. Thanks for the help.

View 2 Replies View Related

Review Date Calculation

Aug 28, 2007

Hi
I have a database for maintaining procurement contracts which includes a start date for each contract and a frequency of reviews required (monthly, qrtly, bi-annual, yearly). I need to be able to generate a report that will tell me when the next review is due for each contract, taking today's date into consideration.

This is the setup so far:

tblRegistry - contains field 'Commence' for the start date of the contract and 'Review Req' to indicate how often a review is required (monthly, yearly etc).

tblReviewReq - contains field 'Review Frequency' (monthly, yearly etc) and 'Days' to assign the number of days eg monthly = 30, yearly = 365 etc.

Next I have a query that includes the above tables using the fields 'Commence' and 'Days' and have created a calculated field to give me the next review date ie contract start is 1/08/07, review freq is monthly (=30 days), therefore the next review is 1/09/07. "NextReview:[Commence]+[Days]"

But how do I get it to tell me the next review date after the 1/09/07? Ultimately I'd like to be able to pull a report at any stage which will tell me all the reviews that are coming up.

Thanks

View 3 Replies View Related

Date & Time Calculation

Dec 19, 2007

Table
------
I have one field called [Time] in dd-mm-yyyy hh:mm format in table A.

I have another field called [Deadline] in text format (Mon 10:00) in table B.

Queries
-------
Need to build one queries that have the above two field, but with additional field called [PullDeadline] that the [Time] will look out the [Deadline] and return the actual time & date value in [PullDeadline]. No matter how the [Time] change, the [PullDeadline] will stick to [Deadline] and return the value in that particular week.

For example,

[Time] = 01-12-2007 05:00:00 AM
[Deadline] = Mon 10:00
[PullDeadline] = 26-11-2007 10:00 AM

How to handle, if the deadline is previous or next week?

Eg. [Deadline] = Sun 10:00

Then [PullDeadline] = 25-11-2007 10:00 AM instead of 02-12-2007 10:00 AM.

View 5 Replies View Related

Forward Date Calculation

Mar 10, 2008

I need to use an update query to calculate the following 1st of April for each of 3000+ records, the only exception is if the Start Date is the 1st of April in which case the date will remain the same.

Examples

StartDateAdjStartDate

01/03/08 01/04/08
10/09/06 01/04/07
15/04/08 04/04/09
01/04/08 01/04/08

Can anyone advise the best way to do this?

Regards

Hawk

View 3 Replies View Related

Variable In A Date Calculation

Aug 14, 2005

I have the following date calculation :
-----------------
Private Sub StatofLimits_GotFocus()
'--Determines Statute of Limitations accounting for minors.
If DateAdd("yyyy", 18, Me.Txt14) > Me.txtBegin Then
Me.StatofLimits = DateAdd("yyyy", 20, Me.Txt14)
Else
Me.StatofLimits = DateAdd("yyyy", 2, Me.txtBegin)
End If
End Sub
------------------------
Here is my problem. The number "2" in the else statement (Me.StatofLimits = DateAdd("yyyy", 2, Me.txtBegin)in the above example is a variable. The variable is selected from a combobox and stored in an unbound textbox. The variable will always represent a number of years-ie 3 years, 4 years, etc. I need to modify my calculation so that it will pull the variable from my textbox:
I know this isn't correct but this is what I need:

Me.StatofLimits = DateAdd("yyyy", (Number from MyTxtBox), Me.txtBegin)



Can anyone give me the correct syntext?

Pat, If you read this, I did listen. The reason I need to modify my calculation and use a variable is because I moved my Statute of Limitation Dates to the same table and different case types have different Statutes. They can be anywhere from 2 to 6 years.

View 4 Replies View Related

Calculation Based On Date

Aug 29, 2006

I am not sure how to do the following and appreciate any help offered.

I need to have create an expression that gives me a total based on the number of days (different arrival and departure dates) where each day has a different value but remains the same from record to record. Perhaps this is a very easy thing to do, but I am just missing it.

Thank you

View 2 Replies View Related

Time And Date Calculation

Oct 15, 2006

OK so I have a form with the following fields which are all date/time type
apptTime
timerOn
pickupDate
reminder - combo box with 1 hour, 2 hour...etc up to 10 hours

so what happens is when user enters apptTime say 18:30 with pickupDate set for current days date 10/14/2006 then chooses 2 hours from the reminder combo. This then sets timerOn to (apptTime-2 hours) so it is 16:30. I have set a conditional formatting to change background color when "timerOn<=Time() and pickupDate=Date()" so user knows to dispatch this load. This all works great until you get to midnight.
If user sets apptTime to 00:30 with pickupDate to 10/15/2006 and chooses 2 hour reminder, so timerOn does get set to 22:30 and you would expect the conditional formatting to work once the current Time() is 22:30, but since the current Date() is 10/14/2006, the expression is false. I have tried the DateAdd such as DateAdd("d", 1, Date()) command in various ways but can't get it to work for "timerOn<=Time() and pickupDate=DateAdd("d", 1, Date())"...I mean it works because it adds 1 day to pickupDate and condition is then true, but it ignores the timerOn<=Time() part and formats even if I reset system time to 22:29. Can anybody give me insite on where the logic is wrong?

View 1 Replies View Related

Using Nz Function In A Date Calculation

Apr 2, 2008

Must admit I have always had problems getting my mind around the Nz function and where to stick it in an expression. have googled this one (as I know it is not a common problem) but to no avail

If i have a calculated field

=DateAdd("d",7,[DateSubmitted]) then how do I deal with the possibility that [DateSubmitted] may be blank??

View 1 Replies View Related

Date - Query Calculation

Mar 14, 2006

Hello Everyone

Its been a while but I'm back (hope everyones fine) with a question that should be fairy simple

In a query

I have 2 Fields

1) Receipt Date - Formatted ##/##/####
2) Warrant Mths - Formatted as Number ie 3 (= 3 Months)

I would like to use the above [receipt date] + [Warranty Mths] to provide the expiry date of the warranty.

Hope that's clear enough

Thanks Aaron

View 10 Replies View Related

Age At Specific Date Calculation

Apr 3, 2007

Hi

In need of some assistance please.

I am looking to calculate in a form someones age in years and months at a specific date.

I have tried various different functions but can't seem to work out where i'm going wrong.

The persons date of birth is entered manually into the "DoB" field. I then want their age to be calculated in a text box below this, if it needs 2 text boxes then so be it.

The specific date is 25/05/2007 (UK date).

I am currently using the datediff function but I need the calc to be more specific than just the year.

Hope this makes sense.
Thanks in advance

View 2 Replies View Related

Calculation On System Date

Feb 19, 2012

I am developing a small application for a school. In the school they teach 5 subjects in five periods. One period's time in 40 minutes. I want to develop a table which will display the next period after previous period is over. The computer must skip to next period on the basis on system time.

View 1 Replies View Related

Date Calculation In Textbox

Feb 17, 2015

Access 2007, Windows 7

On a form I have 2 textboxes, 1 for user input, the other a calculated date. The two values together represent a "Leave Year". The second textbox contains, [Firsttextbox]&" - "&([Firsttextbox]+364). Simple enough so far. The expected result from entering, for example, 2/10/2015, is 2/10/2015 - 2/9/2016, which is exactly what I need. But, using a hard figure like 364 will fail on 3/2/2015 when the extra day in Feb 2016 results in, 3/2/2015 - 2/29/2015. To compensate for this, I've added,

"=IIf([Firsttextbox]>"2/28/2015",1,0)" to a third textbox and added it to the calculation as follows;
[Firsttextbox]&" - "&([Firsttextbox]+364+[Thirdtextbox]) So far so good....almost.

The "Leave Year" calculated is what I want, until 10/1/2015, when something goes awry. At 10/1/2015, the expression in the third text box evaluates to 0,and I lose my extra day.

If the calculation is an Access 2007 problem/issue, will it evaluate correctly if it's migrated to a new version?

Another thing I need to do is similar, in that a date is entered and calculated to 7 days prior. If the calculated date falls on a Saturday, I need to have it adjust to Friday, the day before. Also, if another calculated date falls on Saturday or Sunday, I need to have it adjust to the following Monday. These are two separate calculated fields, but both are necessary.

View 7 Replies View Related

Calculation/Date Rang Problems

Jul 25, 2006

Hey Y'all,

Well...where should I start? This db report has a few calculations in it, so let me explain them real quick. The db tracks work assessments that are categorized as Pass, Fail, or Non-Rated.

The first is an overall percentage rate of all assessments. Non-Rated assessments are not included in this query. It works fine. In the report it's the Group Total.

The second is a "maintenance" percentage, which is calculated from just two types of assessments. This also works and in included in the report in a sub-report. This is the Maintenance Rate.

Our guidance has recently changed, and I'm trying to incorporate these changes. The first one is an addition to the first/overall percentage. The Group Total is

Rate: IIf([Var1]+[Var2]=0,"N/A",[Var1]/([Var1]+[Var2]))

where Var1 is Pass and Var2 is Fail. This works fine for an overall percentage. However, now we need to deduct 0.5 points for each type inspection "TDV", "UCR", "GO", or "DSV." These are some of the assessment types listed as Non-Rated, however, there are other assessment types listed as Non-Rated that are not part of this criteria.

I included this change in a separate query "qryGroupDeductions." In this query, the 0.5 point per specified-assessment type is calculated by grouping and counting these inspections, then multiplying them by 0.5

DeductPoints: [DeductionCount]*0.5

This query counts and calculates properly. In the report there's a bit of conditional formatting that's been working so far. I had problems w/ it, but I think it's working properly now.

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)

If Me.Text64 <= 1 And Me.Text64 >= 0.95 Then
Me.Text66 = "Outstanding"
End If

If Me.Text64 <= 0.9499 And Me.Text64 >= 0.9 Then
Me.Text66 = "Excellent"
End If

If Me.Text64 <= 0.8999 And Me.Text64 >= 0.8 Then
Me.Text66 = "Satisfactory"
End If

If Me.Text64 <= 0.7999 And Me.Text64 >= 0.7 Then
Me.Text66 = "Marginal"
End If

If Me.Text64 <= 0.6999 And Me.Text64 >= 0 Then
Me.Text66 = "Unsatisfactory"
End If

If Me.Text66 = "Outstanding" Then
Me.Text66.FontBold = True
Me.Text66.ForeColor = 8388608
End If

If Me.Text66 = "Excellent" Then
Me.Text66.FontBold = True
Me.Text66.ForeColor = 6723891
End If

If Me.Text66 = "Satisfactory" Then
Me.Text66.FontBold = True
Me.Text66.ForeColor = 0
End If

If Me.Text66 = "Marginal" Then
Me.Text66.FontBold = True
Me.Text66.ForeColor = 65535
End If

If Me.Text66 = "Unsatisfactory" Then
Me.Text66.FontBold = True
Me.Text66.ForeColor = vbRed
End If

End Sub

The problem I'm running into is when you run the queries/reports for a period longer than a month. The instructions on the new guidance don't take into account quarterly or annual reports. When this report is run for periods longer than a month, the 0.5 point deduction makes a disproportional difference because you are deducting a sum from an average. The larger the time-span in the query, the larger the deduction is.

That, my friends, is the problem. I've made one query (qryQtrGrpDeduction) that "solves" this problem. It's based on the "qryGroupDeductions", and uses a basic calculation to create an average for the months.

QtrAvg: [DeductionCount]/3

and

DeductPoints: [QtrAvg]*0.5

This is very basic, but effective. But using would create a much duplication of queries and reports. I would need one set of reports/sub-report/queries for the month, quarter, and the year. This sample db doesn't show it, but our working database does this same calculation for four different organizational levels....So in using this method, we would need approx 15 queries and reports.

Not cool.

There's gotta be a way to effectively accomplish the requirements w/o creating a multitude of reduncies.

BTY, I'm using Access2003.

-----------------------------------
Key words: calculate, percent, count, report conditional formatting

View 2 Replies View Related







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