Dlookup with variable column

[bigmaxnosauce]

I have a table with fields repname, 1-24 I want to reference a column, i.e. 4, this number will be taken from a previous function.

Dim Mon As Double
Dim Yr As Double
Dim Enddate As Date
Dim Col As String
Dim OD As Currency
Enddate = Me.EndingDate
Mon = Month(Enddate)
Yr = Year(Enddate)
Col = Mon + Yr - 2003 - 2
OD = DLookup(""" & Col & """, "Overdraw", "RepName = '" & [RepName] & "'")
Me.Overdraw = OD

How can I get this to work, there is always an error in the dlookup with the Col portion.

Please Help me your my only hope.

 

[pdx_man]

Are your fieldnames then:
repname1, repname2, ... ,repname24?

or actually

1,2, ... ,24?

Or just other columns where you want to get the 4th column?

'Cause then you are going to have to use recordsets:

dim rst as Recordset

set rst = currentdb.openrecordset("Select * from Overdraw where repname = '" & [repname] & "'")
OD = rst.Fields(Col-1)

 

[WayneRyan]

big,

Not sure I'm understanding what Col is meant to be, but
syntactically:

String = Double + Double - Constant - Constant

That doesn't seem to make sense. If Col, is a string then
the following will work:

Col = Mon + Yr - 2003 - 2 
OD = DLookup(Col, "Overdraw", "RepName = '" & [RepName] & "'")

Wayne