Date Query

[Bryan Hughes]

Hello,

I am trying to figure out the best way to create a query that will give the
oldest date in set of records.

my current query looks like this.

'Variables
dtmToday = Date
dtmEligiblity = DateAdd("m", -36, dtmToday)

mstrSQL = "SELECT CDID, FN, LN, MI, DOB "
mstrSQL = mstrSQL & "FROM tblClient "
mstrSQL = mstrSQL & "WHERE CFID ='" & strCFID & "' AND "
mstrSQL = mstrSQL & "DOB >=#" & dtmEligiblity & "#;"

This works fine if only one record matches this criteria, but how would I
get the oldest DOB if multiple records match this criteria?

-TFTH
Bryan

 

[John Vinson]

Hello,

I am trying to figure out the best way to create a query that will give the
oldest date in set of records.

my current query looks like this.

'Variables
dtmToday = Date
dtmEligiblity = DateAdd("m", -36, dtmToday)

mstrSQL = "SELECT CDID, FN, LN, MI, DOB "
mstrSQL = mstrSQL & "FROM tblClient "
mstrSQL = mstrSQL & "WHERE CFID ='" & strCFID & "' AND "
mstrSQL = mstrSQL & "DOB >=#" & dtmEligiblity & "#;"

This works fine if only one record matches this criteria, but how would I
get the oldest DOB if multiple records match this criteria?

-TFTH
Bryan

= DMin("[DOB]", "tblClient", "<criteria>")

As a criterion on DOB, where <criteria> indentifies the subset of
tblClient over which the minimum needs to be evaluated.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Bryan Hughes]

Thanks John,

How would I write this in a SQL string?

-TFTH
Bryan

Hello,

I am trying to figure out the best way to create a query that will give
the
oldest date in set of records.

my current query looks like this.

'Variables
dtmToday = Date
dtmEligiblity = DateAdd("m", -36, dtmToday)

mstrSQL = "SELECT CDID, FN, LN, MI, DOB "
mstrSQL = mstrSQL & "FROM tblClient "
mstrSQL = mstrSQL & "WHERE CFID ='" & strCFID & "' AND "
mstrSQL = mstrSQL & "DOB >=#" & dtmEligiblity & "#;"

This works fine if only one record matches this criteria, but how would I
get the oldest DOB if multiple records match this criteria?

-TFTH
Bryan

= DMin("[DOB]", "tblClient", "<criteria>")

As a criterion on DOB, where <criteria> indentifies the subset of
tblClient over which the minimum needs to be evaluated.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

Thanks John,

How would I write this in a SQL string?

mstrSQL = "SELECT CDID, FN, LN, MI, DOB " _
& "FROM tblClient " _
& "WHERE CFID ='" & strCFID & "' AND DOB = #" & " _
& DMin("[DOB]", "tblClient", "[DOB] <= #" & dtmEligiblity & "#") _
& "#;"


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Bryan Hughes]

Thanks John,

The moment After I posted the question, I figured it out. Momentary brain
cell loss I guess.

-TFTH
Bryan

Thanks John,

How would I write this in a SQL string?

mstrSQL = "SELECT CDID, FN, LN, MI, DOB " _
& "FROM tblClient " _
& "WHERE CFID ='" & strCFID & "' AND DOB = #" & " _
& DMin("[DOB]", "tblClient", "[DOB] <= #" & dtmEligiblity & "#") _
& "#;"


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps