For Each Loop Help 1

[romeerome368]

Hello Everyone,

I am trying to create some VBA code to help create and ending date for my records. I have start dates, and the end date should be the next start date - one day.

For example:

Start Date: 2/25/2011
Next Start Date: 4/30/2011
End Date for 2/25/2011 Start date should be: 4/29/2011

I know that I need to create a For Each for my recordset, but I am having trouble with the syntax for the For Each loop.

Any help would be greatly appreciated.

 

[SkipVought]

hi,

Please post your code.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[romeerome368]

Hi Skip,

Here is a sample of my code.

Dim db As Database
Dim recset As Recordset
Dim x As Variant
Dim EndDate As Date
Dim recdate As Date

Set db = CurrentDB()
Set recset = db.OpenRecordset(“tblCYSmSOrd”)

Do until recset.EOF
      For Each recdate in recset
          x = recdate – 1
          EndDate = x
      Next
Loop

recset.Close

 

[SkipVought]

is there any reason why you are not using a query to return a recordset?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[romeerome368]

I wanted to write directly to the table rather than using the query to be the focus of my recordset. No one else has access to this table. I use it to create reports. I guess if there were multiple users I would use a query.

What would a query do that a table wouldn't do?

 

[SkipVought]

Are you ADDING or UPDATING rows?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[romeerome368]

Updating a null field for each row in my table.

 

[SkipVought]

here's your basic loop

Do until recset.EOF
  'do yer stuff here

  recset.movenext
Loop

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[strongm]

>What would a query do

Well ... you wouldn't need a loop ...

 

[romeerome368]

Thanks for your help so far Skip, but my code within the For Each is not working. I'm trying to figure out how to make it look at the next record first, and then subtract one. It seems to be taking the first record, and subtracting that one to make the end date.

My end date is prior to my start date.

 

[SkipVought]

but my code within the For Each is not working

WHAT code?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

and what does, "is not working" mean in exact terms.

Your statements so far have neither been specific nor descriptive. Help your self by providing clear, concise and complete information about each issue that you raise.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[strongm]

I'd imagine that they mean that:

x = recdate – 1
EndDate = x

isn't "mak[ing] it look at the next record first, and then subtract[ing] one" from it to generate the end date of the current record.

(mind you for this approach to work in any case rather assumes that the data in the table is in a specific order, which goes against one of the fundamental principle's of relational database design that tables be unordered)