Creating a new field based on conditions

I have a database that tracks insurance information for our various vendors.  
Each insurance type has 2 fields - a requirement field (yes/no), and an 
effective field (some show an expiration date, some are yes/no).  I have 
created a query that will return only the records for which insurance is 
required but is expired/missing.  My problem is that I want to create a new 
field that is calculated based on the values in the other two fields in order 
to make the resulting report more user-friendly.

For example, if GLRequired is True and GLExpiration is <Now(), I want the 
new field to say "Expired".  If GLRequired is True and GLExpiration is null, 
I want the new field to say "Missing".  Is this possible?

I'd appreciate any help you can offer!
0
Utf
3/17/2008 6:25:01 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
743 Views

Similar Articles

[PageSpeed] 37

ksto wrote:

>I have a database that tracks insurance information for our various vendors.  
>Each insurance type has 2 fields - a requirement field (yes/no), and an 
>effective field (some show an expiration date, some are yes/no).  I have 
>created a query that will return only the records for which insurance is 
>required but is expired/missing.  My problem is that I want to create a new 
>field that is calculated based on the values in the other two fields in order 
>to make the resulting report more user-friendly.
>
>For example, if GLRequired is True and GLExpiration is <Now(), I want the 
>new field to say "Expired".  If GLRequired is True and GLExpiration is null, 
>I want the new field to say "Missing".  Is this possible?


You should NOT put calculated fields in a table.  Instead,
use a calculated field in a query and use the query as the
report's record source.   OR just out the expression in a
report text box:

Either way the expression would be something like:
	
IIf(GLRequired And GLExpiration<Now(),"Expired",
IIf(GLRequired And GLExpiration Is Null,"Missing",""))

-- 
Marsh
MVP [MS Access]
0
Marshall
3/17/2008 8:00:42 PM
I guess I didn't specify that I am a total beginner : )

The expression definitely makes sense to get back the data I'm looking for, 
so thank you.  I am trying to create the field ("Status") in a query which, 
as you suggest, will be used as the data source for my report.  I just don't 
know where in the query to enter that expression to make it create the new 
field.  I've tried entering it in a blank column in the design view of the 
query but when I try to switch to datasheet view, it asks me to enter the 
parameter value for Status.  I've tried entering "Missing", "Expired" and 
leaving it blank but it never returns any records.

Thanks for helping a new arrival to the world of Access!

"Marshall Barton" wrote:

> ksto wrote:
> 
> >I have a database that tracks insurance information for our various vendors.  
> >Each insurance type has 2 fields - a requirement field (yes/no), and an 
> >effective field (some show an expiration date, some are yes/no).  I have 
> >created a query that will return only the records for which insurance is 
> >required but is expired/missing.  My problem is that I want to create a new 
> >field that is calculated based on the values in the other two fields in order 
> >to make the resulting report more user-friendly.
> >
> >For example, if GLRequired is True and GLExpiration is <Now(), I want the 
> >new field to say "Expired".  If GLRequired is True and GLExpiration is null, 
> >I want the new field to say "Missing".  Is this possible?
> 
> 
> You should NOT put calculated fields in a table.  Instead,
> use a calculated field in a query and use the query as the
> report's record source.   OR just out the expression in a
> report text box:
> 
> Either way the expression would be something like:
> 	
> IIf(GLRequired And GLExpiration<Now(),"Expired",
> IIf(GLRequired And GLExpiration Is Null,"Missing",""))
> 
> -- 
> Marsh
> MVP [MS Access]
> 
0
Utf
3/17/2008 10:16:02 PM
That expression does not include the word Status so I don't
see how you could be prompted for its value.

To create a calulated field in a query, enter this kind of
thing in the Fields row of a blank column in the grid:

Status:  IIf(GLRequired And GLExpiration<Now(),"Expired",
IIf(GLRequired And GLExpiration Is Null,"Missing",""))
-- 
Marsh
MVP [MS Access]


