Date Query

  • Thread starter Thread starter Bryan Hughes
  • Start date Start date
B

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
 
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
Click to expand...

= 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
 
Thanks John,

How would I write this in a SQL string?

-TFTH
Bryan


John Vinson said:
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
Click to expand...

= 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
Click to expand...
 
Thanks John,

How would I write this in a SQL string?
Click to expand...

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
 
Thanks John,

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

-TFTH
Bryan


John Vinson said:
Thanks John,

How would I write this in a SQL string?
Click to expand...

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
Click to expand...
 
Back
Top