Dlookup function

[molletica]

Hi Friends,
I am just learning Access. could anyone help me with the D lookup function?

I have two tables one has Month Name and Month Number ( MonthT) - Ex Jan = 1, Feb = 2 like that
One more table contains actual data when the Month name is one of the fields (ActualT).
I am trying to run a query to bring Month No against Month name in Table Actuals.

could anyone help in how to do that?

 

[theDBguy]

Hi. Welcome to AWF!

You shouldn't need DLookup(). You should be able to join the two tables in your query.

 

[Minty]

You can extract the month number from a date field using inbuilt functions in Access - no real need of the table;

In a query -
MnthNo : DatePart("m",[YourDateField])
MntName : MonthName(DatePart("m",[YourDateField]))

 

[molletica]

Actually, I have other tables wherein I have to look up values in the Query. This is one example.
example of other fields:- I Have Account number in the Actual data table and the corresponding Description in another table

 

[Minty]

The best route would be to join the tables in the query. Same goes for the other "Lookup tables" - something like

 

[Pat Hartman]

The join is ALWAYS more efficient than the domain function and the larger the recordset, the more difference you will see in the execution speed. Every domain function runs its own query. So if you have three domain functions in your query and your query returns 10,000 rows which is not a lot by most standards, the domain functions will be running 30,000 queries with all the overhead that entails of calculating an execution plan for each one each time and then finding (hopefully with an index) the lookup data. Just be careful with the Join. You might need a left join if the foreign key can be null or the target table may not contain all values for the FK and you want the rows even without the lookup value to be returned.

Action queries are ALWAYS more efficient than VBA code loops although the difference won't be as dramatic as a query that includes three domain functions. The database engine code to perform an update is optimized to the extreme and is always faster than VBA which is an interpreted language so it is actually compiling as it executes RBAR