When calling a function from a SQL statement I get back a null

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to convert an integer value in a database to a date. I have
written a function ("Date_Convert") that accepts a string argument (i.e. the
converted integer) and returns a string representation of the date. However,
when I call the function in SQL:

UPDATE
SET
.[Date] = Date_Convert(
.[Date]);

the function returns a null. I have tested the function and does work,
however I read somewhere that functions cannot return values.

Thanks.
 
Functions can return values. That's the difference between a sub and a
function in VBA - a function returns a value, a sub doesn't.

I suspect the problem is probably in the function code. If you post that,
someone can probably tell you what the problem is.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
I am trying to convert an integer value in a database to a date. I
have written a function ("Date_Convert") that accepts a string
argument (i.e. the converted integer) and returns a string
representation of the date. However, when I call the function in SQL:

UPDATE
SET
.[Date] = Date_Convert(
.[Date]);

the function returns a null. I have tested the function and does
work, however I read somewhere that functions cannot return values.
Click to expand...


There are a couple of obvious problems here.

The first may be an artefact: the use of DATE as a field name is a Really
Bad Idea, because it's reserved word in just about every language
imaginable, including Access, VBA and SQL. Perhaps your real situation
uses a better name, though.

The next is logical: you seem to be SETting the same field that you are
passing to the function, and you say the function starts with an integer
parameter and returns a string return. The Table.Date field cannot hold
both an integer and a string value so one way or another there has to be
a datatype failure.

Another problem is to do with Access and Jet. You don't say how you are
calling this SQL command. Remember that Jet itself knows nothing about
the Access GUI or its code, forms or report modules. Access itself,
however, does have a query interpreter that can intercept calls like this
and make it all work. In other words, this will not work:

db.Execute strSQL, dbFailOnError

because the Date_Convert will not be recognised. This should work:

DoCmd.RunSQL strSQL ' no error handling allowed

because it's a GUI statement not a database one, but has other
disadvantages. Similarly, double-clicking the query in the DB window
should be okay too.

Hope that helps


Tim F
 
Back
Top