Academic Year date criteria in a query

[Noel]

Hi. I recently had good replies from Pavel and Cheryl
about a similar question to this but I now realise that my
question is more complicated than I thought. I want to
select records in a query based on the values in a date
field. The problem is twofold. I need to select records
where the values in the date field are between 1 September
in one year and 30 August the following year (i.e. roughly
the span of an academic year). Also, the first years value
needs to be taken from a table and is given by the result
of the following DLookup

=DLookU("[PlacementStartYear]","[tblPlacementStartYear]")

tblPlacementStartYear has just one record with just one
field so a WHERE statement is not needed here.

The users change the value of PlacementStartYear each
September and it is currently set to 2003. So if I can
build the DLookup into the queries criteria somehow, it
could be made to automatically work out the year values
for the current academic year (i.e. between the DLookup
value and DLookup plus one) and the query will not need to
be changed each year.

So, just to labour the point, if the query ran today it
would select records where the date field is between 1
September 2003 and 30 August 2004. If it was run next
April, it would select records where the date field was
between 1 September 2004 and 30 August 2005, because by
then the users would have changed the PlacementStartYear
value to 2004.

So in a crude way the query criteria for the date field
would look something like

Between 1 September (DLookup value) And 30 August (DLookup
value plus one)

How could I build a query criteria to do this? Thanks, Noel

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can use the DateSerial() function to get the correct date:

WHERE DateColumn Between
DateSerial(DLookUp("[PlacementStartYear]","[tblPlacementStartYear]") ,
9, 1) And
DateSerial(DLookUp("[PlacementStartYear]","[tblPlacementStartYear]") ,
8, 30)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQIbd+IechKqOuFEgEQKyvwCff9UisuJaO5X6UZeNckKX5P1eV+wAoIKx
4Xc7Dtt1ly58aH4sdZJGmV/o
=Z5eP
-----END PGP SIGNATURE-----

 

[Noel]

Thanks for this. Two further questions for you. First,
where would I put this code - in the criteria of a query
field? Is DateColumn the name of the date field? Second,
the code doesnt seem to calculate the end of the academic
year which is the current DLookup value plus one. How
would the code look with that aspect added? Would it work
if I added the +1 as follows? Or would this be a bad
syntax. Or does DateSerial do this anyway. Thanks again,
Noel

WHERE DateColumn Between

DateSerial(DLookUp

("[PlacementStartYear]","[tblPlacementStartYear]") ,

9, 1) And
DateSerial(DLookUp

("[PlacementStartYear]","[tblPlacementStartYear]"+1) ,

8, 30)

-----Original Message-----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can use the DateSerial() function to get the correct date:

WHERE DateColumn Between
DateSerial(DLookUp

("[PlacementStartYear]","[tblPlacementStartYear]") ,

9, 1) And
DateSerial(DLookUp

("[PlacementStartYear]","[tblPlacementStartYear]") ,

8, 30)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQIbd+IechKqOuFEgEQKyvwCff9UisuJaO5X6UZeNckKX5P1eV+ wAoIKx
4Xc7Dtt1ly58aH4sdZJGmV/o
=Z5eP
-----END PGP SIGNATURE-----

Hi. I recently had good replies from Pavel and Cheryl
about a similar question to this but I now realise that my
question is more complicated than I thought. I want to
select records in a query based on the values in a date
field. The problem is twofold. I need to select records
where the values in the date field are between 1 September
in one year and 30 August the following year (i.e. roughly
the span of an academic year). Also, the first years value
needs to be taken from a table and is given by the result
of the following DLookup

=DLookU ("[PlacementStartYear]","[tblPlacementStartYear]")

tblPlacementStartYear has just one record with just one
field so a WHERE statement is not needed here.

The users change the value of PlacementStartYear each
September and it is currently set to 2003. So if I can
build the DLookup into the queries criteria somehow, it
could be made to automatically work out the year values
for the current academic year (i.e. between the DLookup
value and DLookup plus one) and the query will not need to
be changed each year.

So, just to labour the point, if the query ran today it
would select records where the date field is between 1
September 2003 and 30 August 2004. If it was run next
April, it would select records where the date field was
between 1 September 2004 and 30 August 2005, because by
then the users would have changed the PlacementStartYear
value to 2004.

So in a crude way the query criteria for the date field
would look something like

Between 1 September (DLookup value) And 30 August (DLookup
value plus one)

