Calculated Date ....Help Please
Aug 13, 2006
Hi Folks
I have a simple table that has a date field.
This is called dateopened and is in the format ddmmyy hh:m
I have a query based on this table.
What I'm trying to do is workout the day that a case was opened from the date entered.
i.e I have an entry that was opened on 10/08/06
My calculated filed works out that this was raised on a Thursday ?
I can then do dcounts etc on how many cases were opened on a Thursday or a Friday etc
I cant really get my head around the syntax in the query fileld
Can anyone advise me how to do this ?
Many thanks
Jimmy
View Replies
ADVERTISEMENT
Mar 14, 2008
Here's the situation.
I'm creating a database for my county, keeping track of all the rabies vaccinations that our organization does. In the form that is used to enter the vaccination data (e.g. owner's name, address, animals name, date of vaccination, etc) The user inputs the Date of Vaccination and whether it is a "1 Year" or "3 Years" vaccine and then they have to fill in another field called Vaccination Expiration, which is the year in the Date of Vaccination plus either 1 or 3 years, depending on which option is chosen. I need to know how to make the Vaccination Expiration field fill in automatically so as to reduce error in the calculation process.
Name ------------------ Description
DATEVAC Date of Vaccination
LENGTH Vaccination Length (1 Year or 3 Years)
VACEXP Vaccination Expiration Date
ANIMAL_VACCINATION Name of the Table AND Form that the data is inputed into
View 3 Replies
View Related
Jun 23, 2005
I use the following bit of code to find the difference in months between the Date of invoice of an item and the beginning of the next financial year.
DateDiff("m",[DepreciationInvoiceDate],IIf(Format$(Date(),"mm")>4,"01/04/" & Format$(Date(),"yy"),"01/04/" & Format$(Date(),"yyyy")
This works great, however now I have been asked to change it so that it says the difference in months between the first of the financial year (01 April) previous to the Date of invoice and the beginning of the next financial year. I have been given a deadline of the end of today to get this working.
Please help.
Thanks in advance
Lee
View 2 Replies
View Related
Feb 15, 2007
I am new and this is my first posting.
I have a simple database that tells me what jobs are waiting on material, and what/ how much that material is. I would like to add a field to a report that shows how many days late by calculating NOW - due date. I would also like to show in the same report total quantities required of each material type for all jobs requiring a specific material (grouped by material type). I am new to access and am a far cry from a programmer by see lots of uses that could make my job easier and would like to learn more. If anyone out there could help me with these two issues I would really appreciate it.
Thanx
View 4 Replies
View Related
Jul 3, 2014
I am trying to add a calculated date field in a query, I have 2 fields and 1 of them has a date and the other one i would like to to be 3 years from the date of the first field.
View 3 Replies
View Related
Mar 16, 2008
I have an expression in a query
Expire: IIf([payterm]="X","",DateAdd([payterm],1,[orderdate]))
However when I sort it it does not sort in correct manner
it's goes like
1/11/2007
1/15/2008
10/10/2006
10/16/2007
10/31/2007
10/5/2006
I have the field properties set to Short Date.
What do I need to do for this to sort right?
View 2 Replies
View Related
Apr 1, 2008
I have a calculated date field in a query...if I try and sort by this field I get a data type mismatch.
[CONTREFF] is a date field in a table, [TERM] is a number field in a table. I am trying to calculate the year the contract expires in the "EndTerm" field. The calculation works fine, but I can't sort it.
EndTerm: DateSerial(Year([CONTREFF])+[TERM],Month([CONTREFF]),Day([CONTREFF]))
Please Help!!! Thank you ...
View 3 Replies
View Related
Apr 18, 2008
I have a form with (3) Text Boxes. Text Box 1 & 2 requires integer input, which is automatically sent/stored in a table. Text Box 3 performs calculations on values in Text Box 1 & 2, but does not automatically update the table.
The following Control Source code is used in Text Box 3 to perform calculation: =([Forms]![Plate Cash]![1-Dollar Bills]*1)+([Forms]![Plate Cash]![5-Dollar Bills]*5)
Thanks in advance.
View 14 Replies
View Related
Jun 4, 2014
I've been struggling with this for a while, and even though I understand the theory, I can't get my code to work correctly.
I have 2 fields:
CallbackFrequency & Last Contacted.
I want to use these fields to populate a third field (callBackOn) so that we can have a list of candidates that need to be called from a certain date.
CallbackFrequency is added from a combobox, so all values are either 1 Month, 3 Months, 6 Months, or 12 Months.
I have tried creating a calculated field directly in the candidates table, but apparently this is not possible.I've therefore tried to create a query using IIF statement that will calculate this value.To get to grips with the code, I'm only dealing with 1 callbackFrequency at the moment. So far I have:
CallbackOn: IIf([Candidates].[CallbackFrequency]="1 Month", [LastContacted],)
I have not factored in the date manipulation yet, as the query does not display the LastContacted date of the record it finds, it only shows a blank cell,
View 8 Replies
View Related
Aug 4, 2013
In a query i have setup, i work out when a tenant is next due to pay their rent.
Looks like this
Tenant Last Payment Date Payment Terms Next Payment Date
User1 01/07/13 Monthly 01/08/13
User2 01/07/13 Weekly 07/07/13
The next payment date is calculated using an IF statement and DateAdd in the query, so if it is weekly it adds 7 days, if it is monthly it adds 1 month.
What i am trying to do is write sub query of this one which shows which tenants are due to pay within the next 7 days.
But the access query seems to ignore the criteria I'm putting in....in the next payment date criteria i have tried specifying >Now() + 7 or >Date() +7 but neither seem to work, it just shows up every record...
View 6 Replies
View Related
Sep 10, 2014
I am having a problem with calculating a date field in a query. Prior to this posting I've done some research and made several changes to my query. This only resulted in fixing one problem but then creating another problem. Original problem was I had 2 fields, arrived (23:36) and stemi (0:07). I use the following calculation AT_ST: DateDiff("n",[arrived],[stemi]) which resulted in -1409. So my research showed me I had a problem with the date whenever the time went past midnight and trying to calculate a zero hour number. I changed my calculation to
AT_ST: IIf([stemi]>=#11:59:00 PM#,(DateDiff("n",[arrived],[stemi])),(DateDiff("n",[arrived],[stemi]+1440) Mod 1440))
This works fine and gives me the result of 31 minutes which is what I want, however the problems comes in when I change to this calculation any where there was a negative time now has a 1400+ plus value. Such as arrived (7:37) and 1st_eck (7:18) = 1426 where as before it would report -14 (yes, negatives are acceptable for my reporting because sometimes a call to the hospital is placed before the patient arrives so we want to report on the negative splits). I've tried using a nested IIF to calculate for stemi time being less than arrived time, this didn't work when I tried to use it on the calculated query field. I was wondering if I could write something to check the value of the calculated field if it is greater than 1440 and if yes - subtract 1440 from it. So in the example above 1426-1440 = -14. Is it possible to do this within the query or do I need to do it using VBA
View 14 Replies
View Related
Jul 29, 2014
I want to use an expression to compare 2 dates and calculate the date of the last set of accounts for a company. The user will enter(DD/MM) of the company year end e.g. 31/12. I then want to compare this with todays date (in another field) to ascertain if the month has already passed in the current year and hence calculate the last year end. i.e. If (current month>company month end), year end date is DD/MM/YYYY (where YYYY is taken from today's date), year end date is DD/MM/(YYYY-1). I am not a programmer and although I understand database theory (from the teaching perspective) I am not expert in developing databases.
View 1 Replies
View Related
Jul 7, 2014
I don't seem to find any query formula in the forum where the age changes on the date of birth. I tried all the once I could find but all of them seem to calculate the age as of 1 January. I find it a bit strange that it doesn't work.
I got BirthDate and Date in the table I want to update the age column back in the history with an update query.
Code:
Int(DateDiff("yyyy",[BirthDate],[Date]))
Code:
Int(DateDiff("d", [BirthDate], DateSerial(DatePart("yyyy,[Date]), 1, 1))/365.25)
Code:
Int(DateDiff("yyyy",[Birthdate],[Date]))+Int(Format([Date],"mmdd")<Format([Birthdate],"mmdd"))
All the above change the age on January 1. It's not a train smash but weird.
View 14 Replies
View Related
Jul 15, 2013
I have an invoice system, where the payment due date is the last working day of the month following the invoice.
For example, if I produce an invoice on 5th June, the payment will be due on the last day of July. What I can put in a text box to automatically calculate that date, based on the Order date?
This is the order date formula : =[Forms]![frmInvoiceMain]![txtOrderDate]
View 2 Replies
View Related
Jul 19, 2013
I am creating a database with over 500 entries. One thing that would speed up the process of entering all of this data manually would be if I could type in the birth date and the age would be automatically calculated. Is there any way this can be done? It is all in the same table.
View 1 Replies
View Related
Jan 13, 2014
In my form I have a calculated field that works out the time elapsed for a job, worked out as the QTY/RUN RATE which gives me the time in hours. However I couldn't format the box as a date/time, as it is a calculation and the times may go over 24 Hours which results in the date/time giving me something like 31/12/1899 16:00 for example.
I've got around this now and I've formatted the results as HH:NN:SS using a public function in a module, but the problem is this is now stored as a text field. I've tried setting this to a number field but the formatting doesn't carry over. I've tried setting a custom format in the table as HH:NN:SS but then all I get is just a bunch of 0's, no calculation results.
So how to format this as a number field properly?
Also for any extra information:
- I know you're not supposed to store calculated results, but this is slightly different, the calculated field is a standalone text box and the bound field is a formatted text box.
- The reason I have to store the calculated results is because my boss wants to eventually see a chart of the total hours of jobs each day, hence why I'm asking if this can be done as a number field.
View 4 Replies
View Related
May 31, 2014
I have created a simple booking DB, i am try to summarize the booking by centre and date so that when a user checks if there is availability the will be able to see how many have booked an activity and how many spaces are left, i have created a new field that calculates the spare places
Spaces: IIf([Totalbookings]=[MaxPeople]"Full"[MaxPeople]-[TotalBookings])
I have also added a link to the main file - [URL] .....
View 5 Replies
View Related
Jul 11, 2015
If I have four date Fields in a query, Astart, Bstart, Cstart, and Dstart and want to have a calculated field to find the latest date for each record how would I do that? I have tried things like:
LatestDate: MAX(Astart, Bstart, Cstart, Dstart).
View 2 Replies
View Related
Jan 21, 2015
I have a database to track temporary decertification's. I have the expiration and max dates calculated out from the original dates at the top of each box. The temp expiration date is calculated by adding 267 days from the first date . When we enter an extension, the new expiration date is 30 days from the extension date. My question is, how can I make the expiration date update when a new extension is put in.
For ex.
Temp Decert Date: 05 Dec 2014
Temp Decert Extens 1:
Temp Decert Extens 2:
Temp Decert Extens 3:
Temp Experation Date: 31 Aug 2015
Max Temp Date: 04 Dec 2015
how can I make the expiration date update to go 30 days from what is in the extens field 1, 2, and 3 (respectively) instead of 267 days from the original date?
So I want it to look like this after updating a field
Temp Decert Date: 05 Dec 2014
Temp Decert Extens 1: 30 Aug 2015
Temp Decert Extens 2:
Temp Decert Extens 3:
Temp Experation Date: 29 Sep 2015
Max Temp Date: 04 Dec 2015
View 14 Replies
View Related
Dec 3, 2012
I've got a members table where my members pay an annual fee. The fee is paid 12 months on from when they originally registered. So, for example, 12 months from today, i would expect to pay my next annual fee.
Now, with different members registering at different times of the year, it isn't so straightforward. I would like my calculated field to indicate to me if a payment is required.Furthermore, I would like to include a '' date range '' so that the calculated field entitled ''Payment Required?'' shows yes for a fortnight. Here is my attempt at the expression which partially works:
PaymentRequired?: IIf((Format(Date(),"dd/mm")>= [RenewalDate] And Format(Date(),"dd/mm")<=[RenewalDateAdd14])Or ([Subscription Paid?]=0),"Yes","No")
i used the Format Date method. But i've got a case where someone is due to pay at the end of November...and today being teh 3rd of December.they should have a good few days yet to pay the fee (before teh aforementioned 14 days is up)
View 3 Replies
View Related
Apr 12, 2006
My query contains two calculated fields [TaxSavings1] and [TaxSavings2], which are based on some currency and number-type fields in one of my underlying tables.
I just created another field in my query which looks like: [TaxSavings1]+[TaxSavings2]. Instead of adding the two fields, it actually lumps the two numbers together. For example, if [TaxSavings1] =135 and [TaxSavings2]=30.25, it will give me: 13530.25. I need it just to simply add, i.e. answer of 165.25.
Does anyone know how to correct this? Thanks in advance.
:confused:
View 1 Replies
View Related
Aug 19, 2015
I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.
I have tried
Code:
Between [StartDate:] And [EndDate:]
And
Code:
Between [StartDate:] & "00:00" And [EndDate:] & "23:59"
Neither of which work ....
View 13 Replies
View Related
Oct 24, 2013
I'm trying to get my "IncidentDate" field to autopopulate two other date fields to a few days from the "IncidentDate". The other two date fields are "ContainDueDate" and "RootDueDate". I'm trying to accomplish this on my "Test CAP Form"
I tried using the following in the BeforeUpdate of "ContainDueDate" and received a complier error: expected =
Code : DateAdd(d,2,[IncidentDate])
so I removed the parenthesis and nothing happened
Code : DateAdd d,2,[IncidentDate]
I even tried redoing it in the AfterUpdate of "IncidentDate" and nothing happened either
Code : DateAdd d,2,[ContainDueDate]
I'm not sure if I'm even using the right function to get what I want.
View 4 Replies
View Related
Oct 9, 2005
Can someone tell me how to get year to date totals, month to date totals, week to dates in a query? I need to get all three for three different fields.
I was not able to get the totals with the formulas given. I received the totals for each day instead. Are there any other suggestions? I am trying to different formulas, but they are not working either. I did try doing different queries with the formulas to see if that would work.
View 9 Replies
View Related
Apr 6, 2006
I have a table that contains 2 columns:
1 = Deduction Reference Code
2 = The Value associated with each Code.
for example
Deduction Reference Value
999997 10000
666 6000
Code 999997 duplicates the value within code 666 so I wish to set up a caluclated value to say:
If deduction reference = 999997 then the value of 999997 minus the value of deduction reference 666.
I have tried several statements but none of which seem to work.
Can anyone help?
Thanks
View 1 Replies
View Related
May 9, 2005
I know it is said that you should not store calculated data in tables so I need some input on how to go about a problem I am having.
I am creating a DBase with some aircraft info in it. The DBase will be around for awhile so I plan to implement so sort of archiving later on.
The problem is one piece of info I am tracking is flight hours for the aircraft along with hours on certain components. I have a table with info pertaining to each aircraft by number (1 record per aircraft) one of the fields is total current flying hours. I have another table that has individual flight info including number of hours per flight. I want to add the individual flight hours with the total flight hours to get a new total flight hours. Easy with a query but if I get rid of old flights then how will you add them in the query. The only thing I can think of now is to save the newly summed number in the table which is supposedly taboo. Same with the components if it is on the aircraft during the flight it needs to get the new flight hours on it also .
Sorry if this is trivial but any ideas on how this should be done would be great.
Thanks in advance.
Frank
View 7 Replies
View Related
