To extract the contents of a table field

[mud2]

How do I get the contents of a field in a table? I pass the table name, and several field names as variables...Arg1, arg2.....
I open a DBO db, set a record set, and
If ![field1] = arg2...do something...works

But none of the following work;
If arg1 = arg2 doesn't,
If !arg1 ....nor
If ![arg1]....nor
If 'Arg1'..... nor
If "arg1"...., nor
If !'arg1'... nor
If !(arg1)....ad naseum!

So Argx on the right works, but argx (or y) on the left doesn't!
If this were C or C++ I'd use *argx...but VBA's default IS by reference...
So, How Do I extract and use the contents of a variable field in a table?

[Norie]

Shouldn't you refer to the field via the recordset?

Something along these lines perhaps?


Msgbox rst.Fields(arg1)

[mud2]

My thanks to Norie! Now it looks simple! I was about to either hard wire the info, or to try to write a shell script in C to do the job! Probably would have squandered another few years!

[mud2]

Dlookup requires all kinds of hyroglyphics to put its arguments into a form it recognizes...and it seems you cannot use variables for some/all of its arguments. With Norie's help I've written a very simple replacement...it WILL accept variables.

Function OGlookup(Field_to_return, Table, Field_to_test, test_Value) As Variant
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim This_Result As Variant
Set db = CurrentDb()
Set rst = db.OpenRecordset(Table)
With rst
.MoveLast
Do Until .BOF
If .Fields(Field_to_test) = test_Value Then
This_Result = .Fields(Field_to_return)
Exit Do
End If
.MovePrevious
Loop
.Close
End With
If (This_Result = " ") Then This_Result = Null
If (This_Result = "") Then This_Result = Null
If (IsNull(This_Result)) Then This_Result = Null
OGlookup = This_Result
End Function

[Norie]

You can use DLookUp with variables, you just need to get the syntax correct.