DLookUp problem with cascading calculated fields in a query. (1 Viewer)

Drutski

Registered User.
Local time
Today, 15:54
Joined
Mar 19, 2013
Messages
12
Ok, so I'm having some trouble with a DLookUp function which is driving me insane. I have an input form which is based on 2 queries. The queries are called [Learner Details Query] and [Aims Query]. The tables are joined so that each learner record in the [tblLearnerDetails] can have multiple aim records in the [tblAims] table.

There is another table which lists window numbers with corresponding window start and end dates called [tblPSCNQNWindowNumbers].

I am trying to use a DLookUp to find for each record in the in the [tblAims] table the corresponding value in the [fldPSCNQNWindowNumber] field on the [tblPSCNQNWindowNumbers] table when the [Planned End Date] field on [tblAims] falls between the [fldPSCNQNWindowStartDate] and [fldPSCNQNWindowEndDate] on the [tblPSCNQNWindowNumbers] table.

Now I know this straight forward DLookUp example is everywhere and I shouldn't need to start a new thread. However, this problem gets a bit complicated down the line. So, at the moment I've got:

1. Attempt using "BETWEEN" and "AND" operators:

Code:
DLookup("[fldPSCNQNWindowNumber]" , "[tblPSCNQNWindowNumbers]" , "[Planned End Date] BETWEEN #" & [tblPSCNQNWindowNumbers]![fldPSCNQNWindowStartDate] & "# AND #" & [tblPSCNQNWindowNumbers]![fldPSCNQNWindowEndDate] & "#" )
2. Attempt using "<=" and ">=" operators:

Code:
DLookUp("[fldPSCNQNWindowNumber]" , "tblPSCNQNWindowNumbers" , "[fldPSCNQNWindowStartDate] <= #" & tblAims.[Planned End Date] & "# AND [fldPSCNQNWindowEndDate] => #" & tblAims.[Planned End Date] & "#" )
So neither of these approaches seem to work. I did have this step of the problem solved in the past but my old machine died and I didn't keep my notes after solving the problem once already (stupid I know).

3. Anyway, another step of the problem is that somtimes there are NULL values for [Planned End Date] so I remember last time I had to wrap the DlookUp in an IIF(ISNULL()) function. Giving me something like this:

Code:
IIF(ISNULL([Planned End Date] , "" , (DLookUp("[fldPSCNQNWindowNumber]" , "[tblPSCNQNWindowNumbers]" , "[Planned End Date] BETWEEN #" & [tblPSCNQNWindowNumbers]![fldPSCNQNWindowStartDate] & "# AND #" & [tblPSCNQNWindowNumbers]![fldPSCNQNWindowEndDate] & "#" ))))
So after doing some reading I also found out that SQL doesn't like the UK date format (dd/mm/yyyy) and that I should use a function to change the format of the date fields to US/ISO/ANSI. Fitting the following snippet somewhere into my code:

Code:
Format([Date],"yyyy/mm/dd")
4. Giving something like this:
Code:
IIF(ISNULL([Planned End Date] , "" , DLookup("[fldPSCNQNWindowNumber]","[tblPSCNQNWindowNumbers]","[Planned End Date] Between "  & Format([fldPSCNQNWindowStartDate],"yyyy-mm-dd") & " And " & Format([fldPSCNQNWindowEndDate],"yyyy-mm-dd"))))
5. Of course, I'm getting way ahead of myself as I haven't got the first little problem sorted yet. Bear with me. Now I have a realisation, as I'm trying to put all this together as the calculated field [Window Number] in the [Aims Query] subquery using the expression builder. I want to use the result of the calculated [Window Number] along with [Area] in a further DLookup to find the values of the field [PSCNumber]. This is as far as my progress got last time. I figure a calculated field can't use the result of a calculated field within the same query? Now I know that it's a bad idea to put calculated fields into tables but I was thinking that I should create the [Window Number] and [PSCNumber] fields in the [tblAims] table and run the calculations as after update event procedures in order to seperate the functions from the same query. Isn't the syntax different in the VBA code builder? This idea seems a messy workaround. Can someone help me with my code along the steps outlined and direct me towards the correct solution to this calculated fields on a query problem?

Thanks in advance, Mike.
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 15:54
Joined
Feb 19, 2013
Messages
16,553
Not sure if I can help but you might try losing the spaces between # and "

BETWEEN # "

Also as you note, dates described between #'s need to be in the american style so unless you are using this style you need to use the format function as "mmddyyyy", not "yyyy-mm-dd"
 