ksto wrote:
>I guess I didn't specify that I am a total beginner : )
>
>The expression definitely makes sense to get back the data I'm looking for, 
>so thank you.  I am trying to create the field ("Status") in a query which, 
>as you suggest, will be used as the data source for my report.  I just don't 
>know where in the query to enter that expression to make it create the new 
>field.  I've tried entering it in a blank column in the design view of the 
>query but when I try to switch to datasheet view, it asks me to enter the 
>parameter value for Status.  I've tried entering "Missing", "Expired" and 
>leaving it blank but it never returns any records.
>
>"Marshall Barton" wrote:
>> You should NOT put calculated fields in a table.  Instead,
>> use a calculated field in a query and use the query as the
>> report's record source.   OR just out the expression in a
>> report text box:
>> 
>> Either way the expression would be something like:
>> 	
>> IIf(GLRequired And GLExpiration<Now(),"Expired",
>> IIf(GLRequired And GLExpiration Is Null,"Missing",""))
>
>> ksto wrote:
>> >I have a database that tracks insurance information for our various vendors.  
>> >Each insurance type has 2 fields - a requirement field (yes/no), and an 
>> >effective field (some show an expiration date, some are yes/no).  I have 
>> >created a query that will return only the records for which insurance is 
>> >required but is expired/missing.  My problem is that I want to create a new 
>> >field that is calculated based on the values in the other two fields in order 
>> >to make the resulting report more user-friendly.
>> >
>> >For example, if GLRequired is True and GLExpiration is <Now(), I want the 
>> >new field to say "Expired".  If GLRequired is True and GLExpiration is null, 
>> >I want the new field to say "Missing".  Is this possible?
0
Marshall
3/18/2008 12:32:25 AM
There's my problem - I was entering the expression in the Criteria row 
instead of the Fields row.  It works perfectly now.  Thank you!!  

"Marshall Barton" wrote:

> That expression does not include the word Status so I don't
> see how you could be prompted for its value.
> 
> To create a calulated field in a query, enter this kind of
> thing in the Fields row of a blank column in the grid:
> 
> Status:  IIf(GLRequired And GLExpiration<Now(),"Expired",
> IIf(GLRequired And GLExpiration Is Null,"Missing",""))
> -- 
> Marsh
> MVP [MS Access]
> 
> 
> ksto wrote:
> >I guess I didn't specify that I am a total beginner : )
> >
> >The expression definitely makes sense to get back the data I'm looking for, 
> >so thank you.  I am trying to create the field ("Status") in a query which, 
> >as you suggest, will be used as the data source for my report.  I just don't 
> >know where in the query to enter that expression to make it create the new 
> >field.  I've tried entering it in a blank column in the design view of the 
> >query but when I try to switch to datasheet view, it asks me to enter the 
> >parameter value for Status.  I've tried entering "Missing", "Expired" and 
> >leaving it blank but it never returns any records.
> >
> >"Marshall Barton" wrote:
> >> You should NOT put calculated fields in a table.  Instead,
> >> use a calculated field in a query and use the query as the
> >> report's record source.   OR just out the expression in a
> >> report text box:
> >> 
> >> Either way the expression would be something like:
> >> 	
> >> IIf(GLRequired And GLExpiration<Now(),"Expired",
> >> IIf(GLRequired And GLExpiration Is Null,"Missing",""))
> >
> >> ksto wrote:
> >> >I have a database that tracks insurance information for our various vendors.  
> >> >Each insurance type has 2 fields - a requirement field (yes/no), and an 
> >> >effective field (some show an expiration date, some are yes/no).  I have 
> >> >created a query that will return only the records for which insurance is 
> >> >required but is expired/missing.  My problem is that I want to create a new 
> >> >field that is calculated based on the values in the other two fields in order 
> >> >to make the resulting report more user-friendly.
> >> >
> >> >For example, if GLRequired is True and GLExpiration is <Now(), I want the 
> >> >new field to say "Expired".  If GLRequired is True and GLExpiration is null, 
> >> >I want the new field to say "Missing".  Is this possible?
> 
0
Utf
3/18/2008 2:25:01 PM
Reply:

Similar Artilces:

First Try at Creating a User Form
I have exhausted my search abilities to find an answer to what should be a simple question. I created a user form that pretty much duplicates the built in Data Form. The major difference is that it does not contain all of the fields in the data base -- just a few of them and it doesn't have all of the controls only a Next and Previous control as well a Close. The form is just what I want but I can't figure out how to get it to display the values of the fields when I open it. That is the first problem.. I want it, like the built in form, to show the values of fields for each recor...

