Need help on Dlookup within query

[cp1981]

Greetings.

Problem: Trying to use DLookUp in a query from a query

Info: I have two tables, tblAccount and tblCategory.
tblCategory refers to an average household "bill" (e.g. "gas", Water", "electric", etc)
tblAccount refers to a specific company
I have a relationship where tblCategory displays many Accounts.

I also have a query, qryAccountExtended.
It shows the following fields AccountID, Account, CategoryID, Category, Amount, DueDate, Income/Expense, IsRecurring (Yes/No)

Desired Result: To have a query that displays the recurring expense account, amount, due date.

Eventually I want to use this for a dashboard form that lets me know what recurring accounts that need to be paid and when.



If yall need more info, just let me know.

Thanks in advance.

Clint

[June7]

How is this requirement related to a DLookup expression?

What is preventing you from building the query?

[cp1981]

How is this requirement related to a DLookup expression?

If you are referring to my "desired result", then I believe in theory (non-expert talking here) I would be able to use this query along with others to develop a notification dashboard on what recurring accounts are not paid or that are due and what aren't.

What is preventing you from building the query?

Basically my inexperience. To be completely honest, Id like for someone to look at the schematics of my db thus far and see what Ive done....to see if its a solid/sound db. I get the results I want to see through other forms, tables, and my other query, but to my knowledge of their worthiness, Im living on hope.

My next question...Why are the St. Louis Cardinals so good? lol

[June7]

I still don't know what you want. How would a DLookup help produce desired result?

If you want to provide db for analysis, follow instructions at bottom of my post.

[cp1981]

Database141.zip

Hope it all works out.

[cp1981]

Hey! Figured it out...took me a lil while. I was WAY overthinking everything. Sorry for the trouble. Good day to yall...Im sure Ill be back for more questions lol

[cp1981]

Ok...sooooo I thought I had it. The expression I have doesnt work entirely, it returns the same account over and over as well as leave other fields filled with their information. I dont know if it's how the expression is written or possibly something going on with my tables and their relationships.

I have a query that runs my Accounts table. In my Accounts table, I have expense accounts and I have an IsRecurring field with a Yes/No box. Also within my Accounts table, I have PaymentDate, DueDate, Amount, and Category in my query. I have another table Category that has a one-to-many with ref. integrity between the CategoryID and the Category field of Accounts.

What I want the query to do is return all the accounts and their respective PaymentDate, DueDate, and Amount when IsRecurring is checked.

Heres what I have:

RecurringAccount: DLookUp("[Account]","tblAccount","[IsRecurring] = True")

[June7]

Then don't understand why you are using a DLookup expression in attempt to apply filter criteria. Just put criteria under the IsRecurring field of: True

There are no records in tblAccount or tblTransaction. Makes it a little difficult to test. I would have to create records.

[cp1981]

Wow...Im just....speechless. So easy. I knew, again, I was overthinking . Just curious, why wont Dlookup expression work? Is it because the query builder is designed that way? Much thanks to you June7. Wish I could borrow your expertise for about a week or so then give it back.

[June7]

DLookup makes no sense in this situation. You want records in tblAccount where IsRecurring = True - simple filter criteria. Has nothing to do with the Access query builder. It is the nature of SQL. Could type SQL statement in the SQL View but that can be frustrating. The Access query builder is a tool to help get SQL syntax correct.