Tables :: Calculated Column Expression Formatting

Nov 4, 2013

I've now since moved everything into one table but i need formatting of my calculated field (I'm new to all this).

Below are the 3 expressions working independently of one another, I just need them combined in to one if possible. As I am getting an "invalid syntax" error or "too many arguments" when I try to get it working

PHP Code:

0.5*IIf([Type]="Offlist" And [APP_LINE_OFFA]>3,1,0)+0.2*IIf([Type]="Offlist" And [APP_LINE_OFFB]>2,1,0)+
IIf([Type]="Offlist",Abs([EP_DETAILS_OFF]*0.15+[REF_DETAILS_OFF]*0.15),0) 

PHP Code:

IIf([Type]="Onlist",Abs([EP_DETAILS_ON]*0.15+[REF_DETAILS_ON]*0.2+[GEN_NOTE_ON]*0.2+[APP_LINE_ON]*0.25+[SPEC_INS_ON]*0.2),0) 

PHP Code:

IIf([Type]="Aged Report", Abs([EP_DETAILS_AGED]*0.2+[REF_DETAILS_AGED]*0.2+[GEN_NOTES_AGED]*0.3+[SPEC_INS_AGED]*0.3),0) 

View Replies


ADVERTISEMENT

Tables :: How To Change Values Within Calculated Column Without Changing Expression

Jul 31, 2014

Currently I have a calculated field in my table; however, under certain circumstances I need to change the value to another value which is not related to the calculation. I understand that the calculated fields are read only (why is that?), so I was wondering if there was any way to change values within a calculated column without actually changing the expression itself.

View 1 Replies View Related

Tables :: Calculated Column In Same Table

May 19, 2014

I have a table with columns as

1- Name

2-Marks in Subject 1

3-Marks in Subject 2

4-Marks in Subject 3

I want to create more columns in same table as

5-Total Obtained Marks

6-Maximum Marks

7-%age of Obtained Marks

How can I do it in one table

View 3 Replies View Related

Calculated Expression

Aug 16, 2005

I have two email addresses in a table one personal and one work and a question asking which to use, I have a query running from the table and have an IIF expression creating a field which looks to see which one to use (that all works fine !) anyway my client has now asked for an extra field in the query which combines the two email addresses, as in aaa@homedomain.com;bbb@workdomain.com so that I can run an email to both addresses at the same time. fraid I just can't figure it out ! I'm sure it's obvious, I can't find a similar question posted either, I've tried the expression builder using AND for the two email addressses but just end up getting -1 in field when I run the query, not the two email addresses separated by a ;

Anyone any ideas

many tks

:)

View 3 Replies View Related

Conditional Formatting, Expression Needed

Apr 1, 2005

Hello,
I have a date field in my form that I would like to have the date change color sixty days prior to the date entered. Can someone give me the expression to use in conditional formatting? For example: If the date is 9/30/05, I would like it to change color on 8/2/05.

Thanks

View 2 Replies View Related

Negative Value Formatting In REPORTS With Calculated Field

Nov 11, 2012

I am completing a report. I've inserted a calculated field in the report. I'm using Access 2010. Here is an example:

=([AccessTotalsOpen]-[AccessTotalsClose])/[AccessTotalsOpen]

I get the correct answer but cannot find a way to Display/FORMAT a negative number with any of the options that I have available to me?.

Any method to format a calculated field in a report?.

I tried this example but could not get it to work. =Format( FIELD ), " 0; (0)";

View 3 Replies View Related

Forms :: Calculated Fields - Formatting Box As Date / Time

Jan 13, 2014

In my form I have a calculated field that works out the time elapsed for a job, worked out as the QTY/RUN RATE which gives me the time in hours. However I couldn't format the box as a date/time, as it is a calculation and the times may go over 24 Hours which results in the date/time giving me something like 31/12/1899 16:00 for example.

I've got around this now and I've formatted the results as HH:NN:SS using a public function in a module, but the problem is this is now stored as a text field. I've tried setting this to a number field but the formatting doesn't carry over. I've tried setting a custom format in the table as HH:NN:SS but then all I get is just a bunch of 0's, no calculation results.

So how to format this as a number field properly?

Also for any extra information:

