For loop with intctr SQL statement

[BusMgr]

I have a table with a field labeled DayID and multiple fields labled From1, From2, To1, To2, etc. I want to create a new table that just has DayID, FromTime and ToTime as the fields.

The issue that I am having is getting the intctr into the strSQL string statement, so that it increments the field name and loops through the whole table to build the new table.

I have corrected my syntax to the following, but I get a "Variable Not Defined " error when I try to compile the statement with the From highlighted.

Dim intCtr as integer
Dim strSQL as string

for intCtr = 1 to 24
strSQL = "INSERT INTO tmpDailyOps IN (DayID, FromTime, ToTime) SELECT Operations.DayID, Operations(' " & From & " ' & intCtr), Operations(' " & To & " ' & intCtr) FROM Operations WHERE (((Operations.DayID)=[Forms]![frmDay]![Status].[Form]![DayID]))"

docmd.runSQL strSQL
next intCtr

End Sub

I've built the query in Access using the query builder, so I know that my data will do this, but I am trying to do it in VBA to speed the table building.

I've tried it with a mix of double quotes, &'s and single quotes without success. The strSQL statement is all on one line enclosed in double quotes.

Thanks for any help in advance.

BusMgr

 

[PaulBricker]

My first guess is that From is a reserved word in Access. Have you tried enclosing it in brackets [From] so that Access knows its a field. That may not help but it's a place to start.

Paul

 

[BusMgr]

Tried it. I've also tried different approaches with the ' " &, the [] appear to help, but I am not getting it to recognize the word 'From'.

 

[mikevh]

The problem is you're treating FROM and TO as though they were variables in your VBA Module, which they're not,
they're names of table fields inside a string, so they need to be inside the quotes with the rest of strSQL. Only intCtr, which is an actual VBA variable, should be outside the quotes, like so:

strSQL = _
"INSERT INTO tmpDailyOps IN (DayID, FromTime, ToTime) SELECT Operations.DayID, Operations.From" & intCtr &
", Operations.To" & intCtr & " FROM Operations WHERE (((Operations.DayID)=[Forms]![frmDay]![Status].[Form]![DayID]))"

Make sure you have no space between "Operations.From" and
the next quote mark or between "Operations.To" and the
next quote mark, and that you do have a comma inside the
quotes before "Operations.To", as in the example above.

Hope this helps.