How could I build a query criteria to do this? Thanks, Noel

.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you're using the Query-By-Example (QBE) grid you remove the word
"WHERE" & put the expression in the Criteria row.

I did forget to put in the + 1 on the final DateSerial() function, but
here is where you'd put it (all one line):

DateSerial(DLookUp("[PlacementStartYear]",
"[tblPlacementStartYear]") + 1, 8, 30)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQId8MIechKqOuFEgEQL3XwCgrDd2kt5QNPtoV6C6QbWm9x2Lvp0Anjub
NhEKasV/bvd2rv5jGLHRq6F5
=TIhv
-----END PGP SIGNATURE-----

Thanks for this. Two further questions for you. First,
where would I put this code - in the criteria of a query
field? Is DateColumn the name of the date field? Second,
the code doesnt seem to calculate the end of the academic
year which is the current DLookup value plus one. How
would the code look with that aspect added? Would it work
if I added the +1 as follows? Or would this be a bad
syntax. Or does DateSerial do this anyway. Thanks again,
Noel

WHERE DateColumn Between

DateSerial(DLookUp

("[PlacementStartYear]","[tblPlacementStartYear]") ,

9, 1) And
DateSerial(DLookUp

("[PlacementStartYear]","[tblPlacementStartYear]"+1) ,

8, 30)


-----Original Message-----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can use the DateSerial() function to get the correct
date:

WHERE DateColumn Between
DateSerial(DLookUp

("[PlacementStartYear]","[tblPlacementStartYear]") ,

9, 1) And
DateSerial(DLookUp

("[PlacementStartYear]","[tblPlacementStartYear]") ,

8, 30)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQIbd+IechKqOuFEgEQKyvwCff9UisuJaO5X6UZeNckKX5P1eV+
wAoIKx

4Xc7Dtt1ly58aH4sdZJGmV/o
=Z5eP
-----END PGP SIGNATURE-----


Noel wrote:

Hi. I recently had good replies from Pavel and Cheryl
about a similar question to this but I now realise that

my

question is more complicated than I thought. I want to
select records in a query based on the values in a date
field. The problem is twofold. I need to select records
where the values in the date field are between 1

September

in one year and 30 August the following year (i.e.

roughly

the span of an academic year). Also, the first years

value

needs to be taken from a table and is given by the

result

of the following DLookup

=DLookU

("[PlacementStartYear]","[tblPlacementStartYear]")

tblPlacementStartYear has just one record with just one
field so a WHERE statement is not needed here.

The users change the value of PlacementStartYear each
September and it is currently set to 2003. So if I can
build the DLookup into the queries criteria somehow, it
could be made to automatically work out the year values
for the current academic year (i.e. between the DLookup
value and DLookup plus one) and the query will not need

to

be changed each year.

So, just to labour the point, if the query ran today it
would select records where the date field is between 1
September 2003 and 30 August 2004. If it was run next
April, it would select records where the date field was
between 1 September 2004 and 30 August 2005, because by
then the users would have changed the

PlacementStartYear

value to 2004.

So in a crude way the query criteria for the date field
would look something like

Between 1 September (DLookup value) And 30 August

(DLookup

value plus one)

How could I build a query criteria to do this? Thanks,

 

[Noel]

Excellent. Thank you very much. Ill be able to try this
later on today and will let you know how I get on.
Cheers, Noel

-----Original Message-----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you're using the Query-By-Example (QBE) grid you remove the word
"WHERE" & put the expression in the Criteria row.

I did forget to put in the + 1 on the final DateSerial() function, but
here is where you'd put it (all one line):

DateSerial(DLookUp("[PlacementStartYear]",
"[tblPlacementStartYear]") + 1, 8, 30)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQId8MIechKqOuFEgEQL3XwCgrDd2kt5QNPtoV6C6QbWm9x2Lv p0Anjub
NhEKasV/bvd2rv5jGLHRq6F5
=TIhv
-----END PGP SIGNATURE-----

Thanks for this. Two further questions for you. First,
where would I put this code - in the criteria of a query
field? Is DateColumn the name of the date field? Second,
the code doesnt seem to calculate the end of the academic
year which is the current DLookup value plus one. How
would the code look with that aspect added? Would it work
if I added the +1 as follows? Or would this be a bad
syntax. Or does DateSerial do this anyway. Thanks again,
Noel

WHERE DateColumn Between

