Kaup, Chester
Chester_Kaup at kindermorgan.com
Wed Dec 5 13:40:21 CST 2012
Tried changing the else "" to "SI" with no change. I checked the source table and there are no nulls in the status field. Thanks for the ideas. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Wednesday, December 05, 2012 11:37 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Data type mismatch in function Change your else to return a real string - 'test' or something like that and see what happens. Also are you sure that you have valid data in that first record to pass in to the function? If the [Status] field has a null and tries to pass that in to a string variable, that will cause a problem. If you are going to have nulls to pass in, then you need to change the Status variable in the function input variable to a variant, and then cast it inside of the function using ToStr() or whatever. John W. Colby Colby Consulting Reality is what refuses to go away when you do not believe in it On 12/5/2012 12:14 PM, Kaup, Chester wrote: > I noticed that right after posting. Unfortunately that did not fix the problem. > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby > Sent: Wednesday, December 05, 2012 11:05 AM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Data type mismatch in function > > I don't see return status dimensioned in the function. If it isn't dimensioned then it will be dynamically dimensioned as type variant which may be causing an issue somehow. > > John W. Colby > Colby Consulting > > Reality is what refuses to go away > when you do not believe in it > > On 12/5/2012 11:54 AM, Kaup, Chester wrote: >> I am calling the following function from a query. I get a type mismatch error at the first record. Value of status is "PA". I don't understand the mismatch. Thanks. >> >> Here is the SQL. dbo_DSS_StatusChanges is an SQL server table with >> status as a data type of text >> >> SELECT PID, WellStatus(Status) AS ReturnStatus FROM >> dbo_DSS_StatusChanges; >> >> Here is the function: >> >> Public Function WellStatus(Status As String) As String >> >> Select Case Status >> Case "FL" Or "FM" Or "FH", "PL" Or "PM" Or "PH" Or "SL" Or "SM" Or "SH" Or "SP" Or "RL" Or "RM" Or "RH" Or "RP" >> ReturnStatus = "PRD" >> Case "CI" Or "WAGC" >> ReturnStatus = "GasI" >> Case "WD" Or "WI" Or "WC" Or "WCH" Or "WF" >> ReturnStatus = "WI" >> Case Else >> ReturnStatus = "" >> End Select >> >> WellStatus = ReturnStatus >> >> End Function >> > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com