Using A Calculated Field In Another Query

Nov 15, 2007

I can't seem to find a problem like this already answered....

I have these two calculated fields:

dtmNPacketDue: IIf([strRevTypeNIRB]="Full" Or [strRevTypeNIRB]="Termination(Full)",[dtmNIRBMtgDate]-28,[dtmNExpDate]-28)

dtmCOIDueDate: IIf([strRevTypeNIRB]="Termination(Full)" Or [strRevTypeNIRB]="Termination(Expedited)","N/A",[dtmNPacketDue]-42)

When I use the one in RED in a calculated field in a new query, things are peachy.
But when I use the one in BLUE in a calculated field in a new query, the message is #Error

My Calculated field (which produces an #Error) in the new Query is:

2ndCOINotification: [dtmCOIDueDate]-14

(if I insert [dtmNPacketDue] instead...it works

Perhaps my problem is that dtmNPacketDue (a calculated variable) is part of the BLUE calculated variable....??

Any ideas greatly appreciated....

View Replies


ADVERTISEMENT

How Do I Add Calculated Field To A Query

Nov 23, 2007

I have an inventory transaction file that has quantity on hand as a calculated field. I am trying to add this unbound field to a report using a query but I can't figure out how to add my quantity on hand field to the query since it is a calculated field and not a bound control.

Any help would be appreciated.

Thanks

darrrellx

View 1 Replies View Related

Calculated Field In A Query

Apr 24, 2006

Hola folks.

I am trying to create a calculated field in a query that subtract 2 fields within the same query.

That is, i have a FiscalWeekStart and FiscalWeekEnd pulling from a table. I created a CountofWeek field that count the weeks between the FiscalWeekStart and End using "DateDiff("w",[FiscalWeekStart],[FiscalWeekEnd])".

I also have a HolidayWeek field that pulls from a table using a
Count expression in the query.

My goal is to subtract the CountofWeek and the Holiday week calling it NonHolidayWeek using "NonHoliday Week: IIf(IsNull([CountofHolidayWeek]),[CountofWeek],[CountofWeek]-[CountofHoldayWeek])"

BUT IT IS ASKING FOR PARMETERS ON THE "CountofHolidayWeek".

Thanks for the help in advance.
TukTuk

View 1 Replies View Related

How To Sort By A Calculated Field In A Query?

Jul 21, 2006

I have a query that draws from two tables, and the field in question looks like this:

X: [TableData]![FieldA]*[TableNumbers]![A]+[TableData]![FieldB]*[TableNumbers]![B]


It all works fine and dandy, but once I set it to sort by this field and run the query, it gives me the parameter prompt, asking me to enter the Parameter Value of FieldA and then for FieldB.

Is there a work-around for this within the query?

The only other solution I have in mind is making another table from this query, and then creating another query just for sorting said table, but that seems inefficient at best.

View 2 Replies View Related

Query Of Calculated Field Gives Blank.

Nov 8, 2006

I have the following calculated field in a query:

LoanNo:IIf([Stats].[LoanCnt]>1,[Stats].[LoanCnt] & " Loans",[Loan].[LnNo])

Within its query, it correctly displays the loan number associated with a particular loan.

However, when I run a second query

SELECT LoanNo
FROM qryLoanInfo

it comes up blank for every record (other fields are OK)

Does anyone have any idea what might be happening?

View 2 Replies View Related

Criteria For Calculated Field In Query

Dec 8, 2006

Hi,

I have a calculated field in my query called "outstanding".

this is how the query looks:


Expr: SubFormTotal………………[qty]*[price]

Discount …………………………...[Discount]

Expr:Total………………………….[SubFormTotal]-[Discount]

Deposit…………………………….[Deposit]

BalanceToDate…………………….

[B]Expr: Outstanding…………………..[Total]-[Deposit]-[BalanceToDate]



PROBLEM: i want to search for all the "amount outstanding" that >0 HOWEVER when i put that as the criteria it asks me to enter values for other calculated fields

what am i doing wrong and how can i solve it?

thanks

View 7 Replies View Related

Parameter Query On Calculated Field

Apr 17, 2007

I hope someone can help as I am stumped at present. I have created a calculated query using dateadd. I have a table that has date fields, ServiceContractAgreed and ServiceFrequency. The service frequency field holds a value of 3,6,9 or 12 in months. Using dateadd I can add the months to the ServiceContractAgreed field in a field called NextService, ie =IIf(IsNull([ServiceContractAgreed]),"N/A",DateAdd("m",[ServiceFrequency],[ServiceContractAgreed]))
This works fine but what I want to do now is create a parameter query to search the NextService field, using the between operator to search between dates. I've created a new query to do this but get every record back, no matter what dates I put in. It's probably something really simple to do but I'd be really grateful for any advice!

Cheers

Dave

View 2 Replies View Related

Like Operator In Calculated Query Field

Aug 1, 2007

Good afternoon,

I have a field that contains semester info ie: F07 Sep.

What I am trying to do is using an if statement to test for which month and returning a section number based on the month. I can make it work using an exact match but am trying to make it work on the partial match "Sep" so I don't have to change it next year as F07 stands for Fall 2007

This is what I've tried and various combinations.
Section1: IIf([tblOnlineCoursesDetails]![Semester]="* Sep","70","")

Having trouble getting this to work.

Any help or suggestions would be appreciated.

Thx

Jerry

View 1 Replies View Related

Queries :: Calculated Field In A Query

Mar 23, 2015

This is not the normal calculated fields in query's. What I want is different, I want it to be like

IFF(ShowID="A",[TotalSales],0)

Thing I have is, I want to know all the Total sales in on row that has Show A in it and then Total sales for Show B. Problem is, I have over 130 shows. How would do that?

View 6 Replies View Related

Queries :: Calculated Field In A Query

Nov 25, 2013

I Have some calculated fields in a query and want to update it in the table .So,is there anyway to store these fields.Otherwise can i store this Query data every month in database so that when i re-run the query the previous month data will not be affected.

View 1 Replies View Related

Queries :: Calculated Date Field In Query

Sep 10, 2014

I am having a problem with calculating a date field in a query. Prior to this posting I've done some research and made several changes to my query. This only resulted in fixing one problem but then creating another problem. Original problem was I had 2 fields, arrived (23:36) and stemi (0:07). I use the following calculation AT_ST: DateDiff("n",[arrived],[stemi]) which resulted in -1409. So my research showed me I had a problem with the date whenever the time went past midnight and trying to calculate a zero hour number. I changed my calculation to

AT_ST: IIf([stemi]>=#11:59:00 PM#,(DateDiff("n",[arrived],[stemi])),(DateDiff("n",[arrived],[stemi]+1440) Mod 1440))

This works fine and gives me the result of 31 minutes which is what I want, however the problems comes in when I change to this calculation any where there was a negative time now has a 1400+ plus value. Such as arrived (7:37) and 1st_eck (7:18) = 1426 where as before it would report -14 (yes, negatives are acceptable for my reporting because sometimes a call to the hospital is placed before the patient arrives so we want to report on the negative splits). I've tried using a nested IIF to calculate for stemi time being less than arrived time, this didn't work when I tried to use it on the calculated query field. I was wondering if I could write something to check the value of the calculated field if it is greater than 1440 and if yes - subtract 1440 from it. So in the example above 1426-1440 = -14. Is it possible to do this within the query or do I need to do it using VBA

View 14 Replies View Related

Queries :: Filter Calculated Query Field

Nov 13, 2014

So I have the following query field which calculates another field.

How do I Filter the records in this calculated field to only return TRUE, as if I put "TRUE" in the Criteria for this field (or anything at all) then a parameter message box pops up asking for [Balance].

Code:
Balance1: IIf([Balance]<>0,"TRUE","FALSE")

View 8 Replies View Related

Tables :: Calculated Field In Query Does Not Work

Mar 17, 2014

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.

View 10 Replies View Related

Queries :: Selecting A New Calculated Field In Same Query

Aug 30, 2013

I want to calculate a field that is Sales*6+Salary, then in that same query I want to select ONLY the greatest Salary per employee. So for example

SELECT EmpId, MonthDate, Sales, Salary, [Sales]*6+[Salary] AS SalTot
FROM EmpTable S1
WHERE SalTot = (SELECT MAX(SalTot) FROM EmpTable S2 WHERE S1.EmpId = S2.EmpId);

Can I not select a value that has been calculated this query?

View 5 Replies View Related

Number Duplicates With Calculated Field In Query

Nov 20, 2011

I'm trying to make a calculated field in query that will number duplicates, not count them.

For example, I have a table with the following:

Joe
Joe
Joe
Mary
Mary
Henry
Dave
Dave
Dave
Dave

I want the query to display:

Joe #1
Joe #2
Joe #3
Mary #1
Mary #2
Henry #1

Dave #1
Dave #2
Dave #3
Dave #4

I'm fairly new to to Access and I've been trying to get that to work for awhile now.

View 1 Replies View Related

Queries :: Rounding Value In Calculated Field In Crosstab Query

Aug 4, 2015

I have a very basic query with the following calculated field in it:

ProjRevNRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentNRC]*[Rev Flow Through],0)

I went into the properties if that field inside the query design and changed it to Standard with 0 decimal places and it worked fine.

But when I based a crosstab query off the query that contained the above calculated field, I cannot seem to get the numbers to format correctly. 1231313.424 is shown instead 1,231,313 and I don't have a line in the Properties window to even change the decimal places. It doesn't recognize when I change the format to Standard. I have tried using Round([ProjRevNRC]) which gets rid of the decimal places as desired but does not show commas.

I'm sure it's a simple part of the Round expression that I am missing but nothing has worked.

View 4 Replies View Related

Modules & VBA :: Adding Calculated Field To Existing Query

Nov 12, 2014

I am looking for a way to add a calculated field to the end of an existing query using VBA. Is there an easy way to do this?

The data I receive from an external supplier shows monthly data split by column with a new column added in each month. I then need to reflect this by adding a new column to the end of the query. It is currently a manual tweak, but I want to automate this with code.

View 6 Replies View Related

Queries :: How To Filter Calculated Field In Query Using A Criteria

Jul 17, 2014

I have query with a calculate field to finds the next service due date but I'm having a problem getting it to only show services due dates in the next 30 days.for some reason I cant add a criteria date()-30...I have to calculate the next service it takes service intervals from maskservicemonths field then find the last service date and generates the next service due date NextService: DateAdd("m",[MaskServiceMonths],[FindLast]).

View 14 Replies View Related

Question On Query To Flag A Field Based On Calculated Data

Apr 25, 2005

I have a table with numerous fields including timeIn and timeOut field (these are date/time format). I need to find out how many people were clocked in during each operating hour (based upon their time in and time out). How can I run a query that will allow me to use the times in and out to 'flag' each hour that each person was on duty (a new field for each hour) so that I can run a count on the completed data for every hour?

View 4 Replies View Related

Queries :: Calculating Bonus - Expression Using Sum Of Another Calculated Field In Query

Mar 28, 2015

I've can do this on excel but don't know how in Access. I'm calculating bonuses. My table has salaries, and my query simply multiples each salary amount by a % to get the bonus amount. But I need to calculate adjustments to the bonuses using the sum total of the bonuses my query calculated:

Salary (from table) Bonus (calc'd by query) Adjustment (to be calced)

100,000 1,000 Sum of total bonuses/salary*4%
90,000 900

How do I capture the total of my calculated bonus column to use to calculate the adjustments in my right-most column?

View 13 Replies View Related

Calculated Field In Access Query Contributes To Crashing Excel Table Upon Import

Oct 24, 2012

I have a simple calculated field (As "Ratio") that is readable in Access 2010 query, but crashes in Excel upon importing/running the query in an .xlsx table.

ERROR MESSAGE in EXCEL: "The query did not run, or the database table could not be opened...check database server..contact your database administrator.."

I am simply taking the cost amounts of each project record (shown as "Record Cost") and dividing it by the "Total Cost" of that project to render "Ratio".

The Total Cost is retrieved from another table.

Testing scenarios:
If we exclude Ratio, the whole query import just fine.
If we filter the query to one project (i.e. "Project A") the ratio field imports fine.

However, the whole unfiltered query does not with the ratio!

Other testing scenarios:

If we use a constant for the denominator AS "1" in Ratio (i.e. recordcost/1) the whole unfiltered query and ratio field imports fine.

If we use a constant for the numerator AS "1" in Ratio (i.e. 1/totalcost) the whole unfiltered query upon import crashes in Excel.

This is a mock example of the dataset, the actual query has 50K+ records:

Project Service Date Record Cost Total Cost Ratio
A Welding 1/1/2012 $100 $120 83%
A Plumbing 2/1/2012 $20 $120 17%
B Welding 1/1/2012 $50 $75 67%
B Plumbing 2/1/2012 $25 $75 33%
C Welding 1/1/2012 $40 $61 66%
C Plumbing 2/1/2012 $21 $61 34%

View 5 Replies View Related

Queries :: Average Calculated Field From A Previous Query - Crosstab Data Type Mismatch

Jun 3, 2014

I am trying to construct a crosstab that averages a calculated field from a previous query. It is returning a "Data Type Mismatch" message.

The field I am trying to average is a subtraction of dates to find total days. I assume my field is not a number so I have tried to wrap it in CDbl() to change the type.

The formula is

Code:
CASE_DAYS: CDbl(IIf([Actual Close Date]-[Creation Date]>=0,[Actual Close Date]-[Creation Date],""))

View 5 Replies View Related

Basing A Calculated Field On Another Calculated Field

Apr 12, 2006

My query contains two calculated fields [TaxSavings1] and [TaxSavings2], which are based on some currency and number-type fields in one of my underlying tables.

I just created another field in my query which looks like: [TaxSavings1]+[TaxSavings2]. Instead of adding the two fields, it actually lumps the two numbers together. For example, if [TaxSavings1] =135 and [TaxSavings2]=30.25, it will give me: 13530.25. I need it just to simply add, i.e. answer of 165.25.

Does anyone know how to correct this? Thanks in advance.

:confused:

View 1 Replies View Related

Tables :: How To Make Calculated Field Based On Field In Another Table

Jun 18, 2014

Table1 contain Two fields (3Months) and (6Months)

Table2 contain Two fields (3Months) and (6Months)

the table 2 is the source of a form that will let the user change the numbers. table 1 should change Date1 and Date2 Fields based on the two fields (3months) and (6months) if i want to make a lookup wizard it should be changed manually and if i make a calculated field i can't find other tables in the expression builder

View 1 Replies View Related

Queries :: Calculated Field To Display Value Of One Field Based On Another

Jul 23, 2015

I have a query with a Date field for EndDate (the dates for end-of-week, Fridays in our case) and another field for Sales (number of sales, not dollars).I want to add 4 calculated fields that represent weeks and have the Sales appear in the correct column (field) for that date.So I will have columns for 10 July 15, 17 July 15, 24 July 15 and 31 July 15 and I want the Sales for each record to land in the correct date column, based on the EndDate column. (The 4 fields is just for the sake of the example, I will actually be having dozens of these calculated date fields).I tried to do it by setting up the 4 calculated fields like:

10Jul15: Sales
and then adding Criteria like:
EndDate = #10/07/2015#
It doesnt work.

View 3 Replies View Related

Tables :: Calculated Field Error - Cannot Define Field More Than Once

Jun 10, 2015

I use access 2010.

I am assuming by the error code, one can not use a calculated field to calculate another field.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved