Creating Date Field For Reminder Date
I want to create a field for displaying a 90 day period date before the time of renewal.
This date will be displayed to remind me to send out a notice to a client and will be based on the actual renewal date field.
Can anyone please provide some insight into creating such a field.
Also, I will be creating a similar data field that will be based on 30 Days after the 90 day notice that I send out.
Thank you in advance...
Hello, In need of some help on something fairly simple (well when you know how) I have a form with a subform, in the subform I have text boxes showing employee names (in a continues form) and reminder date text field boxs showing a list of reminder dates. I would like a message to be displayed when the user enters the main form if a reminder date is the same as whatever todays current date is and displays the Employee name its related to, I think I need something along similar lines to the below code, on the on load event of the main form but this doesn’t work :o Private Sub Form_Load()if("d",Now(),[REMINDER_DATE]) then MsgBox "Reminder date for this employee is today: " + [EMPLOYEE_NAME]End sub(The reminder date name in the query/table is REMINDER_DATE and is called textbox_reminder_date in the subform)Any help appreciated, thanksKevin
This is my problem!!! Bit new to Access, so here goes! I am setting up a claibration database for test equipment, what i need to do is to set up the database so that say a week before a calibration date is due, the database will flag this up, say display a reminder!! Can anyone tell me is this possible and the best way to set this up????
Sorry to open with a question but its usually the way, i'm quite an experienced computer user but never used any office products before and just thought i should learn as i'm losing money every week by not keep track of things properly.
So then to my question..
I've made a table to cover all the orders i have had placed, this includes a date column for when that order was placed. I just wondered if it would be possible to have some kind of system where after 7 days without any acknowledgement from me it either pops up or turns red or something similiar. I'm thinking the acknowledgement could be the yes/no box and i tick that when i recieve the item back, if i dont it either pops up or turns red or something :confused:
any help much appreciated
I one of my tables, I have a date field (DateCompleted) with the format: mm/dd/yyyy
Now, I want to create a query which would create another field (DateExpected) by using the date in the above table and adding 5 months to the date.
The only issue is that for the new field in the query I want it just to have mm/yyyy format.
For example, if the original date in the table is 04/05/2006, after running the query, I would have a new field 09/2006.
Does anyone know the exact expression I would put in the query? Thanks. The expression would be put in the "build" area.
Thanks for your help!!
Anyone know how to create a combo box that has unique month and year entries from a table (month in one column and year in another - i.e. 2 columns)?
I've been able to get the textbox of the combobox to output the correct format using a custom format but it does not affect the combobox data. Also, I'm not sure how to separate this by two columns...
Any help would be much appreciated!
I am still newb for access, doing a database project now.
I have an equipment list which stores all the information about the equipments.
The equipments have manufactured date and expiry date.
Now I need to track down the equipment before it overdues as I have to send the equipment for quality control procedures...
So how can I use Access 2000/2003 to generate a weekly reminder (for example every monday) reminder for myself... Can Access do this?
The reminder will include the list of equipments that will expire within 2 weeks and the equipments' expiry date.
Any help or suggestions in advance will be appreciated, thanks....
I'm sure there's a simple way to do this...
I have a field that stores date/time of an order. ie: 1/9/2007 08:24:00 AM , that kind of thing.
I ulitmately need to get this into a table and export (using VBA) the table to a csv file WITHOUT the time part. That's the first problem. The second problem is the company that wants the csv file wants the date to have two digits on the month & year.
So, I set the format in the query and the table that the data appends to at Short Date. I figured I could worry about the "2 digit" problem later. It looks fine on the screen but when I export, I still get the time.
I created a query that completely strips the time from that field, and it doesn't show up in the table, but when I export it, the time STILL comes but it's all zeros.
In my export spec, there doesn't seem to be a way to exclude the time...
I've considered trying to turn my date field into a text field, or doing a find/replace in the text file to get rid of the times (since now they're all the same "00:00:00")...but I'm sure there has to be a way to get around this without having to take extra steps like that.
I tried DATE([PO_DATE]) but of course that doesn't work. It tells me I have the wrong number of arguments. I even went so far as to try something along the lines of DATE(YEAR([PO_DATE]), MONTH(PO_DATE]), DAY([PO_DATE])), but it still said wrong number of arguments...
Does anyone know how to change the colors for both the background/font on a report's date field. I have three criteria: yellow/black for a value 30 days past system, green/black for a value 60 days past system, red/white for a date 90 days past system. It might seem nit-picky but this is what my client wants. Any help would be greatly appreciate and thanks in advance!
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 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!!!!
How can I create a field in a query, extracting the date portion of a date/time field? The format is mm/dd/yyyy hh:mm:ss. I need just the mm/dd/yyyy so I can prompt the user for a date range, without them having to key in the date mm/dd/yyyy 00:00:00 to mm/dd/yyyy 23:59:59. Please Help
I have an Access database that tracks grievances(Customer Service), the "closing date" generates quarter reports/Semi Annual Reports which are sent to others. There is a problem with users entering data or back dating closure date, after the report has been distributed. I need to know who is doing this and also I need to stop this from happening. I was to trusting in my design and need to lock it down. There was only one person entering data, now there are 5. Any suggestions??
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.
I have a "valueDate" field in form. Before update this field i check if the year entered is open for data entry if yes nothing happens the user can continue entering data but if falls in a year that is closed a message appears.
The problem is when a user click the ok button on the message the focus goes to the next field.
Anybody can help how i can leave the focus on the "ValueDate" field until the correct date is entered?
Here is my code before update of "ValueDate"
insYear = Format(Me.ValueDate, "yyyy")
DateStatus = checkValidDate(insYear)
If DateStatus = "NotAllowed" Then
this will return the following error:
Run-time error '2108'
You must save the field before execute the GoToControl action,
The GoToControl method, or the SetFocus method.
Thanks for your help
Edited by: andreasg60 on Sat Apr 22 13:10:31 EDT 2006.
I am creating an online post, similar to a guestbook. When a user submits an entry, I am storing the date and time in a column titled "Timestamp", which is formatted 8/4/2005 9:16:58 ("General Date"). I am running the table through a query which is then posted on the web.
What I would like to do is this: I would like to use Timestamp to display the date that the entry was submitted, but not the time (basically, "Short Date"). Can I exclude the time using a query without changing the actual data stored in each record of the table? I understand that the time stored is completely different than the time displayed, even in the table. How can I do this?
Here's my problem. We maintain a list of items that are sold on ebay. We keep track of all items purchased by individuals and the dates that those items were purchased. We guarantee shipping by 6 weeks after the sell date...
Therefore, I am trying to create a query that will show me all items that are 6 weeks and older and have not shipped. There is a column that says date purchased and one for date shipped if an item is already completed. Any suggestions!!??!?! :confused:
A database front end which was built by somebody else got handed to me to do some updates (as the person who created it is no longer with the company).
The database currently has roughly ~30 reports, about 15 of which are pulled daily, and the rest weekly/monthly. Part of the report criteria selected by the user are beginning and end dates. Currently the dates are input via text boxes on a form, then referenced by the SQL statement which generates the report data.
The users want the text boxes to be changed to either a drop down list or a calendar which they can just click on a date instead of typing it in. I know .NET provides controls for a built in calendar which you can use for date selection, but this database is simply an Access front end, so I cannot use .NET.
Is there an easy way to accomplish this via VBA?
Is there a way that I can calcualte a total to the current date for the year?
There have been posts now and again from users that need to have 'custom' ID's for their invoices, etc.,
'IN-04-20-07' or 'CA-02-2007'
This link to an Access Watch might help someone out.
Create ID's With A Date
Im not sure where this one belongs.
I need to write a query that shows the results grouping data into Date Groups e.g. Quarter 1 2005. While I think it would be relatively easy to write a simple if statement to group the data for a short period (e.g. one year) the report is based over a longer period and I also do not want to update the report as time moves forward.
What I would like to know is there a generic method of creating date groups such as Quarters, Year to Date, Rolling Twelve months etc.
I know some of this is possible using some clever expressions but I would like to avoid having to continually changing the query.
Any advice anyone can give me would be much appreciated.
I have a form showing projects and on this a sub form which calculates the number of hours worked on a project on any given day (it uses a simple stop watch routine with start and stop buttons).
What I would like to achieve is to be able to create an invoice report which will show the time worked for any particular month.
I can create a combo box in which I can select from a list of 12 months, but I need to differentiate between the year as well. For example I will want to issue an invoice in January 2006 for December 2005 – how can I do this?
I don’t need the invoice to show every day’s work, just to be able to calculate the total number of hours worked in a particular month.
Any help would be gratefully received
I am stumped: After importing from an outside source (an excel spreadsheet) I have to change the short-date format
that I have just imported into a very specific custom format: "yyyymmdd" Ex: 20040828. How do I convert into this
new format? Help will be greatly appreciated...
I have a database with several clients who have a series of appointments. I want to be able to create a report of all the clients to be seen during a range of dates to be inputted by the user. All help would be greatly appreciated.
I am just starting to work with Arrays. I want to pass a date module the holidays in the current month. I have a table with all the holiday dates. I can read the table just fine.
I am not sure the syntax for adding the returned date(s) to the variant.
I am using this borrowed code
Public Function dhPreviousWorkdayA( _
Optional dtmDate As Date = 0, _
Optional adtmDates As Variant = Empty) As Date
' Return the previous working day before the specified date.
' Modified from code in
' "Visual Basic Language Developer's Handbook"
' by Ken Getz and Mike Gilbert
' Copyright 2000; Sybex, Inc. All rights reserved.
' date on which to start looking.
' Use the current date, if none was specified.
' adtmDates (Optional):
' Array containing holiday dates. Can also be a single
' date value.
' Return Value:
' The date of the previous working day, taking
' into account weekends and holidays.
' ' Find the next working date before 1/1/2000
' dtmDate = dhPreviousWorkdayA(#1/1/2000#, Array(#12/31/1999#, #1/1/2000#))
' ' dtmDate should be 12/30/1999, because of the New Year's holidays.
' Did the caller pass in a date? If not, use
' the current date.
If dtmDate = 0 Then
dtmDate = Date
dhPreviousWorkdayA = SkipHolidaysA(adtmDates, dtmDate - 1, -1)
When I build my Select statement, I assume I add the first returned date with a statement like:
adtmDates = "#" & rs![HolidayDate] & "#"
When I find another holiday, what is the sytanx to add that one too?
I searched the forums and I couldn't find my answer.
Here is situation: For example:
We receive an appeal 10/1/06, the State mandates that we complete the Appeal in 45 calendar days. We send the Appeal to claims for handling and sometimes they don't reply back in time and we get fined. So, I want a due date on the form to show the date a reply is needed to the State.
I have a received date field
I have a sent to claims date field
I have a due date field
I have a State field
I have a follow-up days State field
Can you have a date add function that adds the number of follow-up days depending on the State, there are several different date rules, some States are 20 calendar and some are 30 calendar days.
I might be better off just running queries that lists the claims that are close to the due date.
And I might be totally off here, so if you have any suggestions or questions, let me know.
I have a report that shows the mileage my wife, being a salesperson, travels to deliver her Avon. It's based off of a table called Orders...
I have seen this done on other reports, but cannot figure out how to do it... I want to be able to specify a date range prior to the report pulling up only the sales orders in the specified date range... I figured having another table and form would help me in this endavour, however I can figure out how do link the 2 together and utilize it to filter...
I have the fields already set up in the report that I need, I just need to know how to make it filter...
I'm struggling to creat a filter for my form,
Basically i have a database full of contacts with their birth dates and i want to call them on their birthday.
I need a filter that will only show the contacts that have their birthday on the date i select using an activeX calendar control. I am struggling to create the filter as the birthdates would be 1957/04/12, 1988/04/12 and todays date is 2007/04/12. Is their a way to use the Like command to check corresponding months and days only.
I have tried everything i know and still come up short.
Your help on this is much appreciated!!!
I need to create a form where a user can be selected and a date range (which will be a single date field, select two separate dates), then preview a report where the data will show accordingly.
User field - Empl
Date range - TestDt
Report - rptTest
I've never actually created a form that has drop downs for dates and users, and the data selected will filter all the data and report it. There are other fields, just need to get the first piece to work and I can figure out the rest. Thanks for the help!
I am trying to create a SELECT query to search for cases initiated in a CY (and later, FY). Rather than create a new query for each year, I have an input box to prompt for the CY. Then I need to create a beginning date and an end date somehow so I can use the select query to look for cases in that date range.
Here is what I tried, and it no worky-worky.
Private Sub btt_CYcases_Click()
Dim CY, begCY, endCY As Date
On Error GoTo Err_btt_CYcases_Click
CY = InputBox("Enter Year", "Enter Year for Report")
begCY = " #1/1/&CY#"
I am confident in my ability to create the query after I get begCY and endCY set.
I have a small database that tracks who has a patient's paper chart as it's passed around the office. Whenever a chart leaves the record room it is put into the database. Whenever it is moved from one person to another, that person uses a form to update where it is. What I'd like to do is add a control that changes the "DateSent" field to NOW whenever anyone changes the field "SentTo".
What I've tried is linking the form to an update query, so that any time the SentTo field is modified the DateSent is udpated to NOW. However I can't get it working.
Any suggestions on the simplest way to accomplish this?
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.
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.