Need help with data type returned from a function

[punderful]

I asked a question in the reports forum, it appears my question should really be related to queries. My function works in the sense that I don't get anymore errors, but the results are not correct. It should return a date, and it's returning a time (12:00:00am). Here's where my original thread is located:

thread703-486390 please.

 

[scriverb]

My first suggestion would be to look directly at the table data and see what is stored in the field being returned. Make sure that entered Date data is stored there.

Get back with that info please. Bob Scriver

 

[punderful]

The date data is fine, of the 31 records 4 have dates. When I run a query based on the sql I wrote for my function they come up fine, but when I run a query using the function (containing that sql) it comes up incorrect. It's giving me a time of 12:00:00am for all the records that were empty, not showing the records that contain a date value.

 

[scriverb]

Okay, I haven't been involved through your other thread but I am confused. Just what are you trying to return with the function call. Please copy/paste the query that is making the function call. And then tell me what you think is going to be returned from the function call. I don't see anything of significance being found and returned from the function.

Slow walk me through your problem please but give me the code you are using. Bob Scriver

 

[punderful]

I ran a query with the ID#, TaskName, DueDate, (using the same sql as my function) - here's the results:

txtSiteID Document dtmDueDate
SA13 NIER (empty)
TA01 NIER 3/5/2003
SA12 NIER 3/5/2003
TA18 NIER 3/5/2003

I run the same query using my function:
txtSiteID Document getRS function
SA13 NIER 12:00:00 AM
TA01 NIER 12:00:00 AM
SA12 NIER 12:00:00 AM
TA18 NIER 12:00:00 AM

Help please, why is my function returning a time when it should be the same results as dtmDueDate (date)? I need to use a function to build a query that will pull a the due date for a specific document. I will be using the function multiple times in the query, asking for another date, like the rec'd date, or a different document. I'll then use this query for a report. The reason I need to do this is that I have to produce a report with fields from multiple tables, and managements only wants specific documents and dates in thier order, not just putting a subreport in the report.

Public Function GetRS(strTask As String, strDateType As String) As String

'Creates recordset for reporting based on user criteria

Dim rst As ADODB.Recordset
Dim strSQL As String

'create sql string selecting records related to specific task


strSQL = "SELECT tblDocumentDetail.txtSiteID, tblDocumentDetail.txtDocumentName, "
strSQL = strSQL & "tblDocumentDetail." & strDateType
strSQL = strSQL & " FROM tblDocumentDetail "
strSQL = strSQL & "WHERE (((tblDocumentDetail.txtDocumentName)= '" & strTask & "'));"

'Declare and instantiate a recordset
Set rst = New ADODB.Recordset

'Establish the connection, cursor type,
'and lock type, and open the recordset

rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
rst.LockType = adLockOptimistic
rst.Source = strSQL
rst.Open
rst.MoveFirst

rst.Close
Set rst = Nothing

End Function

 

[Zathras]

You never returned anything from the function so GetRS remains an empty string. I think you need to have a line at the end of the code, something like this:

  GetRS = xxxxx

where

xxxxx

is your date in the format you want.

 

[scriverb]

Please post the exact code that you use to call the Function including the parameter values.

This is where the problem lies. the 12:00 am is a default that is showing because within your query the cancatenation of the parameter strDateType isn't being recognized properly. Bob Scriver

 

[scriverb]

As I indicated earlier about my confusion as to what the Function's purpose is and Zathras's posting about making an assignment, there needs to be some additional code to be able to return data from this Function. See the red code below. An assignment of the recordset field that holds your DueDate or the field name being passed to this function has to be made to the Function Name itself. This line of code should do that if your are passing through the calling procedure the name of the field being sought.

rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
rst.LockType = adLockOptimistic
rst.Source = strSQL
rst.Open
rst.MoveFirst
GetRS = rst(strDateType)

I would still like to see the original call from your query. Also, I would like to see the entire query because I am not sure that the entire function couldn't be replaced by a join of some sort to the table tblDocumentDentail and just pick up the DueDate data there.

Bob Scriver