Prev: Convert a form to a split form?
Next: the second coming of Christ to earth.?
From: yes2man on 21 May 2010 11:15
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
From: KenSheridan via AccessMonster.com on 21 May 2010 11:39
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/Forums.aspx/access/201005/1

From: Douglas J. Steele on 21 May 2010 11:41
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" <yes2man(a)discussions.microsoft.com> wrote in message
news:B2B1835C-54A9-4DBD-A8D5-2F19BD0F7206(a)microsoft.com...
> 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


From: Daniel Pineault on 21 May 2010 12:02
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=en&pid=0000000011#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
From: Craig Lindgren on 21 May 2010 12:04
the second coming of christ to earth.?
www.musecrafters.com/ocean
must read.

"yes2man" <yes2man(a)discussions.microsoft.com> wrote in message
news:B2B1835C-54A9-4DBD-A8D5-2F19BD0F7206(a)microsoft.com...
> 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

 |  Next  |  Last
Pages: 1 2
Prev: Convert a form to a split form?
Next: the second coming of Christ to earth.?