Using "Where" in a query

  • Thread starter Thread starter Jer
  • Start date Start date
J

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

I really appreciate the help!
 
Sorry - I should have mentioned its telling me my possible error is an
invalid syntax?
--
Jer


Jer said:
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"])
Click to expand...

I really appreciate the help!
Click to expand...
 
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

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


Jer said:
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"])
Click to expand...

I really appreciate the help!
Click to expand...
Click to expand...
 
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


Jerry Whittle said:
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

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


Jer said:
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!
Click to expand...
Click to expand...
Click to expand...
 
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?
Click to expand...

Please post the complete SQL of the query you're using.
 
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

Jer said:
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


Jerry Whittle said:
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

Jer said:
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!
Click to expand...
Click to expand...
Click to expand...
 
No, it keeps telling me to check the subquery's syntax and enclose it in
parentheses. Sorry - Im lost.
--
Jer


Jerry Whittle said:
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

Jer said:
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


Jerry Whittle said:
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!
Click to expand...
Click to expand...
Click to expand...
 
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, 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#) ;
Click to expand...

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!
 
Back
Top