Dlookup in Query 1

[molly]

I am having trouble with my dlookup that is in my ToDoQuery.
it is supposed to look to the table Account.

AccountName2: DLookUp("AccountName","tblAccount","AccountID = " & [AccountID])

I get a #Err and a syntax message.

I have AccountID in both the tblAccount and the tblToDo.

Can you advise? thanks Molly

 

[dhookom]

Is there a reason why you don't just add tblAccount to your query and join the AccountID fields?

Is AccountID text or numeric?

Duane
Hook'D on Access
MS Access MVP

 

[molly]

Hi Duane. Good question by you. Maybe i have you on a goose chase.

I now see why i could not get tblAccount to work inside my toDo query. meaning connecting tblToDo and TblAccount in the ToDo query.

My brother used the same AccountID of "P" for prospect 5 differet customer records. so this was confusing the query.
it was not a unique situation.

i think i need to rethink the frmToDo.
i was making it a stand alone form to gather customer todo's and anything from "Get milk" to "call Duane".
so that request from my brother meant that i could not make a subform and link it to using my autoCusID field to link the master and child field.

so i have to rethink this.
after i get this working in my head, i will send something to your nice charity. you caught on fast about the error of having multiple records with the same AccountID. Again, i use an automatic auto field with a unique name. whereas AccountID is a text field of input.

i will rethink this.
thanks so much
glen
glen

 

[molly]

Duane - i figured this out. you were right.
i will not revised my tblTodo. i will take out the text field AccountID and put in AutoCusID as a text field.
and use a combo box answer to populate autoCusID in the tblToDo. and then link the autoCusID in tblTodo and tblAccount inside the query. viola.

just as you thought. no dlookup needed.

i will send moolah to a charity for your thoughtfulness.

Molly