Drutski

Registered User.
Local time
Today, 15:54
Joined
Mar 19, 2013
Messages
12
Hi, thanks for your suggestion, I didn't have a chance to write proper reply the other day. I removed the spaces but haven't had a chance to test it out. For the Format() function I've used the ISO standard format which should work as well as the US one.

I'm going to do some more work on it now so I'll post back when I can.
 

boblarson

Smeghead
Local time
Today, 08:54
Joined
Jan 12, 2001
Messages
32,059
Your problem is that you are trying to refer to a table in a way that a table cannot be used. You cannot use

BETWEEN #" & [tblPSCNQNWindowNumbers]![fldPSCNQNWindowStartDate] & "# AND #" & [tblPSCNQNWindowNumbers]![fldPSCNQNWindowEndDate] & "#" )

You can refer to controls on FORMS like that

BETWEEN #" & [Forms]![MyFormNameHere]![fldPSCNQNWindowStartDate] & "# AND #" & [Forms]![MyFormNameHere]![fldPSCNQNWindowEndDate] & "#" )

or if the control is on the same form, or the field is on the same form,

BETWEEN #" & [fldPSCNQNWindowStartDate] & "# AND #" & [fldPSCNQNWindowEndDate] & "#" )
 

Drutski

Registered User.
Local time
Today, 15:54
Joined
Mar 19, 2013
Messages
12
Ahh, as you can probably tell I've been using Access for all of a couple of months being the most tech savy in my office the task has fallen to me to update our data systems from Excel. I have been using the expression builder to write my query and the full form control reference is what is given when selecting from the expression elements list.

Now using Bob's code gives me an 'Enter parameter value' dialogue for [fldPSCNQNWindowStartDate] and [fldPSCNQNWindowEndDate] and a subsequent syntax error. I think this was what led me to try and use a more explicit reference. I don't know if it is relevant but I am calculating this field on the Aims query underlying the Input form and not the form itself.

I'm going to put some more hours in and I'll update if I make any progress.

Thanks,

Mike.
 
Last edited:

Drutski

Registered User.
Local time
Today, 15:54
Joined
Mar 19, 2013
Messages
12
I've just read somewhere:

"All referenced fields and tables must also be present in the saved query.
Plus, each field should be precisely specified by a tablename. And
"external" values (e.g. a textbox on a form) must be concatenated into the
criteria expression."

I remember now that the last time I solved this problem I did it on the Input form but I want to run reports from the calculated fields and this is not possible to do from a form is I'm not mistaken? Anyway, I don't understand then how I am supposed to reference a value in another table when all of the fields have to be included in the query. I tried adding the [fldPSCNQNWindowStartDate] and [fldPSCNQNWindowEndDate] fields to the Aims query but of course the values of those fields for each Aim record don't make sense as they are determined by the [Planned End Date] field for which the 'relationship' as it were is supposed to be defined by the DLookUp. There is something I'm missing here.
 

Drutski

Registered User.
Local time
Today, 15:54
Joined
Mar 19, 2013
Messages
12
I have put the 3 fields: [fldPSCNQNWindowNumber], [fldPSCNQNWindowStartDate] and [fldPSCNQNWindowEndDate] from the [tblPSCNQNWindowNumbers] table into the query and put the following line into the query criteria of the [Planned End Date] field:

Code:
Between [fldPSCNQNWindowStartDate] And [fldPSCNQNWindowEndDate]

Now the query correctly returns the corresponding window numbers for the planned end dates but there are a couple of problems.

1. All records where the [Planned End Date] is null or are outside of the ranges included window dates are all excluded. This is as you would expect from using query criteria but is not the behavior I require. I just want all records displayed with the correct window numbers based on the planned end date and if the planned end is null then the window number is null.

2. This doesn't solve the next part of the problem with looking up the [PSCNumber] and [PSQNumber] based on the window number and the area. If I add the PSCN's and PSQN's to the query it causes an ambiguous joins error and if I delete the relationships the query seems to hang whilst processing.

It seems that DLookUp isn't designed to be used in queries.
 

boblarson

Smeghead
Local time
Today, 08:54
Joined
Jan 12, 2001
Messages
32,059
I have put the 3 fields: [fldPSCNQNWindowNumber], [fldPSCNQNWindowStartDate] and [fldPSCNQNWindowEndDate] from the [tblPSCNQNWindowNumbers] table into the query and put the following line into the query criteria of the [Planned End Date] field:

