Dlookup In Query Not Working In Query

[rjgriffin46]

I am attempting to create a report of meals served in our Kids Feeding program that compares the number of meals served at each Site between 2 years. In addition to selecting what 2 years to compare, I would like to be able to select one the following options as to which records from each year to include:

Option 1: Both Years/all records – that’s what its doing now by default.
Option 2: Only matching (Sites that have records in both years).
Option 3: All 1st yr Site records and 2nd yr records for matching sites.
Option 4: All 2nd yr Site records and 1st yr records for matching sites.
Option 5: Only non-matching sites from each year (ie., Sites with records in either year 1 or 2 but not in both).

Note: Refer to the attached test database for the following:

I evaluated and tried several approaches, one being the subject of this post, using Dlookup in a query (Weekly Counts Dlookup Test Query), looking up from the site meal records table (Sites Weekly Counts) to another query, or in this example database a table (Prog1 Lookup). In the sample database attached, I am simply attempting to see if the DLookup expression works by having it return the value in the "Count Prog1" field in the Prog1 Lookup table, if/where the Site Names match. As you see in the database, this currently returns "#Error" for all records. In other iterations I've tried, it seemed to return the same value for every record. I did get DLookup to work on a form where it was only doing the look up from the current record on the form.

So I have a couple of questions I'd appreciate thoughts on:
1. Can DLookup function as I wish to use it in a query and if so what's wrong/needs to be changed?
2. If I just want to know if the Dlookup finds a match, how is that done using Dlookup - IFF ISNull?
3. Is there another, and perhaps better approach?

At present I am creating separate queries for the 5 options above by having the Sites Weekly Counts and Prog1 Lookup tables in the same query and altering the 3 join properties appropriately for each option. I have found some old but hopefully correct VBA code that modifies a Report's Data Record Source which I would then use to change the Record Source to the appropriate query for the option selected. The code can be found via the following link:

https://www.devhut.net/2011/03/09/ms-access-report-change-a-reports-recordsource/

Thoughts and recommendations will be appreciated.



Thanks

[CJ_London]

your dlookup should be like this


DLookUp("[Count Prog1]","[Prog1 Lookup]","[Site Weekly Counts_SITE NAME] = '" & [SITE NAME] & "'")

you currently have

DLookUp("[Count Prog1]","Prog1 Lookup","'[Site Weekly Counts_SITE NAME] = ' " & [SITE NAME] & " ' ")

strongly recommended not to have spaces in field names

using domain functions in queries works but it is not their intended purpose - they should be used as control sources in forms and reports and occasionally in code - because they are slow, not noticeable on small datasets, but will be on larger ones

dlookup returns null if there is nothing to be found. But to just find if there is a match, use dcount which returns 0 if no matches found. alternatively use a left join between the two tables

better approach is to just use queries

I, and many others will not visit sites outside of the forum for code/example databases etc for security reasons.

[isladogs]

First of all your Site Weekly Counts table is a spreadsheet layout & isn't suitable for a database
Tables should be long and narrow - lots of records & few fields.
The data in that table should be obtained using a crosstab query when necessary

Secondly, although Dlookups can be used in tables, yours failed because it contained errors
The correct code is:

InProg1: DLookUp("[Count Prog1]","[Prog1 Lookup]","[Site Weekly Counts_SITE NAME] = '" & [SITE NAME] & "'")

But its best to avoid Dlookups in queries as it needs to be run for each record which is inefficient

In this case the query can easily be rewritten to get the same result:

SELECT [Site Weekly Counts].[SITE NAME], [Site Weekly Counts].[Week No], [Site Weekly Counts].[Meal Type], [Site Weekly Counts].Approved, [Site Weekly Counts].[Week Start], [Prog1 Lookup].[Count Prog1]FROM [Prog1 Lookup] RIGHT JOIN [Site Weekly Counts] ON [Prog1 Lookup].[Site Weekly Counts_SITE NAME] = [Site Weekly Counts].[SITE NAME];

NOTE: I have used an outer (right) join as one of your site names has no 'Count Prog1' value

SITE NAME	Week No	Meal Type	Approved	Week Start	Count Prog1
AC Youth Enrichment Camp	1	Breakfast		26/06/2017
ACX Multicultural Center	1	Breakfast	400	25/06/2018	1
Cumberland County College	2	Lunch	55	02/07/2018	21

If you don't need those sites included, use an INNER join

SELECT [Site Weekly Counts].[SITE NAME], [Site Weekly Counts].[Week No], [Site Weekly Counts].[Meal Type], [Site Weekly Counts].Approved, [Site Weekly Counts].[Week Start], [Prog1 Lookup].[Count Prog1]
FROM [Prog1 Lookup] INNER JOIN [Site Weekly Counts] ON [Prog1 Lookup].[Site Weekly Counts_SITE NAME] = [Site Weekly Counts].[SITE NAME];

Thirdly, I STRONGLY recommend you use a simpler naming system for tables/fields/queries etc with no spaces - use 'CamelCase' instead
e.g. Site Weekly Counts_SITE NAME => SiteName

As for the main points of your post, I suggest having a form with combo boxes to allow users to select the required options.
These would then be combined to create SQL versions of queries e.g. for use in suitable reports
Happy to return to this later once you've had time to consider the above

See attached for modified & new queries

[isladogs]

Ajax:

I, and many others will not visit sites outside of the forum for code/example databases etc for security reasons.

Whilst I agree, the database was attached. The link was to an article in Daniel Pineault's DevHut site though when I tried it, the link was broken

[rjgriffin46]

I thank you both for your interest an information. I did correct the DLookup per Ajax's example and it worked perfectly. I also tried a Left Join query which seems to be the Unmatched Records query in the Wizard and that did accomplish identifying the unmatched Site Weekly record.

I do have a couple more questions:

1. Even if I use queries vs. DLookup, it seems to me that I still end up with a different final query for each option. Do you agree?
2. If this is the case, would I then still need a way to change the record source for the report based on the chosen option? And if so, how would you do that?
   
   Note: I've attached a Word doc with a copy of the code referenced in my original post. My apologies for the non-functioning link.

My report menu form does have an Option group with buttons for all the options. I've used this before successfully.

Thanks again!

[isladogs]

With careful planning you only need one report to cover all options

For example, in the example below there are many different options that can be chosen & used to create a chart
A printed report is also available
This is done using code rather than queries.
I build up the appropriate SQL string by concatenating the output from each combobox selected




Here's another example with several subreports showing different charts



HTH

[rjgriffin46]

Thanks Again. I am proceeding utilizing your information and guidance.