SQL in VBA

[northy]

I have the following code as part of an SQL statement in VBA:

([PRS Project Details].[Project Ref])= 3

Can anyone tell me the syntax to replace the 3 with a variable eg. and integer called ID?

Thanks

 

[Chris RR]

Not sure I understand you, don't you just want to do:
Dim intID as integer
.
.
intID = 3
.
.
([PRS Project Details].[Project Ref])= intID

 

[northy]

When you do that...it brings up a message box prompting you to enter the value for the variable name you entered.

The variable is definitely defined and working because it is used elsewhere in my code.

I have done something similar in Excel queries which required this format:

`" & Variable & "`

in order to replace a hardcoded value with a variable, so it can definitely be done...just need to know what the syntax in SQL is.

 

[axa]

northy,

unfortunatly it is not possible to reference a vb variable directly from a query. however there is a simple work-around...

you can reference user-defined functions from inside a query, so you could create a function to act as a wrapper for the variable you wish to use in your query. the function must be stored in a standard vba module, and it must be public.

___________________________________

Dim intID As Integer

Public Function GetID() As Integer
    GetID = intID
End Function
___________________________________


you could then update your sql statment like so

([PRS Project Details].[Project Ref])= GetID()


and when you run your query, it will use the current value stored in intID for its criteria value.

Hope that helps

axa


[This message has been edited by axa (edited 08-23-2001).]

 

[northy]

Thanks...that's done it!

One thing though...the variable which stores the value I need is still in the function with the sql statement in it, so what's the best way to set the output of GetID() to be equal to this value?

 

[Drew]

easiest is probably to make intID public, the function will be able to return it then

HTH

Drew

 

[northy]

Sorry, that last question was a result of a long day.

Thanks for all the help...it's working just as I wanted it to now.