Date Query

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
 
J

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
 
B

Bryan Hughes

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

= 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
 
J

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
 
B

Bryan Hughes

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?

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date Problem 1
Current Date in Query 12
Append Query with Date 1
Still stumped - can't get a date (range). 6
Query Does Not Pick Up Data 5
Date query 1
Top Ten Date Query 1
Better way to Group Ages? 3

Top