Still unable to run successful query

I've used the IFF suggestions given but it returns only Not Active results. 
I'm working on a case problem that reads: 

Create a query to display all matching records from the tblProgram and 
tblMember tables, selecting the ProgramType and MonthlyFee fields from the 
tblProgram table, and the FirstName and LastName fields from the tblMember 
table. 

Add a calculated field named MonthlyFeeStatus as the last column that equals 
Active if the MembershipStatus field is equal to Active and equals Not Active 
otherwise. 


-- 
jj
0
Utf
2/20/2008 6:25:00 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
1763 Views

Similar Articles

[PageSpeed] 28

Assuming MembershipStatus is a string (varchar) data field, not an integer 
automatically formatted through a lookup, the expression could be:


iif( MembershipStatus = "Active", "Active", "Not Active" )




which covers the case where the membershipStatus is NULL.  If you get all 
"Not Active" result, probably your MembershipStatus is an integer, not a 
string, and you see "Active", or something else, under it, BECAUSE of a 
formatted - LOOKUP. A formatted value IS NOT the stored value, necessary; as 
example:  3.141592... formatted to two decimal places, is exactly  3.14, but 
that displayed 3.14 is NOT the stored value 3.141592... Go in table design 
and check what is really your field membershipStatus.




Vanderghast, Access MVP



"JJ" <jj@discussions.microsoft.com> wrote in message 
news:04B7E9AB-63B3-46AC-9FEF-2C2E1409A4D0@microsoft.com...
> I've used the IFF suggestions given but it returns only Not Active 
> results.
> I'm working on a case problem that reads:
>
> Create a query to display all matching records from the tblProgram and
> tblMember tables, selecting the ProgramType and MonthlyFee fields from the
> tblProgram table, and the FirstName and LastName fields from the tblMember
> table.
>
> Add a calculated field named MonthlyFeeStatus as the last column that 
> equals
> Active if the MembershipStatus field is equal to Active and equals Not 
> Active
> otherwise.
>
>
> -- 
> jj 


0
Michel
2/20/2008 6:47:04 PM
JJ,

If doing this in the query design view, enter this....

MonthlyFeeStatus: 
Iif([TableQualifier].[MembershipStatus]="Active","Active","Not Active")

....into a "Field:" cell in the design grid.



If doing this in SQL veiw, then put this...

Iif([TableQualifier].[MembershipStatus]="Active","Active","Not Active") as 
MonthlyFeeStatus

....in the SELECT clause (make sure to separate columns/fields with commas in 
the SELECT clause, but no comma after the last column).




NOTE:  Be sure to change [TableQualifier] to the appropriate table name.  I 
didn't want to assume which table that field was in.

I haven't tested this, but I think it is right.

HTH,

Conan




"JJ" <jj@discussions.microsoft.com> wrote in message 
news:04B7E9AB-63B3-46AC-9FEF-2C2E1409A4D0@microsoft.com...
> I've used the IFF suggestions given but it returns only Not Active 
> results.
> I'm working on a case problem that reads:
>
> Create a query to display all matching records from the tblProgram and
> tblMember tables, selecting the ProgramType and MonthlyFee fields from the
> tblProgram table, and the FirstName and LastName fields from the tblMember
> table.
>
> Add a calculated field named MonthlyFeeStatus as the last column that 
> equals
> Active if the MembershipStatus field is equal to Active and equals Not 
> Active
> otherwise.
>
>
> -- 
> jj 


0
Conan
2/20/2008 6:57:02 PM
Reply:

Similar Artilces:

Unable to launch GP
We have one workstation that cannot launch GP. It had worked in the past but now we cannot launch GP from the icon or from Start > Programs. We've tried dragging and dropping the .set file onto the .exe file. Even double-clicking on the .exe file does nothing. This PC has the same anti-virus software as the other machines - Symantec. We also tried copying the GP folder over from a working workstation. Any suggestions? Exactly what does happen when you try to launch? Does anything show up in the task manager? -- Leslie Vail, CPA, MCT, MVP MCBSP-Application for Microsoft Dy...

