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
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:
DLookup("[fldPSCNQNWindowNumber]" , "[tblPSCNQNWindowNumbers]" , "[Planned End Date] BETWEEN #" &
[tblPSCNQNWindowNumbers]![fldPSCNQNWindowStartDate] & "# AND #" & [tblPSCNQNWindowNumbers]![fldPSCNQNWindowEndDate] & "#" )
2. Attempt using "=" operators:
DLookUp("[fldPSCNQNWindowNumber]" , "tblPSCNQNWindowNumbers" , "[fldPSCNQNWindowStartDate] #" & 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:
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
4. Giving something like this:
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.