sql statement as a control source in Access reports

[Anja]

Hi everyone,

I am trying to use the expression builder to create input to a control
in an Access report.

I have a table called Records and I want to select the minimum date for
a record where the student = studentID..

The sql is pretty simple: "Select MIN(RecDate) from records_T where
studentID = 1".

Now, I have been trying to get this statement to work with the
expression builder, but no go!!

Is it possible to get this working someho with the expression builder
or supplying the SQL directly to the control source property??

Thanks,
Anja

[Larry Linson]

SQL cannot be used as Control Source; it can be used as Record Source for a
Form or Report, and a Field in the Record Source used as Control Source. A
Control Source may also be an expression, a constant, or a Function. The
DMIN Function may provide the information you want... I'd say chances are
slim to none that you actually want to specify the student id as a constant,
so you need to determine how you are going to obtain it... you may well need
to use VBA code to implement what you want.

If you could explain what it is that you have and what you are trying to
accomplish, not how you are trying to accomplish it (e.g., "use the
expression builder to create a control source"), perhaps someone could be
more helpful.

Larry Linson
Microsoft Access MVP
"Anja" <an*******@googlemail.comwrote in message
news:11**********************@73g2000cwn.googlegro ups.com...

Hi everyone,

I am trying to use the expression builder to create input to a control
in an Access report.

I have a table called Records and I want to select the minimum date for
a record where the student = studentID..

The sql is pretty simple: "Select MIN(RecDate) from records_T where
studentID = 1".

Now, I have been trying to get this statement to work with the
expression builder, but no go!!

Is it possible to get this working someho with the expression builder
or supplying the SQL directly to the control source property??

Thanks,
Anja

[Anja]

Larry Linson wrote:

SQL cannot be used as Control Source; it can be used as Record Source for a
Form or Report, and a Field in the Record Source used as Control Source. A
Control Source may also be an expression, a constant, or a Function. The
DMIN Function may provide the information you want... I'd say chances are
slim to none that you actually want to specify the student id as a constant,
so you need to determine how you are going to obtain it... you may well need
to use VBA code to implement what you want.

If you could explain what it is that you have and what you are trying to
accomplish, not how you are trying to accomplish it (e.g., "use the
expression builder to create a control source"), perhaps someone could be
more helpful.

Larry Linson
Microsoft Access MVP
"Anja" <an*******@googlemail.comwrote in message
news:11**********************@73g2000cwn.googlegro ups.com...

Hi everyone,

I am trying to use the expression builder to create input to a control
in an Access report.

I have a table called Records and I want to select the minimum date for
a record where the student = studentID..

The sql is pretty simple: "Select MIN(RecDate) from records_T where
studentID = 1".

Now, I have been trying to get this statement to work with the
expression builder, but no go!!

Is it possible to get this working someho with the expression builder
or supplying the SQL directly to the control source property??

Thanks,
Anja

Thanks for the reply. Well, I tried using the DLookup function and it
does not give me any errors. However, the date field that it is
supposed to return is formatted as some scientific expression!

So, my statement is:

=CStr(DLookUp(Min("[RecDate]"),"[Records_T]","[Student]=" &
Forms![Student Manager]!MM_GradesByStudent))

[Anja]

Anja wrote:

Larry Linson wrote:

SQL cannot be used as Control Source; it can be used as Record Source for a
Form or Report, and a Field in the Record Source used as Control Source. A
Control Source may also be an expression, a constant, or a Function. The
DMIN Function may provide the information you want... I'd say chances are
slim to none that you actually want to specify the student id as a constant,
so you need to determine how you are going to obtain it... you may well need
to use VBA code to implement what you want.

If you could explain what it is that you have and what you are trying to
accomplish, not how you are trying to accomplish it (e.g., "use the
expression builder to create a control source"), perhaps someone could be
more helpful.

Larry Linson
Microsoft Access MVP
"Anja" <an*******@googlemail.comwrote in message
news:11**********************@73g2000cwn.googlegro ups.com...

Hi everyone,
>
I am trying to use the expression builder to create input to a control
in an Access report.
>
I have a table called Records and I want to select the minimum date for
a record where the student = studentID..
>
The sql is pretty simple: "Select MIN(RecDate) from records_T where
studentID = 1".
>
Now, I have been trying to get this statement to work with the
expression builder, but no go!!
>
Is it possible to get this working someho with the expression builder
or supplying the SQL directly to the control source property??
>
Thanks,
Anja
>

Thanks for the reply. Well, I tried using the DLookup function and it
does not give me any errors. However, the date field that it is
supposed to return is formatted as some scientific expression!

So, my statement is:

=CStr(DLookUp(Min("[RecDate]"),"[Records_T]","[Student]=" &
Forms![Student Manager]!MM_GradesByStudent))

A bit more progress:

yet a bit more!

=CStr(CDate((DLookUp(Min("[StuDate]"),"[Records_T]","[Student]=" &

Forms![Student Manager]!SM_GradesByStudent)))

always returns 00:00::05!!

Anja

[Larry Linson]

I'm not sure what I wrote that was unclear, but I do not see the DMin
function that I suggested in either of your followups.

The following Control Source works nicely to return the earliest order for
the customer selected in "cboCustomerID" on a Form whose RecordSource is the
Orders table from the Northwind sample database. The CustomerID in the
Orders table is a text Field, which is why the value must be in quotes in
the DMin statement.

=DMin("OrderDate","Orders","CustomerID = """ & [cboCustomerID] & """")

The DMin domain aggregate function has the purpose of finding and returning
the minimum value of a Field in a Domain -- see the Help. And if this fills
your needs, you don't need to explain what it is that you are trying to
accomplish, so we might suggest alternative approaches.

Larry Linson
Microsoft Access MVP
"Anja" <an*******@googlemail.comwrote in message
news:11*********************@n67g2000cwd.googlegro ups.com...

>
Anja wrote:

>Larry Linson wrote:

SQL cannot be used as Control Source; it can be used as Record Source
for a
Form or Report, and a Field in the Record Source used as Control
Source. A
Control Source may also be an expression, a constant, or a Function.
The
DMIN Function may provide the information you want... I'd say chances
are
slim to none that you actually want to specify the student id as a
constant,
so you need to determine how you are going to obtain it... you may well
need
to use VBA code to implement what you want.

If you could explain what it is that you have and what you are trying
to
accomplish, not how you are trying to accomplish it (e.g., "use the
expression builder to create a control source"), perhaps someone could
be
more helpful.

Larry Linson
Microsoft Access MVP
"Anja" <an*******@googlemail.comwrote in message
news:11**********************@73g2000cwn.googlegro ups.com...
Hi everyone,

I am trying to use the expression builder to create input to a
control
in an Access report.

I have a table called Records and I want to select the minimum date
for
a record where the student = studentID..

The sql is pretty simple: "Select MIN(RecDate) from records_T where
studentID = 1".

Now, I have been trying to get this statement to work with the
expression builder, but no go!!

Is it possible to get this working someho with the expression builder
or supplying the SQL directly to the control source property??

Thanks,
Anja

Thanks for the reply. Well, I tried using the DLookup function and it
does not give me any errors. However, the date field that it is
supposed to return is formatted as some scientific expression!

So, my statement is:

=CStr(DLookUp(Min("[RecDate]"),"[Records_T]","[Student]=" &
Forms![Student Manager]!MM_GradesByStudent))

A bit more progress:

yet a bit more!

=CStr(CDate((DLookUp(Min("[StuDate]"),"[Records_T]","[Student]=" &

Forms![Student Manager]!SM_GradesByStudent)))

always returns 00:00::05!!

Anja