SQL Field name as a Variable

[ted.martin]

I have some SQL code that refers to the field name PUB1Val.

I need to refer to this field in my SQL statement as variable and have been trying


..... AND (([Invoice Details].Fields(strdescription))>0) etc ; where strDescription = PUB1Val but without success.

When I use .... AND (([Invoice Details].PUB1Val)>0) the SQL code runs fine.

It is just that as there are 6 PUB*Val fields and I need to select the field I need from a simple drop-down box of data.

Hope someone can shed some light. Thanks

 

[Kryst51]

Duplicate Post.

Origianal Here.

 

[tranchemontaigne]

build your SQL statement in a VBA module and things should work fine for you.

'declare variables
dim strSQL as string
dim strDescription as string

'define strSQL
strSQL = strsql & "AND "
strSQL = strsql & "("
strSQL = strsql & "([Invoice Details].Fields(" & strDescription & ")>0"
strSQL = strsql & ")"
strSQL = strsql & ";"

'run query
with docmd
.setwarnings false
.runsql strsql
.setwarnings true

end with

this should work because you are using VBA to write the variable inside of an string thta just happens to be an SQL statement

once the SQL statement is complete, you can pass this statement along to MS Jet. MS Jet (the DB engine) offers minimal native support for variable managment
________
Toyota Fj Cruiser

 

[Kryst51]

Hi tranchemontaigne,

Next time post on the OP, so things don't get confusing.