Need help to create Subquery

[March]

Hi All,

I want to create query with sub query in ms access using vba. I want to get
max(Filed1) from table i have.

For example:

TB1
===

Field1 Field2 Field3 Field4
==== ===== ===== =====
4/3/08 ZZ ZA1 199
4/4/08 AA AA1 230
4/7/08 BB BB1 235
4/8/08 CC CC1 237


As the records on my TB1 for example have more 100000 records before 4/3/08.

Field1 (Date ) the missing one are holiday or Sat and Sun in the table.

My question is :

I want to get max(Filed1) from TB1 where Field1 < 4/7/08.

4/7/08 would be other.

To get the exactly date of Fir 4/4/08 if today is Mon 4/7/08.

Please give suggestion.


Thank you so much

March

 

[KARL DEWEY]

SELECT *
FROM TB1
WHERE Field1 >= (SELECT Max([Field1]) FROM TB1);

 

[KARL DEWEY]

Reread --
SELECT *, (SELECT Max([Field1]) FROM TB1) AS MaxOfField1
FROM TB1
WHERE Field1 < #04/07/2008#;
--
KARL DEWEY
Build a little - Test a little

SELECT *
FROM TB1
WHERE Field1 >= (SELECT Max([Field1]) FROM TB1);
--
KARL DEWEY
Build a little - Test a little

Hi All,

I want to create query with sub query in ms access using vba. I want to get
max(Filed1) from table i have.

For example:

TB1
===

Field1 Field2 Field3 Field4
==== ===== ===== =====
4/3/08 ZZ ZA1 199
4/4/08 AA AA1 230
4/7/08 BB BB1 235
4/8/08 CC CC1 237


As the records on my TB1 for example have more 100000 records before 4/3/08.

Field1 (Date ) the missing one are holiday or Sat and Sun in the table.

My question is :

I want to get max(Filed1) from TB1 where Field1 < 4/7/08.

4/7/08 would be other.

To get the exactly date of Fir 4/4/08 if today is Mon 4/7/08.

Please give suggestion.


Thank you so much

March