IIF Query for Numeric Values in Text Field - #ERROR

I’m attempting to flag interest rate spread errors and omissions in a file 
from a sales database using IIF statements I adapted from another database 
(I’m a novice at this).  The field I’m querying is text format and contains 
both alpha and numeric values.  When I test for a null value the IIF works 
fine, but when I test for a numeric value I get an #ERROR.

Spread Error1: IIf(([Tbl 1 Eligible Closed Deposit Opps Appended]!Spread Is 
Null),1,0)

Spread Error2: IIf(([CCC Fall 2007 Eligible Products]![IB/NIB]="NIB" And 
[Tbl 1 Eligible Closed Deposit Opps Appended]!Spread<4),1,0)

Spread Error3: IIf(([CCC Fall 2007 Eligible Products]![IB/NIB]="NIB" And 
[Tbl 1 Eligible Closed Deposit Opps Appended]!Spread>6),1,0)

Spread Error4: IIf(([CCC Fall 2007 Eligible Products]![IB/NIB]<>"NIB" And 
[Tbl 1 Eligible Closed Deposit Opps Appended]!Spread<0.1) Or ([CCC Fall 2007 
Eligible Products]![IB/NIB]<>"NIB" And [Tbl 1 Eligible Closed Deposit Opps 
Appended]!Spread>6),1,0)

I originally had Spread Error 2 & 3 in a single “and” IIF and split them to 
see if I could isolate the problem, but no luck.

Any ideas?  I can't control the formatting in the source application, can 
only work with a file extract.  I'm working with Access 2003.


0
Utf
12/5/2007 2:42:01 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
1037 Views

Similar Articles

[PageSpeed] 37

There are 2 fields froom table [CCC Fall 2007 Eligible Products] in your 
expression:
    [IB/NIB]
    [Spread]
If you open the table in design view, are *both* these fields Text type?

If so, the quotes around the NIB value correct, but you will also need 
quotes around the "4" on the Spread field. This probably won't work as you 
expect: when you use text comparisons, 10 is less than 2 (because the first 
character - the one - is less than the first character of the second value - 
the two.) To correct this you would need to use Val() to get the value. But 
Val() can't handle nulls, so you need to use Nz() inside the Val().

Of course, the simpler alternate would be to change Spread in the table so 
it is a Number type field.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"CFJOHN1" <CFJOHN1@discussions.microsoft.com> wrote in message
news:1C0ED5B9-4875-46F6-92E7-EA9E65AC2229@microsoft.com...
> I’m attempting to flag interest rate spread errors and omissions in a file
> from a sales database using IIF statements I adapted from another database
> (I’m a novice at this).  The field I’m querying is text format and 
> contains
> both alpha and numeric values.  When I test for a null value the IIF works
> fine, but when I test for a numeric value I get an #ERROR.
>
> Spread Error1: IIf(([Tbl 1 Eligible Closed Deposit Opps Appended]!Spread 
> Is
> Null),1,0)
>
> Spread Error2: IIf(([CCC Fall 2007 Eligible Products]![IB/NIB]="NIB" And
> [Tbl 1 Eligible Closed Deposit Opps Appended]!Spread<4),1,0)
>
> Spread Error3: IIf(([CCC Fall 2007 Eligible Products]![IB/NIB]="NIB" And
> [Tbl 1 Eligible Closed Deposit Opps Appended]!Spread>6),1,0)
>
> Spread Error4: IIf(([CCC Fall 2007 Eligible Products]![IB/NIB]<>"NIB" And
> [Tbl 1 Eligible Closed Deposit Opps Appended]!Spread<0.1) Or ([CCC Fall 
> 2007
> Eligible Products]![IB/NIB]<>"NIB" And [Tbl 1 Eligible Closed Deposit Opps
> Appended]!Spread>6),1,0)
>
> I originally had Spread Error 2 & 3 in a single “and” IIF and split them 
> to
> see if I could isolate the problem, but no luck.
>
> Any ideas?  I can't control the formatting in the source application, can
> only work with a file extract.  I'm working with Access 2003. 

0
Allen
12/5/2007 3:01:15 PM
Thanks Allen.   I'll work with this and see what I can accomplish.   Curt 
Johnson

"Allen Browne" wrote:

