On 25 Feb 2004 13:29:01 -0800,
gheaci1@gmx.net (Rhanda Zak) wrote:
[color=blue]
>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)"[/color]
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.
[color=blue]
>
> '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 & ")"
>[/color]
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.
[color=blue]
>
> 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?[/color]
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") & "#...
[color=blue]
>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.[/color]
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.
