SQL and percents... 1

[snayjay]

Ok, I'm having a problem with this SQL and I think it has to do with the fields that are percents. Should I be using a delimiter here... Please look this over... sorry for the length, I thought it best to show the whole thing.

(tn = TableName and the TableName has hypens in it... does that matter... ie., RMR-27-Oct-06)

Dim saveSQL
    saveSQL = "INSERT INTO " & tn & " (UnitName,AsOfDate,Assigned,Overall,OverallP"
    saveSQL = saveSQL & ",PHA,PHAP,Dental,DentalP,Immunizations,ImmunizationsP"
    saveSQL = saveSQL & ",Labtests,LabtestsP,MaskFitTest,MaskFitTestP,Inserts"
    saveSQL = saveSQL & ",InsertsP,OccHealth,OccHealthP,CATM,CATMP,CWDT,CWDTP"
    saveSQL = saveSQL & ",SABC,SABCP,Fitness,FitnessP,LegalReadiness,LegalReadinessP"
    saveSQL = saveSQL & ",NotonProfile,NotonProfileP,UNITTOTAL,UNITTOTALP,UNITCOMMENTS) 
    saveSQL = saveSQL & " VALUES ('" & UnitName.Value & "',#"
    saveSQL = saveSQL & AsOfDate.Value & "#,"
    saveSQL = saveSQL & Assigned.Value & ","
    saveSQL = saveSQL & Overall.Value & "," & OverallP.Value & ","
    saveSQL = saveSQL & PHA.Value & "," & PHAP.Value & ","
    saveSQL = saveSQL & Dental.Value & "," & DentalP.Value & ","
    saveSQL = saveSQL & Immunizations.Value & "," & ImmunizationsP.Value & ","
    saveSQL = saveSQL & LabTests.Value & "," & LabTestsP.Value & ","
    saveSQL = saveSQL & MaskFitTest.Value & "," & MaskFitTestP.Value & ","
    saveSQL = saveSQL & Inserts.Value & "," & InsertsP.Value & ","
    saveSQL = saveSQL & OccHealth.Value & "," & OccHealthP.Value & ","
    saveSQL = saveSQL & CATM.Value & "," & CATMP.Value & ","
    saveSQL = saveSQL & CWDT.Value & "," & CWDTP.Value & ","
    saveSQL = saveSQL & SABC.Value & "," & SABCP.Value & ","
    saveSQL = saveSQL & Fitness.Value & "," & FitnessP.Value & ","
    saveSQL = saveSQL & LegalReadiness.Value & "," & LegalReadinessP.Value & ","
    saveSQL = saveSQL & NotonProfile.Value & "," & NotonProfileP.Value & ","
    saveSQL = saveSQL & UNITTOTAL.Value & "," & UNITTOTALP.Value & ",'"
    saveSQL = saveSQL & UNITCOMMENTS.Value & "')"

    DoCmd.SetWarnings False
    Debug.Print saveSQL
    DoCmd.RunSQL (saveSQL)

 

[Remou]

You need to put table name in square brackets [], if it has hyphens.

 

[JerryKlmns]

snayjay

Could it be your non formated numeric values look like 1.254,3845
You should reformat them to be 1254.3875
And for dates use this ANSI format yyyy-mm-dd

 

[snayjay]

The brackets did the trick... sorry I didn't get back right away. I'm working on this project at work and my home comp has just been reformatted and haven't gotten around to reloading Office on it. Too much damn Xbox 360. Thanks again Remou, and JerryKlmns thanks for the post.