Code:SELECT RawData.OutlineNumber, RawData.OutlineDescription, RawData.DeliverableDesc, IIf(subQuery1.TotalProgress=0,"",IIf(subQuery2.TotalDaysForDeliverable=0,"",FormatPercent((RawData.PercentComplete/subQuery1.TotalProgress)*(RawData.Finish-RawData.Start)/(subQuery2.TotalDaysForDeliverable)))) AS WeightedContributionFROM RawData, [SELECT DeliverableDesc, SUM(PercentComplete) AS TotalProgress FROM RawData GROUP BY DeliverableDesc]. AS subQuery1, [SELECT DeliverableDesc, SUM(RawData.Finish-RawData.Start) AS TotalDaysForDeliverable FROM RawData GROUP BY DeliverableDesc]. AS subQuery2WHERE ((RawData.DeliverableDesc)=(subQuery1.DeliverableD esc) And (RawData.DeliverableDesc)=(subQuery2.DeliverableDe sc) AND RawData.ReleasePeriod=ReleaseMonth);
I am now try to set that query as the record source in a sub form using VBA but it throws a syntax error, run time error ‘3075’
Code:strRowSource = "SELECT RawData.OutlineNumber, RawData.OutlineDescription, RawData.DeliverableDesc, IIf(subQuery1.TotalProgress=0,"",IIf(subQuery2.TotalDaysForDeliverable=0,"",FormatPercent((RawData.PercentComplete/subQuery1.TotalProgress)*(RawData.Finish-RawData.Start)/(subQuery2.TotalDaysForDeliverable)))) AS WeightedContribution " & _ "FROM RawData, [SELECT DeliverableDesc, SUM(PercentComplete) AS TotalProgress FROM RawData GROUP BY DeliverableDesc]. AS subQuery1, [SELECT DeliverableDesc, SUM(RawData.Finish-RawData.Start) AS TotalDaysForDeliverable FROM RawData GROUP BY DeliverableDesc]. AS subQuery2 " & _ "WHERE ((RawData.DeliverableDesc)=(subQuery1.DeliverableD esc) And (RawData.DeliverableDesc)=(subQuery2.DeliverableDe sc) And RawData.ReleasePeriod = '" & Me.Combo10.value & "' )" Me.DeliverableStatus_Contribution_Of_Tasks_subform .Form.RecordSource = strRowSource
How is it that the query works when I run it but when I try to apply it to a sub form it gives a syntax error, any ideas how to correct this
I have a simple select query on a SQL table from Access. The query is:
SELECT tbl_Orders.OrderID, tbl_Orders.Approved FROM tbl_Orders WHERE (((tbl_Orders.Approved)=0) AND ((tbl_Orders.Completed)<>0)) ORDER BY tbl_Orders.OrderID;
The strange thing is that sometimes it pulls 34 results, and sometimes 38. From what I can tell, it should be pulling all 38.
What can I do to make sure it gets all the records?
I work for a bank and have build a few little DB's for differant groups but my current project is a work flow tool for a department. I have it 90% completed and i still have to build in the reporting side. Part of the reporting is caclulating times and this is where i am having problem.
I have some coding built but i am getting inconsistant results, as an example if i am trying to calculate the total amount of time between [start Time], [End Time] then minus any [Stop Time].
I have this on a command button, i have this same code for a few other calculations and in some cases it seems to be working i have just changed my start and stop fields along with what stop time to take out in each case. I have attached some parts of my DB, one of the modules, my main table and the form where i am running the coding.
I dont get any errors with this code. Let me explain what im doing: T1 = temparary table (not really temparary but the values just change so often it might as well be) this table has field names: partname, order, maxorder, etc. stuff like that pretty much its a table that has records for each part name.
T2 = main database this table holds the order for all parts for a particular time and day
T1 is the one that people are familiar with, where as T2 's set up is something that they would not be, so for clarity we use T1 for displaying etc.
now my problem is, i try to transfer data from one table to another but it works sometimes and doesnt work others, i dont get any errors and i have option explicit on as well. if someone could look at this code and tell me if there are any problems that you see, the quicker the response the better.
technical specs: Access 2000 Windows 2000
Code: Private Sub insertRecMainDB(ByVal typeOfOrder As String) 'This sub will insert a new record of type O (Ordered) and put in all values from the table Order 'Also it will make a duplicate record of type R (Received) and put in same values so when later 'if there are no changes (meaning all product was received) no entry has to occur, however if you 'are missing a part then you can enter how many on the other form.
Dim yesOrNo As String Dim tempSQL As String Dim rec As New ADODB.Recordset Dim orderAmountRec As New ADODB.Recordset Dim partNameRec As New ADODB.Recordset Dim numOfRecords As Integer Dim cnt As Integer
'Checks the type of order and either checks or does not check it in the DB If typeOfOrder = "O" Then yesOrNo = "yes" Else yesOrNo = "no" End If
'Counts the number of parts we have to order, saves it in numOfRecords rec.Open ("SELECT* FROM [Order]"), conn, adOpenStatic, adLockReadOnly numOfRecords = rec.RecordCount rec.Close 'Loop to transfer data from the table Order to the table MainDB For cnt = 1 To numOfRecords
'Gets the name of each part tempSQL = "SELECT [Description] FROM [Order] WHERE [PartNumber]=" + CStr(cnt) + ";" partNameRec.Open (tempSQL), conn, adOpenStatic, adLockReadOnly
'Gets the amount ordered of that part tempSQL = "SELECT [MaterialOrdered] FROM [Order] WHERE [PartNumber]=" + CStr(cnt) + ";" orderAmountRec.Open (tempSQL), conn, adOpenStatic, adLockReadOnly
'Updates that part name with the ordered amount tempSQL = "UPDATE [MainDB] SET [" + CStr(partNameRec.Fields(0)) + "]=" + CStr(orderAmountRec.Fields(0)) + " WHERE [OrderNumber]=" + CStr(txtOrderNumber.Value) + " AND [DateTime]='" + CStr(lblDateTime.Caption) + "' AND [TypeOfOrder]='" + typeOfOrder + "';" CurrentDb.Execute (tempSQL)
partNameRec.Close orderAmountRec.Close Next End Sub
I have a table with several date fields. All the fields are formatted to medium date, but the data is being saved in to different ways. Some records are yy-mmm-dd and some are dd-mmm-yy. Each field has at least a few inconsistent dates but not always in the same record. I can't figure out how to make them consistent again. Has anyone else ever had this problem?
I am making a race results database that is supposed to sum an entrant's two times together to obtain a total combined time.
Let's say that the two times I have are '41.43' (41 seconds and 43 milliseconds, there is no colon) and '1:48.17' (1 minute and 48 seconds and 17 milliseconds), and both are currently of the short text data type in my table. How would I go about obtaining the sum of these two times? I have already tried using queries to convert the strings to seconds with no success.
I am at a loss as to why my dates in my table datasheet are not consistent in the Date/Time format. In the table and specifically the Date/Time field it is formatted as the selection "Short Date". I am located in the U.S. using MS Access 2003. The database I'm using was a free download from the MS website called "Accounting Ledger" and it is for Access 2003.
While I have dabbled in databases some I am really at a loss as to why I am seeing two >>different<< Date/Time formats in the *same* table.
Below is the date range I've entered from January 2007 to May 2007 and below has been copied and pasted directly from the datasheet. Trying to do a date sort in any fashion is out of the question until I resolve this.
I am consistent in my date input... April 6, 2007 is entered as 4/6/07, January 18, 2007 is entered as 1/18/07, February 21, 2007 is entered as 2/21/07 and so on.
However, some dates are showing as mm/dd/yyyy format while others are showing as dd/mm/yyyy format.
Note: if I choose a general date or long date format Access reads these as July, September and October dates in some cases.
Here is January (as copied and pasted from the datasheet)
01/03/2007 (these first 7 show a mm/dd/yyyy format) 01/03/2007 01/02/2007 01/10/2007 01/09/2007 01/05/2007 01/11/2007
18/01/2007 (these last 6 show a dd/mm/yyyy format) 24/01/2007 27/01/2007 27/01/2007 02/01/2007 31/01/2007
I have split the database, with the back-end residing on the server. Only 1 other person is working in Access right now; she's verifying the data. Today she's working directly in the back-end, could this be the problem. If it is, I'm going to have to create a front-end for her quickly.
My problem is this...I'm working on queries and forms so that the scientists who will ultimately be using this application, won't be able to go in and inadvertently change something in the tables set-up. When I'm in the back-end main data table, it says I have 2723 records.
When I create a front-end query to query all the records in that table, it says I have 2160 records.
The input form that I created with most of the same fields as the query (created BEFORE I created the query...I'll have it pull from the query now instead of directly from the table) also says we only have 2160 records.
I have a continuous subform on my main form where one text box is a hyperlink that opens a form. All is well, except....
When the mouse hovers over the text box of the top record, the cursor does not change to the hyperlink pointed finger thing (it's I-beam), even though the text in the textbox displays in hyperlink format.
If I hover over the hyperlink/text boxes of records 2 thorough x, the hand-cursor appears.
More info: 1. The hyperlink functions normally (i.e., the appropriate form opens to the appropriate record) 2. When I then return to the main form, the hand-cursor magically appears when hovering the first record. 3. I have 2 other continuous subforms on the same main form that behave the same way.
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.
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
Is there a way to write one query that will work for different criteria and is run by different buttons to produce bar graphs or a basic report depending on the button the user clicks??
I am working on a query but can manage to get it to do what I want. I have a main table with a job_ID and a faults table which documents faults that occur related to that Job_ID the tables are related with a one to many relationship. I want the query to bring up all the jobs even those with no faults, at present it is only bring up those jobs that have faults, how do I include details of all the jobs and if there are no faults then that field is left blank, probably very simple to do but after working on this db for hours my brain is no longer functioning!! :eek:
I have a table called tblODF and within that table, I have these fields: - ODFNumber - ODFScanDate - Status (Combo box: Pending, Complete, Licensing) - LastFollowup
In my query, I have the same fields, except I added a calculated field. I added DayCount to
Codecalculate:(Date()-[ODFScanDate]
This should calculate the number we've had the ODF.
What I want to happen is, let's say the status is Maturity, I want the DayCount to say 0. If it's pending, I want it to calculate the actual days.
I've just started using Access 2007, but I've been an Access 2003 user for some years and have a couple of databases, both of which I've now brought over to 2007.
Something I've noticed and is probably very simple. In 2007, after I entered information in a table via one of my forms, I queried on that information but it wasn't included in the results. But everything else - from 2003 - was. I then went thru each field in the underlying table to "match" fields there were filled in for a 2003 record and now the new entry does appear in the query results.
This hadn't happened before in 2003 - it didn't matter if all or even particular fields were filled in. But now it needs for at least some particular fields to be filled in. Don't know if it's something different in 2007, or probably coincidental. I haven't changed anything in the table, query or entry form.
It's not a crisis (now that I see my results), but I'm wondering what is going on - now, that apparently wasn't occurring before?
I hope I can explain this clearly. I'd be really grateful if anyone could help.
I need to set up some sort of contacts database at work.
I need (I think, after having a little think about it) one table, filled with people, (their names, contact details ect) and basically various queries to pull up people in certain groups. Say for instance, people who attend meeting a, b, c, (in a simplised, condensed version of the truth)
I did think about doing this with various tables actually. A contacts table, a meetings table, blah blah, but anyway. (Any advice on this incidentally would be great).
My main problem though. We often have to email all the people who attend say meeting a. We have contacts on our email systems, but they're different from person to person, and as the company is updating email systems, we can't even send contacts lists to everyone, as they're not compatible.
What I'd love is to be able to call up a list of people attending meeting a from my database and email each of those people (their contact details would include email address of course)
:( I have such problem : my query works right, but in ComboBox I got results with wrong sort. Query results is: Street 8 Street 8a Street 10 Street 10/12
But in ComboBox data views such: Street 10/12 Street 10 Street 8a Street 8
I have a query with two table's in them. One is filled, the other is still empty. Both table's are related to each other with the field "document number".
This was working fine in access 2000, now in 2003 it returns zero results. I am starting to think it is because one table is empty it returns nothing. How can i adapt the query criteria or something else that it shows the results from table one, while table two can still be empty. One table holds the documents and all information about it, the other holds the information wich document is referenced on wich document. Now since i can have documens which don't reference to a document, i still want those in the list.
I know SQL, and I know databases, but this has me confrused.
I describe it in terms of macros, but I get the same results if I manually execute the queries.
A client has an Access DB that is used by the 5 employees in the office. He has a macro that creates an interim user list, then compares it the the user list from the start of the month.
If he runs the macro to create the interim user list from his computer (computer 1) then any computer can run the other macro and get the differences (about 200 rows).
If any other computer creates the interim user list, then any computer that runs the macro to calculate differences gets invalid results (>3000 rows).
A union of the 'good' interim user list and 'bad' interim user list shows about 3000 rows (out of 4345 rows in each original table), so the macro produces 1345 differenct rows, depending which computer it is on.
I verified that each computer is opening the correct database, and the query is a moderately simple INSERT INTO ... SELECT FROM query.
Does anyone know why this might happen, and/or an easy way to determine which rows are different, and why? It is Access 2003 with SP1, everyone has the same version.
Does anyone know how to do a query so that the user can find all the things that will expiry at the end of the current month? The user will click on a command button and it will show the results of a product that will expiry at the end of a current month. Have tried with parameters to which the user manually types the end date in and then it will show the results but having problems. Cheers
