Is there something like a pointer variable of C in Access?

[Sunil Korah]

I dont know whether this is a stupid question. But does access have
something akin to the pointer variable in C - or for that matter the
'&' operator in xbase.

I will explain my reason for wanting it too. I often use SQLs in which
the where condition gets the data from a variable. e.g

SELECT * from Customer
WHERE CustID = strID

strID being a public variable. But of course the above SQL does not
work as such, because SQL can't read that variable. So I use a
function like

Public Function GetID()
GetID=strID
End Function

and the actual SQL I use is

SELECT * from Customer
WHERE CustID = GetID()

The problem is, each time I have to write a function. Whereas in xBase
for example I would have a single function like this

Function GetVar
Parameters InVar
GetVar = &InVAr
Return GetVar

so that if I call the function as GetVar(strID) I would get the strID
value.

Any help would be appreciated
Sunil Korah

[Terry Kreft]

Hmm, ok, it's not exactly clear what you are doing.

If you are building SQL statements on the fly then you can just concatenate
the variable value into the statement.

e.g.
dim strSQL as string

strSQL = "SELECT * from Customer " _
& "WHERE CustID = " & strID

If you're using a query and your using it to open a recordset then you can
use parameters and fill the parameters.

In DAO it would go like this

dim loDB as DAO.Database
Dim loQdf as DAO.Querydef
Dim loRst as DAO.Recordset

set loDb = Currentdb
Set loQdf = loDb.QueryDefs("YourQuery")
With loQdf
.Parameters(0) = strID
set loRst = .OpenRecordset
End with

In ADO
Dim loDB As ADODB.Connection
Dim loQdf As ADODB.Command
Dim loRst As ADODB.Recordset
Dim strID As String

strID = 1
Set loDB = Application.CurrentProject.Connection
Set loQdf = New ADODB.Command
With loQdf
Set .ActiveConnection = loDB
.CommandText = "YourQuery"
.CommandType = adCmdStoredProc
Set loRst = .Execute(Parameters:=Array("1"))
End With

If your using the query's as recordsources or rowsources then you could just
create a generic function to return generic variables

Sou you could just have a function
Function QueryParam(Index as long) as Variant
Select Case Index
Case 0
QueryParam = strID
Case 1
QueryParam = lngID
Case 2
QueryParam = dblAnotherVar
' ...
End Select
End function

Or you could programatically change the SQL of the query.
--
Terry Kreft
MVP Microsoft Access
"Sunil Korah" <hb*****@indiatimes.com> wrote in message
news:72**************************@posting.google.c om...

I dont know whether this is a stupid question. But does access have
something akin to the pointer variable in C - or for that matter the
'&' operator in xbase.

I will explain my reason for wanting it too. I often use SQLs in which
the where condition gets the data from a variable. e.g

SELECT * from Customer
WHERE CustID = strID

strID being a public variable. But of course the above SQL does not
work as such, because SQL can't read that variable. So I use a
function like

Public Function GetID()
GetID=strID
End Function

and the actual SQL I use is

SELECT * from Customer
WHERE CustID = GetID()

The problem is, each time I have to write a function. Whereas in xBase
for example I would have a single function like this

Function GetVar
Parameters InVar
GetVar = &InVAr
Return GetVar

so that if I call the function as GetVar(strID) I would get the strID
value.

Any help would be appreciated
Sunil Korah

[Terry Kreft]

Whoops,
Set loRst = .Execute(Parameters:=Array("1"))

should be
Set loRst = .Execute(Parameters:=Array(strID))

--
Terry Kreft
MVP Microsoft Access
"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:Hz********************@karoo.co.uk...

Hmm, ok, it's not exactly clear what you are doing.

If you are building SQL statements on the fly then you can just concatenate the variable value into the statement.

e.g.
dim strSQL as string

strSQL = "SELECT * from Customer " _
& "WHERE CustID = " & strID

If you're using a query and your using it to open a recordset then you can
use parameters and fill the parameters.

In DAO it would go like this

dim loDB as DAO.Database
Dim loQdf as DAO.Querydef
Dim loRst as DAO.Recordset

set loDb = Currentdb
Set loQdf = loDb.QueryDefs("YourQuery")
With loQdf
.Parameters(0) = strID
set loRst = .OpenRecordset
End with

In ADO
Dim loDB As ADODB.Connection
Dim loQdf As ADODB.Command
Dim loRst As ADODB.Recordset
Dim strID As String