Unable to switch to native mode
I am trying to convert from Mixed to Native mode using the steps in Q272314. Our environment is 4 sites that were upgraded from Exchange 5.5 to Exchange 2k3 and we have moved all mailboxes, public folders to the new servers. I have followed all the steps in the Q article above and made sure that all recipient CA's were removed. I've removed all the Directory Replication Connectors (except ADNAutoDRC) When I get to the final Step 11 and 12 I get an error that the "SRS is function9ing as the bridgehead for one or more directory replication connectors and cannot be deleted.&qu...

Build Dynamic Query from Form
I am trying to build a dynamic Query from a Form. I keep getting an error that reads ‘Object qryFilter already exists’ I suspect it has something to do with the string of dates being passed to the Query; strDateCondition = "([Trades].[TDATE] Between [Forms]![SearchForm]![cboFrom] And [Forms]![SearchForm]![cboTo])" I am trying to add a means for a user to Query by Customer and Trader AND all records between two dates. This was working fine for Customer and Trader; when I added in the code to filter by dates I started having problems. I know the SQL will be li...

Unable to map drive on seperate subnet of Windows 2008 Server
On our current Windows 2003 Server I'm able to map a drive to a shared folder from a workstation from a different subnet. Even if this machine is not a member of the Domain, I can add the servers IP address and machine name to the hosts file. If I have a user account setup on the server I can then pass the username and password credentials when mapping the drive and everything is OK. When migrating to the new server (Windows 2008 R2) I try to do the same thing, it gives me the following error: The network path \\servername\share could not be found. Is there something...

unable to access mail after re-enable account
Hi, After enable an disable account and change its password, it can login to its mail account. What's happening. Is it normal? Thanks a lot What is the error message when user tries to access mailbox? Does the user still show in ADUC has having Exchange attributes? Have you looked at the MB using ESM to see if it is disconnected? "Raymond" <anonymous@discussions.microsoft.com> wrote in message news:136c01c46f11$d16f8390$a301280a@phx.gbl... > Hi, > > After enable an disable account and change its password, > it can login to its mail account. What's hap...

Unable to repy using word editor
When repyling to an e-mail I receive the following message, "This form requires Word as your e-mail editor, but Word is either busy, or cannot be found. The form will be opened in the Outlook editor instead". I found article 284900, that states you may get this message when you have office 2000 and office xp installed on the same computer. This is not the case on my computer, Office 2000 does not exist, only office xp. Can anyone help me? There appear to be a whole host of causes for this error message. Most have no solution. Here are a few that do: http://support.microsoft...

please help with this query
Ost Ocity Dstate Dcity Carrier Price Rank Diff A B C D X 1200 1 100 A B C D Y 1300 2 100 A B C D Z 1350 3 100 A B C D W 1789 4 100 A1 B1 C1 D1 X1 785 1 A1 B1 C1 D1 Y1 789 2 The rank for every carrier is based on the price . If rank1 carrier is not a pariticular carrier(say if it is not X1 or Y1 or Z1), then i want to calculate the difference be...

Unable to enter correct product key
I have purchased a Mac G4 PowerBook and installed my Microsoft Office (student and teacher edition). Not understanding the significance of the Product Key, I entered a code that I have already used on another computer. Now I am unable to open Microsoft Word on my hew computer because I also have the same product key used on my old computer. I have three product keys. How can I install the program in my new computer using the correct product key? In article <d10c35c6.0408302116.39c1cc84@posting.google.com>, gstoddard@charter.net (george) wrote: > I have purchased a Mac G4 PowerBook ...

Unable to restore Money file in MM 2006
Greetings once again all... Recently I have had to restore my computer and am now not able to use the money file I previously backed up, no matter what I try. It either asks me for a password or when I do manage to open the money file by clicking File>Open and browsing for the backed up money file, it constantly asks me for my password and when I enter it it tells me that it's wrong even though I am signed in with it in Windows Messenger. And when I do manage to get beyonde the sign in screen it does not display any of my accounts. Could anyone please help? Elaine...

