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
1011 Views

Similar Articles

[PageSpeed] 22

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:

How do I fix this so I don't get an "Error 2015"
Dim x x = Application.CountIf("E5,E6000", "T13065") How do I fix this so I don't get an "Error 2015" Thanks, Rick Not tried it but I would think x=Application.Countif(Range("E5:E6000"),"T13065") Providing T13065 is a string you are counting. If it's a range reference x=Application.Countif(Range("E5:E6000"),Range("T13065")) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS www.nickhodge.co.uk "2D Rick" <rbrowniii@compuserve.com> wrote in m...

Excel Text Function
Hi anyone who can help me... I have some info in a spreadsheet as follows: A1 B1 C1 Centra Dublin Centra Belfast Centra London If I want to get just Centra out into another cell I would use =LEFT(A1:C1,6) and this works fine. But I want to actually get out the area - Dublin, Belfast or London and some other areas that might have more or less than 7 letters. Any ideas??? Thanks in advance Ann (Dublin, Ireland) =TRIM(SUBSTITUTE(A1,"Centra","")) will work if you have city names and centra.. -- Regards, Peo Sjoblom "Ann&q...

Stagger X-axis text
In 1-2-3 I could stagger the text in the X-axis. In Excel it seems that I can only rotate the text to 90 degrees. Is there a way to stagger and leave horizontal? Specifically, I have all the provinces (or 10 of them) across the axis and they take up room when spelt out (no abbreviations allowed). I would rather the first, third, fifth ... etc. be higher and the second, fourth etc. be lower to allow the chart to be narrower and still read the text clearly. Cheers, Deborah >-----Original Message----- >In 1-2-3 I could stagger the text in the X-axis. In Excel it seems Deborah I would...

How do I get excel to accept (c) as text and not change to copyri.
How do I enter the text (c) in Excel without having it changed into the copyright symbol? Hi Daffyd, Try: Tools | Autocorrect | Select (c) | Delete | OK --- Regards, Norman "daffyd" <daffyd@discussions.microsoft.com> wrote in message news:8CCC3C1A-6F19-4F62-B934-8A71F236A4FD@microsoft.com... > How do I enter the text (c) in Excel without having it changed into the > copyright symbol? Go into the Tools Menu. Look for AutoCorrect. In the bottom half of the AutoCorrect Tab, look at the list for Replace text as you type. Delete the entry for (c). tj "da...

Setting series values on Excel Chart
Hi I'm struggling very hard in getting beyond this error when trying to use VB to generate a chart from data previously inserted into the sheet. Unable to set the Values property of the Series class. The code works absolutely fine in XL2007 but I am having to port it back to 2003 and the inference is that its empty values that cause it to hiccup - something I cannot avoid. My code is: 1. Retrieve selected data from database and place it at the top of the sheet 2. Add The Chart 3. Iterate through the data (amount can be variable depending upon user inputs) For j as i...

Getting an Error when there is nothing to show
-- The bottom of my report which is a statement shows #Error when there is no Invoices to add up. Can I have it blank instead. this my text Box Control Source thanks for any Help....Bob =Reports!rptOwnerPaymentMethod!subChildOwnerInvoiceAmount!tbAmount.Value Try: =IIf([subChildOwnerInvoiceAmount].[HasData], [subChildOwnerInvoiceAmount].[tbAmount]) Explanation in: Bring the total from a subreport onto a main report at: http://allenbrowne.com/casu-18.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.h...

Cannot send mail from outlook, error 0x800CCC78
This message started to appear back in Sept and I haven't been able to send e-mail from Outlook ever since (apart from a 2 day period in Novemeber when the issue disappeared just as quickly as it appeared). I now have to cut and paste e-mail i recieve into the e-mail interface of my provider in order to get e-mail sent. I have posted this error 3 times now and haven't got a solution, plenty of questions about versions, platforms, port numbers etc etc, but no fix. Judging by the hits on google to this, it is and established problem. Come on Microsoft, do your stuff.....

Ho to make one field required based on critera of another field?
I'm creating a form and need to make the "comments" field required if the "code" field is =>20. I appreciate suggestions! Deadline Monster is lurking! User enters the job processing endcode value (numeric) into the "code" field. If the endcode is =>20, comments are required. (P.S. I don't know VB) Thanks! Star You would put your validation code in the Form's BeforeUpdate event. If Me.EndCode >19 Then If Len(Me.Comments & "") = 0 Then MsgBox "Comments are required" Cancel = True End If End If ...