DateSerial(DLookUp

("[PlacementStartYear]","[tblPlacementStartYear]") ,

9, 1) And
DateSerial(DLookUp

("[PlacementStartYear]","[tblPlacementStartYear]"+1) ,

8, 30)


-----Original Message-----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can use the DateSerial() function to get the

correct

date:

WHERE DateColumn Between
DateSerial(DLookUp

("[PlacementStartYear]","[tblPlacementStartYear]") ,

9, 1) And
DateSerial(DLookUp

("[PlacementStartYear]","[tblPlacementStartYear]") ,

8, 30)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQIbd+IechKqOuFEgEQKyvwCff9UisuJaO5X6UZeNckKX5P1

eV+

wAoIKx

4Xc7Dtt1ly58aH4sdZJGmV/o
=Z5eP
-----END PGP SIGNATURE-----


Noel wrote:


Hi. I recently had good replies from Pavel and Cheryl
about a similar question to this but I now realise

that

my

question is more complicated than I thought. I want to
select records in a query based on the values in a date
field. The problem is twofold. I need to select records
where the values in the date field are between 1
September

in one year and 30 August the following year (i.e.
roughly

the span of an academic year). Also, the first years
value

needs to be taken from a table and is given by the
result

of the following DLookup

=DLookU
("[PlacementStartYear]","[tblPlacementStartYear]")

tblPlacementStartYear has just one record with just one
field so a WHERE statement is not needed here.

The users change the value of PlacementStartYear each
September and it is currently set to 2003. So if I can
build the DLookup into the queries criteria somehow, it
could be made to automatically work out the year values
for the current academic year (i.e. between the DLookup
value and DLookup plus one) and the query will not

need

to

be changed each year.

So, just to labour the point, if the query ran today it
would select records where the date field is between 1
September 2003 and 30 August 2004. If it was run next
April, it would select records where the date field was
between 1 September 2004 and 30 August 2005, because by
then the users would have changed the
PlacementStartYear

value to 2004.

So in a crude way the query criteria for the date field
would look something like

Between 1 September (DLookup value) And 30 August
(DLookup

value plus one)

How could I build a query criteria to do this?

Thanks,

.

 

[Noel]

Hi again. Ive just tried this and Im getting an Unknown
Function Name message. It could be the PC Im trying it on
(its not my usual one) but I thought Id check if Im doing
this correctly. Im dragging a date/time field called
TrainingInitial down into a queries design grid column
then Im putting the following code in the criteria field
for that column

"TrainingInitial" Between DateSerial(DLookUp
("[PlacementStartYear]","[tblPlacementStartYear]"),9,1)
And DateSerial(DLookUp
("[PlacementStartYear]","[tblPlacementStartYear]")+1,8,30)

Is that correct? Ive also tried it without
the "TrainingInitial" bit at the front but no go. Im
using Access 2003. Something Ive spotted - the DLookup
field is actually a text field. The reason for this is I
want the users to just enter the year, not a whole date.
My db uses DLookup to present this year in various forms
and reports and Ive also been able to do a DLookup+1
calculation without a problem. Ill try this later today
on my usual PC but can you spot where Im going wrong?
Thanks, Noel

-----Original Message-----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you're using the Query-By-Example (QBE) grid you remove the word
"WHERE" & put the expression in the Criteria row.

I did forget to put in the + 1 on the final DateSerial() function, but
here is where you'd put it (all one line):

DateSerial(DLookUp("[PlacementStartYear]",
"[tblPlacementStartYear]") + 1, 8, 30)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQId8MIechKqOuFEgEQL3XwCgrDd2kt5QNPtoV6C6QbWm9x2Lv p0Anjub
NhEKasV/bvd2rv5jGLHRq6F5
=TIhv
-----END PGP SIGNATURE-----

Thanks for this. Two further questions for you. First,
where would I put this code - in the criteria of a query
field? Is DateColumn the name of the date field? Second,
the code doesnt seem to calculate the end of the academic
year which is the current DLookup value plus one. How
would the code look with that aspect added? Would it work
if I added the +1 as follows? Or would this be a bad
syntax. Or does DateSerial do this anyway. Thanks again,
Noel

WHERE DateColumn Between

DateSerial(DLookUp

("[PlacementStartYear]","[tblPlacementStartYear]") ,

9, 1) And
DateSerial(DLookUp

("[PlacementStartYear]","[tblPlacementStartYear]"+1) ,

8, 30)


