I have one Query with the following:
Main.ID, Main.First Name, Main.Last Name, Main.Assigned Date
I created a Daily Report using this Query to tell me when I need to meet again with these people.
I have a Report that has the following:
Main.ID, Main.First Name, Main. Last Name, Main.Assigned Date, [Assigned Date]+30,[Assigned Date]+60,[Assigned Date]+90,[Assigned Date]+120,[Assigned Date]+150,[Assigned Date]+180
What I would like to do:
The current report shows ALL records with the requested information. How can I have the Query or Report filter out the records that do not have any of the review dates falling on today's date?
I hope I explained myself. If further explanation is necessary let me know.
I've got an unmatched query set up that works pretty well using a linked Excel file, and an Access Table. It compares a Name, Day of the Week, Start Time and End Time.
The problem is that some that I KNOW are matching are turning up as unmatched. It typically seems to happen when it's an "odd" time, with and End Time like 10:20 PM or 9:40 AM.
I suspect this may be because Excel actually sees dates as decimals and the way Access is interpreting that time is different and it sees it as not matching.
I need these "stored" as times, so I can't change the values to text, where I think they would match.
I've linked access to an external database and created a query to pull results from the linked access table that was between certain times on certain dates (i.e. between 11/12/2007 11:01 AM to 12/13/2007 12:01 PM). The query I have thus far is "SELECT * FROM TABLE1 WHERE DATETIME >= #11/12/2007 11:01 AM# AND DATETIME <=#12/13/2007 12:01 PM#". That works, but I want the date and time to be changeable through text fields on a form. Something like "SELECT * FROM TABLE1 WHERE DATETIME >= #forms.form1.startdate forms.form1.starttime# AND DATETIME <= #forms.form1.stopdate forms.form1.stoptime#". The dates are going to be automatically generated according to other parameters but the times are going to be entered manually. How can I accomplish this?
I am needing a fairly simple query to show records from a table that occurred between a set of dates specified by the user; I have quickly built the query using the wizard, however the date is stored as DateTime;
In the criteria, I tried - Between [Enter Start Date:] And [Enter End Date:] which returns an empty result (presumably because there are no records that match exactly, as they all have the time included?)
Is there a way to have the query show all results between the dates specified by the user, regardless of the time?
'WHERE ((OperationalRiskEventTable.DateReported)>=Forms!U pdateForm!UDateBegin And (OperationalRiskEventTable.DateReported)<=Forms!Up dateForm!UDateEnd)'
in a query by form.
The problem is that you have to enter a date in the between values for results to show. If I don't enter information into a different field such as Full Name but I enter in 40 into Age then everyone that is 40 years old will show. On the other hand if I enter 40 into the Age field but I leave the Date Reported fields empty then no results will show.
How can I change it so that I don't have to enter dates into the date reported fields for results to show?
hi. I am trying to write a code to sum the total cost of all jobs that are selected as 'yes' in a combo box.
Once the user selects 'yes' in a combo box, they enter in a cost for that specific job. Each client may have 10s of jobs. I need to write a code that will sum up all the 'yes' selected jobs for a client. I know it should be an 'if' statement but I can't seem to get it to work. I need this value for a report. How would I do this and where would i put the code.
Help is appreciated.
Here is the code that I was thinking of, I don't think its right but it might be a start.
Dim Count As Variant Dim CountImplementedTotal As Variant
CountCost = 0 CountImplementedTotal = 0
Dim rst As DAO.Recordset Set rst = CurrentDb.OpenRecordSet("ECM Details") Do While Not rst.EOF If rst![AUDIT ID] = Forms![audit info]![AUDIT ID] Then If rst![Has Measure been Selected] = "YES" Then Count = Count + rst![Total Measure Cost] End If End If rst.MoveNext Loop
Hi I have a database for maintaining procurement contracts which includes a start date for each contract and a frequency of reviews required (monthly, qrtly, bi-annual, yearly). I need to be able to generate a report that will tell me when the next review is due for each contract, taking today's date into consideration.
This is the setup so far:
tblRegistry - contains field 'Commence' for the start date of the contract and 'Review Req' to indicate how often a review is required (monthly, yearly etc).
tblReviewReq - contains field 'Review Frequency' (monthly, yearly etc) and 'Days' to assign the number of days eg monthly = 30, yearly = 365 etc.
Next I have a query that includes the above tables using the fields 'Commence' and 'Days' and have created a calculated field to give me the next review date ie contract start is 1/08/07, review freq is monthly (=30 days), therefore the next review is 1/09/07. "NextReview:[Commence]+[Days]"
But how do I get it to tell me the next review date after the 1/09/07? Ultimately I'd like to be able to pull a report at any stage which will tell me all the reviews that are coming up.
I have built an access database (Access 2002) which contains around 370 records.
It would be useful to be able to click a button on a form to review which records (forms) have been amended or what records are new.
This could be presented by either (1) sorting the records so the newest/or latest changed records came first, followed by the next most recent etc etc, or by (2) presenting a list of record names which could be followed manually.
I have tried several text books but have been unable to find a solution so any help from the experts would be very welcome.
This database is for a car event that's held once a year. It has been a work in progress for about 15 weeks now and is basically finished. There is only a couple of errors and not sure where to start. The database is so big it is getting a little out of reach for me to organize everything.
Idea of the database is that I can enter entrant's information, there car details and any merchandise they want.
Main errors I’ve noticed are;
In email form if you select one user it still emails everyone. In the car details form the induction box does not show the value list I created in the car table. when entering a t-shirt in the products form you select the shirt size. Is this the best way to lay this out. In the Order Form what is the best way to add a freight box between Sub-Total & Total where you can type the freight in and it's automatically added to the Total Box
Have a look over the db and let me know what ya's think.
I am working on a database to keep track of meter readings from several meters. Am at the table design stage and have come up with two seperate table designs but am wondering what would be best.
Each individual 'Meter' tracks more than one 'Fuel'. All of the regular meter readings should occur on the last day of the the month. The difference between the current month's reading and the reading from the previous month is the usage for the current month.
Though rare, there are occasions when there could be adjustments to the meter. A meter may be reset to 0 or to any other number due to a number of issues (replacement, maintenance).
The usage for a Meter-Fuel combination calculated by the current meter reading & previuos meter reading is assigned to current readings' date, As readings should onlt be taken at the end of the month, all other readings should be adjustments ONLY!
I think that the second version is the way to go, since I would still have to create a query to join data from both the reading and adjustment tables in the first design that would look like the data above.
Here what I consider to be the drawbacks to either table structure
Version 1 The first version requires more complicated queries to calculate the difference between meter readings.
Version 2 The second version will require some coding/querying to make sure that there are always two adjustment records for one date and determining which is the pre-adjustment and post-adjustment meter reading since it may not be true that the meter is reset to zero; it may even be set at number higher than the pre-adjectment reading.
Any thoughts on which design is better? Or any problems I could run into later with designing forms/queries/reports that perhaps I have not thought through?
I'm having some problems with VBA code I'm writing that references a subform. I'm pretty new to VBA and am not sure what the problem could be. The parent form (TrackingForm) tracks dates different tasks were completed. The subform (Child2) tracks exceptions that also need to be marked completed. I need ALL dates to be marked as completed before the entire tracking record can be marked as completed.
I have the following code entered into the OnFocus event of the tracking records Me.Completed_Date field:
Private Sub Completed_Date_GotFocus() If IsNull(Closing_Date) Or IsNull(Package_Received) _ Or IsNull(Upload_By) Or IsNull(Initial_Review_Date) _ Or IsNull(Me.Child2.Form![Date Exception Completed]) Then Me.Completed_Date.Locked = True MsgBox "Completed Date cannot be entered - outstanding items.", vbOKOnly, "Warning!" Else: Me.Completed_Date.Locked = False End If End Sub
What ends up happening is that, if there is more than one exception record on Child2, the code only seems to care if one of the records (usually the first record) has something entered in the [Date Exception Completed] field. I can't seem to make it look at any successive records in Child2 to ensure they are also completed prior to unlocking the Me.Date_Completed field. I have googled this extensively over the last day and the only response that seems to recur involves using a Recordset function that I have never used and am unsure how to.
I've got a database that allows all users to review an address register. If they find errors or mostly get updated information they have another form they can fill out to make the recommended changes. Only I have access to the main DB to make the change permanent.
The question I have is there a way for Access to send me a notification that someone has requested the change?
I know that sending an email is doable - in Access. However, that's not an option for our network here. I was thinking something along the command prompt "Net Send" command. However I've not been able to get that to work by itself - let alone within Access...
I have used VB code in the past to store what PC is using the database, I could modify that to alert me whenever I open the DB up from my PC. But I was trying to get away from having to open this particular DB up every day and check for changes.
I used to queries ,1 to get items that are taken ( its all about sign in sign out for equipment) and other query is list of all items. How can i make 3rd query which will give me all but taken items from query1? (of course items from query 1 are in query2) thx in advance
I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).
I have 3 queries which count how many patients come in 5, 4 and 3 times/week.
In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".
I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.
(I'm not using SQL view, I'm using the query design view)
In the "update to:" row, I use the Build function and locate the count I'm looking for.
Problem: when I run the query I get the error: Operation must use an updateable query.
Works great, but when I hit the number "3", (3 times in row) it will let me into the form. I want it to not let me in IF I don't know the password.
Where did I go wrong?
Private Sub Form_Load() Dim pw As Variant
If InputBox("What is the password?", "Password") = "1" Then Else MsgBox "Invalid Password", vbCritical, "Sorry Charlie" DoCmd.Close If InputBox("What is the password?", "Password") = "2" Then Else MsgBox "Invalid Password", vbCritical, "Sorry Charlie" DoCmd.Close End If End If
Here's my problem. I need to generate a report that says how much of each individual product was produced and as well as the total produced for a specified category in a time period. Something like the following:
I currently have a query that queries a database and pulls out all products that were produced in a specified period and the categories they belong to and dump them into a local access table. Now what I need to do is search through the query results and count up how many of each product were produced (02 AA, 01 AB, etc...) and the totals for each category. The number of categories is pretty limited (6), but there are hundreds of product codes, so I need a way to do this without having to type in each induvidual product code as the requirement by which the query searches. Also, the product codes that get returned are different every day.
I was thinking something along the lines of take the product code of the first row and check for any others in the results that match and write that into another table. Then move onto row 2 and use its product code as a search parameter and search through the query results for any matches. Then continue that until the end of the query results. Can I do that? Is there a better way to achieve what I need?
I am trying to filter a form to show the entire weekend's activity on Monday but only yesterday's activity Tuesday through Friday. Using this code I can return Friday's results on Monday and yesterday's for the rest. How do I get the range Friday to Sunday?
I have a query that displays results in a form, but if the query is null, I want to display a different form, or just an error message that says something like "your query returned no results" (right now it will display the form with no fields)
I am a beginning Access/VBA user and have searched and browsed the forum for combinations of null/query/form, but haven't found what I need. Can anyone point me in the right direction?
I am wanting my query results to appear in my main form so that i can edit them, rather than a table the query is just a look up of my form that stores about 500 records and stores all the same fields
