By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,747 Members | 1,808 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,747 IT Pros & Developers. It's quick & easy.

Passing a string to an expression in Access

P: 16
I am trying to take the results of a subroutine (which is the string strFields in the code below) and pass them to an expression in an Access query. Is this even possible or am I thinking about this the wrong way? Can I only use functions in an expression?

Expand|Select|Wrap|Line Numbers
  1. Sub Fieldnames() 
  2.     Dim Rst As Recordset
  3.     Dim strFields As String
  4.     Dim db As Database
  5.     Dim f As Field
  6.     Dim qdfParmQry As QueryDef
  7.         Set db = CurrentDb()
  8.         Set qdfParmQry = db.QueryDefs("qry_1test")
  9.         qdfParmQry("Forms!Occu_formatting!Species") = [Forms]![Occu_formatting]![Species]
  10.         Set Rst = qdfParmQry.OpenRecordset()
  11.  
  12.     For Each f In Rst.Fields
  13.       strFields = strFields & f.Name & ", "
  14.     Next
  15.     Rst.Close
  16.  
  17. strFields = Left(strFields, Len(strFields) - 2)
  18.  
  19. End Sub
Mar 13 '14 #1

✓ answered by GKJR

I've read that one of the distinctions between functions and subroutines is that only functions can be called from a query. Couldn't you just change this sub to a function?

Share this Question
Share on Google+
3 Replies


100+
P: 104
I've read that one of the distinctions between functions and subroutines is that only functions can be called from a query. Couldn't you just change this sub to a function?
Mar 13 '14 #2

P: 16
Ok that worked...now on to the next problem!
Mar 13 '14 #3

NeoPa
Expert Mod 15k+
P: 31,769
Good answer. Only function procedures (code that returns values) can be called from SQL. Sub procedures may not even be invoked from SQL so even if you want code to execute and don't care about the returned value, you still need a function if called from SQL.

NB. Queries are written in SQL.
Mar 13 '14 #4

Post your reply

Sign in to post your reply or Sign up for a free account.