Date Query Problem

[Guest]

I've tried a lot of different things, but so far no luck.

Here's a sample of what the database looks like.

ID CODE DATE INDICATOR
103 400 12/31/2001 1
103 400 9/30/2005 0
103 400 12/31/2005 1
103 500 7/1/2001 1
103 500 3/31/2003 0
103 500 5/31/2003 1
103 600 5/31/2001 1

I want to query on what these particular accounts looked like on a
particular date. If I query on 04/30/2003, my answer set should look like
this:

ID CODE DATE INDICATOR
103 400 12/31/2001 1
103 500 03/31/2003 0
103 600 5/31/2001 1


Please help!!!!!

 

[Guest]

Try something like

Select * From TableName Where [Date] = DMax("[Date]","[TableNAme]","[Date]
<= #" & [Please select a Date] & "#")

Note: If the field name is [Date] you better not use it, it's a build in
function in access that returns the current date, and it can cause error in
certain conditions.

 

[Graham R Seach]

SELECT * FROM myTable
WHERE [Date] = #04/30/2003#

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Guest]

That's giving me the following result:

ID CODE DATE INDICATOR
103 500 3/31/2003 0

This doesn't seem right...

Try something like

Select * From TableName Where [Date] = DMax("[Date]","[TableNAme]","[Date]
<= #" & [Please select a Date] & "#")

Note: If the field name is [Date] you better not use it, it's a build in
function in access that returns the current date, and it can cause error in
certain conditions.

--
Good Luck
BS"D

I've tried a lot of different things, but so far no luck.

Here's a sample of what the database looks like.

ID CODE DATE INDICATOR
103 400 12/31/2001 1
103 400 9/30/2005 0
103 400 12/31/2005 1
103 500 7/1/2001 1
103 500 3/31/2003 0
103 500 5/31/2003 1
103 600 5/31/2001 1

I want to query on what these particular accounts looked like on a
particular date. If I query on 04/30/2003, my answer set should look like
this:

ID CODE DATE INDICATOR
103 400 12/31/2001 1
103 500 03/31/2003 0
103 600 5/31/2001 1


Please help!!!!!

 

[John Vinson]

I've tried a lot of different things, but so far no luck.

Here's a sample of what the database looks like.

ID CODE DATE INDICATOR
103 400 12/31/2001 1
103 400 9/30/2005 0
103 400 12/31/2005 1
103 500 7/1/2001 1
103 500 3/31/2003 0
103 500 5/31/2003 1
103 600 5/31/2001 1

What's the meaning of ID? Is it a link to some other table?

I want to query on what these particular accounts looked like on a
particular date. If I query on 04/30/2003, my answer set should look like
this:

So you want the INDICATOR and ID for the most recent date prior to
your query date, for this particular value of CODE: ok... you'll need
a Subquery:

ID CODE DATE INDICATOR
103 400 12/31/2001 1
103 500 03/31/2003 0
103 600 5/31/2001 1

SELECT ID, CODE, [DATE], [INDICATOR]
FROM yourtable
WHERE [DATE] = (SELECT Max([DATE] FROM [yourtable] AS X WHERE X.

= [yourtable].[CODE] AND X.[DATE] <= [yourtable].[DATE]);

This ignores the ID, since I have no idea how or if it is involved in
finding the right record.

Note that DATE is a very dangerous fieldname and should be changed.
It's a reserved word for the builtin Date() function.

John W. Vinson[MVP]

 

[Guest]

I don't think the <= is working right.

Try something like

Select * From TableName Where [Date] = DMax("[Date]","[TableNAme]","[Date]
<= #" & [Please select a Date] & "#")

Note: If the field name is [Date] you better not use it, it's a build in
function in access that returns the current date, and it can cause error in
certain conditions.

--
Good Luck
BS"D

I've tried a lot of different things, but so far no luck.

Here's a sample of what the database looks like.

ID CODE DATE INDICATOR
103 400 12/31/2001 1
103 400 9/30/2005 0
103 400 12/31/2005 1
103 500 7/1/2001 1
103 500 3/31/2003 0
103 500 5/31/2003 1
103 600 5/31/2001 1

I want to query on what these particular accounts looked like on a
particular date. If I query on 04/30/2003, my answer set should look like
this:

ID CODE DATE INDICATOR
103 400 12/31/2001 1
103 500 03/31/2003 0
103 600 5/31/2001 1


Please help!!!!!

 

[Guest]

Try

Select * From TableName Where [Date] = DMax("[Date]","[TableNAme]","[Date]
<= " & Format([Please select a Date], "\#mm\/dd\/yyyy\#"))


--
Good Luck
BS"D

I don't think the <= is working right.

Try something like

Select * From TableName Where [Date] = DMax("[Date]","[TableNAme]","[Date]
<= #" & [Please select a Date] & "#")

Note: If the field name is [Date] you better not use it, it's a build in
function in access that returns the current date, and it can cause error in
certain conditions.

--
Good Luck
BS"D

I've tried a lot of different things, but so far no luck.

Here's a sample of what the database looks like.

ID CODE DATE INDICATOR
103 400 12/31/2001 1
103 400 9/30/2005 0
103 400 12/31/2005 1
103 500 7/1/2001 1
103 500 3/31/2003 0
103 500 5/31/2003 1
103 600 5/31/2001 1

I want to query on what these particular accounts looked like on a
particular date. If I query on 04/30/2003, my answer set should look like
this:

ID CODE DATE INDICATOR
103 400 12/31/2001 1
103 500 03/31/2003 0
103 600 5/31/2001 1


Please help!!!!!

 

[Guest]

The "ID" is just an account marker. There will be others that will be 104,
105, 106, etc... Some accounts will have more than one marker.

You are right -- I need all those fields to output.

I've tried a lot of different things, but so far no luck.

Here's a sample of what the database looks like.

ID CODE DATE INDICATOR
103 400 12/31/2001 1
103 400 9/30/2005 0
103 400 12/31/2005 1
103 500 7/1/2001 1
103 500 3/31/2003 0
103 500 5/31/2003 1
103 600 5/31/2001 1

What's the meaning of ID? Is it a link to some other table?

I want to query on what these particular accounts looked like on a
particular date. If I query on 04/30/2003, my answer set should look like
this:

So you want the INDICATOR and ID for the most recent date prior to
your query date, for this particular value of CODE: ok... you'll need
a Subquery:

ID CODE DATE INDICATOR
103 400 12/31/2001 1
103 500 03/31/2003 0
103 600 5/31/2001 1

SELECT ID, CODE, [DATE], [INDICATOR]
FROM yourtable
WHERE [DATE] = (SELECT Max([DATE] FROM [yourtable] AS X WHERE X.

= [yourtable].[CODE] AND X.[DATE] <= [yourtable].[DATE]);

This ignores the ID, since I have no idea how or if it is involved in
finding the right record.

Note that DATE is a very dangerous fieldname and should be changed.
It's a reserved word for the builtin Date() function.

John W. Vinson[MVP]
[/QUOTE]

 

[Guest]

ALSO -- I'm wondering, is there a way to make this work with a typical MS
Access query?

 

[John Vinson]

ALSO -- I'm wondering, is there a way to make this work with a typical MS
Access query?

ummm...

Everything anyone has suggested IS a typical MS Access Query.

A Query is its SQL. The query grid is NOTHING except a tool (somewhat
limited at that) to construct SQL.

If you wish, post any of these suggested SQL strings into a new
query's SQL window and then go back to Design view to see how it looks
in the grid.

John W. Vinson[MVP]

 

[Guest]

What I'm saying is that none of the SQL suggestions made here are working.
Typically, when I'm given SQL advice to plug into an MS Access database, the
luck I have with it is mixed at best. Advice that is a pure MS Access
solution typically works best.

I've tried cascading queries, max dates, etc., but without any luck.

I don't think the subtle nuance of my request is being satisfied by the
well-thought out, and appreciated, responses.