> There are 2 fields froom table [CCC Fall 2007 Eligible Products] in your 
> expression:
>     [IB/NIB]
>     [Spread]
> If you open the table in design view, are *both* these fields Text type?
> 
> If so, the quotes around the NIB value correct, but you will also need 
> quotes around the "4" on the Spread field. This probably won't work as you 
> expect: when you use text comparisons, 10 is less than 2 (because the first 
> character - the one - is less than the first character of the second value - 
> the two.) To correct this you would need to use Val() to get the value. But 
> Val() can't handle nulls, so you need to use Nz() inside the Val().
> 
> Of course, the simpler alternate would be to change Spread in the table so 
> it is a Number type field.
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "CFJOHN1" <CFJOHN1@discussions.microsoft.com> wrote in message
> news:1C0ED5B9-4875-46F6-92E7-EA9E65AC2229@microsoft.com...
> > I’m attempting to flag interest rate spread errors and omissions in a file
> > from a sales database using IIF statements I adapted from another database
> > (I’m a novice at this).  The field I’m querying is text format and 
> > contains
> > both alpha and numeric values.  When I test for a null value the IIF works
> > fine, but when I test for a numeric value I get an #ERROR.
> >
> > Spread Error1: IIf(([Tbl 1 Eligible Closed Deposit Opps Appended]!Spread 
> > Is
> > Null),1,0)
> >
> > Spread Error2: IIf(([CCC Fall 2007 Eligible Products]![IB/NIB]="NIB" And
> > [Tbl 1 Eligible Closed Deposit Opps Appended]!Spread<4),1,0)
> >
> > Spread Error3: IIf(([CCC Fall 2007 Eligible Products]![IB/NIB]="NIB" And
> > [Tbl 1 Eligible Closed Deposit Opps Appended]!Spread>6),1,0)
> >
> > Spread Error4: IIf(([CCC Fall 2007 Eligible Products]![IB/NIB]<>"NIB" And
> > [Tbl 1 Eligible Closed Deposit Opps Appended]!Spread<0.1) Or ([CCC Fall 
> > 2007
> > Eligible Products]![IB/NIB]<>"NIB" And [Tbl 1 Eligible Closed Deposit Opps
> > Appended]!Spread>6),1,0)
> >
> > I originally had Spread Error 2 & 3 in a single “and” IIF and split them 
> > to
> > see if I could isolate the problem, but no luck.
> >
> > Any ideas?  I can't control the formatting in the source application, can
> > only work with a file extract.  I'm working with Access 2003. 
> 
> 
0
Utf
12/6/2007 12:58:01 PM
Reply:

Similar Artilces:

Web Query question
I am trying to download stock options through the following vba code, however i am having problem with separator between "m" field which is month and "s" field which is stock symbol, I appreciate any help i can get, With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://finance.yahoo.com/q/op?s=" & Range("b1").Value;&m=2010-05", Destination:=Range("$A$11")) thanks david I think you are mising (at least) one quote? Reg Migrant "David" wrote: > I am trying to download stock optio...

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

Change <value> in outlook
We Have Exchange 2003 sp2 and outlook 2003 sp2. I have renamed 2 AD accounts. Changed all display names, accounts name etc with the 2 accounts. When users open outlook and send to renamed account they see account name <oldaccountname> in the autocomplete list. I have cleared nk2 files in profile , but it still shows newaccount <oldaccountname> in drop down box. Is there a way to edit the <oldaccountnam> value of the renamed account? I tried the same thing with a freshly loaded PC. with the same result. Thanks You can delete the nickname files. -- Ed Crowley MVP - E...

error sending from 5.5 to 2000
When a user sends from an exchange 5.5 mailbox to a user in exchange 2000, they receive the following error: The message was undeliverable because the recipient specified in the recipient postal address was not known at this address The MTS-ID of the original message is: c=US;a= ;p=Express-Scripts;l=TEMMS014-041001192533Z-210060 <mail5.express-scripts.com #5.1.1> The same user on the 5.5 server can send to other users on the 2000 server. I looked the error up in technet and got 3 kb articles with only 1, kb295377 being anything close to my problem. I checked the entr...