- I know you're not supposed to store calculated results, but this is slightly different, the calculated field is a standalone text box and the bound field is a formatted text box.
- The reason I have to store the calculated results is because my boss wants to eventually see a chart of the total hours of jobs each day, hence why I'm asking if this can be done as a number field.

View 4 Replies View Related

Need Help With A Calculated Expression/data Table.

Mar 30, 2007

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.

Thank you

View 4 Replies View Related

Queries :: Expression Builder Calculated Yes / No On Dates

May 8, 2013

I having a problem with the expression builder in a table.I got a lot of fields with dates and I what a calculated field so I can see if any of the dates are newer than 7 days.I have been trying this formula:

IIf([Field1]or[Field2]or[Field3]<"Date() -7";true;false)

But it don't seen to work.

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

Queries :: Filter Error With Calculated Fields - Missing Operator In Expression

Jan 9, 2014

I tend to work with large amounts of data (extracts from company systems) and I create a lot of Access/VBA based tools to automate processes.

I have an annoying error which has always appeared but I don't understand the root cause of it.

When viewing a query, if I filter, I get an error message pop up (though after clicking through the error I can still use the filter function):

"syntax error missing operator in expression 'name of field'".

This seems to happen when I add several calculated fields. Here are some examples of the conditions and calculated field formulas I'm using in this current one:

Conditions:
<>"CINEMATIC" And <>"SFX"
Not Like "*_ZZ*" And Not Like "*test*" And Not Like "EP_*"
[Forms]![FRMscriptPrintReview]![selectLangCombo]

Calculated Field:
Audio Ref Guide: IIf(Left$(Right$([TBLdata]![Script Resource],2),1)<>"_","?",Right$([TBLdata]![Script Resource],2))

I get the impression that its more of a bug with Access as the formulas aren't complicated really but need confirmation on this and if there is a way I can avoid it.

View 2 Replies View Related

Formatting Date In Multi-Column Combo Box

Apr 14, 2015

I have combo boxes that contain attorney names and phone numbers. The attorney name is what is filled into the field when a selection is made from the combo box into the form. The phone number is just there for the occasions that the person managing the data in the database needs to call that attorney. Just a quicker way instead of having to go to another form to find the phone number. I created a query to combine the first and last name to one field (FullName) and this is where the combo box gets it's info.

