Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-13-2011, 12:34 PM   #1
PISI Guy
Newly Registered User
 
Join Date: Dec 2011
Posts: 8
Thanks: 2
Thanked 0 Times in 0 Posts
PISI Guy is on a distinguished road
Type conversion error on select/update/make table query

I've written a function that takes three integers and returns a double. Any time I try to call it from a query it returns an error; select query will return #error, update or make table return a type conversion failure. ??

1) The code works fine when run in the immediate window of VBA editor
2) I'm sure that the values I'm passing it are of the correct type (integer)
3) I've tried update queries where the field for the calculated value is double (what it should be), variant, and text; all give type conversion error
4) When I first tested the code it worked fine, except that certain calls resulted in division by zero. I changed one line of arithmatic code, now I'm having this problem

Code:
Function plot_live_C(intInst As Integer, intPlot As Integer, intMeas As Integer) As Double
 
'some stuff happens in here, works great in VBA editor
 
plot_live_c=dblTotalC
 
exit function
sample SQL select query is
Code:
 SELECT tMeasurement_Dates.installation, tMeasurement_Dates.plot, tMeasurement_Dates.measurement, plot_live_C([tMeasurement_Dates]![installation]),([tMeasurement_Dates]![plot],[tMeasurement_Dates]![measurement]) AS liveC
FROM tMeasurement_Dates;
Thanks for any help, I'm baffled!

PISI Guy is offline   Reply With Quote
Old 12-13-2011, 12:40 PM   #2
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: Type conversion error on select/update/make table query

and you have valid not-null input data in all the records?
spikepl is offline   Reply With Quote
Old 12-13-2011, 12:46 PM   #3
PISI Guy
Newly Registered User
 
Join Date: Dec 2011
Posts: 8
Thanks: 2
Thanked 0 Times in 0 Posts
PISI Guy is on a distinguished road
Re: Type conversion error on select/update/make table query

Yup, all that's handled in the table definition. In addition I've tried it selecting for a single record with known (valid) values. I also tried just passing values directly in the query e.g. plot_live_C(1,1010,1) and had the same problem. There must be something I'm missing...

PISI Guy is offline   Reply With Quote
Old 12-13-2011, 12:49 PM   #4
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: Type conversion error on select/update/make table query

Weird. Try to wrap the call in Cdbl

Cdbl(Yourfunction)
spikepl is offline   Reply With Quote
Old 12-13-2011, 12:52 PM   #5
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: Type conversion error on select/update/make table query

Ah - your function should be Public in a code module -the default is Private

Last edited by spikepl; 12-13-2011 at 01:01 PM.
spikepl is offline   Reply With Quote
Old 12-13-2011, 12:55 PM   #6
PISI Guy
Newly Registered User
 
Join Date: Dec 2011
Posts: 8
Thanks: 2
Thanked 0 Times in 0 Posts
PISI Guy is on a distinguished road
Re: Type conversion error on select/update/make table query

Sadly, still #error. I tried some other similar queries as well and I have the same result. The other thing is that the result comes back almost instantly, when actually running the query for the full recordset should take a fair amount of time - a few minutes at least. This makes me think it's a problem with the query call, not the code, and not the handling of the returned value. There are also a few debug.prints in the code, and they do not output to the immediate window when I call the function from a query, again making me think that the code never executes.
PISI Guy is offline   Reply With Quote
Old 12-13-2011, 01:01 PM   #7
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: Type conversion error on select/update/make table query

See mod above

spikepl is offline   Reply With Quote
The Following User Says Thank You to spikepl For This Useful Post:
PISI Guy (12-13-2011)
Old 12-13-2011, 01:12 PM   #8
PISI Guy
Newly Registered User
 
Join Date: Dec 2011
Posts: 8
Thanks: 2
Thanked 0 Times in 0 Posts
PISI Guy is on a distinguished road
Re: Type conversion error on select/update/make table query

Bingo. I knew it had to be something simple. Thanks!!

PISI Guy is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Update query - type conversion failure mrshah Queries 4 12-06-2011 04:49 PM
"Type conversion failure" error when running update query ktmchugh Queries 2 04-07-2009 07:38 PM
Type Conversion Failure in Update Query Jemmo Queries 5 10-09-2008 06:21 AM
Conversion Type Error Cheerfulanne Queries 7 08-06-2008 07:59 PM
Type Conversion errors on update query robert_neville Queries 2 04-30-2003 05:28 PM




All times are GMT -8. The time now is 05:36 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World