Scroll through records and get match

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

Guest

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
 
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?
 
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) said:
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?
Click to expand...
 
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 said:
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
Click to expand...
 
Hi Ken,

I have used your code and placed it on a button (under build event).

2 Questions

First Question
To run code is there anyway of writing the code and getting it to run
without having to attach it to a button (sorry if this seems a stupid
question but it's the only way I know how to kick it off).


Second Question

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click


sSQL = "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());"
DoCmd.RunSQL sSQL

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub


When I run the above I get the error


A RunSQL statemnt requires an argument consisting of an SQL statement.

From my limited knowledge it is telling me I need to make an update query.
I want to pass the T and Q values through to the next SQL statement (I
haven't put that in this query).

--
Andrew


Ken Snell (MVP) said:
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 said:
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) said:
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>

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
Click to expand...
Click to expand...
Click to expand...
 
You can use any of the events attached to controls and forms to run code.
You need to decide which event would be useful; however, for your situation
I would think that using a command button is probably the best method as
that means the code runs only when you want it to (explicitly clicking the
button).

DoCmd.RunSQL is used to run action queries, not select queries. The query
that I posted is a select query. You can use the DoCmd.OpenQuery to open
select queries, but they must be ones already stored in the database -- you
cannot use it to open an SQL statement that you generate via code.

If you want the Period and Week values from the query that you're generating
in the code, you must open a recordset based on the SQL statement, and then
read the values from the recordset and use them in your code. Something like
this:


Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngPeriod As Long, lngWeek As Long
Dim strSQL As String
Set dbs = CurrentDb()
strSQL = "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());"
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
If rst.EOF = False And rst.BOF = False Then
rst.MoveFirst
lngPeriod = rst![Period].Value
lngWeek = rst![Week].Value
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
' now you can use the lngPeriod and lngWeek values
' in your code for what you want to do next


--

Ken Snell
<MS ACCESS MVP>


Andrew said:
Hi Ken,

I have used your code and placed it on a button (under build event).

2 Questions

First Question
To run code is there anyway of writing the code and getting it to run
without having to attach it to a button (sorry if this seems a stupid
question but it's the only way I know how to kick it off).


Second Question

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click


sSQL = "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());"
DoCmd.RunSQL sSQL

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub


When I run the above I get the error


A RunSQL statemnt requires an argument consisting of an SQL statement.

From my limited knowledge it is telling me I need to make an update query.
I want to pass the T and Q values through to the next SQL statement (I
haven't put that in this query).

--
Andrew


Ken Snell (MVP) said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...


< snipped >
 
Hi Ken,

Thanks for explaining how to step through code. From what you say you
ALWAYS need to click on a button to start the code working. In Excel you can
simply be in the code and press F8 or F5 and away you go.

Anyway, when I try to run your code I get an error

"User-defined type not defined" at Dim dbs As DAO.Database. If I comment
that line out (I know I shouldn't but I thought I'd try anyway) I get an
error:

"Invalid argument" Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

PS Thanks for all of your assistance and guidence to date.


--
Andrew


Ken Snell (MVP) said:
You can use any of the events attached to controls and forms to run code.
You need to decide which event would be useful; however, for your situation
I would think that using a command button is probably the best method as
that means the code runs only when you want it to (explicitly clicking the
button).

DoCmd.RunSQL is used to run action queries, not select queries. The query
that I posted is a select query. You can use the DoCmd.OpenQuery to open
select queries, but they must be ones already stored in the database -- you
cannot use it to open an SQL statement that you generate via code.

If you want the Period and Week values from the query that you're generating
in the code, you must open a recordset based on the SQL statement, and then
read the values from the recordset and use them in your code. Something like
this:


Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngPeriod As Long, lngWeek As Long
Dim strSQL As String
Set dbs = CurrentDb()
strSQL = "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());"
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
If rst.EOF = False And rst.BOF = False Then
rst.MoveFirst
lngPeriod = rst![Period].Value
lngWeek = rst![Week].Value
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
' now you can use the lngPeriod and lngWeek values
' in your code for what you want to do next


--

Ken Snell
<MS ACCESS MVP>


Andrew said:
Hi Ken,

I have used your code and placed it on a button (under build event).

2 Questions

First Question
To run code is there anyway of writing the code and getting it to run
without having to attach it to a button (sorry if this seems a stupid
question but it's the only way I know how to kick it off).


Second Question

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click


sSQL = "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());"
DoCmd.RunSQL sSQL

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub


When I run the above I get the error


A RunSQL statemnt requires an argument consisting of an SQL statement.

From my limited knowledge it is telling me I need to make an update query.
I want to pass the T and Q values through to the next SQL statement (I
haven't put that in this query).

--
Andrew


Ken Snell (MVP) said:
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>

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


< snipped >
Click to expand...
 
Sounds as though you don't have a reference set to DAO.

With any code module open, select Tools | References from the menu bar,
scroll through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, and select it.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Andrew said:
Hi Ken,

Thanks for explaining how to step through code. From what you say you
ALWAYS need to click on a button to start the code working. In Excel you
can
simply be in the code and press F8 or F5 and away you go.

Anyway, when I try to run your code I get an error

"User-defined type not defined" at Dim dbs As DAO.Database. If I comment
that line out (I know I shouldn't but I thought I'd try anyway) I get an
error:

"Invalid argument" Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

PS Thanks for all of your assistance and guidence to date.


--
Andrew


Ken Snell (MVP) said:
You can use any of the events attached to controls and forms to run code.
You need to decide which event would be useful; however, for your
situation
I would think that using a command button is probably the best method as
that means the code runs only when you want it to (explicitly clicking
the
button).

DoCmd.RunSQL is used to run action queries, not select queries. The query
that I posted is a select query. You can use the DoCmd.OpenQuery to open
select queries, but they must be ones already stored in the database --
you
cannot use it to open an SQL statement that you generate via code.

If you want the Period and Week values from the query that you're
generating
in the code, you must open a recordset based on the SQL statement, and
then
read the values from the recordset and use them in your code. Something
like
this:


Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngPeriod As Long, lngWeek As Long
Dim strSQL As String
Set dbs = CurrentDb()
strSQL = "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());"
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
If rst.EOF = False And rst.BOF = False Then
rst.MoveFirst
lngPeriod = rst![Period].Value
lngWeek = rst![Week].Value
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
' now you can use the lngPeriod and lngWeek values
' in your code for what you want to do next


--

Ken Snell
<MS ACCESS MVP>


Andrew said:
Hi Ken,

I have used your code and placed it on a button (under build event).

2 Questions

First Question
To run code is there anyway of writing the code and getting it to run
without having to attach it to a button (sorry if this seems a stupid
question but it's the only way I know how to kick it off).


Second Question

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click


sSQL = "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());"
DoCmd.RunSQL sSQL

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub


When I run the above I get the error


A RunSQL statemnt requires an argument consisting of an SQL statement.

From my limited knowledge it is telling me I need to make an update
query.
I want to pass the T and Q values through to the next SQL statement (I
haven't put that in this query).

--
Andrew


:

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>

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


< snipped >
Click to expand...
Click to expand...
 
Back
Top