Date Query Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!!!!!
 
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.
 
SELECT * FROM myTable
WHERE [Date] = #04/30/2003#

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
That's giving me the following result:

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

This doesn't seem right...

Ofer Cohen said:
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


Paperback Writer said:
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!!!!!
Click to expand...
Click to expand...
 
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
Click to expand...

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:
Click to expand...

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

SELECT ID, CODE, [DATE], [INDICATOR]
FROM yourtable
WHERE [DATE] = (SELECT Max([DATE] FROM [yourtable] AS X WHERE X.
Code: 
= [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]
 
I don't think the <= is working right.

Ofer Cohen said:
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


Paperback Writer said:
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!!!!!
Click to expand...
Click to expand...
 
Try

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


--
Good Luck
BS"D


Paperback Writer said:
I don't think the <= is working right.

Ofer Cohen said:
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


Paperback Writer said:
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!!!!!
Click to expand...
Click to expand...
Click to expand...
 
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.


John Vinson said:
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
Click to expand...

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:
Click to expand...

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

SELECT ID, CODE, [DATE], [INDICATOR]
FROM yourtable
WHERE [DATE] = (SELECT Max([DATE] FROM [yourtable] AS X WHERE X.
Code: 
= [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]
Click to expand...
 
ALSO -- I'm wondering, is there a way to make this work with a typical MS
Access query?
 
ALSO -- I'm wondering, is there a way to make this work with a typical MS
Access query?
Click to expand...

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]
 
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.
 
Back
Top