Query logic concerning dates
 

Given a field in a database has dates

In this example lets assume the data base the following dates

1/18/2008
9/3/2008
12/30/2008
1/27/2009

I would like the ability to enter a date and have the query pull the right
information explained below....

Any date starting with 1/18/2008 through 9/2/2008 would pull information
from the first record.

Likewise any date after 1/27/2009 will use the information from that record.
That is until another record is added.

Likewise any date before 1/18/2008 will not provide any information.

Essentially the query performs a vlookup and selects the date closest
(without exceeding) to the date inputted by the user and selects the
information from that particular record? Can this be done without inputting
those key dates in a spreadsheet or in the macro query?

Query logic concerning dates
 

Since your dates will always be changing I think you would want a macro so
you won't have to setup a new query every time you change the date. You can
easily create a macro automatically by using the Macro Recorder an manually
performing the query. Then make minor changes to the recorded macro make the
dates flexible.

"Brad" wrote:

Given a field in a database has dates

In this example lets assume the data base the following dates

1/18/2008
9/3/2008
12/30/2008
1/27/2009

I would like the ability to enter a date and have the query pull the right
information explained below....

Any date starting with 1/18/2008 through 9/2/2008 would pull information
from the first record.

Likewise any date after 1/27/2009 will use the information from that record.
That is until another record is added.

Likewise any date before 1/18/2008 will not provide any information.

Essentially the query performs a vlookup and selects the date closest
(without exceeding) to the date inputted by the user and selects the
information from that particular record? Can this be done without inputting
those key dates in a spreadsheet or in the macro query?

Query logic concerning dates
 

How would you set up the macro if you don't know the dates? I don't want any
maintenance for the dates.


"Joel" wrote:

Since your dates will always be changing I think you would want a macro so
you won't have to setup a new query every time you change the date. You can
easily create a macro automatically by using the Macro Recorder an manually
performing the query. Then make minor changes to the recorded macro make the
dates flexible.

"Brad" wrote:

Given a field in a database has dates

In this example lets assume the data base the following dates

1/18/2008
9/3/2008
12/30/2008
1/27/2009

I would like the ability to enter a date and have the query pull the right
information explained below....

Any date starting with 1/18/2008 through 9/2/2008 would pull information
from the first record.

Likewise any date after 1/27/2009 will use the information from that record.
That is until another record is added.

Likewise any date before 1/18/2008 will not provide any information.

Essentially the query performs a vlookup and selects the date closest
(without exceeding) to the date inputted by the user and selects the
information from that particular record? Can this be done without inputting
those key dates in a spreadsheet or in the macro query?

Query logic concerning dates
 

Either use todays date, a fixed date (ie 1st of current month, or Monday of
the current week), or a cell on the worksheet.

"Brad" wrote:

How would you set up the macro if you don't know the dates? I don't want any
maintenance for the dates.


"Joel" wrote:

Since your dates will always be changing I think you would want a macro so
you won't have to setup a new query every time you change the date. You can
easily create a macro automatically by using the Macro Recorder an manually
performing the query. Then make minor changes to the recorded macro make the
dates flexible.

"Brad" wrote:

Given a field in a database has dates

In this example lets assume the data base the following dates

1/18/2008
9/3/2008
12/30/2008
1/27/2009

I would like the ability to enter a date and have the query pull the right
information explained below....

Any date starting with 1/18/2008 through 9/2/2008 would pull information
from the first record.

Likewise any date after 1/27/2009 will use the information from that record.
That is until another record is added.

Likewise any date before 1/18/2008 will not provide any information.

Essentially the query performs a vlookup and selects the date closest
(without exceeding) to the date inputted by the user and selects the
information from that particular record? Can this be done without inputting
those key dates in a spreadsheet or in the macro query?

Query logic concerning dates
 

My problem is how do I identify the row of the database to use?