When I run the query, the phone number is shown formatted as I want it to ((###)###-####), but in the combo box in the form, the phone number has no format to it. What I need to do to get the phone number to show up formatted in the combo box list?

View 2 Replies View Related

Can't Edit Due To Calculated Column. Workaround?

Mar 30, 2007

Table A has a field for Full Name (Bob Jones)
Table B has two separate fields for first and last (Bob and Jones). Table B's contents are actually based on an XML import so I can not just change the table structure.

I want to join the content from these two tables together based on full name.

So first I created a query on table B with a new field created with the expression =[First] & " " & [Last].

I then created a query with table A and query B where I joined the two full-name columns together.

This works perfectly. However, I can't edit the values in this query and I believe from the help files that it's because there's a calculated column in one of the tables.

Is there a way to get around this?

I thought maybe inside my ImportXML VB function once I imported the XML data I could populate an empty column with First + Last but I've read about 50 articles and they're all over my head and I can't figure out how to just concatenate the two columns together into one using VB. It seems if I did this I would avoid having to use a calculated column in a query and this would fix my problems.

Any help would be greatly appreciated. Thanks so much!

View 8 Replies View Related

Queries :: Conditional Formatting On Column Not Listed In Crosstab Query

Mar 26, 2013

I have a simple crosstab query:

FirstName and Surname as Row Headings.
Date as Column Heading.
OnShift as a Value (Count).

These are from the table DailyActivityLogs. Also in that table is a choice field called Weather that lets you choose the conditions that day (Weather, Work, Part Weather, Subbed).

This is the report the crosstab query generates:

What I would like to do is colour the Count fields depending on what the weather was that day. It seems possible, as Weather sits in the same table, but the field Weather isn't in the crosstab query.

Can I do this?

View 3 Replies View Related

Queries :: Calculated Query Column Cannot Group

Jun 9, 2013

I am trying to group the following Query to find duplicates with no success.

Base Query:

SELECT AccountActivity.AccountID, Mid([details],InStr(1,[details],"-")-1,4) AS FetchedCat, elookup("Neighborhood","Combo","Category='" & [FetchedCat] & "'") AS Neighborhood
FROM AccountActivity
WHERE (((Mid([details],InStr(1,[details],"-")-1,4)) Like "?-??"));

Find Duplicates Query:

SELECT AccountsWithNeighborHood.[AccountID], AccountsWithNeighborHood.[FetchedCat], AccountsWithNeighborHood.[Neighborhood]
FROM AccountsWithNeighborHood
WHERE (((AccountsWithNeighborHood.[AccountID]) In (SELECT [AccountID] FROM [AccountsWithNeighborHood] As Tmp GROUP BY [AccountID] HAVING Count(*)>1 )))
ORDER BY AccountsWithNeighborHood.[AccountID];

View 3 Replies View Related

Using IIF Statement Calculated Column Through Linked Table

Apr 10, 2015

So I have this If statement that goes something like this;

Code:
IIf {ttblEQAnswer_FCLIT.ActionType} = 'Answer Affir Defense' andnot isnull({ttblEQAnswer_FCLIT.METPrep}) and not isnull({ttblEQAnswer_FCLIT.AttyInstr}) and
({ttblEQAnswer_FCLIT.METPrep})> date(1900,1,1) and not isnull({ttblEQAnswer_FCLIT.AttyInstr})
and {ttblEQAnswer_FCLIT.METPrep} < currentdate - 10
then 'Y' as [MET InstructNeed?],

I want to use the above formula to calculate a column. However, do I need to join the tables referenced above in order to get this formula to work? As it stands I'm getting an error that says "Malformed GUID in query expression".

View 3 Replies View Related

General :: Calculate Table Column Expression?

Aug 7, 2012

i am trying to clean up all unneccessary queries. i am now trying to get a calculation to apear in a text box.

what i want to calculate is [tblReceipts].[ReceiptCash] If Date()= [tblReceipts].[DateOfReceipt]

am i going about this the wrong way of is my pricipal correct but my expression wrong?

View 13 Replies View Related

Reports :: Calculate Average For Each Column / Expression?

Jul 4, 2013

I have a report that comes from a query with multiple expressions. The expressions look something like this:

IIF([TimeArrive]>[TimeTriage],DateDiff("n",[TimeArrive],[TimeTriage])+1440,DateDiff([TimeArrive],[TimeTriage]))

This works great and my report gives me my columns for each expression in minutes they way it should. Now here is the question...is there a way to insert a text box for each column/expression so I can calculate the Average for each column/expression?

i.e. I want the report to show the average minutes of Expr1 and Expr2, etc at the bottom of each column.

View 2 Replies View Related

Modules & VBA :: Export To Excel (rich Text) Column Not Formatting Correctly

Feb 6, 2015

I have a process that is getting data from sharepoint page, containing 2 list. Both list contain one field I am having issues in my export, a 'Multiple Lines of Text' type field which is Rich Text in sharepoint. I have an access DB with a linked table to those 2 list. Access show the one field I am having problems with as Memo.

The Linked table pulls/display the rich text data field correctly. My access query pulls/displays the data correctly.

My export procedure does not, the cell it exports to includes the HTML Tags. How can i modify my VBA export procedure to export to excel in plain text, i dont need the same format, just want to drop the html tags.

Code:
Public Sub StatusReportExport()
On Error Resume Next
' Test to see if the file currently exist, if so, delete file, so new file can be written.
Kill ("L:Connect SiteStatus Report " & Year(Date) & "-" & Month(Date) & "-" & Day(Date) & ".xls")
If Dir("L:Connect SiteStatus Report " & Year(Date) & "-" & Month(Date) & "-" & Day(Date) & ".xls") <> "" Then
MsgBox "Status Report.xls File already in use!" & vbNewLine & "Please Close File, then rerun Report."

[code]....

View 6 Replies View Related

Control Can't Be Edited; It's Bound To The Expression '[Form]!EmpList.Column(13)'

Aug 8, 2005

On my form, I currently have a combo box that updates text box data with each new selection. However, after these text boxes for FirstName, LastName, etc. are updated, they cannot be edited. Here is the error message that appears in the status bar:

Control can't be edited; it's bound to the expression '[Form]!EmpList.Column(13)'


I'm thinking it might be a result of the data source for the text boxes being a combo box expression.
Any suggestions on how to fix this?
Thanks. :)

View 2 Replies View Related

General :: Formatting Time Conversion - Calculated Elapsed Time

Dec 3, 2013

Formatting issue regarding elapsed time calculated using DateDiff().

I understand that you can specify the output value for DateDiff(). In my case I have chosen "n" for minutes. Each result in my query shows the correct calculation in terms of minutes.

[PunchIn] = 11/23/2013 8:11:28 AM
[PunchOut] = 11/23/2013 5:43:30 PM

[ShiftLength] =DateDiff("n", [PunchIn],[PunchOut]) = 572 minutes.

Now when I try to format the result in terms of H:MM (be it in a form or a report) I get varied results. I'll illustrate an example below:

=Format(([ShiftLength]/60),"0") & "." & Format(([ShiftLength] Mod 60),"00") Returns 10.32 Not correct

=Format(([ShiftLength]60),"0") & "." & Format(([ShiftLength] Mod 60),"00") Returns 9.32 This is correct but I need my result to be in the form of a decimal such as my next example

=([ShiftLength]/60) Returns 9.53333333. Getting there but how do I have this result only show two decimal points 9.53?

View 6 Replies View Related

Queries :: IIF Statement Expression - Create Alias Column Based On Data From Two Fields

Jun 20, 2013

I am writing a very complex 'if statement' query expression. I need to create an alias column based on data from two data fields.

Here's the logic:

True: If [PP] = "WG" or "WS" or "WL" and If [GR] > 10 Then "Skilled" or
[GR] Between 6-9 Then "Semi - Skilled" or [GR] <6 Then "Unskilled"

False: "GS"

View 2 Replies View Related

Tables :: Calculated Fields As Data Type In Tables - Calculating Total?

Apr 23, 2013

I am using calculated field as a data type in access 2010.

They are working fine.

However, I added a new field and now the final calc won't work.

I have Subtotal adding loads of fields together. Works fine.

Then I have a VATunit field which is a double integer, so enter 20 and my next field is VATTotal calculates the SubTotal + the VATunit by doing (Subtotal/100)*VATunit. This calculation is fine and gives me the correct amount.

The next field is a Total field. Which adds Subtotal and the VATTotal together. Howver, the Total is the same as Subtotal. It is not adding the VATTotal to it?

View 2 Replies View Related

Tables :: Calculated Fields From Two Tables / Based On Relationship In Third Table

May 29, 2014

I have two tables of data, each relating to three business branches (branches A, B and C).

Table 1 shows the expenditure of each branch (by fuel, premises and wages).

Table 2 shows a number of units for each branch (mileage, floorspace and sales).

What I would like to do is calculate unit costs, based on the expenditure in Table 1, divided by a relevant unit in Table 2. The catch is that I want to have a third table which allows the user to specify which expenditure (from Table 1) is combined with which unit (from Table 2) to generate the calculated unit costs. I've been able to do this in Excel, and have attached an example. I've also attached an incomplete Access version with the first two tables. Given the complexity of my actual data, I feel this could be better handled in Access than Excel.

View 6 Replies View Related

Tables :: Sum All Fields In Tables Column And Put Sum Result Into Variable?

May 29, 2015

i would like to sum all the column fields in a tables column where an id is the same as the id in that table.
I know you can use the SUM function in the select statement of your query.

But how can i set this sum result into a variable, so i can use it for calculation further on in the program?

I work with Access 2010

View 4 Replies View Related

New To Access, Auto Formatting Tables

Jun 17, 2005

Hey guys, very new to access.

I've been put in charge of setting up a database for work which stores statement and invoice information.

I'm just wondering how I can auto format a field.
I'm guessing it's in the "Validation" section.

A statement is a collective of invoices for each month.
So each March '05 Statement will contain all March '05 Invoices



Basically, what I would like it to be able to do (:)) is for the date inputed into the database, it creates it's own "statementnumber".

Eg. March '05 will create a number as 305 for example. 3 being the month, 05 being the year.
Because I need a common number to setup a relationship of One-to-Many.
Just wondering if this was possible.

Thanks guys :)
Greg

View 7 Replies View Related







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