When launching Outlook I recieve the following error... please he
When launching Outlook 2003 I recieve the following error: The add-in "C:\PROGRAM~1\SPAMBL~1\Bin\461~1.0\SBOLExt.dll" could not be installed or loaded. This problem may be resolved by using Detect and Repair on the Help menu. I ran the Detect and Repair and that didn't work. I ran the repair option from Add and Remove Program in Control Panel and that didn't work. I also un-installed and then re-installed Outlook 2003 and that didn't work. I'm running Windows 2000. Any ideals...? Did you have something possibly called SpamBlocker installed at some point?...

Money 2006 file repair application error
I cannot delete an old account because I get a "file may be courrupt" error so I am trying to repair my 2006 file. Following the deirections, when the progress bar almost completes, I get an application exception and send an error report to MS. My event log has 2 app errors 1) Event Type: Error Event Source: Application Error Event Category: None Event ID: 1001 Date: 4/12/2006 Time: 10:47:26 PM User: N/A Computer: xxx Description: Fault bucket 02266399. For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. Data: 0000: 42 75 63 6b 65 ...

preview text disappears when email is opened
When someone responds to an my email, I can see what they have typed - until I open the message - then only my original message is viewable. The preview pane shows new message text when email arrives, hoever when the email is opened the preview text disappears - and I just see the earlier part of the message which I had sent. If I mark it as unread it does not return to the previous state. This seems to happen on email responses that people have received and answered from a webmail service not that they downloaded and opened in outlook on their PC OL'03 XP pro sp2 -- Respectfully,...

How can I wrap text across merged cells?
I'm using Excel 2000. I have a set of merged cells A5- E5. I have several lines of text in the cells and I want them to wrap across the merged cells and it is not working. All I get is the first line of text showing and the rest is cut off. It works if the cells are not merged, but I really need to do it in my merged cells. Is there a way to this other than manually resizing the height of the row? Instead of merging cells, have you tried the "Center Across Selection" option ? The appearence is just about identical to what you would get using merged cells, although I d...

Count unique values
Hi, I've some problems to count unique items (Invoice #) in a pivot table. There is the default solution "Add-a-new-calculated-column" as mentioned on http://contextures.com/xlPivot07.html#Unique, but in my case, it doesn't works. My problem is, that these values aren't in a Excel worksheet; it's a external data source - a SQL-Select via DAO/ADO. Because of that, I don't have the possibility to add a new column. Another reason is, that the pivot table should always be dynamic: Group over this field, group over another field - and always show the number of unique...

Error #2950
Hi, I have three tables from which data I use to create a single table. The data is entered into the three tables via a form. I have a macro set up to create/update the single table through some make table queries and update queries of data within the first three tables. I have this macro tied a close form event and I keep getting a 2950 error when in a multiuser environment however the macro works in a single user envioronment. I only really need to have the single table updated daily. Do you have any good tips on error handling procedures so I can skip running of the qu...

Using Sumproduct when some of the values are null
I am having a problem using Sumproduct when some of the cells have null values. I will try to describe the problem. Please let me know if you need additional information. Do any of you have any ideas of how I can fix that sumproduct to get it to work? A B C Row Trend Claims 5 Emergency $0 6 Emergency $0 7 Emergency $0 8 Emergency 81.68% $24,444 9 Emergency 35.00% $164,758 10 Emergency 35.00% ...

is DCOM error 0x00080012 (Not all the requested interfaces were available) not an error ?
Im checking the return value of a DCOM call by using the macro: FAILED(HRESULT). One particular function call returns the error 0x00080012 (Not all the requested interfaces were available) if the DCOM service is down. Calling FAILED with this HRESULT returns false, meaning that it isnt an error! I think it should be reported as an error as my program cannot call ANY interfaaces beyond this point and crashes. The FAILED macro checks the HR to see if it is negative and 0x00080012 is positive!? Any suggestions? Thanks Andy ...

Exchg2003 First Storage Error
Exchange Enterprise 2003, SP1 on Win2003 - When I attempt to view the mailbox information store within First Storage Group I get an error (Id no. 80040d1b). The only ref. to this error I have found is: http://support.microsoft.com/default.aspx?scid=kb;en-us;817309 - checked my registry & server - they match; registry looks fine. I also am getting Veritas Exchg Info Store errors - "database corrupted". I ran the Eseutil defrag - ran successful, but still can't see mailboxes. I found MS tech net info that suggests re-installing Exchange (to repair): http://www.micr...

