Simplifying Expressions By Assigning Parts To Variables Error
May 30, 2012
I have a report with many unbound fields with expressions (over 200). I've began getting this error message. This may seem like a dumb question but what does it mean when it speaks of simplifying the expression by assigning parts to variables. I put a few examples of some of the expressions below.
"This expression 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."
After getting (with a lot of help from ansentry) my database doing exactly what I wanted. I realised I wanted more... by my tiny mind can't see how to approach it - despite on the face of it looking like the simplest thing to do.
So here goes:
I have a table of customers. I also have a table of jobs, or workorders assigned to each customer. Each customer can have multiple jobs added at any time. All this works perfectly.
Now I want to assign parts (i.e. cables, tools, software) to each job. So each Job (sorted by WorkorderID) can have multiple parts added to it.
I'm using control tabs as well and would prefer it if I could use some kind of button on the Customers' Job summary view to "Add a Part".
Here's a link to my previous questions, complete with pictures....
Ideally, it would be great if the Job Summary view I have made could also have a column saying how many parts have been used in the job. (This is very much a nice-to-have!)
I tried a search on the forums for clues, but I reckon I'm either not searching the right way or this is so easy no one else has asked it. Apologies in either case. Any help would be great... :D
Hi I am assigning a value from a table to text box on the form through VBA. It gives the following error :
Run time error 2115
The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing MS Access from saving the data in the field. I have no BeforeUpdate or Validation event for the text box. What else could be the cause ?
The text box is unbound and I use ADODB connection. Please assist.
The following code is in the Form Current. As you can see if BranchCode = 2 I want to do a DLookup on the SystemPreferences table that holds a number Branch fields that hold document archive numbers for that branch. I want to move the number in the CroArchiveNo field into the field ArchiveNumber. I then run an Update Query on the system preferences table to add one to the relative branch archive number.
If Me.[BranchCode] = 2 Then Me.[ArchiveNumber] = DLookup("[CroArchiveNo]", "SystemPreferences", "[SysPrefId] = 1") DoCmd.SetWarnings False DoCmd.OpenQuery "UpdateCroArchiveNo" DoCmd.SetWarnings True End If
My problem is that I get an error
Run Time error 2448 You can't assign a value to this object
What I'm trying to do: I want to be able to use a switchboard button (or a button on a form) to open a unique form. OK, true, that's easy. Here is where I am having trouble. I want that one click to set the title of the form and the criteria for the query of a subform. Basically I want one form/subform/query to service multiple employees.
What I currently have: I have a main form and two subforms, and multiple queries for each employee. They all do the same thing, display the same basic information but it all depends on the employee. If I make a simple change it takes hours to change all the forms for all the employees.
What I've done to find an answer: I bought a Learn Access in 24hrs type book which got me to where I am now. I then purchased an Access VBA book and am currently about 1/3 of the way through that but still have no idea where to go. I've searched through example databases to find one that does something similar but haven't found one. I've done web and forum searches. I think my lack of success there is possibly due to not searching for the right things. I've been experimenting on my own since November of 07. I don't have a skilled friend I can ask.
There is probably a simple solution I just don't know where to find it.
Hi, how do i simplify this function? I tried substituting the value 1 so it becomes like this but i get the "Expected: end of statement" error. Can anyone help? Thanks
Function StdRow1Enabled(Value As String)
Forms!F_ReceivingStandardParts!subform1.Form!txtBa lQty1b.Enabled = Value
End Function
Function StdRow1Enabled(Num As Integer, Value As String)
Forms!F_ReceivingStandardParts!subform1.Form!txtBa lQty(Num)b.Enabled = Value
Hello! Simple one for most, but I have having problems with a Database I am setting up The database controls stock movement (i.e. inwards stock and outwards stock)
I have set up a Parts Inwards table with the following fields: Part Number (linked) Qty In Stock Location
And I have set up a Parts Out bound with the following fields: Part Number (linked) Qty Out Stock Location
Now for the problem, when I run a query to tell me how many parts I have left in the stock room i.e. Qty In - Qty Out, it duplicates the data in the Qty In. I assume its got something to do with the fact that I have multiple Qty out’s for a single Qty In entry…hope this make sense?
In a field “NAME” I have “Adam Smith”. From this field I want in a QBE window (not in VB) to make two new fields “FIRST NAME” “Adam” and “LAST NAME” “Smith”. In Excel there are the functions FIND and SEARCH with which I take the number of the gap “ ” (5) between Adam and Smith and with the functions LEN, RIGHTS and LEFTS I have a result. What could I do in Access 2003?
I am new to this forum :) and learning Access :confused: . The Database Tables: Employees Products ExpectedQuantity PartQTY Parts
Most of the products are similar so they have the same parts for the majority. However they do not all have the same parts. So I created a parts table that contains all the parts needed for any product. Now each Product also can contain multiples of one part (such as wheels) where I would want to list the Product and the parts list and quantity needed of those individual parts for that product. Hence the PartQTY table.
Now what I want to be able to do, is go into a form where each record is one of the products. A subform (datasheet view) for that product that shows all the parts and the quantity of each part that make up the product. Such as 4 wheels, 5 screws, etc for Product A.
I would like there to be an ExpectedQauntity field in the main form which once you found the product you can enter in the number of that product expected. Then this Qauntity would be multiplied by the qauntity of each part required (in the datasheet subform a column with =[expectedQTY]*[PartQTY]) and list next to each part the number needed to order. So if for product A the expected quantity is 50 I would have to order 200 wheels and 250 screws for that product.
A report would Sum each part qauntity after the expectedqauntity has been entered for each product. So I could print a report that tells me how many of each part we need to order based on the expected qauntity of all the products.
I think I am complicating things and would like a fresh mind to assist me in which direction to go.
I know how to Grab the Right and left Characters and grab the mid characters from a field value
ex: Right([fieldName],4)
My question is how to I grab characters based on a space Say for instance "Fred Smith" or a name field. There are going to be varing lengths of last names...but the format is the same...First Name, Space, Last Name....
QUESTIONS:
How do I search the string, count to the space, then take all characters after the Space?
One step further how would I grab the middle name in this example? "Fred M Smith"
How to find out the Salary. A school remains closed for Winter vacation from 15th Dec. of Current year to 28-29 Feb next year.)
The management disburse the salary to the school as per following procedure
If the Employee has not completed 6 months period (180 days) as on 15th December of current year he will not be entitled to winter salary (2 and a half months) from 16th Dec. of current year to Last day of Feb. next year. For rest of the period he will get normal salary. i.e. for 11 days of June and July, August, Sept. Oct, Nov and 15 days of Dec. of current year he will get full salary.
To elaborate it further it means that if an employee has been appointed on 19th June of the current year his winter salary with effect from 16th Dec. of current year to last day of Feb next Year will not be disbursed. For other time period he is entitled for normal salary.
That does the job nicely or the first bulletpoint but where from there? There must be a neater way than having a load of query columns each updating the last. ie.
Heres some example data: 1 M24308/2-2F 2 M24308/4-2F 3 ORD9F0000 4 ORD9M0000 5 D90000VLO 6 D90000JOO
How would I design the table to allow me to define alternatepart numbers and also related parts? For example:
Record 1 is the primary part and record 3 is the alternatepart. Record 2 is the primary part and record 4 is the alternatepart. Records 5 and 6 are related parts to records 1, 2, 3 and 4.
I have a form that allows the user to select a part and displaythe part data. But I also want the alternate parts and related parts displayedon the form, maybe in a subform with tabs. I havent gotten there yet because Icant determine the table design.
i've created a database for city permits to be issued (which works perfectly thanx to alll your help), but now i need to be able to print out 1 permit at a time when it is issued (immediately after the data has been entered into the forms). i have 3 tables & 3 forms: contractors/owners, permtOrders (which has tabs for the different types of permits), and permitOrderDetails. the problem is the forms are set up for data entry (and only open in add mode for obvious reasons)...i need to be able to print an actual permit (like the carbon copy, landscape kind that usually tears out of a book). once the user enters the information into the forms, i need a little bit of that info from each form to be carried onto the actual printable permit. i'm not really sure how to accomplish this. :confused: i don't really know if i explained this right, but any suggestions are greatly appreciated.
I'm not sure what the best way to set this up is, and I'm hoping someone can point me in the right direction.
I have a long list of different parts that I have acquired and taken inventory of. I am selling kits that contain different amounts of certain parts. For example, Kit 1 contains 2 of part A, 3 of part B, and 14 or part L. I will only be selling complete kits and not the individual parts.
Here's what I'm thinking. I need a table for the individual parts that lists the quantities of each. A table for the kits and a subtable for the kits that lists the quantities of each part. A transaction table.
Then I just need a form that has a combo box with the kits listed, date of transaction and quantity shipped, and a button that runs a query to subtract from the quantities of the individual parts based on the kits table and subtable.
Can someone please help? I'd really appreciate any tips.
I have a sales invoice table that contains parts that start with *, for example *SPR362.
I need to select only those parts that start with the *, as this is the wildcard in queries how do i select those products that start with it without it being recognised as the wildcard and selecting all records?
How to calculate all records that have not yet been linked to other records. For example: I have 1 Product, which is linked with multiple components which is subsequently linked to subcomponents. All these types can be linked to Alternate Parts.
In order to reduce redundant information I would like to show the unlinked parts to the user. Therefore I would like to make a query which shows all unlinked parts.
I have the following tables with subsequent PK Part Number field values:
I am unable to find a appropriate query structure with criteria for this issue due to the many different fields required and the fact that if in one query all these tables are shown no data will be shown ...
I am running a query to some me total parts made between 2 dates. The dates are selected from a form with the query looking to this form to selected the date range.
When I run the query it shows me total parts made for each day. What i would like is a single total of parts between the range.
I have a report that is generated daily that is a TXT file that is also the has the same file name each morning. One section out of this file is needed in a database that compiles and sends out reports to our customers. This section of the TXT file is variable in length, but is always preceded and ends with a specific text string that I can search for. It is also formatted in columns that are always the same width.
I currently have to hand enter this section into the database every morning. Not the easiest of tasks... I would like to get this automated.
