#error in calculated query field

I can't figure this one out:

I have a text field that contains either:
1. a double
2. a string beginning with a comparison operator followed by a double
3. null

I have created two functions that will nicely convert either (1) 
or (2) above into two fields,
the operator (if there is one) and the number.

However, if the text field is null, I receive #error in both of 
my new fields.  I have tried returning an empty string from the 
function that fills the operator field, or a null from the 
function that fills the numeric field, but I still
get #error.  THIS IS DRIVING ME NUTS!!

Here are the two little functions:

Function Ext_Qual(strField As String) As String
Ext_Qual = ""
If Not IsNull(strField) Then
     If Not IsNumeric(strField) Then
         Ext_Qual = Left(strField, 1)
     End If
End If
End Function


Function Ext_Conc(strField As String) As Double
Ext_Conc = Null
If Not IsNull(strField) Then
     If IsNumeric(strField) Then
         Ext_Conc = Val(strField)
     Else
         Ext_Conc = Val(Right(strField, Len(strField) - 1))
     End If
End If
End Function


Thank you,
Kate
0
Kate
5/3/2007 3:37:35 PM
access 16762 articles. 3 followers. Follow

2 Replies
1418 Views

Similar Articles

[PageSpeed] 2

Sorry, that second function should return a variant, not a 
double, or an invalid use of null occurs.

Kate wrote:
> I can't figure this one out:
> 
> I have a text field that contains either:
> 1. a double
> 2. a string beginning with a comparison operator followed by a double
> 3. null
> 
> I have created two functions that will nicely convert either (1) or (2) 
> above into two fields,
> the operator (if there is one) and the number.
> 
> However, if the text field is null, I receive #error in both of my new 
> fields.  I have tried returning an empty string from the function that 
> fills the operator field, or a null from the function that fills the 
> numeric field, but I still
> get #error.  THIS IS DRIVING ME NUTS!!
> 
> Here are the two little functions:
> 
> Function Ext_Qual(strField As String) As String
> Ext_Qual = ""
> If Not IsNull(strField) Then
>     If Not IsNumeric(strField) Then
>         Ext_Qual = Left(strField, 1)
>     End If
> End If
> End Function
> 
> 
> Function Ext_Conc(strField As String) As Double
> Ext_Conc = Null
> If Not IsNull(strField) Then
>     If IsNumeric(strField) Then
>         Ext_Conc = Val(strField)
>     Else
>         Ext_Conc = Val(Right(strField, Len(strField) - 1))
>     End If
> End If
> End Function
> 
> 
> Thank you,
> Kate
0
Kate
5/3/2007 3:42:44 PM
I've solved the problem.  When calling the functions from within 
the query,
I need to use nz thus:
New_fld: Ext_qual(nz([field],""))
as both functions are expecting a string.

Thanks for listening,
Kate

Kate wrote:
> I can't figure this one out:
> 
> I have a text field that contains either:
> 1. a double
> 2. a string beginning with a comparison operator followed by a double
> 3. null
> 
> I have created two functions that will nicely convert either (1) or (2) 
> above into two fields,
> the operator (if there is one) and the number.
> 
> However, if the text field is null, I receive #error in both of my new 
> fields.  I have tried returning an empty string from the function that 
> fills the operator field, or a null from the function that fills the 
> numeric field, but I still
> get #error.  THIS IS DRIVING ME NUTS!!
> 
> Here are the two little functions:
> 
> Function Ext_Qual(strField As String) As String
> Ext_Qual = ""
> If Not IsNull(strField) Then
>     If Not IsNumeric(strField) Then
>         Ext_Qual = Left(strField, 1)
>     End If
> End If
> End Function
> 
> 
> Function Ext_Conc(strField As String) As Double
> Ext_Conc = Null
> If Not IsNull(strField) Then
>     If IsNumeric(strField) Then
>         Ext_Conc = Val(strField)
>     Else
>         Ext_Conc = Val(Right(strField, Len(strField) - 1))
>     End If
> End If
> End Function
> 
> 
> Thank you,
> Kate
0
Kate
5/3/2007 4:27:55 PM
Reply:

