SQL Statement (DoCmd) 2

[Squibbles]

Hi,

I am writing a prog. in VBA and i have a vairable (x), and I want to lookup in another table (table_y) to find the relavent group noumber and assign this number to another variable, for later use.

Table y has two fields:

field 1 - x
field 2 - Group No.

My SQL knowledge is non-existent.

I want to avoid opening up table y as a recordset and cycling through the recods, so i thought SQL would be the way to go.

Can anyone help please?

Many Thanks,

Squibs

 

[lupins46]

If x is a number....

othervariable = dlookup("groupnofield","table_y", "Somefield = " & x)

 

[Squibbles]

Excellent thank you very much.

I assume it will work even if x was a string?


Many thanks,

Squibs

 

[jrbarnett]

No, if X is a string then you need to delimit the x field with apostrophes, such as:

othervariable = dlookup("groupnofield","table_y", "Somefield = '" & x & "'")

John

 

[Squibbles]

marvelous,
this tek forum is fantastic!
Thank you very much,

Squibs