Variable as FieldName in DLookup

[Waffeltje]

Hi All,

I want to use a variable as FieldName in the DLookup function.

Normal DLookup:

DLookup("FieldName" , "TableName" , "Criteria= 'string'")

I want to use:

DLookup(variable, "TableName", "Criteria= 'string'")

Where variable is an integer, which represents the column number in the table.

Is this possible?

Thanks.

 

[vbaInet]

You can't refer to the index of the fields in a table that way in a DLookup and in fact your reason for referencing a field by index (or column number like you call it) and not by name raises suspicion that your table wasn't built properly and isn't normalised.

 

[gemma-the-husky]

You could open a recordset, and then use the syntax

Rst.fields(columncount)

I would prefer not to rely on the numerical column position, though.

Note that even if you have an normalised data table, with fields called, say, month1, month2, month3 etc - then you can refer to the field with a string, rather than the column number.

"Month" & monthno

 

[Waffeltje]

I'm using a normalised data table, I use it for refering to zones; so columns are numbered Zone 1, Zone 2,....., Zone N.

I'll try if "Zone" & zoneno works.

 

[Waffeltje]

Sorry for the bad explanation at first, thought that explanation would make it clearer, guess not...

 

[vbaInet]

Ok, it will work in that fashion:
DLookup("Zone" & varnumber, ... etc)

 

[gemma-the-husky]

I'm using a normalised data table, I use it for refering to zones; so columns are numbered Zone 1, Zone 2,....., Zone N.

I'll try if "Zone" & zoneno works.

that isn't really normalised, though.

every time you add a new zone, you will need to redesign your app! Either that, or there is an artificial limit to the number of zones.

 

[Galaxiom]

Normal DLookup:

DLookup("FieldName" , "TableName" , "Criteria= 'string'")

No actually. The first argument is an expression. A fieldname can be an expression but it is not limited to that.

I also agree that your table structure is not right.