Numbers in a text field-can I add them up?
Hi everyone! Using A02 on XP. I have a table of data with survey response fields that contain a 0,1,2,3,4 or 5. However, the fields are formatted as text, not numbers. I need to add up certain blocks (Items 1-6, Items 7-23, etc.) and then do some averaging. I cannot change the field types from text. Must I append to a new table or can I do something right in my query? I've got one field in my query like this: ES: [Item1]+[Item2]+[Item3]+[Item4]+[Item5]+[Item6] My result is: 553453 or 554444, etc. I want: 25 or 22, etc. I would really appreciate any help or advice. Thanks...

data value in Form field if no table entry
I have a form with a field which pulls through and concentenates 2 fields called [ContactFirstname] and [ContactLastName]from my table There are however some customers for whom I do not have names and therefore instead I would like Sir/Madam to appear in the field in the form I think I have seen this done somewhere using ELSE? but can't find it Any help/ideas gratefully received Perhaps something like this: Nz(Trim([ContactFirstname] & " " + [ContactLastName]), "Sir/Madam") -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access use...

Change outbound server in header to fix 550 Can't verify your host name error
The headers on the outbound emails show the internal DNS name of our exchange server; obviously this won't resolve properly at the destination. How/where in Exchange 5.5 can I force the IMC to use a real fqdn on outbound mail? Thanks! Frinky You can do this in TCP/IP properties\Advanced\DNS tab of machine. And yes, this is not just for Exchange, so you may consider forwarding all outgoing mail to some relay server (your firewall or ISP's server). Professor Frink wrote: > The headers on the outbound emails show the internal DNS name of our > exchange server; obviously this...

help!! smtp protocol error occurred
exchange 2000 srv/windows 2k srv smtp protocol error occurred when trying to send mails. receives fine. Still waiting for some relavent information so we can help you solve your problem. >-----Original Message----- >exchange 2000 srv/windows 2k srv >smtp protocol error occurred when trying to send mails. >receives fine. > > >. > I had that problem, I telneted into the servers that the error occurred and found I was on a blacklist. Had to sumit to an open relay test, and bam, no more problems. ...

Error- Project is used by another user -Please select another proj
Please see the subject line, when im trying to open a particular project. it gives me following error. I checked the current users, there are no users. Apart from me. There are some non PA users,i tried to remove them from activity. inspite of me removing them from activity, im geting the same error. Help !!! Ramakrishnan Hello Ram, There is a stranded user in PA000001 table. You need to clear this User/Project combination. Ajay "Ram" wrote: > Please see the subject line, when im trying to open a particular project. > it gives me following error. > > I che...

Constructing Hyperlink from the Database Record fields
I am working on a Windows XP environment using MS Office 2007 including Access 2007. I want to open a document from Access 2007 which I can easily do with Hyperlink type field. However since all the necessary information is already in the Database Record I try to avoid creating additional field which would be a Hyperlink type on the Form unless it is absolutely necessary. Below is the code that I have to construct the FullFileName which consisted of ServerName, Division, Unit, RequirementDirectory, FolderName and the FileName itself. As you can see the Database records has al...

User Defined Required Fields
I have set several field on sub window Sales User-Defined Fields Entry of Sales Transaction Entry as "Required". If the user remembers to click User-Defined, then they become required. But if the user never clicks on User-Defined from Sales Transaction Entry, then they can still save the new document without the required fields entered. Does anyone know what I can do to fix this asside from continual user training? Your answer is VBA. You own Modifer, so you also have VBA enabled. You'll need to write VBA code to open the window (literally, push the button) then set th...

Visual Basic Error
Every time I shut down Outlook, I now get the following Microsoft Visual Basic error: "Compile error in hidden module: Autoexec" This started after I installed the software for People PC Dial -up Service. They haven't responded to my question and Tech Net/Knowledge base come up empty. Help!!! Thanks So, uninstall that software. It's obviously the problem and if they won't support it then it's useless to you. -- Ken Slovak [MVP - Outlook] http://www.slovaktech.com Author: Absolute Beginner's Guide to Microsoft Office Outlook 2003 Reminder Manager, Exten...

