Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dates in SQL query won't match | Excel Programming | |||
How do I export dates into a query? | Excel Discussion (Misc queries) | |||
How do I use variable dates with a SQL Query | Excel Programming | |||
web query in VBA (format dates in url) | Excel Programming | |||
dates in query | Excel Worksheet Functions |