WHERE statement

[Jer]

I've gotten this far. I have received no errors in my syntax or anything
else, yet it doesn't bring me the info Im looking for either. I have two
tables making one query. In the query I have a field which has the following
code in it. As stated before, Im looking to get the max date out of this
field/table, which could include many dates. The other table makes up the
rest of the data, just the date field is being pulled from the other table.
Again - any help is greatly appreciated.

(Select Max(tblMPumpDate.MPumpDate) FROM tblMPumpDate WHERE [MPumpDate]

 

[John W. Vinson]

I've gotten this far. I have received no errors in my syntax or anything
else, yet it doesn't bring me the info Im looking for either. I have two
tables making one query. In the query I have a field which has the following
code in it. As stated before, Im looking to get the max date out of this
field/table, which could include many dates. The other table makes up the
rest of the data, just the date field is being pulled from the other table.
Again - any help is greatly appreciated.

(Select Max(tblMPumpDate.MPumpDate) FROM tblMPumpDate WHERE [MPumpDate]

=#01/01/2006#)

That's exactly what this query should be returning: the maximum value of the
field MPumpDate that exists in table tblMPumpDate. Dates before 2006 will not
be considered.

In what way is it "not working"? Is it returning the wrong date, too many
records, no records, ....???

What data do you have in the table? Could you post a few sample rows?
What result do you want?
What result do you get?

 

[Jer]

The maximum value in some of the data could be as high as today's current
date. The field could contain one entry that may be 06/10/2007 or it could
contain several entries such as 06/10/2006, 12/15/2006, 04/05/2007,
09/12/2007 and 07/15/2008. In this sequence, I would like it to give me
07/15/2008. It should be pulling up about 2,500 records with the max date
from that field. It is showing me no records. I hope this helps.
--
Jer

I've gotten this far. I have received no errors in my syntax or anything
else, yet it doesn't bring me the info Im looking for either. I have two
tables making one query. In the query I have a field which has the following
code in it. As stated before, Im looking to get the max date out of this
field/table, which could include many dates. The other table makes up the
rest of the data, just the date field is being pulled from the other table.
Again - any help is greatly appreciated.

(Select Max(tblMPumpDate.MPumpDate) FROM tblMPumpDate WHERE [MPumpDate]

=#01/01/2006#)

That's exactly what this query should be returning: the maximum value of the
field MPumpDate that exists in table tblMPumpDate. Dates before 2006 will not
be considered.

In what way is it "not working"? Is it returning the wrong date, too many
records, no records, ....???

What data do you have in the table? Could you post a few sample rows?
What result do you want?
What result do you get?

 

[Jer]

Currently, it is pulling no records. The field could have one date in it,
such as 06/24/2007 or it could have as many as 5 dates (all different rows
within table, linked to other table via an "id" number), such as 04/05/2006,
11/14/2006, 02/19/2007, 09/04/2007 and 06/15/2008. In this case, I would be
looking for the only record to show is the one with the date of 06/15/2008.
I have about 2,500 records that should be pulled with this criteria, but none
of them are showing up.

Any suggestions?
--
Jer

I've gotten this far. I have received no errors in my syntax or anything
else, yet it doesn't bring me the info Im looking for either. I have two
tables making one query. In the query I have a field which has the following
code in it. As stated before, Im looking to get the max date out of this
field/table, which could include many dates. The other table makes up the
rest of the data, just the date field is being pulled from the other table.
Again - any help is greatly appreciated.

(Select Max(tblMPumpDate.MPumpDate) FROM tblMPumpDate WHERE [MPumpDate]

=#01/01/2006#)

That's exactly what this query should be returning: the maximum value of the
field MPumpDate that exists in table tblMPumpDate. Dates before 2006 will not
be considered.

In what way is it "not working"? Is it returning the wrong date, too many
records, no records, ....???

What data do you have in the table? Could you post a few sample rows?
What result do you want?
What result do you get?

 

[KARL DEWEY]

I have two tables making one query.
Post the complete query SQL and not just the subquery part.
--
KARL DEWEY
Build a little - Test a little

Currently, it is pulling no records. The field could have one date in it,
such as 06/24/2007 or it could have as many as 5 dates (all different rows
within table, linked to other table via an "id" number), such as 04/05/2006,
11/14/2006, 02/19/2007, 09/04/2007 and 06/15/2008. In this case, I would be
looking for the only record to show is the one with the date of 06/15/2008.
I have about 2,500 records that should be pulled with this criteria, but none
of them are showing up.

Any suggestions?
--
Jer

I've gotten this far. I have received no errors in my syntax or anything
else, yet it doesn't bring me the info Im looking for either. I have two
tables making one query. In the query I have a field which has the following
code in it. As stated before, Im looking to get the max date out of this
field/table, which could include many dates. The other table makes up the
rest of the data, just the date field is being pulled from the other table.
Again - any help is greatly appreciated.

(Select Max(tblMPumpDate.MPumpDate) FROM tblMPumpDate WHERE [MPumpDate]
=#01/01/2006#)

That's exactly what this query should be returning: the maximum value of the
field MPumpDate that exists in table tblMPumpDate. Dates before 2006 will not
be considered.

In what way is it "not working"? Is it returning the wrong date, too many
records, no records, ....???

What data do you have in the table? Could you post a few sample rows?
What result do you want?
What result do you get?

 

[Jer]

I am not good with SQL. I have all records from tblMaintain, where
MMainProgram from tblMaintain is yes and MMailingYear from tblMaintain is
2009 then (Select Max(tblMPumpDate.MPumpDate) FROM tblMPumpDate WHERE
[MPumpDate] >=#01/01/2006#)
--
Jer

Post the complete query SQL and not just the subquery part.
--
KARL DEWEY
Build a little - Test a little

Currently, it is pulling no records. The field could have one date in it,
such as 06/24/2007 or it could have as many as 5 dates (all different rows
within table, linked to other table via an "id" number), such as 04/05/2006,
11/14/2006, 02/19/2007, 09/04/2007 and 06/15/2008. In this case, I would be
looking for the only record to show is the one with the date of 06/15/2008.
I have about 2,500 records that should be pulled with this criteria, but none
of them are showing up.

Any suggestions?
--
Jer

I've gotten this far. I have received no errors in my syntax or anything
else, yet it doesn't bring me the info Im looking for either. I have two
tables making one query. In the query I have a field which has the following
code in it. As stated before, Im looking to get the max date out of this
field/table, which could include many dates. The other table makes up the
rest of the data, just the date field is being pulled from the other table.
Again - any help is greatly appreciated.

(Select Max(tblMPumpDate.MPumpDate) FROM tblMPumpDate WHERE [MPumpDate]
=#01/01/2006#)

That's exactly what this query should be returning: the maximum value of the
field MPumpDate that exists in table tblMPumpDate. Dates before 2006 will not
be considered.

In what way is it "not working"? Is it returning the wrong date, too many
records, no records, ....???

What data do you have in the table? Could you post a few sample rows?
What result do you want?
What result do you get?

 

[John W. Vinson]

I am not good with SQL.

You don't need to read it - just post it. Open the Query in design view;
select View... SQL from the menu; and copy and paste the entire SQL string to
a message here.