I am trying to make a query using fields from multiple tables. I used the Expression builder to create it and it is a formula field where multiple table fields are involved. But whenever i run the query Access asks for a parameter value in a modal dialogue for the value of the fields. Is it because the field names has Square brackets around it? But access puts that automatically. How to make it work properly?
View RepliesI have created a new inventory db based on our old one. It looks very similar with a few minor changes. In the old db, there was a formula in the subform that calculated the # purchased minus # used. In the main form, the Units in Stock referenced this formula.
I copied the formula exactly for the new db, but it doesn't seem to want to work. I can't see any vb code attached to the formula. This is frustrating. I'm also wondering if it's because they used an access template to create the old db, and I am doing the new one from scratch.
Can someone help or would be willing to look at the old and new db. I have eliminated most of the data... only a few things left in inventory to help guide me with the formulas, etc. The form in the old db is Products and Products subform. The form on the new db is frmProductEntry and subfrmTransactionDetails.
I would appreciate any help possible. We want to get started entering our real data. Thanks.
I have a query that calculates input information into a value that then needs to be compared to another query values and will be used to output a % change in a third query. Is there any way to make this happen? Thank you in advance!
View 2 Replies View RelatedI got a table salaries master I want to extract some info out of and calculate some values. The formula below doesn't work, I'm pretty sure it has to do with [pay period] being a text field. Is there no way to make it work?
'Gross monthly Salary: IIf([Salaries Master]![Pay Period] = "weekly";[Salaries Master]![Daily Wage]*5*52/12,IIf([Salaries Master]![Pay Period] = "monthly";[Salaries Master]![Daily Wage]*20))'
I have a
Order Table:
Order ID(Autonumber)
Client ID(Text)
Client Name(Short Text)
Install Gross Rate (Currency)
Install Discount Given in % (Number)
Install Discount Given in GBP (Currency)
Then I designed a Query to calculate the Install Net Rate
Query
I selected
Client ID
Client Name
Order ID
Expression: [Install Gross Rate]*(1-[Install Discount Given in %]/100)-[Install Discount Given in GBP]
I run the query, but only ONE order calculated correctly, rest of Orders returned blank row.
I've got a table set up with sales info and all other kinds of data. How do I get a formula to work? Here are the inputs I have: Total sales (by outside company), total purchases (from my company). I want to know what percent of their total sales are made up of the poducts they purchase from me and have that field update automatically. Its a simple formula really, I just don't know how to enter it properly. Thanks.
View 2 Replies View RelatedHey guys hope someone can help me out here...
i need to have a currency field in one of my tables, the user will enter the price in euros, and the output will be in pounds... therefor i need the following formula to work:
[enterdprice]*[current exchangerate] = price in pounds
i would very much appreciate suggestions/help
hi i have a text field in with this calculation
=([Total amount requested]+[text3])/[Initial valuation]
it displays a div/0 error which i want to remove, i know i need to put this calcualtion in an iif statement but whenever i try and make the formula it never works
=iif([Total amount requested]+[text3])/[Initial valuation],?,""
where the question mark is i am not sure what should go there, as i want the total to of the calculation there and if 0 hide 0 with the ""
this has been bugging me for a while now and i can just get my head around it, probably very simple for someone to answer
regards
chris
Hi all,
A nice easy one for you. I am creating a database, one of the fields is total cost. Is there an easy way to have another field, which calculates the VAT without using a query? I would like this done as soon as the total is entered.
I have done this using a query, but didnt like this way.
I am sure there must be a very simple way of doing this but it has escaped me.
Help
Phil
I have a field in my query which returns results based on a formula that is a function of other fields. The results are: Pass and Fail.
I want to make a query that returns only Fail rows. When I enter Fail as the criteria, a parameter box pops up requesting information be entered before continuing.
How can this problem be rectified?
I need to add a text box to my report that shows the value of a record in a certain field that is corresponding to a given value in another field.
For example, if my report shows the following:
N City
3 D
7 F
2 H
9 K
4 A
the text box should show the name of the city corresponding to the largest number which is in this case "K". is it possible through formula builder?
I'm sure this is a simple issue, but so simple I can't find the answer.
I have a recipe DB and have calories and fat and fiber fields, I want to have another field which will display a point count for a formula (calories/50+Fat/2+fiber/5) but the catch is if the fiber number entered is over 4 I only want the calculation to use 4 max.
Not sure how or where to create this formula to populate a field in the DB.
As you can tell I'm new to all this and any help would be appreciated.
Thanks in advance
Mike
I have a field in my query which returns results based on a formula that is a function of other fields. The results are: Pass and Fail. I want to make a query that returns only Fail rows. When I enter Fail as the criteria, a parameter box pops up requesting information be entered before continuing.
View 1 Replies View RelatedHi All:running Access 2000.I have a qry with a formula field that performs some calculation (Extension: CCur(([OnHand]*[Price])/[UnitsPer]))and it works fine. How can I update the actual table with the result of the calculation for every record? The problem I have is that there's some null values in the results therefore when I try to get totals on that field, it errors out saying "invalid null use"thanks
View 7 Replies View RelatedI have a date in "date to engineering" of 13/ 01/2010 but I am not getting a value in my field which should be 1479 my field is just always returning an empty field
Code:
=IIf(IsDate([date to engineering]),"",IIf(IsDate([date from engineering]),Now()-[date to engineering],[date from engineering]-[date to engineering]))
I am new to the access database world- I have a database in which I have projects that each have a priority- to get the priority # there are about 10 categories that need to total up to give me that priority #
Every formula I have tried, has given me an error message of some sort! PLEASE HELP, and please let me know if I am not explaining this enough!:confused:
Hello,
Im hoping someone can help me. Im not sure if its possible to do what I want...
I have a query with a column Ive created called Indicator. This field looks up the values of 2 other columns and returns a result based on whether they match or not. The formula is:
Indicator: +IIf(Table!EST=Table!ACT,"Match","No Match")
I need to tweek this formula so that it takes into account a variable. For example, if Est is 5 and ACT is 5.001, I want it to be a match.
Any help is much appreciated.
Thanks
First off can someone link me to a helpful page if there is one so that i can learn all these formulas, i know excel v.well but not access..
My query is:
i have the fields:
Product Quantity Unit Price Discount Discount Total Price
prod1 2 222.50 10.0% 44.50 400.50
now i have a whole number of products being listed, and 1 price for each row.. then below at the bottom of the page i have a Sub-Total / Shipping Charge / Total + VAT.
What is the formula i must use to calculate all the cells listed in the Price column (so i can work out the sub-total automatically)?
See Attached image.
Hello,
I'm quite new at this and would appreciate some advice. I have a table of football stats I am keeping. Here's what I am trying to accomplish with three columns (points_for, points_against and wins):
if points_for > points_against then 1 otherwise 0. I can do this in Excel but I'm not familiar with Access.
Thanks!
i am using a query view in an access project, using the following sql
SELECT comturbineLifeID, cycles_used, cycle_limit, cycles_used / cycle_limit AS rata
FROM dbo.tblcomturbineRata
the fields cycles_used and cycle_limit can be a null value
where cycles_used and cycle_limit are positive values, the formula cycles_used / cycle_limit just returns 0
can anybody explain what i am doing wrong, please
how do i write if null then equal to 0, the query does not accept the nz function
Hi guys,
Please would some be able to help me?
I have created a query (its a holiday chart). The table the query is created from shows dates taken in each of the 12 months under the 'month name' column and then in the next column it has the total for each of the individual month.
For example,
Field 1 (Jan)
Field 2 (total Jan)
Field 3 (Feb)
Field 4 (Total Feb)
Field 5 (March)
Field 6 (Total March)
and so on.
Then at the end I have a field for amount left. So what I need the query to do is add all the numbers in each individuals record in all the 'total month' column and then show this in another column which is my 'amount taken' column.
Thank you in advance for your help.
Hi does any body no the access syntax to create this excel formula
=POWER(SQRT(L5/10)*47.1,2)/3769.9
Into access
Thanks
John
How to apply the formula, IF (COUNTIF (O:O;O3)> 1; K3/SUMIF (O: O;O3; K: K), 1)from an Excel spreadsheet into an Access query.
View 2 Replies View RelatedHi
I would like to count number of items witin a text field,
but breaking it down identifying the different items within the
text field.
e.g.
Got a field with fruits listed. Now I am identifying the fruit but want
to know how many rotten fruit there was for each specified fruit.
At the moment my query is by date and the fruit including the column
specifying if the fruit is rotten or not. But there is 3 options in the last
column. How can I count this last column to count how many of these
3 options there are for each fruit in my report?
Please let me know if this makes any sense or not otherwise I will try to explain
it a little bit better.
Need a formula which can capture filename for me. For example, I have a db saved at "C:DlocationofficeChina.accdb". Is there a way to capture "China" in a query?
View 2 Replies View RelatedCould someone look at my code and tell me why my “Me.TargetDate.Undo” won’t work?
First, let me say that the TargetDate field has an On-Click event that calls a module written by Allen Browne), which is shown below and works great.
=CalendarFor([TargetDate],"Select a date from the calendar ")
What happens is that the Me.TargetDate.Undo event always keeps the date selected in the calendar.
Based on something I read from Rual Guy on another issue, I’ve used the TargetDate_LostFocus routine to check for some additional data entry errors. I’ve tried moving the code in “Private Sub TargetDate_Exit(Cancel As Integer)” to the LostFocus, but it doesn’t make any difference to the Undo. I’ve also tried “I’ve tried “Private Sub TargetDate_Change(),Me.Undo…” but that doesn’t work either. :confused:
Private Sub TargetDate_Enter()
If Not IsNull(Me.TargetDate) Then
OldTarget = Me.TargetDate
End If
End Sub
Private Sub TargetDate_Exit(Cancel As Integer)
If Not IsNull(Me.TargetDate) Then
If DateDiff("d", Me.DateOpened, Me.TargetDate) < 0 Then
MsgBox "You have selected a date that is before…bla bla"
Me.TargetDate.Undo
Me.TargetDate.SetFocus
ElseIf DateDiff("d", Date, Me.TargetDate) < 0 Then
MsgBox "You have selected a date that is prior to today's date…bla bla"
Me.TargetDate.Undo
Me.TargetDate.SetFocus
ElseIf Weekday(Me.TargetDate) = 1 Or Weekday(Me.TargetDate) = 7 Then
MsgBox "You have selected a date that falls on a weekend..bla bla"
Me.TargetDate.Undo
Me.TargetDate.SetFocus
End If
End If
End Sub
Private Sub TargetDate_LostFocus()
If Me.TargetDate > OldTarget And Me.cboAssignToID <> OldAssignee Then
MsgBox "The target date can only be changed if …bla bla"
Me.TargetDate.Undo
Me.TargetDate.SetFocus
End If
End Sub
Ever thankful for this forum,
Christine