Please.. Some One Help Me In Building This Expression In The Forms
Apr 21, 2005
Hi Guys,
Iam doing an Access project And i have poblem in calculation in a form. This calculation basically is between a form and a table. I have a form called "Yearly _Amount" in which there is a field called "Total_Hours". Now i want to calculate this Total_Hours as "Sum of Hours" (Hours is record in "Time_Sheets" table) Whenthree fields are equal ie., Employee_#,Fiscal_Year and Project_# in Yearly_Amount form is equal to
Employee_#,Fiscal_Year and Project_# in Time_Sheets Table.
Please guys help me out. Iam struggling too hard but iam not finding a solution.
View Replies
ADVERTISEMENT
Sep 23, 2006
I've been trying to figure this out, but it looks like a no go.
I wanted to give my users the ability to create/modify queries, but NOT create/modify tables.
Does anyone know if this is possible in Access 2002?
Thanks,
Earl
View 2 Replies
View Related
Sep 18, 2013
I want to put an expression that count all Debt in a field named "Payment". What would be an expression for this: =Count([Order].[Payment], "debt")?
View 1 Replies
View Related
Jan 2, 2006
Hi, I am desparate!! I work for a high school and need a database to record student community service hours over their high school careers. I have been trying to do this on and off since the summer. I promised I would have it done by the end of winter break but........I may have to go back to a spreadsheet if I can't get this right. That has proved to be a royal pain in the past few years.
I have set up tables but getting the relationships right is a problem. I dont know if it is possible but I would like to set it up so that I have a student form with 4 subforms for each school year. Each subform (4 tabs) will have the title of the school year (i.e. 05-06). We don't allow them to get credit if they go over on their hours for a given year. In other words, they can't do all their service hours in say, freshman year. They have to do 10 as freshmen, 20 as sophomores, 30 as juniors and 40 as seniors.
Each year I can refresh from the school db (for new students, etc.) but I can't go in and create a report in that program as we don't have the capability.
I am trying to teach myself but every tutorial I come across doesn't give reasons why you do something so I can figure out how to convert that info to help me. I have been researching this for a long time and looking at sample dbs.
Can anyone help? I am going to try to attach a copy of the mess I have made so far, Hopefully
Thanks in advance.
View 6 Replies
View Related
May 11, 2006
Access 2000
I have an “unbound” form with a list box, a text box, and a combo box. The list box’s Row Source Type is a Query. The query’s criteria come from the textbox and a combo on the form. Once the information is entered into textbox and the combo box a macro is set to run on the combo box’s “On Change” event, which runs a Do Command Refresh Page. This expression [Forms]![Form46]![month] is an example of how I am setting the criteria in the query; which when I put the month “May” in the text box passes that information to the query’s design view “Month column. The combo also works the same way. What I need is to know what type of expression do I use so that I can use “All “ in the text box and combo box.
Thank You
Carbo
View 1 Replies
View Related
Apr 30, 2014
I'm trying to create a text box on a form which displays a value based on numerous criteria.
Basically I manage a database of employees. Some are external employees, some are internal. Some have left the organisation, and some are still employees.
I have two fields which need to be pulled into this expression:
is_leaver - text box, with value either "Yes" or NULL
organisation - name of organisation (text)... lets say my organisation is called Happy People Ltd
The text box I have is called employee_status
I can see that there are 4 possible combinations:
1. Internal Employee - Is an internal employee and still works here
2. Internal Ex-Employee - Is an internal employee but has left
3. External Employee - Is an external employee who still works here
4. External Ex-Employee - Is an external employee who has left
So far I've managed this:
Code:
=IIf(IsNull([is_leaver]),"Employee","Ex-Employee")
This displays whether they're an employee or ex-employee. How would I form the expression to combine the two fields, and calcuate whether they're internal or external?
Due to the way the form is set up I don't want to have two separate text boxes and would prefer to combine it.
View 5 Replies
View Related
Dec 9, 2013
I'm using A2003 (yes, still). An accounting type form requires the "Type" be entered as either (numeric) 1, 11, or 2. On the same form, one of the three Yes/No fields must be checked as confirmation.
My hope is that I can use the "SetValue" action in a macro.
My first attempt (to set the "Assiniboine" field to "Yes" or "On"):
Item: [Forms]![DisbursementMade]![Assiniboine]
Expression: IIf([Forms]![DisbursementMade]![Type].[AfterUpdate]=1,Yes)
That isn't doing anything for me.
The default value for the "Type" field would normally be 1 (but that presumes the user will not skip past it without confirming it) so I have no default value.
The other two Yes/No fields would also be Set by the same logic: (if Type=11, [Bank]=Yes) (if Type=2, [PettyCash]=Yes)
Alternatively, I've tried the reverse approach: If [Assiniboine]=Yes, SetValue of [Type] to 1 so far without success. I'm not using VBA, since the bookkeeper changes from year to year and there is no Administrator. Although the computer has A2010, and can run an .mdb file with macros, successfully, making changes within A2010 produces an 'embedded macro' that isn't readable in A2003.
View 2 Replies
View Related
Oct 17, 2006
Hello all,
I need some assitant in building a simple check reconciliation database. I was wondering if anyone can guide me in doing so? Or if anyone has some kind of sample i can use that would be great. thanks
View 4 Replies
View Related
Nov 7, 2006
Hello,
I want to build a agenda, like the one shown in the image below.
I have no idea how to start.
Any help would be greatly appreciated.
Thanks in advance
http://www.wega.nl/images/scherm/agenda.gif
View 5 Replies
View Related
May 8, 2006
I have made an Access database containing contract names and addresses. I need to have each of these contracts linked to another table which shows the allocated engineer for the particular contract. I used a "one 2 many" relationship using a field "LinkID" to do this. Each engineer has a unique "LinkID" in the engineer table but the engineer changes each day. The engineer for each particular day comes via an excel spreadsheet. The way it works at the moment is that the engineer name has to be manually changed each day, and this takes time. I have tried importing a linked excel table which looked like it would work, but was not able to use the linked table in the same "one 2 many" related table format as above.
What i need to do, is find a way for the fields containing the engineer name to be automatically updated somehow, possible by reading the values from the excel sheet.
Anybody know of any way for this to be done?
Thanks, and hope this makes sense got its got me baffled.
*** Edit **** Thinking about it, i could have an imported execl sheet which contains all of the engineers for each day. Would there be a way using a macro, to update the data in the engineers table that is related to the contracts, by overwriting it with the data from the linked spreadsheet?
View 4 Replies
View Related
Jun 20, 2005
I have at least 15000 records and all of them should be corrected if there is existing duplicates with same EMBG and different name. More precisely if there are 2 persons with same EBMG lets say 123456789 but one with name Naim Arifi and other one Naum Arifi then query should present to me Naim and Naum. Example
1. Naim Arifi 123456789
2 Elton John 123456452
3. Naum Arifi 123456789
4. Naim Arifi 123456789
* *
* *
* *
* *
722. Naim Arifski 321546798
* *
* *
So the record 1 3 and 4 and 722 should be highlited because they have same EMBG and I need to find where is the mistake manualy (correcting from Naum to Naim). In this case row number 4 is mistake instead of Naim is Naum. I need to correct it manualy.
Could someone provide me some help
View 4 Replies
View Related
Jun 21, 2005
I'm running a 9 parameter query, where values for the criteria are drawn from a form. I'm confused as to how I build an "if, then" string so that if one of the parameters is left blank, the program will skip it and go to the next.
Any suggestions?
View 8 Replies
View Related
Nov 8, 2004
Ok, I currently have a query built with requests. What I want to do is initially set each request with a priority.
Then when one request is closed the query will take the requests with priority 2 and change it to priority 1, change the request with priority 3 to priority 2 and so on. Also any new requests that are entered I want to be able to set their priority as well. Can anyone help me with this, or is it even possible?
Any help would be greatly appreciated!
View 4 Replies
View Related
Mar 11, 2005
I need to create an sql statement where the having statement can grow depending on how many conditions I pass to it. There maybe 10 conditions or there may only be 1 condition statement. I guess I'm really looking more for proper structure. Should I use "if" statements or is there a way to structure a variable with "OR" statements in it and use that.
conditionStr = "Rental"
autoSeg = -221205415
strSQL = "SELECT tblSubSegments.fkSegment, tblSubSegments.fkType, " & _
"Sum([tblSubSegments]![Quantity]*[tblSubSegments]![Units]) AS Expr1 " & _
"FROM tblSubSegments " & _
"GROUP BY tblSubSegments.fkSegment, tblSubSegments.fkType " & _
"HAVING (((tblSubSegments.fkSegment)= " & autoSeg & ") AND " & _
"((tblSubSegments.fkType)= '" & conditionStr & "'))"
rst.Open strSQL, gblEstDataCnn, adOpenKeyset, adLockPessimistic
With rst
count = .RecordCount
End With
This works fine and returns a record count of 1 however if I change the conditionStr to:
conditionStr = " '& PMTravel & 'OR' & Rental &' "
This of course does not work. Thank you for any help.
View 2 Replies
View Related
Mar 29, 2013
I have a form used for taking inventory. One table to lookup items, one for writing values. Tables linked by an ID #. I created a text box on my form to lookup a value called PARQTY in the lookup table. I need to be able to subtract that value from the QTY that is entered into the Qty_Counted box on teh same form when they take inventory.
I created a new Text box and in the control source for it, I entered an expresion that basically says subtract The PARQTY from the QTY_COUNTED it reads = [QTY_COUNTED] - [PARQTY]
The box is subtracting the QTY_COUNTED from the ID # that is linking my two tables and displaying it. Its not supposed to be subtracting the QTY_COUNTED at all. It should be subtracting the PARQTY value that is displayed from the QTY_COUNTED. The PARQTY box does display the right value from the lookup table so I can't figure out why it's pulling that ID number or where from. The rest of the form / program works and we've been using it for a few years. I also created that, but like now, I had to muddle through each and every little thing I did.
View 9 Replies
View Related
Mar 23, 2014
I've never used DLookup before and I can't get it to work for me so far.
I have 1 table which contains products and different properties of each product, such as the weight of the product.
I have created a query which sums the weight of all products, but only for those that have a value >0 in a certain field. This all works fine.
Now I simply want to display that calculated total weight in a text box on a form. So I thought DLookup could be used for that. But I can't get it to work, maybe because I'm not putting in any criteria? In the control source of the text box
I've put the following:
=dlookup("[TotalWeight]","qryTotals")
I don't have any criteria, I just want the value from my qry expression. The textbox on my form now displays #name?
View 3 Replies
View Related
Jun 4, 2015
This error message keeps popping up no matter what I do. "Database can't find the field 'QuotationNumber' referred to in your expression." I tried to define a text box by using a DLookUp function, however that did not work. I deleted the function, and now this error message keeps popping up. I have searched everywhere for an expression with 'QuotationNumber' in it, and I cannot find it. What do I do?
View 7 Replies
View Related
Jun 4, 2013
1. I have a table called "CONTRACT NAMES AND NUMBERS" with a field called "REDUCED_USERS", this field is a checkbox (Yes/No in the table). This table houses all of the customers with their id numbers and basic info.
2. I have another table called "REQUESTS" which houses their orders. This also has a field called "REDUCED_USERS".
In my form "Amendment Request Tracking" I have tried to do a DLookUp in Expression Builder to check the box, per order, if the customer has reduced users in the "CONTRACT NAMES AND NUMBERS".
I have tried many variations and have just realised that this is probably because it is a yes/no field so may struggle with what to populate with (currenly nothing!).
My most recent variation of expression is (where NAD_NUMBER is the common field in both Tables and Form with relevant relationship):
=DLookUp("[REDUCED_USERS]","[CONTRACT NAMES AND NUMBERS]","[CONTRACT NAMES AND NUMBERS]![NAD_NUMBER]=[NAD_NUMBER]")
View 10 Replies
View Related
Feb 23, 2006
Hi,
I am hoping this is the right place to find some help.
I have just started access (2003) and i am trying to build a list.
Let me explain.
I have a table that contains
cartcode, description, price
and another containing
manufacturer, model
I then created a new table that links these tables together so that if i click the little + next to a cartcode i can see a list of models linked with that cartridge.
What i want to do though is create a query/table/anything that will create me a list of cartridges and then in the second column a list, hopefully seperated by a comma or all the printers models related to that cartridge.
I hope that makes sence.
So my question is how do i create the list which included related printers?
Thank you
View 4 Replies
View Related
Mar 17, 2006
Hi all, i'm new here and I have a query about a database i'm building with which any help would be appreciated.
I'm designing a form and I need to be able to have an option to save to a temporary table for retrieval by users so that they can continue their saved record at a later time. Does anyone have any ideas on the best way to do this as I am relatively inexperienced and I have to have the whole database complete before Wednesday:confused:
View 2 Replies
View Related
Feb 21, 2007
I have been asked to add a building entry log (Session Log) for a gym members database I created 2 years ago, unfortunately having racked my brain for the last week I am no nearer a solution.
Background:
I am a novice with a little knowledge (Dangerous I know), the main structure of the database is Contact [Storing address and name], Payments [Storing membership info - renewal date etc] and Sessions [Storing session date & time] this sessions table was recently added as a stop gap measure to allow the staff to enter user sessions manually.
Problem:
System needs to log user as IN and record the current time on first entry of userID (5 digit number), then the second time userID is entered it must log user out and record the time.
Resources:
I have just a single laptop running windows 98SE, and a USB numeric keypad. I should be able to get hold of a second monitor if required, but beyond that it is a case of beg, borrow, or steal as my budget is ZERO.
Any help, or suggestions to get me on the right track would be greatly appreciated.
View 3 Replies
View Related
Jun 28, 2007
Hi i have a assignment in which i have to build a database with documentation (attached file has details about the assignment) I have no prior database experience, and have no idea where to start.I want to build the database for a computer store which sells computer products. I don't want to make it very complex, since my database skills at this stage are very poor. I will appreciate if someone gives me a run down or steps in building this database. I have no clue where to start, since this is probably the first database i have ever built.
View 3 Replies
View Related
Aug 15, 2007
Hi,
I want to build an access application for a bicycle shop, I have a little access knowledge and would like to get more information on doing this.
Is there a place to look for on the web on how to build an application like this ?
thanks.
View 2 Replies
View Related
Jun 19, 2007
Hey Guys
Great Forum and another newbie here
I have created an invoicing system for my business, as i was unhappy with MYOB.
Basically i have Product ID and Desciption in 1 table.
In another, called registry, this is where i input the data for the order.
What i basically want to do is?
When i type in the Product ID in the registry table, i want the description field to automatically appear in the cell next to it. As this would save a lot of time
Any help would be much appreciated
Thanks
Tarek
View 3 Replies
View Related
Sep 20, 2005
OK, here's the deal. I have two data tables, one of which holds many types of customer information and has a very large number of records, including, name, address, phone, and email. The other table contains only email addresses. Each email in the second, smaller table corresponds to a record in the larger list , by virtue of both records sharing the same email address (primary key?). I need to be able to take a short list of email addresses from one table and produce all of the records from the larger table that have corresponding email addys, essentially. Can somebody shed some light on how I can structure a query to solve this dilemma? Sorry for the newb question, just getting started out using db's.. Thanks!
View 1 Replies
View Related
Jan 23, 2007
Hello,
I have a query where I would like to include names from a name table. The problem is that I get this "ambiguous outer joins" error when I try to include it in my query.
I have a main table, the name table, and a join table with the IDs from both of the other two tables in order to model a many-to-many relationship between main table and name table.
Is there a way around this?
Right now the query contains the main table and the join table so I get the name ID, just not the real names.
Thank you.
View 2 Replies
View Related