-----Original Message-----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can use the DateSerial() function to get the

correct

date:

WHERE DateColumn Between
DateSerial(DLookUp

("[PlacementStartYear]","[tblPlacementStartYear]") ,

9, 1) And
DateSerial(DLookUp

("[PlacementStartYear]","[tblPlacementStartYear]") ,

8, 30)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQIbd+IechKqOuFEgEQKyvwCff9UisuJaO5X6UZeNckKX5P1

eV+

wAoIKx

4Xc7Dtt1ly58aH4sdZJGmV/o
=Z5eP
-----END PGP SIGNATURE-----


Noel wrote:


Hi. I recently had good replies from Pavel and Cheryl
about a similar question to this but I now realise

that

my

question is more complicated than I thought. I want to
select records in a query based on the values in a date
field. The problem is twofold. I need to select records
where the values in the date field are between 1
September

in one year and 30 August the following year (i.e.
roughly

the span of an academic year). Also, the first years
value

needs to be taken from a table and is given by the
result

of the following DLookup

=DLookU
("[PlacementStartYear]","[tblPlacementStartYear]")

tblPlacementStartYear has just one record with just one
field so a WHERE statement is not needed here.

The users change the value of PlacementStartYear each
September and it is currently set to 2003. So if I can
build the DLookup into the queries criteria somehow, it
could be made to automatically work out the year values
for the current academic year (i.e. between the DLookup
value and DLookup plus one) and the query will not

need

to

be changed each year.

So, just to labour the point, if the query ran today it
would select records where the date field is between 1
September 2003 and 30 August 2004. If it was run next
April, it would select records where the date field was
between 1 September 2004 and 30 August 2005, because by
then the users would have changed the
PlacementStartYear

value to 2004.

So in a crude way the query criteria for the date field
would look something like

Between 1 September (DLookup value) And 30 August
(DLookup

value plus one)

How could I build a query criteria to do this?

Thanks,

.

 

[Noel]

Hi. Ive just discovered that on this PC Im getting an
Unknown Function Message elsewhere in the database.
Please hold off replying until I get to another PC later
today. Sorry for all the posts. Cheers, Noel

-----Original Message-----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you're using the Query-By-Example (QBE) grid you remove the word
"WHERE" & put the expression in the Criteria row.

I did forget to put in the + 1 on the final DateSerial() function, but
here is where you'd put it (all one line):

DateSerial(DLookUp("[PlacementStartYear]",
"[tblPlacementStartYear]") + 1, 8, 30)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQId8MIechKqOuFEgEQL3XwCgrDd2kt5QNPtoV6C6QbWm9x2Lv p0Anjub
NhEKasV/bvd2rv5jGLHRq6F5
=TIhv
-----END PGP SIGNATURE-----

Thanks for this. Two further questions for you. First,
where would I put this code - in the criteria of a query
field? Is DateColumn the name of the date field? Second,
the code doesnt seem to calculate the end of the academic
year which is the current DLookup value plus one. How
would the code look with that aspect added? Would it work
if I added the +1 as follows? Or would this be a bad
syntax. Or does DateSerial do this anyway. Thanks again,
Noel

WHERE DateColumn Between

DateSerial(DLookUp

("[PlacementStartYear]","[tblPlacementStartYear]") ,

9, 1) And
DateSerial(DLookUp

("[PlacementStartYear]","[tblPlacementStartYear]"+1) ,

8, 30)


-----Original Message-----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can use the DateSerial() function to get the

correct

date:

WHERE DateColumn Between
DateSerial(DLookUp

("[PlacementStartYear]","[tblPlacementStartYear]") ,

9, 1) And
DateSerial(DLookUp

("[PlacementStartYear]","[tblPlacementStartYear]") ,

8, 30)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQIbd+IechKqOuFEgEQKyvwCff9UisuJaO5X6UZeNckKX5P1

eV+

wAoIKx

4Xc7Dtt1ly58aH4sdZJGmV/o
=Z5eP
-----END PGP SIGNATURE-----


Noel wrote:


Hi. I recently had good replies from Pavel and Cheryl
about a similar question to this but I now realise

that

my

question is more complicated than I thought. I want to
select records in a query based on the values in a date
field. The problem is twofold. I need to select records
where the values in the date field are between 1
September

