Deriving / Reformatting Date Field From Another Date Field In Same Table
Jun 19, 2014
I've inherited a database into which the user (not me) is entering essentially the same Date data twice: field 1 contains 6/19/2014 and for field 2 they enter June-14 (the month and year from field 1).
I suspect there should be a way to derive and then reformat the display of field 2 based on the contents of field 1, such that the user only has to enter the first. Is this true? If so, how?
(I know there really is no need for the 2nd field, but existing reports and queries are built around it -- so for right now, I'm just trying to eliminate the duplicate data entry)...
I am building a database to track contract of employees so that I can know which contracts are valid and which are expired.
My table has the following fields:
ID (Primary key) Employee ID (Foreign key to link to the employee table) Start_Date End_Date Status (Either valid or expired) Challenge
I want when I enter the end date, the system checks the end date against the current date and fills in the status field with either valid or expired as appropriate. For instance if the contract end date is March 10,2016, the status must be filled in the word valid.
I am building an Access database for a client. It is an employee staffing database. With that being said they would like the ability to automatically populate the "T2PPCD" date field based on what is entered into the "Report Date" field. (Same table)
The date is the Monday after 180 days from the report date.
I already know how to get it to auto fill 180 days from the "Report Date" but I'm not sure how to tell it to give me the Monday after 180 days.
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.
I have a problem with my access base and i would like your advice. I have two tables in my base, in the first “Data” I have a field which name “Mtt”. I would like to transform the field name “Mtt” by the name of month preceding (if i‘am in August, I would like July like in the base DataAfter) but I would like make it by the code but i don’t know how can i do this ?. Is it possible to use the function Now().
In the second Table “Database” I have a lot of field and I would like insert a new field each month. I would like take the data “July” in the base “DataAfter” and insert this new field in the table DataBase. I would to do this operation each month. How can I do this?.
I have an address field currently formatted 1 Streetname, city, post/zip code it is displayed on a form within a multi-line textbox. I'd like to change the commas to a line return so it will display
1 streetname city postcode
I was thinking that a small macro might go through each record and change the comma to a line return? But not sure where to start exactly?
I have a table with postcodes (1.6M rows) that have had all the spaces removed. I want to match another table to it and ordinarily would have removed the spaces from the postcodes in that but I think because of the vol (2.5M rows) Access is reluctant (it either crashes or only updates a few thousand). A better way would be to reformat the postcode using some SQL or VBA code. The trouble is the postcodes are different numbers of characters and the space would either need to be after the 3rd when there are 6 characters or after the 4th when there are 7 characters. eg PO121DL becomes PO12 1DL PO91UL becomes PO9 1UL
I can concatenate fields and count characters but am unsure as to the best way to approach this in terms of coding and structure? Using Access 2002.
I have a report that displays simple date fields. One of the fields is "lease execution". On the report, I want "lease execution" to display differently depending on the date the report was run.
So:
- If the "lease execution" date is more than 120 days away from today, I want it to display as Q YYYY. - If the "lease execution" date is between 120 and 90 days away from today, I want it to display as MM/YYYY - If the "lease execution" date is 90 days or less away, I want it to display the normal date MM/DD/YYYY
I was thinking I would need to do DateDiff() to figure out an amount of days that's between Now() and [Lease_Execution]. Then based on that amount make the report show it differently. Pseudocode would be: if DateDiff() = 40, then display [lease_execution] as MM/DD/YYYY
I am creating a repayment schedule (as a report) and I want to display a series of fields as a column which return (show) a date one month after the date in the field above.
The first repayment date field (Line 1) will show a date one month after the loan was paid out. the Next field below will show the date one month later.
I can't simply insert the "Date + 30" because that would get out of since over the year. If the loan was issued on say the 15th of January, I need the first field to display 15th February and the next would be .... 15th March.... Yes - You've got it!
Now I could do that in Excel, but I don't know how to get Access to do it.
We use access to enter our service tickets in at work.What we have are three date fields.
Call Date Start Date End Date
We are 24/7 operation.Currently all 3 just autopopulate with the current date.What i would like to do is ADD a CHECKBOX next to each Date Field.And make it work like this.
1. let them autopopulate as they are currently 2. if you end the call AFTER MIDNIGHT (the next day). CHECKING the box would automatically populate yesterdays date in each of the fields that has the check box CHECKED
Is there a way to show the earliest and latest dates of a report generated by a non-date field?
E.g. I generate a report based on Food, and it'll list the days that this food is associated with. Is there a way to show the first and last day that appears in this report (i.e. the range of dates that the report shows based on the food selected)
I have a date and time stamp in a Date/Time field of General Date format (3/1/2006 7:52:25 AM).
I wish to select query on the table's Date/Time field by date portion only (3/1/2006) and not include the time portion (7:52:25 AM) of the field.
Using this expression in the query's criteria - "Between [Enter Start Date: (MM/DD/YY Format)] And [End Date: (MM/DD/YY]" will not return the date ranges as desired without also typing in the full time string.
How can the date integer be parsed out and the query properly expression ed on the criteria field without using VB?
I have a query based on a table which has a date field. the field both in the table and the query have the time also in the date value so when I try to query on a date I get nothing if I copy the date and time from the field I will get the result for that record if I just use the date I get nothing. I have tried the format which does display just date but if you click on the field the time is also there You must be able to query for a date only and get all the records.
In VBA, how to compare date variable with date/time field from table, as when defined the data type in table, date/time option is the only choice for date data type even though I don't want the time portion.
I am using Access 2007 and I have had a curious issue arise.
I like to use the Date Picker feature on all my forms to make it easier to enter dates. I have set up each table's date field property to "show date picker for dates", as per attached (.jpg)
When I use that particular field in a form, I also set up the field to allow for the Date Picker option (date picker form property sheet.jpg)
The curious part is that not every form that I set up actually shows the date picker when I select the date field. Some forms it works perfectly, others it doesn't.
I have a table which has a form as a front end. One of the fields is for the date, I put the date in fill in the rest of the form and everthing is ok. Its then time to add more info to the database under the same date, so i put in the same date on the form but instead of adding onto the existing date field it creates a new row within the table. This means I have 2 rows within the table with the same date, and all i want is one date and you add onto that date range until the next day?
I am trying to compare a value in my databse produced by the date() function, short date format, to one that is exactly 24 hours after the value recorded by the date() function. If the value in the database is 24 hours prior to the current date(), I need to flag a text box a diffrent color to alert the user. I am unsure on the If statement that I will need to produce this result.
Any help would be greatly appreciated!!!! Thanks Mikeco555
i have just started to use access and i know how to insert the current date into a field using date() but i am not sure will this change the date everytime i open the form ? i want to create a form for invoices that shows the date the invoice was created and doesnt change if i re open for editing,
Working in MS Access 2010. Data must be entered and updated in Access only. Trying to figure out if it's even possible to auto populate dates in multiple fields in a form that are triggered when I enter ANY date in a primary date field on the form. I have close to half a dozen fields I'd like to do this for but here are a couple of the fields I'm working with:
Re Insp Date 90 Day Notice(Form field to be triggered and show a date 90 days prior to the trigger date) Re Insp Date 60 Day Notice (Form field to be triggered and show a date 60 days prior to the trigger date) Re Insp Date (Trigger date)
What I'd like to do is enter values in the "Re Insp Date" field (in the form) and have it trigger/auto-fill/auto-populate the "Re Insp Date 90 Day Notice" so that it shows the date 90 days prior to the "Re Insp Date" and the "Re Insp Date 60 Day Notice" so that it shows the date 60 days prior to the "Re Insp Date"........all this, and have it auto-update the queries and tables its linked to.
I've tried looking online and in instructional material to see if there is a formula, equation, macro, an expression or VBA coding that I can use to execute this but am having no luck.
The MAIN OBJECTIVE is to have the the linked query and table update automatically. Possible??
I have tried doing =([Re Insp Date])-90 but no dice...
I have a make-table query that pulls all the fields from 1 table (MainTable), and creates a new table with a date stamp based apon a form value entered (New Table = MainTableWithDate).
Currently, I setup the query to pull info from the form field like this:
DateField: [Forms]![frmmain]![DateField]
However, when the make-table query is done - all date fields are blank (all other fields are correctly created), and when I look at the new created table (mainTableWIthDate), the typeassigned to the date field is "Binary" (in the form, I've specified LongDate).
I am extracting information from a datawarehouse with a make table query. I will run this queary one time to create the table. Then I plan on creating an append queary to extract the same fields on a weekly basis. I want to keep track of the datasets that i extract weekly. I thought I could create a field with a julian date or the week of the year. Does anyone know how I could create this field in my append queary? OR any thoughts on how i can keep track of the records appended each week?
Having issues with getting auto update of Yes/No field dependant on todays date.
When users open the form the code runs through and checks all entries and updates the records if either of the dates in two separate fields is less than today. The issue I have is when some users are already using this form a second user cannot open it. Error shows other user information as having the form locked and code halts at highlighted spot below. Is there another way of ensuring this field is updated to Yes or No if the date in the field is less than current date.
Code: Private Sub Form_Open(Cancel As Integer) Dim rst As DAO.Recordset Dim dbs As DAO.Database Set dbs = CurrentDb
