Dlookup syntax problem

[El-d]

Hi all,

I'm trying to set a text box value from a table based on a combobox selection but keep getting a missing table error.

Me.ExpiryClose is the textbox
Me.WrittenOptions is combo
expirytable is a string variable
TGDec16 is my table

When I debug print expirytable it prints TGDec16

Me.ExpiryClose = DLookup("[Expiry]", " & expirytable & ", "[Company]= '" & Me.WrittenOptions & "'")

If i replace the variable, it works.

Me.ExpiryClose = DLookup("[Expiry]", "TGDec16", "[Company]= '" & Me.WrittenOptions & "'")

I'm no doubt missing some syntax but can't see what.
Any help appreciated.

El-d.

 

[MarkK]

You don't need these quotes and ampersands...

Me.ExpiryClose = DLookup("[Expiry]", " & expirytable & ", "[Company]= '" & Me.WrittenOptions & "'")

The parameter takes a string, so if the variable is a string, that's fine...

Me.ExpiryClose = DLookup("[Expiry]", expirytable, "[Company]= '" & Me.WrittenOptions & "'")

On another note though, having the date in the name of a table, and having to swap out table names, is almost certainly a design flaw in your Db. The date should always be data in a field. With a table called "TGDec16", you have put date data in the name of your object. Now you have to change tables to look at other data, and how do you aggregate statistics across a year if monthly data is isolated in its own table?

Merge all your data into one table, and add date fields to distinguish it, and then you can run a DLookup() that modifies the criteria, not the table name, and many other common database operations will be far, far simpler.

Hope this helps,

 

[El-d]

as Homer once said "Doh!"

Much appreciated.
And yes you are correct in pointing out that I don't need to write separate tables. It was a quarterly thing I was writing out data for but as I'm writing the date into the table, I can continue to write each quarter indefinitely as a single table and add the date to the query rather than writing a new table each quarter. Still trying to get my head around database mentality.
Thanks again.
El-d