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 wish from a simple drop-down box of data.

Hope someone can shed some light. Thanks

 

[MarkK]

To include the value of a variable, not it's name, that variable must be evaluated outside the string, so using the example you posted...

  dim field as string
  field = "PUB3Val"
  ...
  ..." AND (InvoiceDetails." & field & " > 0 ) ... "
  - not -
  ..." AND (InvoiceDetails.field > 0 ) ... "

 

[Galaxiom]

Although certainly not always the case, sql that needs a variable as the fieldname can often be avoided with a different table structure.

 

[MarkK]

No, no, G. Consider MyDLookup()

[FONT="Verdana"][SIZE="1"]function MyDLookup(field as string, table as string, optional criteria as string) as variant
  dim rst as dao.recordset

  if criteria <> "" then criteria = "WHERE " & criteria

  set rst = currentdb.openrecordset( _
    "SELECT " & field & " " & _
    "FROM " & table & " " & _
    criteria)
  with rst
    if not .eof then MyDLookup = .fields(0).value
    .close
  end with
end function[/SIZE][/FONT]

The field, table and where clause are variable! This says nothing about table design.
Variable field name can be exactly what you want.
Cheers,

 

[Galaxiom]

As I said lagbolt, it is "certainly not always the case" and I was thinking specifically of search and lookup functions as an exception. However generally speaking, a well designed table structure should not require sql with variable fields (most of the time).

We see many cases here where Excel oriented developers are trying to query across several fields when the data should have been structured differently. (How many times have posters asked how to overcome the 256 field limit?)

In the case of the OP I have little doubt that the six fields (PUB*Val) would be better held as a table with SomeForeignKey, PUBType and PubValue fields. This would allow them to query the data without the need for code using where clauses like:
WHERE PUBType = [Forms]![formname].[controlname] AND PUBValue = {whatever}

 

[MarkK]

Ok, I see what you're getting at. The PUB1Val, PUB2Val, ..., PUB6Val as mutiple fields in a single record.
Agreed. I was looking at Ted's post as more of syntactical problem, but you make a very good point that this looks like weak table design.
Thanks for clarifying.
Cheers,