User Authentication Error
Hello All, I am running CRM 3.0. I have the system administrator role. When attempting to merge 2 account records I receive an error that states, "The user authentication passed to the platform is not valid." I cannot merge the records. If anyone can, please advise. thanks. Keith ...

Report Error 11-24-04
When attempting to run CRM Reports for the first time we are receiving the error "Information is needed before this report can be processed" I have found documentation that states that this can happen if your server has an underscore. Our server does not it is named ourcompany.server.eeecrm Can the periods have the same effect as the underscore. Or does anyone have another cause? Thank you Hi There - Hope you don't mind but I wish to piggy-back on your post. I get the same error on our custom reports that use OLE DB to connect to another SQL server other than the MS CRM ...

Userform combobox matchrequired = True; error with no selection
I have a userform that includes a combobox which is optional for the user, but if they do use it, they have to select one of the three values provided (no creativity allowed). This works fine if the user ignores the field, or if they make a selection. They can even click the combobox 'arrow' to see the values and then click to another part of the form without making a selection. They can tab through controls including this one without any problem. All good. However, a user may enter the field (mouseclick) thinking they might want to make a selection, then decide again...

Script Errors 08-12-04
When launching CRM 1.2 through the web interface or through Outlook I am experiencing errors. This occurs on all the PCs on our network. CRM installed on Windows 2000 Advanced Server Client PCs running 2000 or XP Pro Browser IE6 with all updates. The main 3 I see are: Error #1 Line: 3 Char: 1 Error: Syntax Error Code: 0 URL: https://crm/home/home_home.aspx Error #2 Line: 104 Char: 1 Error: 'ORG_DATE_START_DAY' is undefined Code: 0 URL: https://crm/home/home_home.aspx Error #3 Line: 15 Char: 3 Error: 'ORG_DATE_FORMAT' is undefined Code: 0 URL: https://crm/home/home_home...

Outlook 2002 SP3- Runtime Error when opening Outlook
I'm getting a runtime error- Microsoft Visual C++ Runtime Library / Program C:/Program Files/ microsoftoffice/ office10 /outlook.exe. This App has requested the runtime to terminate it in an unusal way. This only happen the first time I open Outlook for the day. It shuts down, then I can reopen it and all is fine. I have try to debug it and the detect & repair option in the help tab. Any help is Appreciated! ...

Failed to open mmf error appears
A pop up window appears with the message 'failed to oepn mmf'. I assume it is referring to Microsoft mail format. What could be triggering this error. What version of Outlook, and when do you see this error? EllenB wrote: > A pop up window appears with the message 'failed to oepn > mmf'. I assume it is referring to Microsoft mail format. > > > What could be triggering this error. >-----Original Message----- >What version of Outlook, and when do you see this error? > >EllenB wrote: >> A pop up window appears with the message 'fail...

Autoenrollment errors after adding SSL certificate
Hello, I'm running an all Windows 2003 domain with an exchange server which is setup so that Outlook web access is using SSL which required that I installed a certificate. Ever since I've set up exchange to use this certificate I receive the following errors in the Application log for both my Domain Controllers (both also Windows Server 2003) which occurs every 8 hours. Both Domain Controllers show the following errors in the Application Log Source: AutoEnrollment Type: Error User: N/A Computer: DomainController1 or 2 Event ID: 13 Description: Automatic certificate enrollment for loc...

Unknown error code
Hi, I'm trying to set up email in outlook 2007. After I put in all the information and click on "test account settings", I'm getting an error message. I mean, in the task tab, the "log onto incoming mail server (POP3), the status is completed" but on "send test email message, the status is Failed. and the error message is giving is "an unknown error code: 0x80040607 Does anybody knows out to fix this? Have you been able to send mail out before using that account, possibly with another mail client? "DM" <DM@discussions.microsoft.com> ...

error message in Outlook after running windows and office updates
Hello, I am hoping someone can help me with this. When ever a few staff tries to send a new e-mail, a message "Do you want to save changes" come up and on the back of this message I see the spell checker box come up. The problem is that if you click on "no" on the error message, it comes up with another message something like "running out of memory". I just ran all the windows and office updates on all the machines in the company and so far two employees are having this problem. Is this something to do with the updates? Any suggestions? did you try ...