I am running a query that keeps asking for me to enter data for some fields before it opens the query, which I don't. When the query opens, the data in the fields is there.? Why? This is what is written in the field (query):
Total Jumbos: nz([pathjbo50],0)+nz([esjbo50],0)+nz([f1jbo50],0)
The quote "Total Jumbos:" is my label. The rest is my expression. This setup works on a few other columns I entered but this one (and a few others) don't. Any suggestions would be appreciated.
FYI: The query information is gathered from a table populated by a form. The label "Total Jumbos" is not on the original table but I have other labels in the same query that are also not on the original table that do work.
I have an MS Access 2003 front-end (queries, forms, reports) connected to a SQL server back-end database (SQL Server 2000).
When I try to run an append query against any of my SQL tables with a unique identifier, I get the error...
"Explicit value must be specified for Identity Column it table 'table1' when IDENTITY_INSERT is set to ON (#545)"
I am trying to create a duplicate record process for my users, by appending data using append queries to the same table. I used the MS Access built in wizard to duplicate the main record on the form, but was going to have to use append query code to duplicate the sub-form records.
Is there anyway around this error, or am I stuck as far as writing code to run this append query on the fly?
I am modifying some queries in a DB which is only 411,000 KB. It still has plenty of room left before reaching 2GB. When I go through and test the queries, some run fine with no issues.Then I get to one of the last queries and get this error: "The query cannot be completed. Either the size of the query result is larger than the maximum size of a database (2 GB), or there is not enough temporary storage on the disk to store the query itself."
I still have about 1.5 GB of RAM available when this query is running, so it doesn't appear to be a RAM issue, and the DB still has plenty of room left to store the result set of this query.
SELECT SubscheduleID, EventID, WeekOrder, DayID, StartTime, EndTime, Priority, CanJoin, PatientTitle, PatientNickname, IncludesPatient, IncludesAftercare, Letter1 FROM [qryScheduleCombinedDetails] WHERE (SubscheduleID = 1 AND IncludesPatient = -1 AND DuringAftercare <> "AC only" AND (WeekOrder = "All" OR WeekOrder = 3 OR (WeekOrder = 1 AND Letter1 = "XYZ")) AND DayID = 2 AND StartTime <= #8:00:00 AM# AND EndTime >= #8:30:00 AM#);
When I try to run it, I get a "data type mismatch" error. When I put the same code into a query, I get the same error. However, it will run if I delete either condition from within the (WeekOrder = 1 AND Letter1 = "XYZ") pairing. I can't figure why it can run with either of those, but not both together.
WeekOrder is defined as String. Letter1 is calculated as Cstr(Nz(IIf(Letter,"XYZ","ABC"))) within [qryScheduleCombinedDetails], because I wanted to make sure that it would be recognized as a string.
I have a query that uses the input from a form as criteria, which is then used in a report. The form input is a drop down based on another table. This is a sales pipeline report, and the list is a list of sales people. The report works perfect for all sales people except one. When I run it for the one, I get the following error:
"This expression is is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables".
I DO NOT get the same error when running the query by itself - so assuming there is something in the report causing this. I do have some sum formulas in the report.
Again, no other salespersons selected cause this error -- so I am assuming there is something in the dataset for this person that is causing the error.
Hi, I get this error when trying to rung a piece of code: The expression On Load you entered as the event property setting produced the following error: Member already exists in an object module from which this object module derives.
I have the EXACT same code working on other forms using different fields and I just can't figure out why this particular one isn't working, it's driving me nuts! My code is this: Private Sub Form_Load() Dim strReason As String
strReason2 = Nz(Ref3Poor_Reason, " ")
Select Case strReason3 Case "Not Known", "Unwilling to Give" Poor1NavRef3.Visible = False Poor2NavRef3.Visible = True
Case Else Poor2NavRef3.Visible = False Poor1NavRef3.Visible = True
End Select
Dim Ref3PoorCheck As Boolean Ref3PoorCheck = Ref3Poor_Reference
Select Case Ref3PoorCheck
Case "True" Ref3Poor_Reason.Visible = True
Case "False" Ref3Poor_Reason.Visible = False
End Select
End Sub
Can anybody tell me where I'm going wrong please? Thanks, Dan
I get a syntax error when I run this query from an Access Module but I don't get an error when I run it as a straight query. Can anyone see what's wrong with it? I am using Access 2003
Dim sqlStmt As String
sqlStmt = "SELECT [CCC Companies].ESTBLMT_NO, SUM(subWeight) AS weight " sqlStmt = sqlStmt + "FROM [CCC Companies], [SELECT ESTBLMT_NO, COUNT(*) * 10 AS subWeight " sqlStmt = sqlStmt + "FROM CCCWords " sqlStmt = sqlStmt + "WHERE Word IN ( Select word from CBCWords where vendor = '" sqlStmt = sqlStmt + strVendor sqlStmt = sqlStmt + "') GROUP BY ESTBLMT_NO " sqlStmt = sqlStmt + "UNION " sqlStmt = sqlStmt + "SELECT ESTBLMT_NO,COUNT(*) * 25 AS subWeight " sqlStmt = sqlStmt + "FROM CCCCleansedPhone " sqlStmt = sqlStmt + "WHERE MID(STRIPPED_PHONE,1, 5) IN ( Select MID(STRIPPED_PHONE,1 ,5) FROM CBCCleansedPhone WHERE vendor_no = '" sqlStmt = sqlStmt + strVendor sqlStmt = sqlStmt + "') GROUP BY ESTBLMT_NO " sqlStmt = sqlStmt + "UNION " sqlStmt = sqlStmt + "SELECT ESTBLMT_NO, COUNT(*) * 50 AS subWeight " sqlStmt = sqlStmt + "FROM CCCCleansedPhone " sqlStmt = sqlStmt + "WHERE MID(STRIPPED_PHONE,1, 7) IN ( Select MID(STRIPPED_PHONE,1 ,7) FROM CBCCleansedPhone WHERE vendor_no = '" sqlStmt = sqlStmt + strVendor sqlStmt = sqlStmt + "') GROUP BY ESTBLMT_NO " sqlStmt = sqlStmt + "UNION " sqlStmt = sqlStmt + "SELECT ESTBLMT_NO, COUNT(*) * 50 AS subWeight " sqlStmt = sqlStmt + "FROM CCCCleansedPostalCode " sqlStmt = sqlStmt + "WHERE MID(STRIPPED_POSTAL,1, 6) IN ( Select MID(STRIPPED_POSTAL,1 ,6) FROM CBCCleansedPostalCode WHERE vendor_no = '" sqlStmt = sqlStmt + strVendor sqlStmt = sqlStmt + "') GROUP BY ESTBLMT_NO " sqlStmt = sqlStmt + "]. AS dupWeight " sqlStmt = sqlStmt + "WHERE dupWeight.ESTBLMT_NO = [CCC Companies].ESTBLMT_NO " sqlStmt = sqlStmt + "GROUP BY [CCC Companies].ESTBLMT_NO " sqlStmt = sqlStmt + "HAVING SUM(subWeight) >= 60 " sqlStmt = sqlStmt + "ORDER BY SUM(subWeight) DESC"
DoCmd.OpenForm "Show Probabilities", , , sqlStmt
Here is the code that runs ok in a straight query:
SELECT [CCC Companies].ESTBLMT_NO, SUM(subWeight) AS weight FROM [CCC Companies], [SELECT ESTBLMT_NO, COUNT(*) * 10 AS subWeight FROM CCCWords WHERE Word IN ( Select word from CBCWords where vendor = '100024') GROUP BY ESTBLMT_NO UNION SELECT ESTBLMT_NO, COUNT(*) * 25 AS subWeight FROM CCCCleansedPhone WHERE MID(STRIPPED_PHONE,1, 5) IN ( Select MID(STRIPPED_PHONE,1 ,5) FROM CBCCleansedPhone WHERE vendor_no = '100024') GROUP BY ESTBLMT_NO UNION SELECT ESTBLMT_NO, COUNT(*) * 50 AS subWeight FROM CCCCleansedPhone WHERE MID(STRIPPED_PHONE,1, 7) IN ( Select MID(STRIPPED_PHONE,1 ,7) FROM CBCCleansedPhone WHERE vendor_no = '100024') GROUP BY ESTBLMT_NO UNION SELECT ESTBLMT_NO, COUNT(*) * 50 AS subWeight FROM CCCCleansedPostalCode WHERE MID(STRIPPED_POSTAL,1, 6) IN ( Select MID(STRIPPED_POSTAL,1 ,6) FROM CBCCleansedPostalCode WHERE vendor_no = '100024') GROUP BY ESTBLMT_NO ]. AS dupWeight WHERE dupWeight.ESTBLMT_NO=[CCC Companies].ESTBLMT_NO GROUP BY [CCC Companies].ESTBLMT_NO HAVING SUM(subWeight)>=60 ORDER BY SUM(subWeight) DESC;
The problem is lately; while testing I am running multiple queries in a routine. When it fails; its hard to identify which query has the problem. So I hit control break; debug and try to find it. After I fix it; I debug and reset; i get this continuing hourglass thing in the form of a spinning circle until I close and reopen the database. I think I need better error handling but not something really complicated because I need to put it in quite a few routines throughout the database.
Can anyone tell me how to get a running balance on a report. I know how to create a running total, by setting the "running sum" property of a text box to "Over all".
I can't however see how I can adapt this to give a running balance (as in a bank statement for example). Attempts to do so end up in failure!!
Sorry I am to bother you. But I was wondering whether it is possible to create a running sum in a query (from each previous record just a simple add up)?
I know how to do in report (it is described in the help function) but for a query I cannot find anything.
I have another table, Transaction, that's based off the Loan Number field on the loans table.
Fields: LoanNumber Payment Type (2 choices, payment and advance) Amount
I have a user form where users can specify the payment type, and amount, based on a certain loan number.
I'm attempting to do this:
User enters a transaction into the database. once the transaction is entered, the Principal Balance field for the specified loan number is updated.
If the payment type is advance, the query will subtract the amount from principalbalnce. if payment, the query wll add the amount to the principal balance.
Here's what I have so far.
The userform updates the transactions table with the information correctly.
My next step is to update the principal balance.
I'm thinking I could use some sort of update query, but I don't know where to begin.
HELP!!
If there is a better (normalized) way to accomplish this, I'm definitely open to ideas. Please just provide a basic example so I can understand. :o :cool:
I have a query that shows part numbers with a total qty ordered for a specified time period. This first part of the query is fine and is made into a table.
I am then trying to use this table to create a running sum with the list sorted in descending order, and ultimately break the list where the sum reaches 80% of the total qty across the whole list. I have tried the following formula and the table has been indexed in the descending order it would need to be in. The IndexAlias has also been created and made into a fixed table prior to running the 'Running Sum' query.
I have taken this formula from the microsoft link below, (Method 2), and adapted it to my query. However, although I have a decent understanding of Access I do not understand the '&' in this formula. Removing this and the double quotes on the end returns an error and leaving them in returns zeros all the way down the 'RunTot' field. If anyone can help I would appreciate it as I am stuck on this.
hey there, i am trying to create running sum in a query. i found the function for a report but not i a query
From MS Access Help: ("You can number the items in your report by using a calculated control and the RunningSum property. First, you create a text box and set its ControlSource property to =1. Then, you set the RunningSum property for the text box. If you want the numbering to start over for each group, set the property to Over Group. If you want to accumulate a running sum for the entire report, set the property to Over All")
my overall goal is to number each Employees assignments that they have. That is Employee1 may be associated to 2 assignments, thus resulting in employee1, (assignment number) 1, employee1, (assignment number) 2, and Employee2 may have 4 assignments, employee2, (assignmetn number)1, employee2, (assignmetn number)2,employee2, (assignmetn number)3, employee2, (assignmetn number)4.
Does anyon ehave any experience of running totals in an access query. I'm reporting the data through excel not access reports so need a query not a report solution..
What I would like is to have an additional column which keeps a monthly summary of spend based on running total month 1to 12. All items have months 1 - 12 and are ordered in that fashion.
Hi - Once again I'm asking for your assistance. Ideally, below is how the form (and eventually a report) would look; the first three fields (ID, Date & Vol) I currently have in a table (tblMaster):
I am helping a company reorganize its employee data to reflect recent changes in the company's organization. It involves a lot of data manipulation that, for the most part, can't be done programatically.
I can save a little time using SQL update queries like this:
update personnel set Department = "Support Staff" where Jobtitle="Shipper" update personnel set Department = "Maintenance" where Jobtitle="Mechanic" update personnel set Department = "Regional" where Jobtitle="HR Manager"
I have prepared a text file containing 530 such queries that can be pasted into Access. But as much time as that saves me, it is still a day or two of cutting and pasting.
What would be the best way to talk Access into running all the queries, without having to paste in every one individually?
Is there a way to run a query with macros? I don't want to open it. I just want to run it, generate a report which will then be sent via email. How do I go about it?
I am running a query that has over 14000 records. What I tell the query to is read two other queries and tell me which record does not match with the other one. My problem is, that it takes over 10 minutes to do. I have already compressed the file. I do not have a primary key (trust me on it, I can not do that). Are there any other ways to resolve this?
I have a query that have three columns invoice number, tran number and amount. What I would like to do is to sum the invoice number if they are equal. example:
I have been trying to get this all night. This was my last attempt. I know it is all wrong and messed up but after 10 hrs I go for help. There is no date field to work with.
I have a query with 40 fields and 16,000 records. Field 1 is Location, of which I have 197 unique values. What I would like to do is have a running count for each unique location and when the location changes start the count over again.
I am using Order By to get the proper sort. In some other instances I may want the count to be dependent on the combination of Location and Function codes. Can someone provide some SQL tip(s) that would allow me to accomplish this.
Behind a command button, i have 3 queries being run. 2 of these queries are append queries, copying the specified record into another table (criteria set to ID of the main form) and the other query is to delete the record from the current table once it has been copied. This is then followed by another delete (not query)
When each of these are run, boxes pop up asking if you're sure you want to do it. I was wondering if it would be possibly to bring up my own box to ask if the user is sure they want to continue, and when they click yes, all the questions that the query asks about wanting to do it, are automatically chosen as yes.
I.E there is only one option box, yes and no, and no others appear.
Hello, Say for example I have a database containing the date of birth of people in the format of YYYYMMDD (a string/text field) and another field about the age of the person (a numerical field) and another field is, say, the date they had their birthday last year. I would like to check whether the year in the date of birth field and age adds together is the year in the date they had the last birthday field (or off by no more than 2 years), and give me the list of the people who are not. Is it possible to do that by writing a query or I have to use a macro and write an SQL statement for it? In both case, how should that be done?
Am trying to create a query for a chart where I can total the employees over time but am having real trouble creating a running total from the "Total" field within a query but cannot seem to get it at all.
SELECT Sum([CountOfStartDate]-[CountOfLeftDate]) AS Total, Atest1.StartDate, Atest1.LeftDate, Sum([CountOfStartDate]-[CountOfLeftDate]) AS RunningTotal FROM Atest1 GROUP BY Atest1.StartDate, Atest1.LeftDate;