Similar Artilces:

Time Calculation 03-26-10
I am kind of beating my head over this problem. I am trying to create a rule by where the user cannot enter data if todays date is greater than a function date - max time given in a query So lets say the user has 72 hrs to respond before an event date lets say Jan 1, 2010. I tried this so far but its not working : EventDatePlusMaxTime: DateDiff("n",[functiondate]+[maxtime],[functiondate]) On Fri, 26 Mar 2010 14:57:01 -0700, smartcookie <smartcookie@discussions.microsoft.com> wrote: >I am kind of beating my head over this problem. > >I am t...

Error 0x8004010a
Anybody know what this is? We're using Outlook 2002, and we can send messages out fine, but not recieve. When I hit the send/recieve button it goes up to 50%, then sits there for a while, then gives me a "sending and receiving report error 0x8004010a". Don What sort of mail account do you have? That message means that an object that Outlook was trying to access was deleted. Do you perhaps have some anti-virus or anti-spam software installed that could be doing something to messages as they're received? -- Jeff Stephenson Outlook Development This posting is provided...

msimn error
my outlook gives the error: "Msimn has caused an error in DIRECTDB.DLL. Msimn will now close. If you continue to experience problems, try restarting your computer." any ideas, its a windowsME compaq laptop.i have already re-installed the system and updated the antivirus. ...

loadsim error not seeing exchange server
I installed loadsim on an XP client. The outlook client on the client can connect to the exchange server (exchange 2003 on windows 2003 cluster) and get mail. However the loadsim 2003 topology property can not see the exchange server. The error message is: Apr 19 15:36:29: ERROR: Failed searching in CN=First Organization,CN=Microsoft Exchange,CN=Services,CN=Configuration,DC=poc,DC=local: No Such Object Apr 19 15:36:29: Running with Outlook version 11.0.5604.0. Apr 19 15:36:29: Protocol DLL ("loadout.dll") initialized successfully. Apr 19 15:36:29: LoadSim initialized. Maxim...

Errors in queue jobs
We suddenly started to have errors in job queue when someone is trying to save timesheet. There are two different errors that are related to timesheet saving. We have tried to find the problem but no luck so far. Any suggestions what is causing this? Errors are listed down below. Error summary/areas: Queue GeneralQueueJobFailed Error details: <?xml version="1.0" encoding="utf-16"?> <errinfo> <general> <class name="Queue"> <error id="26000" name="GeneralQueueJobFailed" uid="df309f8...

Blank To: Field
Whenever I try to send a message to a certain mailing list, the To: field shows up as being empty and moves the list to the CC: field. If the cc: field is already occupied, the message is not sent. This only happens to a specific mailing list (on the server). any suggestions? thanks stewart sschwartz@nal.usda.gov ...

