Hi I am doing a date expiry query which has worked for one of my other queries i have copied the same criteria for another table but for some reason it does not work now
Below is the SQL i am using
SELECT Computers.[Machine ID], Computers.[Machine Type], Computers.User, Computers.Department, Computers.[Installed Applications], Computers.[Serial Tag], Computers.[OS Installed], Computers.[Pc Brought]
FROM Computers
WHERE (((Computers.[Pc Brought]) Between DateAdd("d",30,Date()) And DateAdd("d",-30,Date())));
I have 3 records which are within the criteria but for some reason i do not get these.
Any idea why this does not work but works for my other query
the below script in my query is to automatically increase the date by one year, this works fine but i would like to be able to back date the script if necassary.
i have checked the properties and the field is not locked. but it wont let me change the date.
Hello, im new to this forum, and i need some help fast.
I have a database which is a list of all of member with some information and an expiry date for there membership. What i would like to be able to do is run a query and for it to search a table and give me the names of the people whos member ship has run out or is going ot run out within a month of the query being run.
I have tried several things already but access isn't realy my thing, i do Visual basic .net .
If any one has any ideas about how i could sort this would you please be able to respond, thanks in advance!
i need a method to store an expiry date. i have a column prepared in my table but i do not know how to reach the desired result.
the date will always be different depending on certain criteria
the course length. some courses we have are valid for a month. some for 6 months. some for a year and some for only 14 days.
i can use the date add to add the date period to the purchase date but i do not know where or how to store course length to be able to use it.
i have approached this before and i had a column in my courses table as below
("m",3,Date())
i pland on haveing this type for each course and varying it as applicable but this did not work and apparantley i was breaching the fundamental rules of relational data.
What I am trying to do is create a stock database and iv imported a blank version of the northwind template and filled it in with my own products which is fine up to a point.
What i also have though is i get weekly reports of the stock of each product with their batch numbers, expiry date and quantity remaining for that individual lot sent to me from another company. (So i might have several lines with the same batch number and expiry date but diffeent quantities) So what i want to ask is:
Is there any way for me to combine the data in the weekly reports with the stock level data in the northwind template?
What i want to be able to have is where somehow i can see the total quantity of eac product with the same batch number and expiry date and then it tells me when that batch has expired and it also automatically deducts the stock from my total stock when it gets to within e.g 9 months of the expiry date as we will not likely sell those.
I need to do this as at the moment all i can get from my database is how much stock has been ordered, sold and what we have in total on hand but if the stock is close to expiring then i wouldnt know and i need to be able to see when to order stock in advance as i want a minimum of 6 months cover and ideally 9 months cover as orders for stock dont come in straight away.
Also, how i can import the data from the weekly reports into excel/access easily as they come as word files presented as a table but the data is put in frames i think and iv tried removing them but then it all looks messed up.
I have a query which shows data about certain checks that are carried out employees. One of the fields is Expiry Date of the check. What I want is for the query to show only records that have a expiry date which has expired or is going to expire within the next three months of today's date. But I cannot work out how to do it!
I have a query call courses and field name expiry date I have add additional field within the query to show how many day left and how many days over the expiry date
Code : DayLeft:[expirydate]-now()
but i just get a error when i run the query
I also would like to ask is this possible to be done within the table e.g adding another field dayleft and some how add formula to calculate number days left or over due .
I have main form call frmemployee and subform call frmlcourse subform the main form hold's employee information e.g. first name addresss the sub form hold's all the training courses that the employee has been on
I have a text box called txtnextcourse due to display when the next course due date is
I am very new to access and I would like to pull some information from my tables.
I am using this for training records, I can calculate the expiry date of certificates using a query. What I would like is a report (even better an alarm of some sort) to show when things are due to expire. I can't seem to find this option as a report.Also, is it possible to have reports go into excel?
I have a form (Access 2010) in which i insert contracts; each contract has a start and an expiry date, but instead of dd/mm/yyyy i would like the user to be able to insert just the year, while the day and month are predefined values and they are automatically inserted (i.e. 01/10/yyyy; the year being the only value that changes, and it is manually inserted by the user).
I would like to set a pop up remainder (on form load) x days before the expiry date, but, because too many of them have the same expiry date i am wondering if the reminder can be set on different days, based on another field (i.e. partner location [country]).. i.e. reminder for contracts with Austria to pop up 60 days before expiring, for UK = 67 days and so on.. or even a specific day for each, i.e. for Austria = 01/08/yyyy, for UK 01/09/yyyy).
I have a task completion database. Each record of model consists of an expiry date. I need to review and update the records before the expiry date. Each model may have more than one record as I have updated the models a couple of times. I would like to select all of records with the expiry date less than 30 days so I can plan to review and update them on time. How to select all these records.
We work in 7 states I want to build a skills database so i can search for particular skills, sometimes excluding people based on home state for travel reasons..
I have a table for logging experience for employees related to a given requirement.
The requirement is that any employee, to maintain proficiency ("be current") , must maintain an event count of minimum 5 within last 3 months. Or else the employee is not current and other measures must be taken.
I want to know when the currency expires for each employee (= at which date does the employee no longer have a count of minimum 5 within last 3 months).
I want to have an input mask on an 'Expiry Data' Field so that the input method is 'MON-YY" and I need access to realise it as a data. And then I also need when a user opens a record an anything that is 2 weeks from expiring I need an error message to pop up.
I'm using access database. i want to code my database so that my database automatically send email notification to client and also to admin of loan expire information ( Expired date and Loan Limit which is already in my database).
I have built a query to calculate the expiry dates of training courses but I am trying to input a criteria so that only dates within 90 days of todays date show. I am using Date()<90 but it doesn't return the correct information. What the criteria should be for this?
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"
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.
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 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?
I have a form with Date of Death (DOD) field. I would like update DOD from a table dbo_patient into Z_Patients table.
I have set the datatype as Date/Time in the form for Date of Death.
Code: Private Sub Update_DOD() Dim rcMain As New ADODB.Recordset, rcLocalDOD As New ADODB.Recordset Dim DOD As String rcMain.Open "select distinct PatientKey from Z_Patients", CurrentProject.Connection
[Code] ....
However I am getting some error Run-time error '-2147217913 Date type mismatch in criteria expression in section below.
Code: CurrentProject.Connection.Execute "update Z_MAIN_Processed_Patients set DateOfDeath = '" & rcLocalDOD!date_of_death & "' where PatientKey = " & !PatientKey
Using access 2010; i have a form that includes a date field. Is there a way to force the user to only choose a month end date? When the user clicks the date from the popup, they may use 9/1/2013 when the mgr. want them to use only 8/31/2013. I am thinking validation field to put a msg but want to be able to force it not the option.
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
Table 1 has all the items I am trying to sell with sell by date after which I cannot sell this item. Then in Table 2 I have forecasted sales. So now I am trying to calculate stock consumption to see if I will be left with any stock that I cannot sell.
So now somehow I need to deduct sales forecast from my stock holding but it needs to go by date i.e. consume all stock for Item 1 with date 16/09 before moving to Item 1 with sale by date 23/09.
So based on the attached example, I can see that on 16/09 I will consume only 5 cases from sell by date 16/09 and another sale is 18/09. So that would give me information that I will be left with 95 items dated 16/09, which I cannot sell because they will be out of date.
Ideally I would like also to include the logic that if Item is out of date it would move to the next sell by date.
So in this case sale of Item 1 forecasted for 18/09 (94) would consume the whole stock (50) with date 23/09 and another 44 from date 01/10
For Item 2 I can see that units with Sell by date 30/09 will be consumed on 25/09 and I will start taking stock from next sell by date which is 14/10.
