Re: Scroll through records and get match

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Something like this?

SELECT T.[Period], T.[Week]
FROM Tbl_Dates AS T
WHERE T.W_C_Date =
(SELECT Max(Q.W_C_Date)
FROM Tbl_Dates AS Q
WHERE Q.W_C_Date <= Date());

--

Ken Snell
<MS ACCESS MVP>

"Andrew" <Andrew@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BDB7293F-24FB-490A-8929-413F49185778@xxxxxxxxxxxxxxxx
Hi Ken,

Thanks for replying.

Hi Ken,

Thanks for replying.

I used the Animal table as an example to try and get a simple answer.
Then
with the reply I was going to adapt it to my requirements.

The real requirement is to get the period from the following table names
Tbl_Dates:

W_C_Date Year Season Month Period Week
02/07/06 2006 W July 12 1
09/07/06 2006 W July 12 2
16/07/06 2006 W July 12 3
23/07/06 2006 W July 12 4
30/07/06 2006 S August 1 1
06/08/06 2006 S August 1 2
13/08/06 2006 S August 1 3
20/08/06 2006 S August 1 4

I want the period (12) and week (4) if today's date [date()] is 25/07/06.
I'm sorry if I confused you by saying to scroll through.

I'm wanting to pass the value of Period and Week through to some more SQL
(I
have this part working!!!!!)

Thanks Ken
--
Andrew


"Ken Snell (MVP)" wrote:

The query's SQL statement to do this would be

SELECT Animal FROM Tbl_Table
WHERE W_C_Date = Date();

Not sure under which scenarios you want to loop through the data? Can you
explain a bit more about your setup and what you want to do?

--

Ken Snell
<MS ACCESS MVP>

"Andrew" <Andrew@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0DF5517E-C204-4F15-A937-17F9C39DE37A@xxxxxxxxxxxxxxxx
Hi,

I have a table (Tbl_Table) in the following format:

W_C_Date Animal
02/07/06 Cat
09/07/06 Dog
16/07/06 Fox
etc
etc
etc

Can someone please provide me with the SQL to loop through and find the
Animal based on todays date. If todays date is 02/07/06 the answer is
Cat.
If todays date is 03/07/06 the answer is Cat. If todays date is
09/07/06
the
answer is Dog.

Thanks in advance





--
Andrew
040706





.

Relevant Pages

  • Re: Scroll through records and get match
    ... A RunSQL statemnt requires an argument consisting of an SQL statement. ... "Ken Snell " wrote: ... <MS ACCESS MVP> ... Animal based on todays date. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Scroll through records and get match
    ... "Ken Snell " wrote: ... <MS ACCESS MVP> ... Can someone please provide me with the SQL to loop through and find the ... Animal based on todays date. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Import/Export Multiple Excel Files to Access
    ... Your MacExcelInput macro should have just a single action in it: ... <MS ACCESS MVP> ... "Ken Snell " wrote: ... Dim strPathFile As String, strFile As String, strPath As String ...
    (microsoft.public.access.macros)
  • Re: Export by group to a specific folder and name
    ... <MS ACCESS MVP> ... "Ken Snell MVP" wrote: ... ' Get list of ManagerID values -- note: ...
    (microsoft.public.access.externaldata)
  • Re: Union All query truncating
    ... Ken Snell ... <MS ACCESS MVP> ... Then you use a UNION ALL query ... GROUP BY statements or other situations known to cause truncation. ...
    (microsoft.public.access.reports)