DLookup in Query

[Spott]

I'm trying to return all active training records in a query. Trainer's name needs to show up. Trainer is listed as emp_id number and I'm trying to reference their name to show up. If no trainer is listed the active record doesn't show up in my current query. I need those to show up too.

My attempt at DLookup is:
TrainerName: DLookUp("[Name]","[Active Employee Query]","[trn_emp2class]![trainer]"="[Active Employee Query]![emp_id]&quot

I've tried several variations so far, all the active records show up, but no trainer names are displayed. No error messages.

Thanks for any help!

 

[dterrie]

This is correct assuming the [emd_id] is numeric:

TrainerName: DLookUp("[Name]","Active Employee Query","[trn_emp2class].[trainer]=" & [emp_id])

If it's a string, this is correct:

TrainerName: DLookUp("[Name]","Active Employee Query","[trn_emp2class].[trainer]='" & [emp_id] & "'&quot

The name of the query is not used. However, if there are multiple tables in the query using the field name you are referencing, you'll need to use [tablename].[fieldname].

 

[Spott]

Thanks Dterrie, I'm still trying to figure the dlookups out. emp_id and trainer are both numeric. I'll try these and see what happens.

I also found a rtfm solution thanks to a coworker. add "Active Employee Query" a second time (since it is joined separately to pull the trainee's name) and a one way join "trainer" to "name". Editing the join relationship to choice 2, "All records from trn_emp2class and only records from Active Employee Query_1 where joined feilds are equal."

Guess those access classes I took weren't that helpful!