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:
2. Attempt using "<=" and ">=" operators:
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:
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:
4. Giving something like this:
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.
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] & "#" )
Code:
DLookUp("[fldPSCNQNWindowNumber]" , "tblPSCNQNWindowNumbers" , "[fldPSCNQNWindowStartDate] <= #" & tblAims.[Planned End Date] & "# AND [fldPSCNQNWindowEndDate] => #" & tblAims.[Planned End Date] & "#" )
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] & "#" ))))
Code:
Format([Date],"yyyy/mm/dd")
Code:
IIF(ISNULL([Planned End Date] , "" , DLookup("[fldPSCNQNWindowNumber]","[tblPSCNQNWindowNumbers]","[Planned End Date] Between " & Format([fldPSCNQNWindowStartDate],"yyyy-mm-dd") & " And " & Format([fldPSCNQNWindowEndDate],"yyyy-mm-dd"))))
Thanks in advance, Mike.
Last edited: