Queries :: Task Completion Database - Search Records With Specific Expiry Date
Nov 17, 2014
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.
View Replies
ADVERTISEMENT
Dec 11, 2013
I have a database which is working ok where i have a set of tasks in a table with a bunch of irrelevant fields. On creation of a task i assign users to those tasks into a multivalue field(this is all fine).
Using a query i can report to each user what tasks they are assigned to easily.
To make the database more complex i would now like to assign these users to a task and then allow them to assign there portion complete (but not the whole task complete) but i can not assign a bool variable to a .value (can i?).
My thoughts are my database is just not set up for this by using the multivalue field to hold the assigned users?
View 1 Replies
View Related
May 7, 2013
I am using the following expression to calculate a completion date for a project which is based on the due date. My problem that I am running into is when I get to a project that is due on a Tuesday, it returns a date for Sunday when it should be Friday.
IIf(Weekday([Due Date])=2,DateAdd("d",-4,[Due Date]),DateAdd("d",-2,[Due Date]))
View 3 Replies
View Related
Aug 18, 2013
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?
View 3 Replies
View Related
Apr 23, 2013
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.
View 1 Replies
View Related
Aug 7, 2015
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!
View 2 Replies
View Related
Nov 26, 2014
I have a query from a table (to reduce the number of fields).The list is all the positions people have performed, as person could have one record another could have 10 the number is unlimited.
Fields are:-
-employee ID
-start date
-position
I need to find any records that were active ON or AFTER 01/09/2014. This will be 1 record for most but some could have multiple.I think it makes it more difficult that there is no END DATE.
View 6 Replies
View Related
Mar 5, 2014
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 .
View 2 Replies
View Related
Aug 11, 2014
I have a drawing register database which notes the revisions for all drawings issued. One drawing can be revised several times so I have a details table which notes the revision letter, date issued and the notes for each revision. Using this table I created a max date query to show the latest issue date and revision for each drawing. This query is used for a transmittal form for issuing drawings. Both the form and report is working perfectly.
The TBLTransmittal consists of the following fields
ContractName
IssueDate
SubSup
DwgNo (multi-value checkbox)
Using the TBLDwgRegisterDtls or the query QRYDtldDwgReg (which includes the drawing titles) I would like to create a query that would look up the latest revision at the date of issue and show the revision letter.
I started a query combining TBLTransmittal and TBLDwgRegisterDtls and in the criteria for DateIssued of the TBLSDwgRegisterDtls (which is when the drawing was issued by the architect to the contractor) I put "Not > [IssueDate]". This is filtering the information to show revisions issued up to the date we (the contractor) issued the drawing to our sub-contractor but I need now to pull the max revision only from this information.
Before I go I will give you an example of what I want from the end result
Drawing BK2-02 rev. D was issued by the architect to the contractor on July 17th. This drawing was in-turn issued to our sub-contractor (Sub "A") this Saturday, August 9th. The same drawing was revised this morning and rev. E was issued to Sub "A" today, August 11th.
I want the end report to show that Sub A received Rev. D on 09-08-14 and Rev. E on 11-08-14.
I'm wondering if another max date query on this new query would work? I'll try and see what happens.
View 2 Replies
View Related
Jun 7, 2015
I have a CSV file and want to convert it in a text format with some filtered data and with some formatting. This is an everyday task for me. So I made a table and imported the data in to it by the command :
DoCmd.TransferText acImportDelim, "fo Import Specification", "fo", FileName:="C:UserswelcomeDesktopfo.csv", HasFieldNames:=True
Actually I have a column "SERIES", contains various series like "EQ", "BE", "DR", "BZ", "D1" and so on. And one more column with the dates having 4 / 5 current months dates and one next months date and one next to next month's date. And every date has got several thousand records.
now the issue is that : After importing these several thousand records, I want to export it but with a specific date and with a specific series.
The other thing is that, these dates change every month so if hard coded, the problem will occur the next month.
I use this code for export :
DoCmd.TransferText acExportDelim, "NewFnoSpec", "fnoquery", "C:UserswelcomeDesktopFO Output.txt", True
this code is working fine but when the month will change, the code won't work.
Can we have a date & series picker attached to this query, so it can export the records with the specified SERIES & DATE.
I tried putting a textbox on the form named TxtDate and in a Query ( Design mode ) under the date column, in criteria I have put [Forms]![Futures]![TxtDate] and after putting this line, the query becomes empty and no data is there.
View 14 Replies
View Related
Feb 22, 2006
Hi,
Can anyone help. I am trying to search an access database for entries for a specific date i.e 26/02/2006
The below works fine, but how do i search for all entries for a specific date i.e 26/02/2006
sSQL="SELECT * FROM tavern_tbl ORDER By ReturnDate ASC, ReturnTime ASC"
Table name is tavern_tbl
ReturnDate / ReturnTime are column names
Any help would be appreciated.
View 3 Replies
View Related
Jun 6, 2005
Hi,
Is there a way to search for queries that use specific criteria?
Let's say I have 60 queries in total, but only 35 of them use the "Province" field as criteria. The criteria is set to retrieve all records that are in Province AB, SK, ON.
Suddenly we need to also include Province MB to all of these 35 queries.
Is there a way to identify these 35 queries (all the queries use criteria in the "Province" field). These are the queries that would need to be modified to include "MB" as part of the criteria.
I hope my explanation is clear.
Thanks upfront for any suggestions!
BJS
View 4 Replies
View Related
Apr 17, 2015
This works:
Code:
SELECT sum(iif(dDateClosed is null,0,1))/sum(iif(dDateFound is null,0,1))*100 AS Calcul
FROM Q_Defects
WHERE (Q_Defects.dAreaFK)=[Forms]![F_MainMenu].[cboStatsArea];
This doesn't, can't find out why...
Code:
TRANSFORM nz(sum(iif(dDateClosed is null,0,1))/sum(iif(dDateFound is null,0,1))*100,0) AS Completion
SELECT sum(iif(dDateFound is null,0,1)) AS [Found], sum(iif(dDateClosed is null,0,1)) AS Fixed, sum(iif(dDateClosed is null,0,1))/sum(iif(dDateFound is null,0,1))*100 AS Compl
FROM Q_Defects
WHERE (dAreaFK=Forms!F_MainMenu.cboStatsArea
OR Forms!F_MainMenu!cboStatsArea IS NULL)
AND (dShiftFK=Forms!F_MainMenu.cboStatsShift
OR Forms!F_MainMenu!cboStatsShift IS NULL
AND month(dDateClosed)=month(Date()))
GROUP BY month([dDateClosed])
PIVOT month([dDateClosed])
IN (1,2,3,4,5,6,7,8,9,10,11,12);
View 5 Replies
View Related
Jul 2, 2013
I am trying to allow a calendar to search through a text box with a large amount of data in which will have headings using the date so i wanted to use a calendar search. Hopefully this will be be like the find function in word where you type (or in this case select a date) then click search or next which will take you to that specific date.
View 14 Replies
View Related
Sep 26, 2006
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
Many thanks
View 2 Replies
View Related
May 25, 2005
I have a issue date and an expiry date feilds,
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.
any ideas
Expirydate: DateAdd("m",12,[Issueddate])
View 4 Replies
View Related
Jan 23, 2006
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!
View 5 Replies
View Related
Dec 9, 2013
Can a Form/Query warn when a date is approaching; like in a pop up or any other behaviour?
View 7 Replies
View Related
Nov 18, 2013
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.
View 8 Replies
View Related
Nov 20, 2005
Basically, i have a field where the expiry date of a credit card is input, but i want to set an input mask so that it loks something like this:
MM/YY but i cant find a way of doing this!
I then have validation text which makes sure that this value is either equal to or more than todays date! this looks like this:
>=int(now())
So the input mask has to accept this validation...
If you could help, i would be very greatful!
Cheers
View 1 Replies
View Related
Aug 7, 2014
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
Code:
=DMin("Expirydate","tblcourses","[ID]=" & [empID])
this works perfect. I have check box on my subform called ex course if this is true. I don't want the dmin to count that record I have tried
Code:
=DMin("Expirydate","tblcourses","[ID]=" & [empID]) [forms]![frmlcourse subform]![ex course]=false
View 14 Replies
View Related
Apr 29, 2014
I have built a custom search form in a MS Access 2010 database so that users can find specific records to edit. After entering the search criteria and hitting a Search button, another form opens up that shows the search results. This second form includes a command button for generating a report of the search results.
Right now, the custom search form and the search results form are both working properly, but the search results report is showing every record in the database instead of just the search results. This is true whether I access the report via the command button in the form or the navigation pane. I'm not sure if I need to correct my VBA code or the report's properties.
View 4 Replies
View Related
Jul 8, 2015
how to read a specific line in a CSV file (using VBA), to see if the phrase "There are no records available." is present.
If it is present, then I'm going to do a debug.print stating that there are no records to load - and then the script will move on to the next file. If the phrase isn't present, then I'm going to upload the file to Access, parse the information, and then upload it to a CRM. (I already have the latter portion of the code up and running....I just need to account for the first part, where I can determine if the file has data or not).
The structure of the file never changes. The first row is composed of eight column headers (Post Date, Card Number, Card Type, Auth Date, Batch Date, Reference Number, Reason, Amount) and (if) the phrase "There are no records available." is present, it will show up on the second row, in the first column (under Post Date).
View 3 Replies
View Related
Jul 26, 2015
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).
View 2 Replies
View Related
Nov 6, 2014
I am trying to create a button on an access form that will prompt the user to enter a search month and then export to excel all records in that database that match that month.
So, if the user prompt "what month" is October, then THE 30 records in the field [receivedate] that are in october (10/XX/XXXX) are exported to excel.
View 7 Replies
View Related
May 25, 2014
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).
So with the data below
tblProficiency
--------------
ProfID (PK, autonumber)
ReqID (FK)
EmpID (FK)
Completed (Date)
EvenCount (integer)
Code:
ProfID EmpID ReqID CompletedDT EventCount
10 2 1 10/04/2014 4
11 2 1 11/04/2014 3
12 2 1 12/05/2014 2
13 2 1 13/05/2014 5
14 2 1 14/05/2014 3
15 2 1 15/05/2014 2
16 2 1 16/05/2014 1
17 3 1 17/05/2014 3
18 3 1 18/05/2014 4
19 3 1 19/05/2014 3
20 3 1 20/05/2014 1
The result should be:
EmpID Expires
2 14/08/2014
3 18/08/2014
I need a hint for some SQL to do this.
View 4 Replies
View Related
