HOW TO MAKE DLOOKUP expr "TABLE FIELD" VARIABLE

[ENGiPhone]

DEAR ALL

THIS IS MY FIRS POST HERE IN THIS VERY NICE FORUM

MY ISSUE IS I WANT TO HAVE DLOOKUP FUNCTION BUT WITH VARIABLE expr OR TABLE FIELD

MEANS

MY CODE IS

Private Sub Combo3_AfterUpdate()
Dim i As Integer
For i = 1 To 36
Me.Controls("TEXT" & i).Value = Me.Controls("TXT" & i).Value * DLookup([& i], "dftbl2", " APPARATUS = '" & Me.Combo3.Value & "'")
Next i
End Sub

I HAVE COMBOBOX NAMED Combo3

NEED TO BE AFTER UPDATE ITS VALUE GOES TO DLOOKUP AS CRITERIA *THIS PART IS DONE PERFECTLY
THEN THE VALUE OF THE STEP OR COUNTER "i" TO BE IN THE DLOOKUP FUNCTION AS expr (TABLE FIELD)

MY APPLICATION CONTAIN:

TABLE: dftbl2
TABLE COLUMNS NAMES ARE: 1,2,3,.....,36
THE TABLE HAS ID COLUMN WITH PRIMARY KEY (FIRST COLUMN)
THE SECOND COLUMN NAMED: APPARATUS

THE ISSUE IS WHEN I UPDATE Combo3 "AFTER UPDATE"
I FOUND ERROR SAY'S THAT ACCESS CAN'T FIND THE FIELD "|1" .....

MEANS EVERY THING WORKED CORRECT AND i STARTED WITH THE FIRST VALUE 1 BUT I DON'T NOW WHY ACCESS PUT THIS"|" BEFORE 1 ("|1")

I SEARCHED ALOT IN INTERNET BUT WITH NO PINIFIT
IF ANYBODY CAN HELP ME PLEASE REPLY AS SOON AS POSSIBLE

THANK YOU ALL

 

[The_Doc_Man]

Whoa! First, this isn't a text phone. Turn off CAPS lock. Your shift key works just as well and produced better (and more easily readable) posts. Watch in the body of my reply to see how much better it looks when you use all-caps only for emphasis. And we try to offer help to folks who are inexperienced and need help, but we don't have time to be totally diplomatic if we see something so totally wrong as to defy description.

Second point. When Access reports that it can't find field "|1" it is telling you that the name of the field was not properly specified. I.e. your field name parameter wasn't there so Access doesn't know what to call it. You have misinterpreted the message because you have, sorry to have to tell you this, totally useless field names. (See next comment.) That message is not a reference to your field named "1" - it is a reference to the first field of the implied SQL statement that is generated "behind the scenes" of the DLookup. If you did that with explicit SQL and the first field was a reasonable name but the 2nd wasn't, that message would reference "|2". The "|" is something that occurs only when SQL returns a bad result.

IF you know the name of the field for a DLookup, you CAN do substitution on the fly, but not the way you did it. Look at this example.

    MyFieldName = "XYZ"
    MyVar = DLookup( "[" & MyFieldName & "]", "MyTable", "[MyKeyField]=" & Cstr(MyKeyNumber) )

Third thing: Having numbers as field names is so totally wrong on so many fronts that I really don't know where to begin. However, I'll say this: It isn't going to work very well for you. The first time you use a function that needs a field name, you never know how that function will react to the number. A field name can be mixed alphameric, like A1, but all-numeric fields? I've NEVER run across that in over 25 years of using MS Access.

Here is a simple-minded example using your column "names" to show you why. And this is straight-up SQL standard syntax, so I'm not blowing smoke at you.

If you execute this SQL statement from your table,

    SELECT 1, 2, 3 FROM dftbl2 ;

Your result will be one row containing the values 1, 2, and 3 (respectively) for each record that exists in dftbl2, REGARDLESS of what was actually in the first, second, and third fields.

Names have to be at least mixed alphameric if not totally alphabetic because otherwise the SQL parser will see a numeric constant and return THAT for a value in the recordset that results from executing the query.