strID = 1
Set loDB = Application.CurrentProject.Connection
Set loQdf = New ADODB.Command
With loQdf
Set .ActiveConnection = loDB
.CommandText = "YourQuery"
.CommandType = adCmdStoredProc
Set loRst = .Execute(Parameters:=Array("1"))
End With

If your using the query's as recordsources or rowsources then you could just create a generic function to return generic variables

Sou you could just have a function
Function QueryParam(Index as long) as Variant
Select Case Index
Case 0
QueryParam = strID
Case 1
QueryParam = lngID
Case 2
QueryParam = dblAnotherVar
' ...
End Select
End function

Or you could programatically change the SQL of the query.
--
Terry Kreft
MVP Microsoft Access
"Sunil Korah" <hb*****@indiatimes.com> wrote in message
news:72**************************@posting.google.c om...

I dont know whether this is a stupid question. But does access have
something akin to the pointer variable in C - or for that matter the
'&' operator in xbase.

I will explain my reason for wanting it too. I often use SQLs in which
the where condition gets the data from a variable. e.g

SELECT * from Customer
WHERE CustID = strID

strID being a public variable. But of course the above SQL does not
work as such, because SQL can't read that variable. So I use a
function like

Public Function GetID()
GetID=strID
End Function

and the actual SQL I use is

SELECT * from Customer
WHERE CustID = GetID()

The problem is, each time I have to write a function. Whereas in xBase
for example I would have a single function like this

Function GetVar
Parameters InVar
GetVar = &InVAr
Return GetVar

so that if I call the function as GetVar(strID) I would get the strID
value.

Any help would be appreciated
Sunil Korah

[Sunil Korah]

Terry,

Thanks for such a detailed reply. I've been using the concatenation
approach mostly, but it becomes a pain whenever some fields are added
etc- you have to redo the whole concantenation stuff. So I started
using queries and I think the parameter method will work for me.

Sunil Korah

"Terry Kreft" <te*********@mps.co.uk> wrote in message news:<G6********************@karoo.co.uk>...

Whoops,
Set loRst = .Execute(Parameters:=Array("1"))

should be
Set loRst = .Execute(Parameters:=Array(strID))

--
Terry Kreft
MVP Microsoft Access
"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:Hz********************@karoo.co.uk...

Hmm, ok, it's not exactly clear what you are doing.

If you are building SQL statements on the fly then you can just

concatenate

the variable value into the statement.

e.g.
dim strSQL as string

strSQL = "SELECT * from Customer " _
& "WHERE CustID = " & strID

If you're using a query and your using it to open a recordset then you can
use parameters and fill the parameters.

In DAO it would go like this

dim loDB as DAO.Database
Dim loQdf as DAO.Querydef
Dim loRst as DAO.Recordset

set loDb = Currentdb
Set loQdf = loDb.QueryDefs("YourQuery")
With loQdf
.Parameters(0) = strID
set loRst = .OpenRecordset
End with

In ADO
Dim loDB As ADODB.Connection
Dim loQdf As ADODB.Command
Dim loRst As ADODB.Recordset
Dim strID As String

strID = 1
Set loDB = Application.CurrentProject.Connection
Set loQdf = New ADODB.Command
With loQdf
Set .ActiveConnection = loDB
.CommandText = "YourQuery"
.CommandType = adCmdStoredProc
Set loRst = .Execute(Parameters:=Array("1"))
End With

If your using the query's as recordsources or rowsources then you could

just

create a generic function to return generic variables

Sou you could just have a function
Function QueryParam(Index as long) as Variant
Select Case Index
Case 0
QueryParam = strID
Case 1
QueryParam = lngID
Case 2
QueryParam = dblAnotherVar
' ...
End Select
End function

Or you could programatically change the SQL of the query.
--
Terry Kreft
MVP Microsoft Access
"Sunil Korah" <hb*****@indiatimes.com> wrote in message
news:72**************************@posting.google.c om...

I dont know whether this is a stupid question. But does access have
something akin to the pointer variable in C - or for that matter the
'&' operator in xbase.

I will explain my reason for wanting it too. I often use SQLs in which
the where condition gets the data from a variable. e.g

SELECT * from Customer
WHERE CustID = strID

strID being a public variable. But of course the above SQL does not
work as such, because SQL can't read that variable. So I use a
function like

Public Function GetID()
GetID=strID
End Function

and the actual SQL I use is

SELECT * from Customer
WHERE CustID = GetID()

The problem is, each time I have to write a function. Whereas in xBase
for example I would have a single function like this

Function GetVar
Parameters InVar
GetVar = &InVAr
Return GetVar

so that if I call the function as GetVar(strID) I would get the strID
value.

Any help would be appreciated
Sunil Korah