Hello all, I'm quite new to Access. I've read many Access tutorials and site but I have yet to found the solution for my problem. So here it is (pardon my english):
Field AmountTotal is
=(Nz([AmountSubTotal])+Nz([AmountSH])-Nz([Discounts]))
I manually input AmountSubTotal, AmountSH and Discounts. I expected an automatic calculation for AmountTotal.
In "Form view" I get the result of AmountTotal that I wanted. But the calculation result doesn't get recorded in my field data table AmountTotal. It just shows blank.
How do I record this automated results into my original table?
I am just learning how to use Access and have created a basic form. I am stuck though on getting my form to calculate the Age (from Date of Birth) in Years and Months.
The info on this forum I have tried the following Expression:
=DateDiff("yyyy",[DoB],Date()) & " years and " & DateDiff("m",[DoB],Date())-(DateDiff("yyyy",[DoB],Date())+Int(Format(Date(),"mmdd") & lt;Format([DoB],"mmdd")))*12 & " months "
But my problem is that it tells me:
''The expression you entered contains invalid syntax You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounung it in quotation marks''
Can anyone let me know what error I've made and how to fix it? It would be much appreciated. Thanks
I am just learning how to use Access and have created a basic form. I am stuck though on getting my form to calculate the Age (from Date of Birth) in Years and Months.
The info on this forum I have tried the following Expression:
=DateDiff("yyyy",[DoB],Date()) & " years and " & DateDiff("m",[DoB],Date())-(DateDiff("yyyy",[DoB],Date())+Int(Format(Date(),"mmdd") & lt;Format([DoB],"mmdd")))*12 & " months "
But my problem is that it tells me:
''The expression you entered contains invalid syntax You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounung it in quotation marks''
Can anyone let me know what error I've made and how to fix it? It would be much appreciated. Thanks
I am trying to build a Fitness tracking database, where i am trying to input a calculated item. there are two inputs and i want an out put, i put in Height and Weight, and then i have a TBL that has Height then the maximum weight that applies to that height...
so TXT BOX 1 = HEIGHT TXT BOX 2 = current Weight TXT box 3 = MAX Weight
my tbl looks like this
58" 131lbs 59" 136lbs 60" 141lbs 61" 145lbs
What im trying to do is have the form read automaticaly what i put in TXT BOX 1 and enter in automatically the MAX weight for that height.
I'm doing a query for a call centre that is to calculate the number of calls for locations over 100k Square foot. However, I want to normalize the calculation by 100k SF. So the calculation should count the call centre work orders and then multiply it by 100k and divide it by the location Area.
I have no clue where to start, below is where I'm at. the new expression that i've added "wo" is where I want to count the total ([CallID]*100000)/[Area]. this isn't working.
Using Access 2010 and ODBC connections to pull data from 3 sources (SQL Server 2008, a customized Documentum application, and Windows AD accounts). Problem is user_login_name is sometimes recorded as lowercase, sometimes as propercase, and sometimes as uppercase depending on which table or source being used. For example, lowercase would be "abcd123", propercase would be "Abcd123", and uppercase would be "ABCD123". I thought I could just wrap each join comparison in the From stmt in the query(s) with the UCase() function, but Access doesn't like that.
How can I convert or cast the various user_login_names within the FROM join statement to be the same text case so joins will pull correct data?
I have a multivalue text field called "Groups" that is generated through a relationship with another table called "Comments". When I set this up I left the field as a text field when it should have been a number field, and now I can't use the database with sharepoint, because the field needs to be a number field.
My Question is there a way to convert it without loosing all my data, as there are over 5000 records?
My Table with the multivalue field is called "Contacts", the field is called "Groups".
I tried update and append queries but I must be doing something wrong....
I am very inexperinced with Access, please be kind. My problem is that from the form view the simple division of 2 #'s are placed in the correct text box I selected, but I am unable to get the value to enter into the data table. Please help.
I am trying to convert an excel formula into the expression builder on my form.
the excel formula is this: IF(AND(E4>=2.65,F4<1.85),"Poor",IF(AND(E4<2.65,F4< 1.85),"Unacceptable",IF(AND(E4>=2.65,F4>=1.85,F4<2 .65),"Generally",IF(AND(E4<2.65,E4>=1.85,F4<2.65,F 4>=1.85),"Poor",IF(AND(E4<1.85,F4<2.65,F4>=1.85)," Unacceptable",IF(AND(E4>=3.45,F4<3.45,F4>=2.65),"H igh",IF(AND(E4>=2.65,E4<3.45,F4<3.45,F4>=2.65),
I've got a Table with a string of data delimited by an "*". The first part of the string before the "*" can be variable in length, so if I use a LEFT expression to break apart the 2 parts of the string on each side of the "*", my results are incorrect. In MS Excel, I can use the following formula to solve the problem:
=LEFT(B6,(FIND("*",B6)))
You are just substituting the number of positions with a Find command that looks for the "*", and returns anything left of the "*". Unfortunately, the brilliant programmers at Microsoft, once again, don't have an Excel function in Access. how to recreate that functionality in a table in Access?
I have a database that is designed with a front and back end, with the FE linked to the BE. Some potential customers want to play around with the database, and the easiest way for me to get them access to it would be for me to combine the FE and BE and just give them a copy of that. how to link a table, and I know how to import a table. What I'm unsure of is how to cleanly convert a linked table to a local table.
I'm looking for a way to create two feilds in a table, one for lbs and one for kgs. The data comes to me in both forms and I would like Access to calculate whichever field is missing. Meaning sometimes I can enter the weight in pounds and it will fill in the kgs and vice versa.
Is this possible and if so, how the heck do I do it?
I am designing a bead ornament database for my mom to track inventory of beads, inventory of finished ornaments and cost/pricing. I have a table that joins the Item and bead part with the quantity of each bead part needed, it has the following fields: ItemMatlID, ItemID, BeadPartID, QtySo I have multiple rows of ItemIDs for all the BeadPartID & Qtys.Now I need a query with 1 line per Item and all BeadParts and their quantities. However, I need the BeadPart to be a row data and not a column heading. So a crosstab is out, I believe since it wants to make the BeadPart the column heading and not data in the query.
I have a spreadsheet of generators with associated data: Make, Model, etc.
When I build the tables for the Make, each make will have it's own PK. Is there a good way to update the spreadsheet, replacing the Make for the PK other than using the find and replace function in excel (or update query in Access).
Example:
tblGeneratorMake GeneratorID (PK) GeneratorMake
John Deere has PK of 1 Kohler has PK of 2
If my spreadsheet has a list of Kohler generators, I need to change "Kohler" to "2".
On my form I have calculated fields based on information from my sub forms. I have the calculations all working fine however I can seam to figure out how to save them in the table that my form is linked to as my control source is the calculation. Should I be using my calculations in an area other than the control source?
I am thinking, probably not, but always worth "putting it out there"!
Is there a way that I can define a minimum data set (ie fields which ideally should be completed (but I cannot make them compulsory since the information may simply not be available) and then have a sort of bar thingey (like a progress bar but static) that says "Your minimum data set is x% completed".
Simplistic example - if age, gender, hometown and mothers age are the (ideal) minimum data set and three of these fileds are completed then there would be a field (probably doesn't even have to be a graphic - maybe just a label) that says "Your data is 75% complete"
I have a (crosstab) query that summarizes information in the way that I need for a module that I have written. However, I've noticed that the each time the module tries to pull a value from the query (every step of a Do loop) it takes seconds, compared to a "real-time" pull from a table. I have had a program running for about 2 days now, whereas a similar program that pulled data from only tables took only 15 minutes. I don't forsee needing the data in its original form.
How do I convert a query to a table? (Export the query to Excel and then import.)
In a query I would like to extract the last ten years. This is what the data looks like:12/13 (Data type = text)(Short for 2012/2013 which actually represents 7/1/2012 to 6/30/2013.)
I am able to extract the "12" and turn it into the general number 2012 using: ("20" & Left([TAXYR],2))*1..But how can I convert that to the data type-date so I can include those records in the past ten years from todays date? I was trying to use DateAdd but I think the problem is the data type and where converting to date gives me "1905" or "9/##/1905." I get why it does that, but is there a workaround? Maybe adding 39,785 days?? (2014-1905)*365
