SQL in VBA + iterating through recordset

[Rhanda Zak]

Hi NG!

I just managed copying data from one table to another table (boah,
what a big deal :). But there are still some problems:

1)
I want to use the keyword "BETWEEN" in combination with date fields in
my SELECT statement, but Execute does not like it. I

tried different variations, none of them works :(

My code: (IFrom and ITo are input fiels with format date, short)

sInsSQL = "INSERT INTO _VATAP_temp (REC, PVM, AIKA, TAPLAJI,
TUOTE, VARASTO ) "
sSelSQL = "SELECT REC, PVM, AIKA, TAPLAJI, TUOTE, VARASTO FROM
_VATAP_temp2 "

'does not work
'sSelSQL = sSelSQL & "WHERE (PVM Between Forms!abc_1!IFrom And
Forms!abc_1!ITo)"

'also not this
'sSelSQL = sSelSQL & "WHERE (PVM Between #" & IFrom & "# And #"
'sSelSQL = sSelSQL & ITo & "#)"

'and also not this alternative solution...
'sSelSQL = sSelSQL & "WHERE (PVM >= " & IFrom & " And PVM <="
'sSelSQL = sSelSQL & ITo & ")"
sInsSQL = sInsSQL & sSelSQL

'always stops with runtime error 3075, syntax error
dbs.Execute sInsSQL

I noticed that the query would require the date in the format
#12/07/80#. Is there a conventient way of doing that?
2)
In the next step I have to iterate through all results, make some
calculation and write the result to another column in this

table.

For Each In Table._VATAP_temp
// well, the next line is C++-style, but I hope you know what I mean
with that...
currentRow.setField("result", currentRow.getField("data") * 1,22)
End For
As you can see I am new in VBA, so I would really appreciate to give
me some links for good VBA-tutorials.
Thank you in advance,
Rhanda

[Steve Jorgensen]

On 25 Feb 2004 13:29:01 -0800, gh*****@gmx.net (Rhanda Zak) wrote:

Hi NG!

I just managed copying data from one table to another table (boah,
what a big deal :). But there are still some problems:

1)
I want to use the keyword "BETWEEN" in combination with date fields in
my SELECT statement, but Execute does not like it. I

tried different variations, none of them works :(

My code: (IFrom and ITo are input fiels with format date, short)

sInsSQL = "INSERT INTO _VATAP_temp (REC, PVM, AIKA, TAPLAJI,
TUOTE, VARASTO ) "
sSelSQL = "SELECT REC, PVM, AIKA, TAPLAJI, TUOTE, VARASTO FROM
_VATAP_temp2 "

'does not work
'sSelSQL = sSelSQL & "WHERE (PVM Between Forms!abc_1!IFrom And
Forms!abc_1!ITo)"
Right. If you're using DAO/ADO to execute the query, the query does not "see"
Access obects, so you have to either make a querydef that takes parameters or
insert the data into the SQL as you are trying to do.

'also not this
'sSelSQL = sSelSQL & "WHERE (PVM Between #" & IFrom & "# And #"
'sSelSQL = sSelSQL & ITo & "#)"

'and also not this alternative solution...
'sSelSQL = sSelSQL & "WHERE (PVM >= " & IFrom & " And PVM <="
'sSelSQL = sSelSQL & ITo & ")"

These should work, and I can't tell you exactly why they don't. By the way,
it's probably better to say Me!IFrom instead of IFrom. The next thing to try
is to print out sInsSQL before executing it to make sure it looks like we
expect it ti.

sInsSQL = sInsSQL & sSelSQL

'always stops with runtime error 3075, syntax error
dbs.Execute sInsSQL

I noticed that the query would require the date in the format
#12/07/80#. Is there a conventient way of doing that?
Actually, it will work with any valid date format, but it's best to make it an
unambiguous format with respect to internationalization. That would be ...#"
& Format(Me!IFrom,"yyyy-mm-dd") & "#...
2)
In the next step I have to iterate through all results, make some
calculation and write the result to another column in this

table.

For Each In Table._VATAP_temp
// well, the next line is C++-style, but I hope you know what I mean
with that...
currentRow.setField("result", currentRow.getField("data") * 1,22)
End For
As you can see I am new in VBA, so I would really appreciate to give
me some links for good VBA-tutorials.

1. For Each requires a variable between the "Each" and the "In"
2. A recordset is not a collection, so you can't iterate it using For Each
anyway. Instead, in DAO, you first need an If block to skip the loop entirely
unless .RecordCount <> 0 (.RecordCount may not be accurate, but it works and
is correct for this purpose), then loop While Not rst.EOF, and do rst.Movenext
just before the Wend statement at the end of the loop.
3. A tabledef is not a recordset. I contains information about the table's
definition, not its contents. You must open a recordset based on the table
name or a Select statement to loop through a table's rows.

[Bradley]

"Rhanda Zak" <gh*****@gmx.net> wrote in message
news:16*************************@posting.google.co m...

<snip>

'also not this
'sSelSQL = sSelSQL & "WHERE (PVM Between #" & IFrom & "# And #"
'sSelSQL = sSelSQL & ITo & "#)"

try formatting the date in Amercian format...

'sSelSQL = sSelSQL & "WHERE (PVM Between #" & Format(IFrom,
"mm/dd/yyyy") & "# And #"
'sSelSQL = sSelSQL & Format(ITo, "mm/dd/yyyy") & "#)"
<snip>
--
Bradley
Software Developer www.hrsystems.com.au
A Christian Response www.pastornet.net.au/response