i am ultimatley aiming to end up with 3 Fields - Expiry date (working), Status (working) and Remark (not working)
Remark is based on this excel query
=IF(AL3<NOW(),(IF(AL3<(NOW()-365/4),"(3) Expired more than 3 months ago","(2) EXPIRED during last 3 months")),(IF(AL3<(NOW()+365/4),"(1) Expires within next 3 months","(4) Expires in more than 3 months time")))
which i need to change to an acess query.
Where AL3 is the expiry date.
Please help
Hi there, Thanks for having me as a member of your forum!
I'm completely new to all things Access (apart from using databases set up by others!), but now as a small business owner need to set up a simple inventory database.
I've got the database set up and all historical inventory transactions are entered. What I need now is the abiliy to run a report that will allow me to see what the inventory status was on a specific date. I will gladly send the database to anyone that can assist. Can anyone help me achieve this?
I've puzzled over this for days now without success! Thanks for any help that can be offered.
Hey guys, I am trying to creat a query from my Status table, but having NO luck getting it to work. Basicly the Query is going to create brand new columns with the Status entry from the row selected. I have been able to create a query with 1 status column, but I cann't seem to figure out how to code it where it creates a 2nd. Heres what I have
SELECT SiteStatus.Status AS MailStatus FROM SiteStatus WHERE (([SiteStatus].[Site Component]="mail"));
Some how I need the Where statement to go along with the new Mailstatus column and then create a new column with the same effect, have it select the status from the row what contains the "component" in the "component" col. Anyone get any of this cause its starting to confuse me even now. Using Access 2003
Hi folks, This is my first posting to this forum and I hope someone will be able to help me out with this. I have a table called FILETRANS in a DB.Essentially this table is to track the in-flow and out-flow of files in a documentation unit. The schema of the table is as follows Filetrans_id Pk int Auto Files_id int Filetransdate date Filestatus_id int
A filestatus can either be 1(IN) or 2 (out) I need a query that will give me a list of all files whose last recorded status is 2(out) and vice versa. Any help will be greatly appreciated. Regards Charles
I'm trying to create a report eventually, however, I need to get my query set up.
My report should look like the following when complete.
Timeframe (mth,qtr,year) # of Total Projects Completed - regardless of status # and % of Projects Completed within Requested Delivery Date # and % of Projects Completed within Committed Delivery Date # and % of Projects Completed within Requested and Committed Delivery Date
I have 3 different fields to base my calculation from: Actual Delivery Date Requested Delivery Date Committed Delivery Date
I have created 2 new fields that calculate the number of days: [Actual Delivery Date] - [Requested Delivery Date] [Actual Delivery Date] - [Committed Delivery Date]
I'm trying to set up a "Status" field to indicate whether the project was completed "Within Requested", "Within Committed", or "Within Requested and Committed", however, I'm stumped. I've tried an Iff statement to no resolve. I don't want 10 queries just to get me there either.
I have a database where there are 3 tables. table "d_cases", table "d_user" and table "d_status"
The table "d_cases" has the users and statuses only with a number. Peter has ID 2, John ID 1. There is status ID 1 that is New, ID 2 Started and so on.
How can I run a query that will show me the names and status instead of the numbers?
I need to design a query that contains about a dozen fields for stationery items along with another dozen fields (checkboxes) to show whether these items were delivered (Yes, I know, not great db design).
I ran out of space with the the query criteria window.
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've been avoiding this project but it has come up again. I have a file given to me each month. It contains a member number and status. The status could be any of the reason codes the user comes up with. So far there are ten. I imported the file into an access table. The users need a report summarizing the status changes if any. If there are any changes; the reason code will have a X under the reason code which pertains to them. I ran the unmatched query; but that gives me if the person was added or deleted. How do I search the reason code fields to check if there have been changes. If I ask for the nulls or is not null for each field; How would I know if it was null or not null in the beginning. I need a way to check to see if for example: Smith did not have a X under reason code 1 for the month of June but does in the month of July. I hope I didn't confuse anyone. Please help Thanks
Does anyone know how turn the status bar off with code. I need to turn it off when the main form opens. Then when it closes I need it to come back on...
I have a database that has records at different statuses along the process, like "created", "submitted to finance", etc.
I have a form where I can choose the status from a dropdown and it will total the number of records at that status and display in an unbound field "Total".
My only problem is that when there are not any records at that status it will still display the previous statuses total. Am I missing something in my code?
Private Sub status_AfterUpdate()
If Not IsNull(Me![status]) Then Call CalcTotal Else Me![Total] = 0 End If
End Sub
Private Sub CalcTotal() On Error GoTo Err_CalcTotal
Dim db As Database Dim rs As Recordset Dim Q As QueryDef
DoCmd.Hourglass True
Set db = CurrentDb() Set Q = db.QueryDefs("qry_total") Q.Parameters("[status]") = Me![status] Set rs = Q.OpenRecordset() rs.MoveFirst
Me![Total] = rs![Status Total]
Exit_CalcTotal: DoCmd.Hourglass False Set rs = Nothing Set db = Nothing Exit Sub
I've already run searches on this but found nothing exactly matched.
Here is the situation that hopefully you guys, experts, can help.
I have several databases and on the main one (master one), I want to be able to show status of other databases, whether they are in use (meaning being open) or not. Is this possible?
I have developed several applications in code which take anywhere from 15-30 minutes to run, depending on the amount of data and local server traffic. I would like my clients to be able to tell that their process is actually running through some kind of "percentage complete" status bar or progress indicator, rather than their computer sitting there with an hourglass visible for what seems like forever. I would like to set this up where it runs from the form which triggers the code or the like.....any easy way to set this up or any code repository make this easier?
I have some code behind a button which take data from form and inserts in history table. while problem is that when ever i press the button the data goes to the table but the form went to first record. so how to stop the form on its current record
I have a form with all the navigation keys and it also lists your own name which you logged in with. Value comes from a string called strUser.
Now i have made a query (based on tblUsers) that returns me the user that is logged in and his status and acces rights. Criteria comes from the label showing the username on the form. I managed to get the value of that checkbox set to true whenever he logs in through a query on the login screen.
Now, whenever the user quits the application i want the status changed. So that the checkbox is unchecked when the user quits. this way the administrator can see who is logged in and logged out. How do i get it to false when he logs out. Thanks in advance
I have a databse that has 9 fields that are represented with Check Boxes.
When I display my form, I want the Check Boxes to be "Checked" if there is a "T" in the field that it represents. If the Field has a "F" or blank, I want the check box to appear unchecked. Then, the user will check boxes for each vialation the homeowner has and place a "T" of the appropriate field. When the form is re-displayed I want the check boxes to checked for each vialtion the homeowner has and unchecked for vioaltions the homewoner does not have ("F" or blank)
There are 2 different databases. The Homeowners database contains general information regarding the homeowners. The Violations database is a subset of the Homeowners DB that is opened from a Mainmenu with a selection for Violations.
The Violations database is not linked to or connected to the Homeowners db in any way. It is a distinct db with the following fields: Property Number - index @ Complainer - name of complainer Owner - owner of the property Infraction - Specific data on the exact violation Date Notified - When owner was notified How Notified - How the owner was notified - phone, in person, by mail Result - test describing the results of notification and correction Notes - Miscellaneous notes on violation Landscaping - check box - if checked - there is a landscaping violation - "T" in file Architecture - check box - if checked - the is an architectural violation - "T" in file Weeds - check Tbox - if checked - there is a weeds violation - "T" in file 6 more check boxes denoting violations.
when the violations form is first displayed, boxes should show a check mark when there is a "T" in any of the 9 specific violation fields. If the specific violation fields have a "F" or is blank, the box should not be checked.
If the owner has a new violation, the user will check the box and an "T" will be placed in the appropriate violation field in the DB.
How do I or can I accomplish this in Access.
This is a converted Foxpro database and I do not wish to completely redesign and re-write every form and file. I just want to make it look and act the same as it would in Foxpro.
I am not an expert Access programmer but have written samll apps.
I am working on a Student database for a school. Need changing the student status e.g. A student was enrolled in class Prep. and now promoted to grade 1. How do I solve the problem. I need this for a report (Student Details) Like on 1/1/2014 the student was enrolled in prep and on 1/1/2015 he/she was promoted to grade 1.
Hi, I know this is a fairly easy one. I really try to avoid asking answered questions, but I've honsestly searched through the forum with no results..
I have three statuses: 1 = Ongoing 2 = Complete 3 = Dropped
I want to force status 2, if IIf([EPsDL]/[TotalEps]=0;2;""), so that it would force on status 2, only when the calc. gives zero, and thus letting me change status freely, as long as the calc. is >0. I don't know whether to use IIf, nor where to put it at all. Would it work in a query, and if so - where to put it?
Where Status 1 = Open, 2 = Allocated and 3 = Closed. ---------------------------------------------------------------------------------------------- I want to get the count of number of queries which are not closed (outstanding) at any point in time.
Example: (with a time parameter) Input | Result 22/10/2007 17:00 | 1 22/10/2007 16:05 | 0 22/10/2007 14:00 | 2
I want to achieve this with just 1 query (not by using one query within the other) b,coz I want to further use this query from Excel VBA (write through Excel VBA and not store the query within Access)
I have a form that resembles a spreadsheet and have data in certain fields that is longer than the field is wide. (The widths are set according to client preference and widening the field is not the solution.) I was thinking perhaps I could use the status bar to show the data when the focus is on that particular field. Right now, the only help on the StatusBarText topic that I can find indicates that the status bar can be "programmed" with static text only, but I was hoping to make it dynamic, depending on the field in focus.
If this is the wrong approach (i.e. can't be done), does anyone have any ideas about how to use an external control to show (and possibly edit) data on a spreadsheet-like form?
The client has been using MS Works and is being dragged into Access only if the transition isn't too painful (for him, lol) and having an ability to look and feel like the Works interface is important to him.
just wondering if it was possible to have a box that returns the % completion of the current record depending on how much data/how many of the fields of the record have been filled out.
e.g. fields with containing data / total number of fields * 100
