If anybody on this board knows about Microsoft Office's Access your help would be greatly appreciated. I am setting up a database using Access which involves entering dates but I need future dates calculated. Here is an example: One field is called Current 90 Day Date and what I need is a date to be entered such as 11/21/2005 but the date 90 days after this date to be displayed in another field which would be called 90 Day Due Date. There are a few other fields I need to do this in too. Is there anyway to set up a field so it will calculate dates at all?
Thanks, just got through checking it out but whenever I try and save my table I get an error "Type mismatch" message. I select the DateAdd funtion and it gives me this in the Default Value field: DateAdd («interval», «number», «date») What do I substitute for interval, number, and date? I figured I put +90 in number because that is what I want. The I put the Current Date Field where date is but what do I put under interval.
Interval is the time period you want to increment. This is the example in MSaccess: Expression Description =DateAdd("d", -10, [PromisedDate]) Displays a date that is 10 days before the value of the PromisedDate field. =DateAdd("m", 1, "31-Jan-03") Displays a date that is a month after 31-Jan-03, so the expression will evaluate to 28-Feb-03. =DateAdd("m", 1, "31-Jan-04") Displays a date that is a month after 31-Jan-04, and since 2004 is a leap year, the expression will evaluate to 29-Feb-04. =DateAdd("q", 3, [PromisedDate]) Displays a date that is three quarters after the value of the PromisedDate field; for example, if the value of the PromisedDate field is 18-Jun-03, the expression will evaluate to 18-Mar-04. =DateAdd("h", 3, [ArrivalTime]) Displays a time that is three hours after the value of the ArrivalTime field. You can use these expressions in a calculated field in a query.
OK under my table I have it set it 90DayDate and 90DayDue. In my Default Value field under 90DayDue I have the formula set up like this: =DateAdd("d",+90,[90DayDate]) When I try to save the table I get this error message: The database engine does not recognize either the field '90DayDate' in a validation expression, or the default value in table PTable But I have I have two fields set up in PTable and those are the two listed above so what am I doing incorrectly?
I think you should setup a query to update the table. I do not think you can use the function directly in defining the second field.
So where do I put the formula when making a query? I see Field Table Sort Criteria or but where do I put the formula?
Lets say you create a table called table1 and have two fileds [dateone] and [datetwo] both defined as datetime. create a query called query one and paste this into the sql defination area: UPDATE Table1 SET Table1.datetwo = DateAdd("d",90,[dateone]); That will update the datetwo to 90 days after dateone. hope that helped
OK I set it up and here is what the query looks like in SQL view: SELECT [Practice Table].[90DayDate], [Practice Table].[90DayDue] UPDATE [Practice Table] SET 90DayDate.90DayDue = DateAdd("d",+90,[90DayDate]) FROM [Practice Table]; I get this error message: Syntax error (missing operator) in query expression '[Practice Table].[90DayDate] UPDATE [Practice Table] SET 90DayDate.90DayDue = DateAdd("d",+90,[90DayDate])'.
Do you only have the following in the query? UPDATE [Practice Table] SET [90DayDue] = DateAdd("d",90,[90DayDate]); If that is the case I have no idea why it wouldn't work.
OK, that was the problem. I have other fields that I need dates calculated on also. Can I just add an UPDATE line to the SQL definition area under the query and do those that way too?
UPDATE [Practice Table] SET [Practice Table].[90DayDue] = DateAdd("d",90,[90DayDate]), [Practice Table].[120DayDue] = DateAdd("d",120,[90DayDate]); will update another field to 120 days from the original date.
Try creating a query that includes the calculated field dates. For example, your table might contain the following field: Current90DayDate DateTime Now create a query and include the following: Current90DayDate This is the 90 Day date field from your table DateAdd("d",90,Current90DayDate) This formula will calculate a date 90 days from your Current90DayDate field This is the easiest way to display value. You don't not need store it in your table since it is simply a calculated value. Hope this helps
Is there some type of wizard or something on Access that will help me create a menu like this one: http://img.photobucket.com/albums/v310/jakz34/menu.jpg or is that something that I would have to do myself?
MS Access contains a Form Wizard that will provide the basic skeleton of an input form for your table, but it doesn't have any sort of menu wizard of which I am aware. What are you building, if you don't me asking?
A client database for a counseling company I am working for. Things are so disorganized at the moment I am trying to get at least a few things organized and if I can get the information set up about each client in an Access database that would help a bunch. Here is everything that I need listed in my database: Client ID, Payor Source Number, Date of Birth, First Name, Last Name, PCP Name, PCP Expiration Date, Therapist, Case Manager, Intake Date, Psych Eval Date, Last Psych Visit, 90 Day Due Date, Next 90 Day Due Date, 180 Day Date, Next 180 Day Date, PA Start Date, PA End Date, Level of Care, Diagnosis Axis I & Axis II. After getting this set up I was wondering is there a way to print out all information on a client for a specific therapist or case manager? Example: John Doe is assigned clients Jane Doe, Adam Doe, Beth Doe, and Carl Doe. Can I print out all information on those clients and not other ones stored in the database?