New window behavior?
It seems the Excel folks are always striving to find some way to make Excel behave differently from all other Windows applications in some regard. The most glaring example, of course, is cut, copy, and paste, but I just accidentally discovered another one, and I'm wondering how long (i.e., since what version) Excel has been doing this. I'm using Excel 2003, and when I right-click on a child window's window menu* I get a context menu with things live save, print, new window, etc. I tried this in Access and Word and right-clicking a child window window menu does...absolutely nothing...

Help! How I can create...
I need to create a toolbar (if it is) such as the workspace window in VC++ with a CTreeCtrl within... How I can proceed? Thank you! On Thu, 27 Nov 2003 13:03:19 +0100, Gaetano Sferra <rebusmail@iol.it> wrote: > I need to create a toolbar (if it is) such as the workspace window in > VC++ > with a CTreeCtrl within... > How I can proceed? > > Thank you! > > Hi, Check out www.codeproject.com It has all the answers and more. Paul. > Check out www.codeproject.com It has all the answers and more. Hi Paul, please be more specific... I've already se...

Compare cells, update based on Ifs
I need to compare cells to the cell above them and to the right of them. Based on the comparisons I'll need to update the original cell with one of those adjoining cell values. After I finish with one column then I need to repeat the procedure on the column to the LEFT of the original column. I know IF, THEN and ELSE statesments but I don't know VB for Microsoft Office products. Range could be all 65,000+ rows on a workseet Start on ColumnJ, Row2 If ColumnJ, Row2 is Null _ If ColumnK, Row 2 is Not Null _ If ColumnJ, Row 1 is Not Null _ ColumnJ, Row2 Value is ...

Convert Lead
Hi, I have added extra address fields on the Leads form to capture alternative addresses and when I "Convert Lead" to an account I need these addresses to appear on the "More Addresses Tab. I have mapped them but to no avail. I need to somehow do a workflow on Create Account that first creates a new "More Address" entry and then updates the field. Any ideas, I've been going around in circles. Thks "sherry" <anonymous@discussions.microsoft.com> wrote in message news:<19fe901c44dd5$a8d57230$a401280a@phx.gbl>... > Hi, I have added ex...

MS Query Column Headings and Calculated Fields
I'm using MS Query to pull data from an ODBC data source. Everything works fine and I can extract data just great. I'm wondering, however, calculating some fields with the query. I can't seem to add a column heading without enclosing the heading in quotes. That would normally be fine, but everytime I go to edit the query and extra set of quotes is added to the column heading. Is there any way to get around this? I also would like to know if there is a way to use calculated fields in other calculations. If so, is there any documentation out there on how to do this? Thanks, -...

moving favorites from my old Internet Explorer to a new computers
I am trying to move my favorties (links) from my old computer which was Internet Explorer Windows XP to my new computer which is Internet Explorer Windows 7. What ever I try does not work. Any ideas? Thanks Paul B Is this related to Microsoft Outlook somehow? Using IE, click on File > Export/Import and export your bookmarks to a location like your desktop where you can easily find them. Then copy them onto disk and move them over. Copy them from disk to your new PC, open IE, and import them. "Paul B" <PaulB@discussions.microsoft.com> wrote in me...

Sumif base on 2 criteria
I am having problems coming up with a sumif formula based on two conditions. I have three columns. Column A = client #, Column B = work code, and Column C = hours. I am looking for a sumif formulat that will calculate the total hours worked for employees working on (for example) Client #387 and work code 88. Please help if you know how to do this. thanks. Joe Hi Joe; use sumproduct for this; =SUMPRODUCT((A1:A100=387)*(B1:B100=88),C1:C100) I am assuming 387 and 88 are NUMBERS. If not, enclose them in ""s. "Joe Rotondo" <desjoe@aol.com> wrote in m...

