Can I Use DateAdd To Refer To A Pre-entered Date?
Apr 20, 2008
Hi again
I've done some searching on this question and it seem everyone is pointing to the DateAdd function, so here is my current problem.
I have a date/time field called SponsorshipContractDate. When a contract is signed the date is entered. I need the system to calulate the renewal date for the contract which is alway 1 year ahead.
The DateAdd function works only if I hard code at date into it e.g.
DateAdd ("yyyy", 1, "01-Jan-08")
But as contracts are signed throughout the year this just doesn't work so I tried this:
DateAdd ("yyyy", 1, "SponsorshipContractDate") but the table won't save and this is abviously not right. In my head it makes sense but I don't understand how to get the formula to look at another field during it's calculations
Am I using the wrong function?
I will keep looking around.
Thanks heaps
View Replies
ADVERTISEMENT
Jun 8, 2005
Hello - I have a form with a txtbox that shows a date. I need the querry to refer to that date as selection criteria. I have the following in the criteria of the query: =[forms]![frmUpdatePayments]![txtDate]. I know the problem is I need # at the beginning and at the end of the date, but how do I do that?
View 1 Replies
View Related
May 22, 2005
I have created a query where I use the DateAdd function to calculate a date. I have named this calculated field NextVisitDue. The problem is that I'd like to be able to put an input parameter on NextVisitDue so that I can retrieve records within a date range. For some reason, when I do this, the resulting NextVisitDue dates include dates that are outside of the date range. For instance, if [Begin date:] = 1/1/2005 and [End date:] = 6/1/2005, I will get resulting dates in NextVisitDue like 1/12/2006, 10/21/2006, and 10/6/2007. These results are clearly outside of the date range. What follows is the SQL statement...
SELECT tblSpecialist.User, tblSpecialist.FirstName & " " & tblSpecialist.LastName AS Specialist, tblProvider.ManagerOwnerFN & " " & tblProvider.ManagerOwnerLN AS Provider, tblProvider.FacilityName, tblProvider.LastSupervisoryVisit, DateAdd("m",[tblFrequencyCodes.Frequency],[tblProvider.LastSupervisoryVisit]) AS NextVisitDue, tblProvider.VisitFrequencyCode, tblFrequencyCodes.Frequency
FROM tblSpecialist INNER JOIN (tblCaseLoad INNER JOIN (tblArea INNER JOIN (tblProvider INNER JOIN tblFrequencyCodes ON tblProvider.VisitFrequencyCode = tblFrequencyCodes.FrequencyCode) ON (tblArea.AreaCounty = tblProvider.AreaCounty) AND (tblArea.AreaFacilityType = tblProvider.AreaFacilityType) AND (tblArea.AreaZipCode = tblProvider.AreaZipCode)) ON tblCaseLoad.CaseLoadID = tblArea.CaseLoadID) ON tblSpecialist.SpecialistID = tblCaseLoad.SpecialistID
WHERE (((DateAdd("m",[tblFrequencyCodes.Frequency],[tblProvider.LastSupervisoryVisit])) Between [Begin date:] And [End date:]))
ORDER BY DateAdd("m",[tblFrequencyCodes.Frequency],[tblProvider.LastSupervisoryVisit]);
The real interesting thing is that, if I use literal values for the date range criteria, the query results are as expected! For instance, the following works beautifully (but doesn't allow for user input parameters as required)...
SELECT tblSpecialist.User, tblSpecialist.FirstName & " " & tblSpecialist.LastName AS Specialist, tblProvider.ManagerOwnerFN & " " & tblProvider.ManagerOwnerLN AS Provider, tblProvider.FacilityName, tblProvider.LastSupervisoryVisit, DateAdd("m",[tblFrequencyCodes.Frequency],[tblProvider.LastSupervisoryVisit]) AS NextVisitDue, tblProvider.VisitFrequencyCode, tblFrequencyCodes.Frequency
FROM tblSpecialist INNER JOIN (tblCaseLoad INNER JOIN (tblArea INNER JOIN (tblProvider INNER JOIN tblFrequencyCodes ON tblProvider.VisitFrequencyCode = tblFrequencyCodes.FrequencyCode) ON (tblArea.AreaCounty = tblProvider.AreaCounty) AND (tblArea.AreaFacilityType = tblProvider.AreaFacilityType) AND (tblArea.AreaZipCode = tblProvider.AreaZipCode)) ON tblCaseLoad.CaseLoadID = tblArea.CaseLoadID) ON tblSpecialist.SpecialistID = tblCaseLoad.SpecialistID
WHERE (((DateAdd("m",[tblFrequencyCodes.Frequency],[tblProvider.LastSupervisoryVisit])) Between #1/1/2005# And #6/1/2005#))
ORDER BY DateAdd("m",[tblFrequencyCodes.Frequency],[tblProvider.LastSupervisoryVisit]);
Please help!
-CoddFish
View 4 Replies
View Related
Aug 21, 2007
Hello
I'm currently doing a query, when a report is received, we enter a date, i would like a query which would show records where the received date has not been entered. i have tried putting in isnull() but does not work.
Can anyone help?
Thanks
View 3 Replies
View Related
Nov 26, 2006
Hi all,I'm working on a library database for a small collection.I have two fields, 'Date Out' and 'Date IN'. Is it possible that whatever date is written for the date taken out, I can set the 'Date IN' to be 7 days later than that date?Thanks in advance.
View 4 Replies
View Related
Feb 6, 2006
Hi there all, I tried the search option on this board but maybe its just that i dont know how to ask the question.
I have a text box on my form with and afterupdate event that inputs todays date in to another text box on the same form, can this be set so that once that date is apperas in the text box that no further updates to the date can take place.
Thanks in advance for any help.
TIDMAN.....:confused:
View 6 Replies
View Related
Jun 27, 2006
Hello everyone. I am wanting to store the date the record was entered into the table. But I don't want the users to have to type this in; I thought that Access could grab the date from the system date.
I am new to this and I looked at the Date function but I am thinking it will just always update to the current system date.
Thank you very much if anyone could point me in the right direction.
View 3 Replies
View Related
Nov 21, 2006
Hello, all;
I think this is probably a simple solution for some of you.
I have a database containing records for which I need to set dates to follow up on.
I would like to ensure that the date that is entered is a business day (So that when I run my follow up report for the day - I don't end up missing items that were inadvertantly entered for a weekend!)
I have hunted around for a similar thread - but have not found anything.
I am thinking that a simple bit of validation could be entered for the field properties.
Any ideas?
Your help is greatly appreciated.
View 4 Replies
View Related
May 24, 2005
I have a form where users fill in a date resolved and click complete in the Option Group, it's default value is incomplete. I have a problem with the users forgetting to change it to complete when the enter the date resolved. Is there a way to change the option group value when the date resolved is entered?
View 6 Replies
View Related
May 24, 2005
I have a form where users fill in a date resolved and click complete in the Option Group, it's default value is incomplete. I have a problem with the users forgetting to change it to complete when the enter the date resolved. Is there a way to change the option group value when the date resolved is entered?
--- OR ---
Can it notify the user that they need to change the Option Group?
View 3 Replies
View Related
Dec 21, 2012
I want a default value to equal the first 2 characters of field [first name] and first 3 characters of field [last name] and the numeric datevalue of [DOB] 'date of birth'.
In excel its easy,
C D E resultformula
AndrewTester12/12/1980AnTes-29567
Formula LEFT(c9,2)&LEFT(d9,3)&"-"&e9
How do I get same result in Access?
tried
Field 'PRN', a text field set as default =""""& left([first name],2}+LEFT(Last name],3), date()&""""
Access accepted the above statement but when I input user details the default doesn't work.
View 5 Replies
View Related
Apr 30, 2013
I am looking to (on the mainform) have the user enter into a text box whatever date they would like to enter and have that value become the current date of the system. So that all operations are based off of this newly entered date.
View 4 Replies
View Related
Aug 5, 2014
My friend is looking for the database to auto populate a date, based on information already entered.
A Learner Starts his/her private tuition lessons on dd/mm/yyyy and pays for 6 weeks, the next lot of payments is due on dd/mm/yyyy...
View 1 Replies
View Related
Jan 9, 2015
We are creating a database to log data on a project. There will be thousands of files. Can we input data, using the same table, at separate locations and then merge the data into a master table? We will need to do that many times.
View 1 Replies
View Related
Jun 23, 2015
I have a form with a text box where the user enters a date and then clicks a button titled "Add." I've added an on-click event to the Add button that runs an append query that adds several records to a table [tblTracking]. I have a field in tblTracking called EndDate. I want the date that's entered into the text box by the user to be populated into all the new records added to the tblTracking when the append query is run. Currently, all fields in tblTracking are populated when the append query is run, except the EndDate field.
Is this possible? If so, how?
I've experimented with adding a separate on-click event that adds a record to a separate table containing only the date entered in the textbox and an auto-populated ID field. I thought there might be a way to utilize the ID field to pull the associated EndDate into the Tracking table, but I can't figure that out, either, since I don't know how to tell it to look at the date field in the last record of the table. That sounds unsafe anyway.
View 3 Replies
View Related
Jul 26, 2014
I am trying to add the current date and time into separate fields after an ID is entered.
Code:
Option Compare Database
Private Sub ID_AfterUpdate()
Me.Date_Received = Date()
Me.Time_Received = Format(Now(), "hh:mm AMPM")
End Sub
View 5 Replies
View Related
Nov 10, 2005
I need to create a New Form control for this situation:
If I enter a date into a field and the choice for another field is equal to a certain value. How can I get the date I entered to be automatically populated into another date field.
For example:
If I enter 11/10/2005 in a date field and I choose either "BN", "BA", or "BT" in a text field, I need that date of 11/10/2005 to be automatically populated in another date field on the same form.
Any help is greatly appreciated.
View 2 Replies
View Related
Sep 14, 2007
I have a table that includes a client account number, revenue, and a Date in the format YYYYMM. I would like to create a query that sums the revenue for a client where the date is six months prior to today.
Thanks in advance
View 2 Replies
View Related
Oct 13, 2004
I think I have a simple problem: In a database table I've made there is a field called <date> (in the date&time format), in the table there is another date field, which should contain a date always 6 months later than the <date> field. For conveniance, I created a form to fill in all other fields in the table, including the <date> field. Succesfully I tried to calculate the 6-months-ahead-date in the form with DateAdd, but I tried everything I could (which is not so much) to store this result in the table, since I need it there for a few reports I generate.
Who can help me to solve this problem??
View 2 Replies
View Related
Jul 30, 2012
I am fairly new to access but so far I have been able to get what i need from it, until now. I am trying to find a way of comparing two sets of data to find out an employee's average productivty.
Table 1 -Hours Worked (by day)
- contains 'name' 'date' and '# of hours' worked
- an employee would enter the hours here on a daily basis
Table 2 - Contracts Keyed (by month)
- contains the number of contracts worked that is derived from seperate system
- this is entered on a monthly basis (so for example: John keyed 30 contracts for the month of January)
- the system i am pulling this info from does not have the ability to pull a daily count of contract per employee, only a range of dates and it then provides the sum for that range (unless I ran a query for each day, for each employee which would take me hours)
- employees dont have access to this system to enter their own # of contracts keyed on a daily basis.
- for entry, so far i have just been putting the first of the month and then the # of contracts.
In a nutshell, this is the calculation I am trying to create:
(Sum of "# of hours" for the month) / (total "# of contracts keyed" for the month) = employees average hourly productivity.
I have tried to do this with various types of queries and reports but with no luck, I get a prompt saying that access can't compare the 2 fields.
Is there a way to compare the data that is entered daily with the data i would enter monthly?
View 2 Replies
View Related
Feb 18, 2007
Hi,
This problem has to be easy but I can't make it work. The attached database has a table name BOL information. Inside that table are the two columns I'm having an issue with. I want ETA Date to be 30 days after BOL date. I have been screwing around with the Dateadd function and can't get it to work. I have been putting the following in the default value of ETA date:
Dateadd("d",30,[BOL Date])
The error message I have been getting is The database engine does not recognize either the field 'BOL Date' in a validation expression, or the default value in the table 'BOL information'.
What am I doing wrong? Should I not be putting the formula in the default value? Should I use another way to get this calculation? Your thoughts are most welcome.
Thanks in advance.
Eddie.
View 2 Replies
View Related
Jan 3, 2008
I have a table that tracks the inspection dates for various structures. I have a field called InspectionDate in the form of 99/99/9999. Another field called Interval which can range from 6 months to 48 months. I have always gone in to edit another field called InspectionDueDate. Is there a way to have the InspectionDueDate field automatically be filled based on the other 2 fields? Does this make any sense? Thanks in advance.
View 4 Replies
View Related
Jun 28, 2005
Howdy all,
I've written a simple little function using the DateAdd system function which i call from query. Here's the logic
If (PassFail = "Pass") Then
IsNull (FirstRepairDueDate)
Else
FirstRepairDueDate = DateAdd("d", 5, InspectionDate)
End If
basically if the PassFail argument is "Pass" I would like the function to not display anything, however i'm not sure what to do because my function returns a data type of Date, which it defaults to 12:00:00 AM using the above logic.
Any ideas?
Many thanks
View 5 Replies
View Related
Oct 23, 2005
I'm VERY new at using VBA although I have been programming for many years.
I have a requirement that involves creating a new version of a record and setting the expiry date of the current version to one day less than the effective date of the new version.
Regardless of what I try, the new expiry date that gets set is always 12/30/1899.
I suspect there is something very elementary that I'm missing, but haven't been able to figure out what it might be. Perhaps I need to apply a function to the setExpiryDate variable?
Any suggestions would be appreciated. Here is the current version of the code I'm working with:
Private Sub createNewVersion_Click()
Dim strUpdate As String
Dim intervalType As String
Dim adjustment As Integer
Dim setExpiryDate As Date
Dim newEffDate As Date
intervalType = "d"
adjustment = -1
newEffDate = Forms!fCreateDomainVersion!NewEffectiveDate
setExpiryDate = DateAdd("d", -1, newEffDate)
strUpdate = "UPDATE tDomain " & _
"SET ExpiryDate = " & setExpiryDate & _
" WHERE DomainName = Forms!fCreateDomainVersion!DomainName AND " & _
"EffectiveDate = Forms!fCreateDomainVersion!EffectiveDate;"
DoCmd.RunSQL strUpdate
End Sub
View 1 Replies
View Related
Nov 27, 2005
Hello
I'm very new at this - just found out about the DateAdd function today trying to solve my problem, so that you know who you're dealing with.
Let's say Im operating a video rental shop, and want to calculate a DateDue (=when videos need to be returned) based on DateRented and number of videos rented..
DateAdd ("d", 5, [DateRented]) would be appropriate for 5 videos rented at once.
I want to replace 5 here with a number of videos rented, and this number (TAPECOUNT below) is also calculated in a query using count function (to count the number of videos associated with each rental)
Preferably I would end up with a single query consisting of 4 columns:
RENTAL_ID, TAPECOUNT, DATERENTED, DATEDUE, of which both 2nd and 4th column would be calculated, 2nd in combination with 3rd serving as input for 4th.
Can it be done like this and how? Would I need to store TAPECOUNT value first? Is DateAdd even a proper function for my task? I'm open to all suggestions, just keep in mind I'm a newbie.
View 4 Replies
View Related
Jun 19, 2006
I have a table that has several fields however the ones I'm working with are Date, Letter, RollOff.
Date being the date the record was entered example 6/1/2006
Letter can be letter codes simply a,b,c,d,e etc
RollOff is 1 year from the Date the record was entered.
I've searched though several pages of dateadd info but haven't ran across an exact similar situation so far.
Getting the RollOff is no problem, simply DateAdd("yyyy",1, [Date]). However what I'm attempting to do is I need to add a certain amount of days to the calc between the date entered and the 1 year rolloff that match the Letter of "B".
Each record entered will only have 1 Letter and each entry is only for 1 day.
I'm thinking I will need a second query that will gather the sum of "B" codes to determine the amount of days needed to add to my DateAdd calc.
sample layout would be.
Date Letter RollOff
6/1/2006 A DateAdd("yyyy", 1, [Date]) + 3 days of B Codes for 6/2,3,4
6/2/2006 B
6/3/2006 B
6/4/2006 B
6/5/2006 C
Since 6/1/2006 RollOff is 1 year 6/1/2007 however since there was "B" enteries between the time the record was entered and the RollOff date the amount of days needed to be added so the RollOff would actually be 6/4/2007.
Any help would be appreciated.
View 5 Replies
View Related