List Running Horizontally-Pivot Table Possible?
I just had a customer ask this one and I'm not so sure about it. 1) The customer has setup a list of information that is running horizontally instead of the typical list running vertically. 2) I just taught him how to do pivot tables and he wants to generate a pivot table with his "horizontal" list. 3) I know he can copy and paste special and use the Transpose feature to change the list from horizontally oriented to vertically oriented. 4) the question is: can he leave the table in its original orientation and still generate Pivot tables? I can't seem to make it work ...

Running out of memory
I have some 55 users on an Exchange Server 2003 Std and I am getting a warning 'Out Of Virtual Memory' on the server console. The server has Exchange Server 2003 SP2 installed with 2 GB RAM. The Virual Memory set up is using 'System Managed Size'. I have been running with this config without problems for a few months now. When the message 'Out Of Virtual Memory' appears, no-one can access their mailbox from the clients although the server continues to run OK. The only way that I have been able to resolve this is by rebooting the Exchange Server, which happens ab...

Running an Excel Macro from an Access Command Button
Hi , Can anyone help me with this, I am trying to run an excel macro from an access command button. I want the macro to Bold the Headings on a excel spreadsheet. The code runs without any errors but the macro doesnt seem to work, I think I have got the line ExcelApp.ExecuteExcel4Macro "RUN(""PERSONAL.XLS!BoldHead"")" wrong The code: Private Sub cmdRunMac_Click() Dim ExcelApp As Excel.Application Dim ExcelBook As Excel.Workbook Set ExcelApp = Excel.Application ExcelApp.Visible = True Set ExcelBook = ExcelApp.Workbooks.Open("C:\Documents and Settings\...

Unable to relay #3
Experts, Running Windows Server 2003 SP1. Exchange SP2 and IIS 6.0. Exchange has the SMTP server enabled. But I cannot send any email out from my server. Can't send to my Hotmail, Gmail, or work email address. All reject me with the same unable to relay error. The message could not be sent because one of the recipients was rejected by the server. The rejected e-mail address was 'mypersonaladdress@gmail.com'. Subject 'Test1, 4:53PM', Account: 'mypersonaladdress@mydomain.com', Server: 'server1', Protocol: SMTP, Server Response: '550 5.7.1 Unabl...

Update Query
Access 2003 XP SP2 I am having a problem with an update query. Table is in a one-to-one relationship, referentail integrity and cascading data are checked. (The fileds I want to update are not in both tables) Table name= payForward Has 15 fields ie: ID, Name, MemID, Oct , Nov, Dec, etc Oct-Sep fields are yes/no type I want to "select" a field (Oct-Sep) via a query parameter and repalce "yes" with "no". Here is my query: UPDATE payForward SET [Enter month]=No The messages I get is 'operation must use an updateable query' Wh...

Crosstab Query 04-06-07
I have a crosstab query that shows the products i sale with the number i have sold for each day. instead of showing the sales for each day i would like to show sales dor each months. How do i show it my month Thanks In query design view, enter this into a fresh column in the Field row: TheYear: Year([SaleDate]) Replace SaleDate with your actual field name. In the next column: TheMonth: Month([SaleDate]) You can now group on these fields instead of on each date. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html ...

Can't get rules to run.
I can't get Outlook 2003 rules to run. I am getting my mail from a pop3 account and would like to have new mail sorted to specific folders when it arrives. I set up the rules as I did in previous versions of outlook, but they do not run automatically. I can run the rules manually and they will move the mail. I have this rule (for example Apply this rule after the message arrive from some@email.address and on this machine only move it to the somefolder folde I have tried deselecting on this machine only Thanks. Do you have an Exchange service in this profile? Are you using Cache...

Unable to add documents to My Recent Documents
Hello, Any suggestions as to how to fix "My Recent Documents"? No new documents add to the folder and it remains persistently empty. I get an "Access denied" message when attempting to go to customizing the Start menu advanced tab. Are you Right-Clicking on the Start Button and then selecting Properties from the list? If you select Properties then You need to select Customize from Start Menu Tab. Then from Advanced Tab you should ensure there is a TICK next to "List my Most Recently Opened Documents". hth Gary V wrote: > > Hello, &g...

unable to change password via OWA
Hi, I am having a problem with users changing passwords via OWA. When they click on the change password button, it comes up with change password screen and asked for domain name, user name , old password ,etc. The problem that I have is that when I submit it, it comes up with message saying "Either the password is too short or password uniqueness restrictions have not been met." But I didn't define any password policy on DC. What can I do ? Kindly please give me some idea. Thanks Losa If you are running windows and exchange 2003 then there are password requirements that...

Unable to Send/Recv When Connected to Pub Wireless or DSL
I am unable to send or receieve emails using Outlook when I connect to the Internet using the public library Wireless network, or connect to the Internet using a friends DSL or High Speed Cable connection. Seems I am only able to send and receive when I connect by modem at home. Is there a configuration issue I am not aware of that I can modify to allow me to send and receive when I am not at home on dial-up? Thanks in Advance. I believe because you have outgoing and incoming settings on your home email account (when you set it up) and also because it looks for the IP address is wh...

Unable to receive email from CRM to Outlook
Hi, I have configured the CRM router and am trying to send mail from CRM. I am able to send the mail without any error in CRM. When I click the send button the email form closes. However, I am not getting the mail in outlook. Can anyone provide any hints on what could be the problem... Thanks and Regards Amit Amit, Is it an internal mail? the CRM Exchange Router only works with external mail. Thanks Simon "AmitK" <amitkarnik78@gmail.com> wrote in message news:1129643395.576146.287270@g14g2000cwa.googlegroups.com... > Hi, > I have configured the CRM r...

Run a macro when opening a spreadsheet
How or where do I invoke a macro when I open a spreadsheet. name it autoexec Hi Duncan, In the workbook's ThisWorkbook Module, insert a Sub workbook_open() 'Your code End Sub macro. To access this module, right-click the Excel icon to the left of "File" on th menu bar, select "View Code". Alternatively, in a general module, insert a Sub Auto_Open 'Your Code End Sub macro. If you are unfamiliar with macros, see David McRitchie's Getting Started with Macros and User Defined Fu...

how to set up a query
I am using sql server 2005 express and have 3 tables Table1 Dept_Id (primary key) Dept_Name Table2 Employee_Id (primary key) Dept_Id (foreign key with table1) Employee_Name Table3 WorkSchedule_Id (primary key) Employee_Id (foreing key with table 2) Date_To_Work (date type) I want to list all the Departments (Dept_Name) that do not have anyone scheduled to work on a particular date (ie '1/20/2010' ) Any help would be appriciated. Thanks in advance, RABMissouri2010 Try this: SELECT dept_name FROM Table1 AS D WHERE NOT EXISTS(SELECT * ...

Excel comparative query
I'm respectfully requesting assistance with the correct formula to use for the following query: I'd searching all of column A on spreadsheet 1 to see if a value in column A of spreadsheet 2 is there, and placing a result X (or another) next to (adjacent cell) the confirmed value on spreadsheet 2. Please help if you can. Chris Hi Chris see your answer in puclic.excel -- Regards Frank Kabel Frankfurt, Germany Chris wrote: > I'm respectfully requesting assistance with the correct formula > to use for the following query: > > I'd searching all of column A on ...

Currency Format Lost in Union Query
Thanks to John Spencer, I resolved one problem in my union query (Thank you, John). But I have another problem. Some of my fields in my union query are currencies and others percents. I see both formats are lost. I am sure it is a union query problem, because when I ran an individual query, none of the formats were lost. Is there any way I can keep currency and percent formats without writing, FORMATCURRENCY, etc for each field? Thank you. What data types are these fields? The field in a UNION query will normally take on the data type of the field in the first SELECT. So, if ...

Unable to Create New Task
I am unable to create new task as the option is no longer available. What should I do? Thanks. What version of Outlook? Can you create other items? Is your product activated? -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Navigation Pane Tips & Tricks -Create an Office 2003 CD slipstreamed with Service Pack 1 ----- "Jeffrey" <anonymous@discussions.microsoft.com> wrote in message news:874201c4784e$322d2c60$a401280a@phx.gbl... >I am unable to create new task as the option is no longer > available. What should I do? Thanks. Th...