Using "Where" in a query

[Jer]

Can someone please tell me what is wrong with this query? It keeps bring me
back to the "where" part. I am looking to get the last record set (happens
to be in a date field) where that date field is greater than 01/01/2006. Any
suggestions?

DLast("MPumpDate","tblMPumpDate"["Where «Expr» [MPumpDate] «Expr»

#01/01/2009"])

I really appreciate the help!

 

[Jer]

Sorry - I should have mentioned its telling me my possible error is an
invalid syntax?
--
Jer

Can someone please tell me what is wrong with this query? It keeps bring me
back to the "where" part. I am looking to get the last record set (happens
to be in a date field) where that date field is greater than 01/01/2006. Any
suggestions?

DLast("MPumpDate","tblMPumpDate"["Where «Expr» [MPumpDate] «Expr»

#01/01/2009"])

I really appreciate the help!

 

[Jerry Whittle]

You're missing a trailing # after the date for one thing.

Maybe this will do what you want:

SELECT *
FROM tblMPumpDate
WHERE tblMPumpDate.MPumpDate
IN (SELECT Min(tblMPumpDate.MPumpDate)
FROM tblMPumpDate
WHERE [MPumpDate] >#01/01/2006#) ;
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder

Sorry - I should have mentioned its telling me my possible error is an
invalid syntax?
--
Jer

Can someone please tell me what is wrong with this query? It keeps bring me
back to the "where" part. I am looking to get the last record set (happens
to be in a date field) where that date field is greater than 01/01/2006. Any
suggestions?

DLast("MPumpDate","tblMPumpDate"["Where «Expr» [MPumpDate] «Expr»

#01/01/2009"])

I really appreciate the help!

 

[Jer]

Thank you. I used what you suggested and it came back and told me I needed
to check the subquery's syntax and enclose it in parentheses and takes back
to the word "select"? Whats missing?
--
Jer

You're missing a trailing # after the date for one thing.

Maybe this will do what you want:

SELECT *
FROM tblMPumpDate
WHERE tblMPumpDate.MPumpDate
IN (SELECT Min(tblMPumpDate.MPumpDate)
FROM tblMPumpDate
WHERE [MPumpDate] >#01/01/2006#) ;
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder

Sorry - I should have mentioned its telling me my possible error is an
invalid syntax?
--
Jer

Can someone please tell me what is wrong with this query? It keeps bring me
back to the "where" part. I am looking to get the last record set (happens
to be in a date field) where that date field is greater than 01/01/2006. Any
suggestions?

DLast("MPumpDate","tblMPumpDate"["Where «Expr» [MPumpDate] «Expr»
#01/01/2009"])

I really appreciate the help!

 

[John W. Vinson]

Thank you. I used what you suggested and it came back and told me I needed
to check the subquery's syntax and enclose it in parentheses and takes back
to the word "select"? Whats missing?

Please post the complete SQL of the query you're using.

 

[Jerry Whittle]

Does this part work in that it brings back the expected data?

SELECT Min(tblMPumpDate.MPumpDate)
FROM tblMPumpDate
WHERE [MPumpDate] >#01/01/2006# ;
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder

Thank you. I used what you suggested and it came back and told me I needed
to check the subquery's syntax and enclose it in parentheses and takes back
to the word "select"? Whats missing?
--
Jer

You're missing a trailing # after the date for one thing.

Maybe this will do what you want:

SELECT *
FROM tblMPumpDate
WHERE tblMPumpDate.MPumpDate
IN (SELECT Min(tblMPumpDate.MPumpDate)
FROM tblMPumpDate
WHERE [MPumpDate] >#01/01/2006#) ;
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder

Sorry - I should have mentioned its telling me my possible error is an
invalid syntax?
--
Jer


:

Can someone please tell me what is wrong with this query? It keeps bring me
back to the "where" part. I am looking to get the last record set (happens
to be in a date field) where that date field is greater than 01/01/2006. Any
suggestions?

DLast("MPumpDate","tblMPumpDate"["Where «Expr» [MPumpDate] «Expr»
#01/01/2009"])

I really appreciate the help!

 

[Jer]

No, it keeps telling me to check the subquery's syntax and enclose it in
parentheses. Sorry - Im lost.
--
Jer

Does this part work in that it brings back the expected data?

SELECT Min(tblMPumpDate.MPumpDate)
FROM tblMPumpDate
WHERE [MPumpDate] >#01/01/2006# ;
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder

Thank you. I used what you suggested and it came back and told me I needed
to check the subquery's syntax and enclose it in parentheses and takes back
to the word "select"? Whats missing?
--
Jer

You're missing a trailing # after the date for one thing.

Maybe this will do what you want:

SELECT *
FROM tblMPumpDate
WHERE tblMPumpDate.MPumpDate
IN (SELECT Min(tblMPumpDate.MPumpDate)
FROM tblMPumpDate
WHERE [MPumpDate] >#01/01/2006#) ;
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder

:

Sorry - I should have mentioned its telling me my possible error is an
invalid syntax?
--
Jer


:

Can someone please tell me what is wrong with this query? It keeps bring me
back to the "where" part. I am looking to get the last record set (happens
to be in a date field) where that date field is greater than 01/01/2006. Any
suggestions?

DLast("MPumpDate","tblMPumpDate"["Where «Expr» [MPumpDate] «Expr»
#01/01/2009"])

I really appreciate the help!

 

[Jer]

John, this is what Im trying to use, but I get errors -

SELECT *
FROM tblMPumpDate
WHERE tblMPumpDate.MPumpDate
IN (SELECT Min(tblMPumpDate.MPumpDate)
FROM tblMPumpDate
WHERE [MPumpDate] >#01/01/2006#) ;

 

[John W. Vinson]

John, this is what Im trying to use, but I get errors -

SELECT *
FROM tblMPumpDate
WHERE tblMPumpDate.MPumpDate
IN (SELECT Min(tblMPumpDate.MPumpDate)
FROM tblMPumpDate
WHERE [MPumpDate] >#01/01/2006#) ;

Since you're using the same table name in the main query as in the subquery,
you will need to assign an alias to one of them:

SELECT *
FROM tblMPumpDate
WHERE tblMPumpDate.MPumpDate
IN (SELECT Min(X.MPumpDate)
FROM tblMPumpDate AS X
WHERE X.[MPumpDate] >#01/01/2006#) ;


As written this will retrieve only those records for the first day of 2006
which has any data... I'm not sure that's what you want, but since you have
not given any indication of what you DO in fact want, it's hard to give
specific advice!