Error saving a Shared Workbook
I have a user using a shared workbook and gets an error everytime she does this ONE specific change. She deletes a line and then saves and gets a message that says, "EXCEL.exe has generated errors and will be shutdown by Windows... blah blah blah". If she makes anyother type of changes it saves just fine. I also had another user do the SAME change and that person also got that error message. When the file is changed to "Not Shared" then the line is deleted it saves fine also. The problem is obviously with the file itself and that is shared out. can anybody ...

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

Contact looses Post Office Box field when synchronized to Outlook
When I sync contacts from CRM 4.0 to Outlook, the Post Office Box field from the contact gets empty. Anyone has the same issue? ...

Outlook error message #4
Microsoft Visual C++ Runtime Error. What's the problem? ...

Error Code 80070424 12-21-09
I keep getting this Windows update error every time I boot my PC. In the window, it says Windows can't check for updates. Does anyone have any ideas? Recently I installed BitTorrent so could that be my problem? "Norm" <Norm@discussions.microsoft.com> wrote in message news:D2E5CF0C-D31B-4034-B4A8-A8204763A623@microsoft.com... > I keep getting this Windows update error every time I boot my PC. In the > window, it says Windows can't check for updates. Does anyone have any > ideas? > > Recently I installed BitTorrent so could that be my proble...

Getting Cell Value from the Concatenate formula
I built a concatenate formula that returns the following result: =Jul! $D27 I am looking for the cell contents of Sheet: July Column D Row 27. I tried to use offset, but I am stumped. Can I add something to the front of the concatenate to not only build the reference to the cell, but also return the value instead of the =Jul!$D27 ? Thanks John =indirect(yourformulahere) Don't include the equal sign in your formula. And match the name correctly (Jul or July???). Depending on the name of the worksheet, you may need to have a string that looks like: 'Sheet 99'!d27 =indirec...

Installation errors. 01-09-04
I'm setting up CRM 1.2 on a completely clean test environment of windows 2000 servers. Near the end of the installation a message pops up "Setup was unable to register the security service." What could be causing this The usual story: To get the MCCRMSecurityService starting, have you tried re-registering it as follows: On your crm server, at command prompt, go to program files - microsoft crm - bin, then run these commands crmsecurityservice -u crmsecurityservice -r -s Peter Lynch "J. Bridger" <Bridger@nospam.ofllc.com> wrote in message news:0aa001c3d6...

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

find match then change cell value
In column A I have cells filled with text and in column B I have cells filled with numbers. I need to check if the number in cell C1 equals any of the numbers in column B. If a match is found then I need to change the text in column A to CBO. e.g. Column A Column B Column C aep 5 7 apa 0 gci 59 xto 5000 xle 7 oih 253 ed 8 Since the cell C1 = 7 equals the 7 from column B, I need to change the data in column A from xle to cbo. Is this possible...

Error in Code
Following is some VBS code I have in my Custom Form developed in Outlook 2003. Line 61 is giving me an error stating: Object doesn't support this property or method: 'Item.GetInspector.ModifiedFormPages' Line No: 61 If you look Line 51 is the same method and it works fine. The funny thing is when I run the code, I get the error, I continue on to the Form, and the code after it works. The code that follows line 61 is dependent on line 61 as evident by if I ' comment it out, I get a separate error asking for objPage2. The code also works if I change the value of one of...

Active X error
I have tried removing the signature, unchecking the Smart Tags, changing the editor to Plain Text as well as to Rich text all to no avail....here is the error one of my users gets when she tries to send and email from Outlook - she can receive just fine: "One or more ActiveX controls could not be displayed because either: "1) Your current security settings prohibit running ActiveX controls on this page, or "2) You have blocked a publisher of one of the controls. As a result, the page may not display correctly." ...

Errors found Code 80070490
This appears to be one of those problems that no one seems to have a working solution. Reinstalling an operating system is not a solution it’s a reason to change operating systems. This type of problem was not supposed to happen with Vista. Let’s see some big guns on this one! Update Error: Some Updates not installed: 2 Failed updates (KB967723, KB972145) Errors found Code 80070490 (Unknown Error) Problem signature: Problem Event Name: WindowsUpdateFailure ClientVersion: 7.4.7600.226 Win32HResult: 80070490 UpdateId: AE2C61ED-AC12-4369-BED6-4375CD79CA...