in one year and 30 August the following year (i.e.
roughly

the span of an academic year). Also, the first years
value

needs to be taken from a table and is given by the
result

of the following DLookup

=DLookU
("[PlacementStartYear]","[tblPlacementStartYear]")

tblPlacementStartYear has just one record with just one
field so a WHERE statement is not needed here.

The users change the value of PlacementStartYear each
September and it is currently set to 2003. So if I can
build the DLookup into the queries criteria somehow, it
could be made to automatically work out the year values
for the current academic year (i.e. between the DLookup
value and DLookup plus one) and the query will not

need

to

be changed each year.

So, just to labour the point, if the query ran today it
would select records where the date field is between 1
September 2003 and 30 August 2004. If it was run next
April, it would select records where the date field was
between 1 September 2004 and 30 August 2005, because by
then the users would have changed the
PlacementStartYear

value to 2004.

So in a crude way the query criteria for the date field
would look something like

Between 1 September (DLookup value) And 30 August
(DLookup

value plus one)

How could I build a query criteria to do this?

Thanks,

.

 

[Noel]

Hi. Have now got this to work OK. First PC obviously
needs a service pack. Thanks for the help - it works a
treat. Cheers, Noel

-----Original Message-----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you're using the Query-By-Example (QBE) grid you remove the word
"WHERE" & put the expression in the Criteria row.

I did forget to put in the + 1 on the final DateSerial() function, but
here is where you'd put it (all one line):

DateSerial(DLookUp("[PlacementStartYear]",
"[tblPlacementStartYear]") + 1, 8, 30)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQId8MIechKqOuFEgEQL3XwCgrDd2kt5QNPtoV6C6QbWm9x2Lv p0Anjub
NhEKasV/bvd2rv5jGLHRq6F5
=TIhv
-----END PGP SIGNATURE-----

Thanks for this. Two further questions for you. First,
where would I put this code - in the criteria of a query
field? Is DateColumn the name of the date field? Second,
the code doesnt seem to calculate the end of the academic
year which is the current DLookup value plus one. How
would the code look with that aspect added? Would it work
if I added the +1 as follows? Or would this be a bad
syntax. Or does DateSerial do this anyway. Thanks again,
Noel

WHERE DateColumn Between

DateSerial(DLookUp

("[PlacementStartYear]","[tblPlacementStartYear]") ,

9, 1) And
DateSerial(DLookUp

("[PlacementStartYear]","[tblPlacementStartYear]"+1) ,

8, 30)


-----Original Message-----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can use the DateSerial() function to get the

correct

date:

WHERE DateColumn Between
DateSerial(DLookUp

("[PlacementStartYear]","[tblPlacementStartYear]") ,

9, 1) And
DateSerial(DLookUp

("[PlacementStartYear]","[tblPlacementStartYear]") ,

8, 30)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQIbd+IechKqOuFEgEQKyvwCff9UisuJaO5X6UZeNckKX5P1

eV+

wAoIKx

4Xc7Dtt1ly58aH4sdZJGmV/o
=Z5eP
-----END PGP SIGNATURE-----


Noel wrote:


Hi. I recently had good replies from Pavel and Cheryl
about a similar question to this but I now realise

that

my

question is more complicated than I thought. I want to
select records in a query based on the values in a date
field. The problem is twofold. I need to select records
where the values in the date field are between 1
September

in one year and 30 August the following year (i.e.
roughly

the span of an academic year). Also, the first years
value

needs to be taken from a table and is given by the
result

of the following DLookup

=DLookU
("[PlacementStartYear]","[tblPlacementStartYear]")

tblPlacementStartYear has just one record with just one
field so a WHERE statement is not needed here.

The users change the value of PlacementStartYear each
September and it is currently set to 2003. So if I can
build the DLookup into the queries criteria somehow, it
could be made to automatically work out the year values
for the current academic year (i.e. between the DLookup
value and DLookup plus one) and the query will not

need

to

be changed each year.

So, just to labour the point, if the query ran today it
would select records where the date field is between 1
September 2003 and 30 August 2004. If it was run next
April, it would select records where the date field was
between 1 September 2004 and 30 August 2005, because by
then the users would have changed the
PlacementStartYear

value to 2004.

So in a crude way the query criteria for the date field
would look something like

Between 1 September (DLookup value) And 30 August
(DLookup

value plus one)

How could I build a query criteria to do this?

Thanks,

.