The query processor could not produce a query plan
SQL 2005 with SP3 Before I added the following view to the SELECT that follows it, the whole thing has always run fine. This view gets all the unique dates from a main table (tblNotices) and one of its subtables (tblNotices_AdjournmentDates) ALTER VIEW [dbo].[vwLatestAdjSaleDates] AS SELECT DISTINCT NoticeID, (SELECT TOP 1 AdjournmentDate FROM dbo.tblNotices_AdjournmentDates WHERE NoticeID = A.NoticeID ORDER BY AdjournmentDate DESC) AS DateOfSale FROM dbo.tblNotices_AdjournmentDates A UNION ALL SELECT NoticeID, DateOfSale FROM dbo.tblNotices WHERE NoticeID NOT IN (SE...

append query 03-03-08
i am trying to append from one table (NewItems) to another(Main), but get an error saying it can't append 975 [of 4000] records due to key violations. The target table (Main)has a composite primary key made of date and itemnumber. All the items to be added have a new date that doesn't exist in Main. The append query is left joined on NewItems ItemNumber, so that all records from NewItems table should go into Main table. What am i not seeing? Why are 975 items being rejected? thanks. doh! i was using an expression to add , so i had to use a max query instead of the Main table...

Passing query user input to report body
Hello, I set up a simple query to prompt the user for the date which is used as a criteria. [Enter the start date] is there a way to pass the value entered by the user at the prompt the the report, so I can have in the header 'report from ####/##/## to date()'? Is there a term for what I'm trying to do? I could find anything on it because I don't know what it is I am looking for. Thank you, QB You can refer to the parameter just like a field. Try a text box with Control Source of: =[Enter the start date] -- Allen Browne - Microsoft MVP. Perth, Western Austr...

http405 error sending mail from crm web
CRM 1.0 installation has been in production for 2 months, all features functional, email can be sent IN to CRM queues even, but no mail can be sent FROM CRM. It has never worked. Below is the error I receive when attempting to send mail from CRM web. CRM app and its SQL are on one server, mail server on another. Any ideas? Dan dan@vr-inc.com Event Type: Error Event Source: Microsoft CRM Event Category: None Event ID: 2 Date: 2/9/2004 Time: 4:47:24 PM User: N/A Computer: SIDESHOW Description: MSCRM Platform Error Report: Error Message: An unexpected error occurred. Error Det...

Error sending mail OUTLOOK 2002
Each time I attempt to send an email I receive the message TCP/IP connection was unexpectedly terminated by the server. Error number 0x800ccc0f. Any ideas of what I've done to cause this message. Antivirus or Firewall? Click start, run, enter MSCONFIG, click OK. Change from Normal to Selective startup. Uncheck and disable the 4th option, Load startup items. Click Apply, close, and restart. Open Outlook and test. If it works, Click start, run, enter MSCONFIG, click OK. Go to the Startup tab at the top, Enter everything but your antivirus and/or Firewall software. Norton 2002 = NAVAPW32 (...

Exit error
I have just converted an Access 97 mdb to Access 2003. In my application I have a MainMenu form (using a modified switchboard). The form has an Exit button to quit Access. It uses the code; Application.Quit acQuitPrompt (was Application.Quit acSaveYes in Access 97) This always used to work just fine and apart from changing the option "acSaveYes " to "acQuitPrompt " I have not changed anything else in the code. However, when I click the Exit button I now get Error 91 (object not set... etc.). I have also tried changing Application.Quit acQuitPrompt to Dcmd.Quit but I ...

Error Number 80040E14
Hello, I am getting this error number in the CRM error page, when i try to redirect a request to an URL.The URL when opened separately works fine. The error look up page in the CRM MSDN does not contain any entry for this number. Any idea on what this error is? Regards, Venkat ...

SchedulePlusFreeBusy
Hello, I am running exchange 2000 SP3 and I am receiving daily the following message: Event Type: Error Event Source: MSExchangeFBPublish Event Category: General Event ID: 8275 Description: SchedulePlusFreeBusy folder could not be opened for duplicate message deletion on virtual machine SERVER1. The error number is 0x80040005. I have re-installed the public store from scratch but I am still getting the same error. Any ideas? Thanks, Frank van Niele. Hello, Did you find solution to this problem? I have the same... with Ex2k3 SP1... Regards... "Frank van Niele" wrote: > H...

Global Change to Drop-Down Form Fields
I need to make a global change to a large number of Drop-Down form fields. They currently have the same values, I just want to change those values to something different. Anyone know how to do this? On Fri, 5 Feb 2010 06:05:42 -0800 (PST), iamnu <iamnubw@gmail.com> wrote: >I need to make a global change to a large number of Drop-Down form >fields. They currently have the same values, I just want to change >those values to something different. > >Anyone know how to do this? You can get your knickers in a twist trying to change specific entries, but it&#...

checking queries between programs
Hi all I have several access programs that are extremely similar but written for a purpose at the time I now wish to combine into one access program Is there a simple way of checking queries between these programs to see if they do the same job or not Hope this makes sense, regards, Garry ...

change autocomplete choice in to: cc: or bcc: fields
is there a way to change or remove a name that gets "autocompleted" in the to:, cc:, or bcc: fields? right now i have 2 entries that begin with "jennifer" and i'm always prompted with the one i don't want. is there a way to change this? thanks. ross You can just delete the one you don't want when it pops up. i realize that, but i'd like to change it to prompt me for the correct one. >-----Original Message----- >You can just delete the one you don't want when it pops up. >. > >i realize that, but i'd like to change it to pr...

Calculating a value but omitting cells with empty data
I want to calculate a value based on several cells in say row 4. However, I do not want to include values in any columns that do not also have a non-missing value in, say, row3. Thus my calculated cell in, say column A, should have some syntax like: IF A3 <> MISSING THEN < do calculation of value> I hope this is clear. What is the proper syntax for the pseudocode that I have above? Thanks! Depends on what your calculation is. Are you summing, counting, multiply, etc.?Each has their own formula structure. For instance, the basic summing one is =SUMIF(3:3,"<>&...

OWA Error : -2147023569
Hi - I am getting the follwoing error when trying to implement password change. Everything configured as indicated in MS KB article but when I go into OWA and attempt the change, I get - Error number: -2147023569 Odd this is, If the new password doesn't match, I get a page that says - Passwords Don't Match. So I know at least part of the functionality is there. Any help would be appreciated. Using Windows Calculator in Scientific mode, I enter that code and change to hex, and it translates to 8007052F. Google that code and you'll get some interesting and perhaps useful...

Public Stores errors
I have a public folder store error that occurs each time I try to mount the database. THe error is: The store could not be mounted because the Active Directory information was not replicated yet. The Microsoft Exchange Information Store service could not find the specified object. ID no:c1041722 I have change the permission according to article 823022. I can't place my finger on it but it's like there is something with AD or IIS that's stopping my from mounting the public store database. Here are the rights: Administrators: Full Control Authenticated Users: Read and Ex...

Setting Focus on the Body (Message) field
How do I set the focus on the Body(message) field in outlook 2k? I have tried Set myInspector = Item.GetInspector Set myPage = myInspector.ModifiedFormPages("Message") myPage.Message.SetFocus 'mypage.body.setfocus but this does not seem to be working. Thanks ...

Server Error "/" Application
Whenever i am submitting datas i get an error popping up in xml parsing. It requests to change validateRequest=false. Where to do it? Or what might be the problem? That setting is located in the machine.config. The path will be something like this. C:\WINNT\Microsoft.NET\Framework\v1.1.4322\CONFIG Hope this helps. >-----Original Message----- >Whenever i am submitting datas i get an error popping up >in xml parsing. It requests to change >validateRequest=false. Where to do it? > >Or what might be the problem? > > >. > ...

Unspecified Error
Hi all, I am using Visio 2007 in my application using VB.NET. Applications run normally on any machine which has licensed version of Visio 2007 but if I run the same application on machine which has Trial version of Visio 2007 Visio control throws "Unspecified Error". I even tried to add control through code but it gives the same error. Is there any way to check either installed Visio Version is Trial or not? Thanks Asif ------=_NextPart_0001_7A88E2B1 Content-Type: text/plain Content-Transfer-Encoding: 7bit If you are still looking for resources on this question, there is an ...

Subtracting values in a query
I have a query with multiple repeating values, each value has a set of readings assigned to it How do I subtract the max/min from the readings for each value (looking for change over time)? Example: Value Reading over time 1 2 1 3 1 0.5 2 3 2 4 2 1 3 7 3 2 3 0.3 I would like Access to automatically subtract 3 - 0.5 for value 1; 4 - 1 for value 2; and so on. Is this possible? Thanks! Replace 'z' with your [Value] and 'x' ...

Using saved query for different tables
I want to use the same query for many tables. I'm new to Access. I see where you can save the query and name it, but is there a way to then use it with different tables? I can't see where that can be done. This would be most helpful in Update Queries. -- Barry Barry: You could build the UPDATE statements in code in a dialogue form's module, getting the variable table names from unbound controls on the form, and then execute the statement. You could even modify the SQL property of a saved query in code and then execute it. The fact that you are contemplating thi...