Query/Criteria No Results (1 Viewer)

[DudeSees]

Hello Everyone, Thanks for looking,...

My problem: I have two tables with a one to many relationship. When I add these two tables to a query and select a field from each of the tables; ie,.. Account (number) & Department (text) they appear fine in datasheet view.

Now Account is just a text field from one table while Department is a lookup field combo box in another table. These two tables are linked by the Account field and data entered in a subform is where the Department field is.

The minute I add criteria to the department field such as Like "a*" the datasheet view is empty. If I take the criteria off the I can see everything - no problem.

The exact opposite is true for account. I can add any account criteria and the datasheet view will return data based on just that account. If I add criteria to both it again returns no records in datasheet view?

Im stumped. Any help appreciated.

Thanks, DudeSees (not this problem for sure).

 

[pbaldy]

If it's a lookup field, odds are the actual field is a numeric key field. You'd want to join that table into the query so you can use the actual text field.

http://access.mvps.org/access/lookupfields.htm

 

[DudeSees]

Thank you brother, going to try just that. Appreciate the link too!

 

[plog]

Pictures are worth 1,000; data is worth 10,000 words. Provide some sample data from each table. Then, based on that sample data, post what you data want returned from your query. Be sure to include table and field names. Use this format for posting your data:


Table1NameHere
Field1NameHere, Field2NameHere, Field3NameHere
David, 12, 4/3/2008
Steve, 8, 5/16/2010
Amy, 2, 6/6/2007

 

[pbaldy]

Thank you brother, going to try just that. Appreciate the link too!

No problem; post back if you get stuck. Welcome to the site by the way!