Help With SQL Statement

Hi everyone...back to the well for help...

Here is the code:
strSQL = "SELECT * FROM [Walk Around Schedule: All Items] WHERE [Begin Date]
= #" & walkdate & "#"

Set rswalkaround = dbwalkaround.OpenRecordset(strSQL)

....where 'walkdate' is the value of a text field.

It works just fine BUT the recordset contains only the first record meeting
the criteria from the table, although I know there are multiple records that
meet the criteria in that table...what am I missing?

rswalkaround.recordcount returns 1!

Any/All help greatly appreciated!

Regards
yes2man

One possibility is that the Begin Date column contains values with non-zero
times of day. A common cause of this is the inappropriate use of the Now()
function to insert the current date into the column; the Date() function
should be used for this. To correct any instances of this, which might not
be readily apparent when you look at the data, a simple update query can be
executed:

UPDATE [Walk Around Schedule: All Items]
SET [Begin Date] = DATEVALUE([Begin Date])
WHERE [Begin Date] IS NOT NULL;

Another thing you should do is to ensure that the date literal being built
into the string expression is in an internationally unambiguous date format.
The ISO format for date notation is YYYY-MM-DD, so this is a good one to use:

strSQL = _
"SELECT * FROM [Walk Around Schedule: All Items] " & _
"WHERE [Begin Date] = #" & _
Format(walkdate,"yyyy-mm-dd") & "#"

Another thing you can do is to ensure the recordset is filled by moving to
the last record:

Set rswalkaround = dbwalkaround.OpenRecordset(strSQL)
rswalkaround.MoveLast

If you then want to iterate through the recordset move back to the first
record:

rswalkaround.MoveFirst

Ken Sheridan
Stafford, England

yes2man wrote:
Hi everyone...back to the well for help...

Here is the code:
strSQL = "SELECT * FROM [Walk Around Schedule: All Items] WHERE [Begin Date]
= #" & walkdate & "#"

Set rswalkaround = dbwalkaround.OpenRecordset(strSQL)

...where 'walkdate' is the value of a text field.

It works just fine BUT the recordset contains only the first record meeting
the criteria from the table, although I know there are multiple records that
meet the criteria in that table...what am I missing?

rswalkaround.recordcount returns 1!

Any/All help greatly appreciated!

Regards
yes2man

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201005/1

Your WHERE clause is only looking for a single date. Did you perhaps mean

"..WHERE [Begin Date] #" & walkdate & "#"

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"yes2man" wrote in message
...
Hi everyone...back to the well for help...

Here is the code:
strSQL = "SELECT * FROM [Walk Around Schedule: All Items] WHERE [Begin
Date]
= #" & walkdate & "#"

Set rswalkaround = dbwalkaround.OpenRecordset(strSQL)

...where 'walkdate' is the value of a text field.

It works just fine BUT the recordset contains only the first record
meeting
the criteria from the table, although I know there are multiple records
that
meet the criteria in that table...what am I missing?

rswalkaround.recordcount returns 1!

Any/All help greatly appreciated!

Regards
yes2man

You state :

rswalkaround.recordcount returns 1

Are you sure! Did you use rswalkaround.MoveLast before performing your
recordcount, if not your count will be invalid!

See: http://www.devhut.net/index.php?lang...000011#recsets
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



"yes2man" wrote:

Hi everyone...back to the well for help...

Here is the code:
strSQL = "SELECT * FROM [Walk Around Schedule: All Items] WHERE [Begin Date]
= #" & walkdate & "#"

Set rswalkaround = dbwalkaround.OpenRecordset(strSQL)

...where 'walkdate' is the value of a text field.

It works just fine BUT the recordset contains only the first record meeting
the criteria from the table, although I know there are multiple records that
meet the criteria in that table...what am I missing?

rswalkaround.recordcount returns 1!

Any/All help greatly appreciated!

Regards
yes2man

the second coming of christ to earth.?
www.musecrafters.com/ocean
must read.

"yes2man" wrote in message
...
Hi everyone...back to the well for help...

Here is the code:
strSQL = "SELECT * FROM [Walk Around Schedule: All Items] WHERE [Begin
Date]
= #" & walkdate & "#"

Set rswalkaround = dbwalkaround.OpenRecordset(strSQL)

...where 'walkdate' is the value of a text field.

It works just fine BUT the recordset contains only the first record
meeting
the criteria from the table, although I know there are multiple records
that
meet the criteria in that table...what am I missing?

rswalkaround.recordcount returns 1!

Any/All help greatly appreciated!

Regards
yes2man

THAT WAS IT!! Stupid...so deep didn't even see it...thanks a ton!

"Daniel Pineault" wrote:

You state :

rswalkaround.recordcount returns 1

Are you sure! Did you use rswalkaround.MoveLast before performing your
recordcount, if not your count will be invalid!

See: http://www.devhut.net/index.php?lang...000011#recsets
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



"yes2man" wrote:

Hi everyone...back to the well for help...

Here is the code:
strSQL = "SELECT * FROM [Walk Around Schedule: All Items] WHERE [Begin Date]
= #" & walkdate & "#"

Set rswalkaround = dbwalkaround.OpenRecordset(strSQL)

...where 'walkdate' is the value of a text field.

It works just fine BUT the recordset contains only the first record meeting
the criteria from the table, although I know there are multiple records that
meet the criteria in that table...what am I missing?

rswalkaround.recordcount returns 1!

Any/All help greatly appreciated!

Regards
yes2man