Normalise Data - tweek to code please

[tractorvix]

I'm trying to normalise a dataset that is currently linked in excel. I have created the following which I think should work based on research that I've carried out on here

Function MyData()
Dim rs As DAO.Recordset
Dim x As Integer
Dim sql As String

Set rs = CurrentDb.OpenRecordset("tbl-prodrates")

If rs.EOF And rs.BOF Then
Exit Function
End If

Do Until rs.EOF
For x = 1 To 5
sql = "INSERT INTO tblNew(ProdXrefID, dateval, value)" & _
" VALUES(" & rs(0) & ",'" & rs(x).Name & _
"'," & rs(x) & ")"

DoCmd.RunSQL sql
Next x
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
End Function


In my watch window my sql value is "INSERT INTO tblNEW(ProdXrefID, dateval, value) VALUES(25,'Jan 08',0.03)"

but when I try and run it I get an error stating SYNTAX ERROR IN INSERT INTO STATEMENT

could anyone please advise how to resolve this please?

Thanks
Vicky

 

[SkipVought]

Hi,

Is suspect it's your "date" which is NOT a date, assuming that dateval is indeed a date datatype. 'Jan 08' is a STRING. date-like strings can be CONVERTED to a Date Value, using DateSerial or DateValue. But Year Month AND DAY, must be present. I'd recommend using DateSerial, explicitly specifying YR, MO & DA.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Golom]

And Value is a reserved word in JET. Enclose it in brackets (i.e. ... ProdXrefID, dateval, [Value] ...)

 

[tractorvix]

Thanks for the replies chaps.
My sql code is now working, but the final field does not seem to be pulling through. Please note I've amended the field name VALUE to ProdRate.

Function MyData()
Dim rs As DAO.Recordset
Dim x As Integer
Dim sql As String

Set rs = CurrentDb.OpenRecordset("tbl-prodrates")

If rs.EOF And rs.BOF Then
Exit Function
End If
DoCmd.SetWarnings False
Do Until rs.EOF
For x = 1 To 5
sql = "INSERT INTO tblNew(ProdXrefID, dateval, Prodrate)" & _
" VALUES(" & rs(0) & ",'" & rs(x).Name & _
"'," & rs(x) & ")"

DoCmd.RunSQL sql
Next x
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
DoCmd.SetWarnings True
End Function

again my watch window shows:
"INSERT INTO tblNew(ProdXrefID, dateval, Prodrate) VALUES(1,'Jan 08',4)"

so is obviously picking up the final field, but for some reason is not populating the table with it.

Any ideas?

Thanks
Vicky

 

[PHV]

What happen if you execute a query with the following SQL code ?
INSERT INTO tblNew(ProdXrefID, dateval, Prodrate) VALUES(1,'Jan 08',4)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886