function as tablename in SQL

  • Thread starter Thread starter UpRider
  • Start date Start date
U

UpRider

I'd like to use a function to name tables in SQL statements, e.g. the
function 'fcnTable' below:

strSQL = "SELECT MasterID, masText, masDate, masCode, masLT,
masDelete " _
& "FROM fcnTable() " _
& "WHERE fcnTable().[masDate] between fcnDate() and fcnDate()
+ [masLT];"
Click to expand...
Click to expand...

This would allow me to use substitute tables in a test mode.
The question is, does Access allow this, and how do I write the function?

TIA
Barto
 
Concatenate the value from the function into the string:

strSQL = "SELECT MasterID, masText, masDate, masCode, masLT, masDelete " _
& "FROM " & fcnTable() _
& " WHERE " & fcnTable() & ".[masDate] between " & fcnDate() & " and " &
_
fcnDate() & " + [masLT];"

As for how you write the function? Not sure what you mean, but it would be
something like this:

Public Function fcnTable() As String
fcnTable = "TableName"
End Function


Public Function fcnDate() As Date
fcnDate = #3/31/2005#
End Function

--

Ken Snell
<MS ACCESS MVP>

UpRider said:
I'd like to use a function to name tables in SQL statements, e.g. the
function 'fcnTable' below:

strSQL = "SELECT MasterID, masText, masDate, masCode, masLT,
masDelete " _
& "FROM fcnTable() " _
& "WHERE fcnTable().[masDate] between fcnDate() and fcnDate()
+ [masLT];"
Click to expand...
Click to expand...

This would allow me to use substitute tables in a test mode.
The question is, does Access allow this, and how do I write the function?

TIA
Barto
Click to expand...
 
Thanks, Ken.
That simple..., huh. I was trying to set the function up to resolve to a
tabledef. It merely has to be a string.
And your solution works fine.

Barto.

Ken Snell said:
Concatenate the value from the function into the string:

strSQL = "SELECT MasterID, masText, masDate, masCode, masLT, masDelete " _
& "FROM " & fcnTable() _
& " WHERE " & fcnTable() & ".[masDate] between " & fcnDate() & " and "
& _
fcnDate() & " + [masLT];"

As for how you write the function? Not sure what you mean, but it would be
something like this:

Public Function fcnTable() As String
fcnTable = "TableName"
End Function


Public Function fcnDate() As Date
fcnDate = #3/31/2005#
End Function

--

Ken Snell
<MS ACCESS MVP>

UpRider said:
I'd like to use a function to name tables in SQL statements, e.g. the
function 'fcnTable' below:

strSQL = "SELECT MasterID, masText, masDate, masCode, masLT,
masDelete " _
& "FROM fcnTable() " _
& "WHERE fcnTable().[masDate] between fcnDate() and
fcnDate() + [masLT];"
Click to expand...

This would allow me to use substitute tables in a test mode.
The question is, does Access allow this, and how do I write the function?

TIA
Barto
Click to expand...
Click to expand...
 
Back
Top