Could someone help with a quick dlookup problem?

[ChuckCasey]

DLookUp("[user_defined_field]","sqluser_user_def_fields_MS"," [account_number] = form![member_acct_no] & [sequence_no] = 10")

I'm trying to do a lookup of a user_defined_field in a table that has the same account number as entered on my form and a sequence number of 10. Can someone help me with the syntax? If I remove the sequence number part of the equation, I get the user_defined_field of the first record, so I think my statement works up to that point. I just can't figure out how to incorporate the sequence number. Many thanks.

 

[dhookom]

Try:
DLookUp("[user_defined_field]","sqluser_user_def_fields_MS"," [account_number] = form![member_acct_no] AND [sequence_no] = 10")



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ChuckCasey]

Thanks, but I still can't get it to work. It returns a value if I ask for either/or piece of information (member_acct_no or sequence_no), but not both.

 

[dhookom]

Wait a second. form![member_acct_no] references only a form and not a control. What's up with that?

If Account_Number is numeric and your form name is frmName, try:
DLookUp("[user_defined_field]","sqluser_user_def_fields_MS"," [account_number] = " & form!frmName![member_acct_no] & " AND [sequence_no] = 10")

If it is text try:
DLookUp("[user_defined_field]","sqluser_user_def_fields_MS"," [account_number] = """ & form!frmName![member_acct_no] & """ AND [sequence_no] = 10")




Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]