Conditional Formatting Text!!
Hi I want to conditionally format some text in a spreadsheet using formula: I have column A1 with: 4a Be 3a To 4c To 4b Be 4c Be 5c Be 5c To 6a Be etc... What I want to do is for all cells which contain "5a Be" "5b Be" 5c Be" "4a Be" "4b be" and "4c Be" to be highlighted in Red - is there a formula to put all of them into one formula - If("5a Be" or "5b Be) etc.. Thanks Kiran "Kiran" <kiran.vithal@gmail.com> wrote in message news:1143053337.964222.63000@t31g2000cwb.googlegroups.com... > Hi > ...

Conditional formating using VBA
Hello I have to format cells (I4:J37) if cells (I43:J76) are: 100%-91% (green) 90%-76% (blue) 75%-50% (yellow) <50% (red) How do I write that in VBA? (I am new to VBA so any help would be appreciated) Many thanks Tracey Hi Tracey, I asume your users will enter a value in the range("I43:J76"). In that case you case use the Worksheet_change(Byval Target as Range function like so: Private Sub Worksheet_Change(ByVal Target As Range) ' Check if only 1 cells value is changed If Target.Cells.Count > 1 Then Exit Sub ' Check if changed c...

Creating a simple form in Outlook 2003
This is a multi-part message in MIME format. ------=_NextPart_000_000B_01C3FAEF.C4CC0AA0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Can someone walk me through how I would set up a simple form in Outlook, = that when sent, the form would be in active visual mode when received by = a recipient? I don't find the help very helpful. I.e.... how about as an example it would ask the question "What day = would be best for you:". To the right of that I would like a simple drop = down menu that would have the choices Monday to ...

Need to Select Individual Records Based on Birthdays in Current Mo
That sort of says it. I need to create a list, probably on the same sheet, with the names & birthdays of everybody whose birthday, e.g., falls in October. Trying to do it by hand is embarrassingly inaccurate. Any suggestions would certainly be welcome. Don. Assuming birthdates are in column C In a helper column enter =MONTH(C1) Copy down. Autofilter on Month number..........9 is October. F5>Special>Visible Cells>OK Copy and paste the visible cells to somewhere. Gord Dibben MS Excel MVP On Wed, 7 Oct 2009 13:53:02 -0700, PlarfySoober <PlarfySoober@discussions...

Conditional Formating Help
I would love your help with a conditional formatting goal. I would like to color a cell if it is part of a formula in another cell. Below is an example: A B C 1 12 13 =B1/A1 I then want A1 and B1 colored blue 2 12 13 =B1 I then want A1 and B1 colored blue 3 ...

create a month column from date column
I have a column in which the date is entered in mm/dd/yyyy format. what i would like to have is another column that takes that information and gives me the month in word format and the year in number format. so for example: if e2= 05/07/2009 i would like to have f2= May 2009 is that possible? if so, How do I do that? your help will be greatly appreciated -- thanks in advance In F2, enter: =E2 and format F2 as Custom > mmmm yyyy -- Gary''s Student - gsnu200909 "jcontrer" wrote: > I have a column in which the date is entered in mm/dd/yyyy fo...

New Users can't Access Mailbox
An NT4 BDC with Exchange 5.5 running with W2k3 AD (as PDC emulator) in a single domain. The original NT4 PDC was upgraded in place. Note: old users are those created before the in-place (NT4 to W2k3) upgrade and new users are those created after the upgrade. Why can't new users access their Exch5.5 mailbox while all old users have no problem accessing it? New users can only logon to the domain and that's it. What am I missing? I'm completely lost. Have you specified Primary NT account for mailbox correctly? Compare account properties between old and new, there should be some ...

Query-Based Distribution List based on Group Membership
I need to create Query-Based distribution lists based on Group Membership. Is this possible? I can't seem to find a way to do it in AD. If the users are in a group already, can't you just mail enable it? "Leni1" <Leni1@discussions.microsoft.com> wrote in message news:18FEF406-AC1F-45E0-A36D-67692D74E924@microsoft.com... >I need to create Query-Based distribution lists based on Group Membership. >Is > this possible? I can't seem to find a way to do it in AD. Yes I could. But what if I want a list based on a security group? I need to create distribu...

Return highest count of one field when duplicate found on another field
I have a table with department IDs, department heads and headcount. Sometimes there are two department heads per department ID. I want to show the head with the largest staff as the leader. I've created a total query to show the count of the staff. Where can I go from there? SELECT [DeptHeadcountTable].DeptID, [DeptHeadcountTable].[Director], Count([DeptHeadcountTable].[EmployeeID]) AS [CountOfEmployeeID] FROM [DeptHeadcountTable] GROUP BY [DeptHeadcountTable].DeptID, [DeptHeadcountTable].[Director]; Try this -- SELECT [DeptHeadcountTable].DeptID, (SELECT TOP 1 [XX].[Department...

Conditional Formatting
Apologies in advice for this being an easy one, but I seem to be having a dumb day! I have a conditional format in cell M17 which is a 'Use a formula to determine which cells to format' =$M17<$K17 full red This works fine. I am then trying to copy this condition down several hundred lines, but it copies it exactly as =$M17<$K17 rather than changing the row number each time it is copied. How do I copy it so that it changes the line number every time? Take out the $ signs. If my comments have helped please hit Yes. Thanks. "The Rook" wr...

Create A Batch Recovery Routine For Printing Payroll Checks
There is no easy recovery if the system crashes after you have printed payroll checks but before you have run the Post Checks Routine. A recovery should be created like other posting routines in GP do. See Case ID 8916432 for description of what happened to us. Support was able to duplicate it by simulating a crash after checks printed, but before they were posted. Payroll is too important to not have a gracefull way of recovering when the system crashes. Luckily my problem only involved a run for a single check. It would be a nightmare to fix if this had been on our regular payr...

Subtotal a calculated field in a pivot table
I'm creating a pivot table listing Accounts Receivable transactions by customer. It includes both payments and invoices in the results. Unfortunately both payments and invoices are listed as positive numbers, so I can't just subtotal the "amount" column. I've created a formula field in the pivot called "calcamt" that contains an if statement. The if statement examines the transaction type code and if it indicates a payment, multiplies the amount column by -1. Otherwise it just returns the amount column as a positive number. When I drag the calcamt field int...

MFC dialog based cout and << operator
I would like to debug a class by using the ostream operator. Let(s imagine I wrote a very simple class like the complex class, I would like to overload the << operator to simplify data printing. complex a(10.0f,-2.f); // Calls Constructor cout<<a<<endl; // Calls the overloaded << operator This example should work with a console application but it doesn't in a dialog based. How can I do ? Here is a work around. You can create a console window from your MFC based application using AllocConsole and then you can redirect all cout calls to that window...

Copy above row data into blank fields below
Hi. Certain fields within my data do not repeat, but I'm trying to find a way to populate the blank cells from the preceding row. Here's an example: Employee_Number Last_Name First_Name 123456 Smith John Judy Ben 123457 Johnson Bill Ruth Eddy Sally As you can see, it didn't repeat the Employee Number ...

Show fields on one record not on others?
Hi, I'm trying to keep my form uncluttered by only showing appropriate fields when needed. Some "sales" are in California and then I want to display a field "CA tax". I can make that field visible or not by using a checkbox.... But when I go to the next record, the CA_tax field (and label) are visible even though the checkbox (of that record) is not checked. Any help with this concept would be very appreciated. Richard Stricker 'the checkbox If Me!Calif_tax.Value = True Then 'show CA tax only when calif tax is true.. Me!Label24.Visible = True ...

Create a single worksheet from a template
Hello The macro below (pinched from one of the threads) creates dulplicate worksheets from a list and has served me well over a year. However I now want to export it to another workbook starting at cell N4. I have tried adjusting it accordingly but am failing miserably not least because it doesn't work with only one name or has any error trapping when there isn't any names. Can you help. TIA Shaz Sub CreateSheets() Application.ScreenUpdating = False For Each cell In Range("a1").CurrentRegion.SpecialCells(xlCellTypeConstants) Dim SName As String SName = cell.Value If SheetExi...

Text fields displayed as scientific Notation
I am current working with Access 2002 and have several reports created... On both reports I have the same data to display... A number that is stored as text and can be up to 17 characters long. On one report it displays correctly, the other it displays as scientific Notation... I've checked all the fields, and the are define exactly the same... They are in different positions on the detail line, but have the same input fileds (Text 17 characters)... If the number is less than 17, it displays correctly.. I've doubled the size of the report field, but doesn't do a...