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 Replies


ADVERTISEMENT

Reports :: Calculated Field Showing Blank

Feb 11, 2014

I'm working with a report that totals the number of times a topic is returned from a query. If a topic is not returned at all, i don't want it to show at all. Currently it is showing a blank field for that topic name and blanks in the count as well. Here's the filter i've put in to pull the right data out of my query: =Sum(IIf([Caller Used Resources]="No",1,0))

View 2 Replies View Related

Tables :: Averaging Calculated Fields While One Field Is Blank

Feb 5, 2015

Access 2010 database.

I am trying to get an average from several numbered fields, which some may occasionally be blank.

=Nz([JanUS],0)+Nz([FebUs],0)+Nz([MarUS],0)

I get an error message that says "The expression =Nz([JanUS],0)+Nz([FebUs],0)+Nz([MarUS],0) cannot be used in a calculated column. This is a hypothetical expression being used.

The real expression is ...

([GC A1 - 2]+[GC B1 - 2]+[GC C1 - 2]+[GC D1 - 2]+[GC E1 - 2]+[GC A2 - 2]+[GC B2 - 2]+[GC C2 - 2]+[GC D2 - 2]+[GC E2 - 2])/10

This expression works great until a field is left blank, and then the average box is left blank.

The fields I am trying to add are Long Integer. Would that affect it?

View 1 Replies View Related

Queries :: Calculated Field Is Blank For Records With Even Primary Key

Jun 17, 2013

I have a calculated field in a query. The field name is TotatPt (this is to calculate the total points students have earned during the term). The expression is as follows:

Code:

TotalPt: [Att1Pt]+[Att2Pt]+[Att3Pt]+[Att4Pt]+[Att5Pt]+[Att6Pt]+[Att7Pt]+[Att8Pt]+[Att9Pt]+[Att10Pt]+[Att11Pt]+[Att12Pt]+[Att13Pt]+[Att14Pt]+[Att15Pt]+[Att16Pt]+[Att17Pt]+[Att18Pt]+[Att19Pt]+[Att20Pt]+[Att21Pt]+[Quiz1Pt]+[Quiz2Pt]+[Quiz3Pt]+[Quiz4Pt]+[Quiz5Pt]+[Quiz6Pt]+[Quiz7Pt]+[Quiz8Pt]+[Quiz9Pt]+[Quiz10Pt]+[MidtermWritPt]+[FinalWritPt]+[Proc1Pt]+[Proc2Pt]+[Proc3Pt]+[Proc4Pt]+[Proc5Pt]+[Proc6Pt]+[Proc7Pt]+[Proc8Pt]+[Proc9Pt]+[Proc10Pt]+[Proc11Pt]+[Proc12Pt]+[Proc13Pt]+[Proc14Pt]+[ProcPracPt]

I think this should be no complicated expression (though a bit long) and should just add the fields together. But what I get is that the calculated field appears as expected for records with an odd primary key (1, 3, 5, 7, ...) and turns out blank for records with an even primary key (2, 4, 6, 8, ...)!

I've attached a screenshot.

View 6 Replies View Related

Reports :: Calculated Fields In AcNormal Report Remain Blank

Aug 5, 2013

I have a report with a number of calculated fields, and information that is pulled from the form that calls the report. Pulling the information is done in the Report_Load() event by setting the report.txtBox = Form.txtBox. When I open the report in acPreview all the calculated fields are populated as they should, and when I open the report in acNormal mode (which is what I want) the calculated fields remain blank.

View 6 Replies View Related

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

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 4 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

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

Blank Date Field Default On Query Criteria

Oct 8, 2007

Hello.

I have a query that is pulling data based on a date in a table. For some records, that date field is empty. For those records, the data should be pulled based on the date of 1/1/06. I tried doing this
=IIf([Signed SLA Received] Is Null,1/1/06,>=[Signed SLA Received])

It doesn't like this at all, it brings back no data. I also tried putting 1/1/06 in quotes, but it didn't make any difference.

This is probably a totally incorrect way to get this done, but I don't know how else to try it.

Thanks!
Stacey

View 3 Replies View Related

Unable To Return Query If A Field Is Left Blank

Dec 27, 2007

Hi all,

I am having a problem getting my query to work properly. I have read through this query section but it just got me more confused. I know some have used IIF function but it didn't work for me.

here's my code:

SELECT tblEmployee.UserName, tblODF.ODFNumber, tblQueue.Queue, tblStatus.Status, tblODF.ODFScanDate
FROM tblStatus INNER JOIN (tblQueue INNER JOIN (tblEmployee INNER JOIN tblODF ON tblEmployee.EmployeeID = tblODF.EmployeeID) ON tblQueue.QueueID = tblODF.QueueID) ON tblStatus.StatusID = tblODF.StatusID
ORDER BY tblEmployee.UserName, tblStatus.Status, tblODF.ODFScanDate;


I want everything to show even if one of the fields is blank.

Thank you

View 14 Replies View Related

Adding Blank Field Through Make Table Query?

Aug 28, 2013

I'd like to know how to add a blank filed (memo), called "comments" that is not pulled in from any existing table, in an existing maketable query which is getting it's fields from different linked tables , so that the destination table that comes out of this query, has all those data along with an extra column for me to add comments.

View 5 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

Tables :: Creating Table Via Into Query With Blank Numeric Field

Jun 13, 2014

I am creating a table, using an INTO statement.

I require a blank numeric field, which users will be updating via a form.

How do I make the field numeric, as my current script makes it a text field

'' as Ticket_No

And you can't cast/convert text to numeric ....

View 2 Replies View Related







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