Using the information originally provided
If the user input 10/15/2008 as the date the information is needed I want to
access the 9/3/2008. If I select the records less than 10/15/2008 - I will
get 1/18/2008 and 9/3/2008 information - but I don't want the 1/18/2008
data.....

If this was in Excel a simple vlookup would solve this, not sure if there is
anything like that in a query....

Thanks for your help so far....



"Joel" wrote:

Either use todays date, a fixed date (ie 1st of current month, or Monday of
the current week), or a cell on the worksheet.

"Brad" wrote:

How would you set up the macro if you don't know the dates? I don't want any
maintenance for the dates.


"Joel" wrote:

Since your dates will always be changing I think you would want a macro so
you won't have to setup a new query every time you change the date. You can
easily create a macro automatically by using the Macro Recorder an manually
performing the query. Then make minor changes to the recorded macro make the
dates flexible.

"Brad" wrote:

Given a field in a database has dates

In this example lets assume the data base the following dates

1/18/2008
9/3/2008
12/30/2008
1/27/2009

I would like the ability to enter a date and have the query pull the right
information explained below....

Any date starting with 1/18/2008 through 9/2/2008 would pull information
from the first record.

Likewise any date after 1/27/2009 will use the information from that record.
That is until another record is added.

Likewise any date before 1/18/2008 will not provide any information.

Essentially the query performs a vlookup and selects the date closest
(without exceeding) to the date inputted by the user and selects the
information from that particular record? Can this be done without inputting
those key dates in a spreadsheet or in the macro query?

Query logic concerning dates
 

The command text part of the query is really a SQL command. You really want
to perfrom a filter, sort, and then get the LAST RECORD (not all the
records). I'm not an expert on SQL. A query has a limit number ofn the
number of items to return. You can set this number to 1 and sort backwards
so the last item is the first record.

"Brad" wrote:

My problem is how do I identify the row of the database to use?

Using the information originally provided
If the user input 10/15/2008 as the date the information is needed I want to
access the 9/3/2008. If I select the records less than 10/15/2008 - I will
get 1/18/2008 and 9/3/2008 information - but I don't want the 1/18/2008
data.....

If this was in Excel a simple vlookup would solve this, not sure if there is
anything like that in a query....

Thanks for your help so far....



"Joel" wrote:

Either use todays date, a fixed date (ie 1st of current month, or Monday of
the current week), or a cell on the worksheet.

"Brad" wrote:

How would you set up the macro if you don't know the dates? I don't want any
maintenance for the dates.


"Joel" wrote:

Since your dates will always be changing I think you would want a macro so
you won't have to setup a new query every time you change the date. You can
easily create a macro automatically by using the Macro Recorder an manually
performing the query. Then make minor changes to the recorded macro make the
dates flexible.

"Brad" wrote:

Given a field in a database has dates

In this example lets assume the data base the following dates

1/18/2008
9/3/2008
12/30/2008
1/27/2009

I would like the ability to enter a date and have the query pull the right
information explained below....

Any date starting with 1/18/2008 through 9/2/2008 would pull information
from the first record.

Likewise any date after 1/27/2009 will use the information from that record.
That is until another record is added.

Likewise any date before 1/18/2008 will not provide any information.

Essentially the query performs a vlookup and selects the date closest
(without exceeding) to the date inputted by the user and selects the
information from that particular record? Can this be done without inputting
those key dates in a spreadsheet or in the macro query?

Query logic concerning dates
 

On Thu, 29 Jan 2009 06:16:00 -0800, Joel
wrote:

The command text part of the query is really a SQL command. You really want
to perfrom a filter, sort, and then get the LAST RECORD (not all the
records). I'm not an expert on SQL. A query has a limit number ofn the
number of items to return. You can set this number to 1 and sort backwards
so the last item is the first record.


Maybe this:

SELECT TOP 1 * FROM MyTable WHERE MyDate<=? ORDER BY MyDate DESC;
--
Dick