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

 

[Anja]

Ok, I tried the following but it did not work. It asks me the Records_T
criteria in a message box...Records_T is a table..

=DLookUp(Min(Records_T!StuDate),[Records_T],Records_T!Supplier=Forms![Student
Manager]!SM_GradesBySupplier)

What is wrong with this statement?

Thanks,

Anja

 

[Anja]

Ok, I tried the following but it did not work. It asks me the Records_T
criteria in a message box...Records_T is a table..

=DLookUp(Min(Records_T!StuDate),[Records_T],Records_T!Supplier=Forms![Student
Manager]!SM_GradesBySupplier)

What is wrong with this statement?

Thanks,

Anja

Ok, a bit more progress...

It does not give any errors but the date that it is supposed to return
is formatted as a scientific expression!

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

 

[Anja]

Ok, I tried the following but it did not work. It asks me the Records_T
criteria in a message box...Records_T is a table..

=DLookUp(Min(Records_T!StuDate),[Records_T],Records_T!Supplier=Forms![Student
Manager]!SM_GradesBySupplier)

What is wrong with this statement?

Thanks,

Anja

Ok, a bit more progress...

It does not give any errors but the date that it is supposed to return
is formatted as a scientific expression!

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

yet a bit more!

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

Forms![Student Manager]!SM_GradesByStudent)))

always returns 00:00::05!!

Anja

 

[Van T. Dinh]

Check Access Help on the DMin() function ...

--
HTH
Van T. Dinh
MVP (Access)



news:[email protected]...

 

[Larry Linson]

Anja, if you feel compelled to post to multiple newsgroups, please
_crosspost_ so all the newsgroups show in the header, replies go to all, and
you don't get confused. I've answered your post, in detail, with code that
works in comp.databases.ms-access. I'm not going to copy that answer here...
you can visit that newsgroup to get the answer.

You _multiposted_ this, identical copies separately to at least two
newsgroups, and you apparently aren't looking in both of them for responses
to your question.

Larry Linson
Microsoft Access MVP