Trying To Create A Status Query
Sep 7, 2005
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
View Replies
ADVERTISEMENT
Jan 8, 2006
Ok the question may sound cryptic, but I have a large database with many forms that all contains VBA code that can be iniated via a button and will perform some time consuming work.
I would like to be able to view some kind of status or progress of the work performed. Perhaps even dynamically like in from 0-100% work done.
Example send some status text to either a popup form or even the bottom "statusbar" text area. The problem with the latter is that I know how to change it via VBA, but as far as I know it requires me to access and change it using form and object. But I need this from so many forms and objects (buttons) that I would need to do a lot of code rework to actually know where I am calling my VBA code from.
Is it possible to change the status text "universally", that is without regards to a form and object?
Or how else can I go about to display some status/progress text which will work from any form and object without passing form and object name.
View 4 Replies
View Related
May 25, 2005
i am trying to make a cross tab query that will give me the status of the unavalble rooms in my hotel
somthing like that:
romm num today today+1 today+1 today+2 today+3 today+4 and so on...
1 X X
2 X X X
3 X X X
4 X X
5 X
thas how i will see where do i have a free rooms in a range of dates that i want to insert.
please advice,
Thanks
View 1 Replies
View Related
Feb 4, 2007
Hi there,
i am writing a query that calculates a date
ExpiryDate: DateAdd("yyyy",[NATATable].[NATAExpiryPeriod],[NATATable].[NATADate])
which works
The expiry period is always 2 or 4 and the natadate is a date i want it to add 2 or 4 years to the date the NATA was done... works no problems.
Now i want a query in another field as a status
ExpiryStatus: Expired or Valid
ExpiryStatus: IIf([ExpiryDate]>=Now(),"Expired", "Valid")
Which works
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
Thanks
Kris
View 1 Replies
View Related
Sep 27, 2006
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
View 4 Replies
View Related
Jan 25, 2007
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.
Please help! :confused:
Thanks!
View 2 Replies
View Related
Jun 8, 2015
I want to run a query that returns a "small" or "medium" or "large" etc on each respective row against another columns respective numeric value...
< 500 = "small"
500 up to 1000 = "medium"
1001 up to 10000 = "large"
10001 up to 100000 = "very large".
View 8 Replies
View Related
Feb 12, 2007
Afternoon.
I have had a search through the forum however am unable to find anything...
I have two questions:
a) How do I check whether someone else is currently accessing a record / form?
i.e. Opening the form (SchemeDetails) like this:
DoCmd.OpenForm "SchemeDetails", acNormal, "", "", acEdit, acNormal
Forms!SchemeDetails.SetFocus
DoCmd.GoToRecord acForm, "SchemeDetails", acGoTo, tempVal
Can I check if someone else is in the specified record before I open it? Record Locking is (deliberately) set to "No Locks".
b) How do I check to see if a table exists?
i.e. If DataTable2.Exists = True then ...
Or something like this?
Hope someone can help me!
View 6 Replies
View Related
Jun 19, 2015
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 have attached the database (2003 format) ....
View 2 Replies
View Related
Feb 10, 2014
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.
View 6 Replies
View Related
Oct 16, 2006
Would anybody know how to insert a status bar on a form and control it.Need help on this.Thanks in adv.
View 1 Replies
View Related
Aug 30, 2007
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
View 4 Replies
View Related
Mar 17, 2005
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...
Thanks,
Ken
View 5 Replies
View Related
Apr 18, 2005
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
Err_CalcTotal:
Resume Exit_CalcTotal
End Sub
Thank you ahead of time for your help!
Adam
View 8 Replies
View Related
Aug 12, 2005
Hello all,
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?
They are all in the same drive.
Thank you all in advance. ;)
Access 2003
View 6 Replies
View Related
Dec 27, 2005
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?
Thanks. Happy new year to all!!
View 3 Replies
View Related
Nov 22, 2006
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
View 4 Replies
View Related
Apr 27, 2005
Ok, am stuck on this one.
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
View 2 Replies
View Related
Jan 22, 2008
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.
Thanks for any and all help.
View 12 Replies
View Related
Nov 24, 2014
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.
View 3 Replies
View Related
Oct 17, 2007
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?
Thanks in advance :)
View 1 Replies
View Related
Oct 22, 2007
Dear All,
I have a MAIN table which stores the most recent info of a record with
following details:
Unique_Ref_Num|Status|Dept
1 |6 | 1
and a second table called history which records changes in the main table
HistoryID | Status | DateStamp
1 | 1 | #22/10/2007 09:00#
1 | 2 | #22/10/2007 09:01#
2 | 1 | #22/10/2007 09:05#
2 | 2 | #22/10/2007 09:06#
1 | 2 | #22/10/2007 11:00#
2 | 3 | #22/10/2007 15:00#
1 | 3 | #22/10/2007 16:00#
2 | 2 | #22/10/2007 16:10#
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)
Any help will be greatly appreciated
--
Many Thanks
Baapi
:confused:
View 4 Replies
View Related
May 24, 2006
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.
Thanks,
- Terri J.
View 3 Replies
View Related
Dec 4, 2006
hi everyone,
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
possible or not? if so...how is it done?
thanks, james
View 4 Replies
View Related
Nov 21, 2005
Dear All:
I have a list of names (First, Last) which is send to a printer in a report generated from Access.
Does anyone know how I can add a field in the report thats called "Status" where when all who are printed, under "Status" it says Done?
Thanking in advance,
Dion
View 2 Replies
View Related
Dec 15, 2007
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.
Craigie
View 1 Replies
View Related