Code:
Between [fldPSCNQNWindowStartDate] And [fldPSCNQNWindowEndDate]

I must not be understanding why you are attempting to put TABLE FIELDS in as criteria. I kind of get what you have but not fully. Can you post a copy of the database with some fake data in the tables and explain what to look at? I think you are going about this wrong and so I need to see exactly what you are seeing.
It seems that DLookUp isn't designed to be used in queries.
And that is incorrect thinking. It can be used just fine in queries, forms, reports, macros and code. But it has limitations.
 

Drutski

Registered User.
Local time
Today, 15:54
Joined
Mar 19, 2013
Messages
12
Ok, I've cut a lot out to get it under 2Mb but enough of the relationships are still there to show my intent. I think this is an issue with how I've set up the relationships as the full relationship structure breaks my queries. I've been working with a stripped down structure but when I try to put it all back together the same 'ambiguous joins' error persists.
 

Attachments

  • Curriculum Matrix 12-13 (18-03-13) Full intended relationships.accdb
    1.1 MB · Views: 157

Drutski

Registered User.
Local time
Today, 15:54
Joined
Mar 19, 2013
Messages
12
This database is to be used to store all the data for a sports education provider. The [tblLearnerDetails] stores a record for each student (actually, each student can have more than one record if they leave and return for a new period of learning). Each student can be on multiple aims (qualifications) which is represented by the 1 to many relationship with the [tblAims] table. The data for each aim is stored in the [tblQualifications] table and relates to the [tblAims] table with a 1 to many relationship. Our company has a number of centres in different areas in which we operate. Each student will stay in a single centre during a period of learning. The data for the areas / centres is stored in the [tblAreas] table which relates to the [tblLearnerDetails] table with a 1 to many relationship.

The complicated part is with our archiving system which is in 2 parts. The PSCN (Progress Sports Compliance Number) and PSQN (Progress Sports Qualification Number) system. Each student's overall period of learning is represented by a compliance portfolio which is archived in the PSCN system, the details of which are stored on the [tblPSCNs] table. Within each period of learning a student will complete multiple aims which are represented by a qualification portfolio and are archived in the PSQN system, the details of which are stored on the [tblPSQNs] table. In order to reduce the number of archiving boxes down to a managable number, all the portfolios for an area within a time window are assigned the same PSCN. Likewise, all the Qualification portfolios for an area within a time window are assigned the same PSQN. The time windows are predefined into roughly 12 week blocks in the [tblPSCNQNWindowNumbers] table which relates to both the [tblPSCNs] and [tblPSQNs] in a 1 to many relationship. The [tblAreas] table also relates to both the [tblPSCNs] and [tblPSQNs] in a 1 to many relationship.

There are also a number of other tables related to the main [tblLearnerDetails] table used to reference validation lists for things such as gender, ethnicity, school names etc.

The keys:

Each learner's period of learning is represented by a [Learner ID] field which is primary key on the [tblLearnerDetails] table and foreign key on the [tblAims] table.
Each aim a learner is on is represented by an [Aim ID] field which is primary key on the [tblAims] table.
Each qualification is represented by a [fldQualificationLARACode] field which is primary key on the [tblQualifications] table, and foreign key on the [tblAims] and [tblPSQNs] tables.
Each area is represented by an [Area] field which is primary key on the [tblAreas] table, and foreign key on the [tblPSCNs] and [tblPSQNs] tables.
Each time window is represented by an [fldPSCNQNWindowNumber] field which is primary key on the [tblPSCNQNWindowNumbers] table, and foreign key on the [tblPSCNs] and [tblPSQNs] tables.
 

Drutski

Registered User.
Local time
Today, 15:54
Joined
Mar 19, 2013
Messages
12
Ahhhhh, queries of queries. Down the rabbit hole, this looks like the right direction...
 

Drutski

Registered User.
Local time
Today, 15:54
Joined
Mar 19, 2013
Messages
12
So I solved my problem with retrieving the data using a series of cascading queries of queries (subqueries?) I based my main input form and subform on those queries but now I am getting a 'This recordset is not updateable' error when trying to input into the form. I changed the recordset of both forms to 'Dynaset (inconsistent)' but I think the enforced referential integrity of my table relationships is locking the tables from being edited. From what I've read I think inconsistent updates are going to mess up my data anyway. I may have found a work around. Let's see if I can figure this one out. Anyway, I've got a busy couple of weeks ahead so it might take a while.
 

Users who are viewing this thread

Top Bottom