Survey Stats Converter Update Issues. (1 Viewer)

rsmonkey · Aug 24, 2007

Hi,

Baisically I got a load of surveys off survey moneky and I have put them all into Access via Linked tables from their downloaded Excel spreadsheets.
From here i want Access to go through and change these 'string' values i.e. extremely, adequate etc.. into a numbering system i have devised. This will then be placed into a seperate excel spreadsheet i have designed so its easy to read/colour coded etc...

So far i've got all all the way to the UPDATE phase of changing the texts values' into integers however something is wrong with me UPDATE value. VB keeps stoping at 'CurrentDB.Execute (strSQL)' line giving a Runtime Error - 3144 - Syntax error in Update Statement. However this was working a couple days ago i've literally comeback and now it refuses to work. Here's the code:

Code:

Option Compare Database

Private Sub Command0_Click()

Dim db As DAO.Database
Dim varItem As Variant
Dim strSQL As String
Dim rs As Recordset
Dim I As Integer

Call InsertATC
Call InsertCAT
Call InsertEITS
Call InsertHRS
Call InsertIPS
Call InsertIRS
Call InsertPESES
Call InsertPESRE
Call InsertPESWPS

Set db = CurrentDb()

strSQL = "SELECT budata.buid, budata.bu, budata.BUnumberOfQuestions FROM budata"
Set rs = db.OpenRecordset(strSQL)
If Not rs.EOF Then
    Do While Not rs.EOF
    
    strBu = rs!bu
    valQuestionCount = rs!BUnumberOfQuestions
    
    For I = 1 To valQuestionCount
        Score = "Score" & I
        Need = "Need" & I
        Trend = "Trend" & I
        Rel = "Rel" & I
        
        
        strSQL = "UPDATE " & strBu & " SET " & Score & " = 10 WHERE [" & Need & "]='Always' AND [" & Trend & "]='Improving'"
        CurrentDb.Execute (strSQL)
        strSQL = "UPDATE " & strBu & " SET " & Score & " = 10 WHERE [" & Need & "]='Always' AND [" & Trend & "]='Same'"
        CurrentDb.Execute (strSQL)
        strSQL = "UPDATE " & strBu & " SET " & Score & " = 9 WHERE [" & Need & "]='Always' AND [" & Trend & "]='Getting Worse'"
        CurrentDb.Execute (strSQL)
        strSQL = "UPDATE " & strBu & " SET " & Score & " = 8 WHERE [" & Need & "]='Generally' AND [" & Trend & "]='Improving'"
        CurrentDb.Execute (strSQL)
        strSQL = "UPDATE " & strBu & " SET " & Score & " = 7 WHERE [" & Need & "]='Generally' AND [" & Trend & "]='Same'"
        CurrentDb.Execute (strSQL)
        strSQL = "UPDATE " & strBu & " SET " & Score & " = 6 WHERE [" & Need & "]='Generally' AND [" & Trend & "]='Getting Worse'"
        CurrentDb.Execute (strSQL)
        strSQL = "UPDATE " & strBu & " SET " & Score & " = 5 WHERE [" & Need & "]='Sometimes' AND [" & Trend & "]='Improving'"
        CurrentDb.Execute (strSQL)
        strSQL = "UPDATE " & strBu & " SET " & Score & " = 4 WHERE [" & Need & "]='Sometimes' AND [" & Trend & "]='Same'"
        CurrentDb.Execute (strSQL)
        strSQL = "UPDATE " & strBu & " SET " & Score & " = 3 WHERE [" & Need & "]='Sometimes' AND [" & Trend & "]='Getting Worse'"
        CurrentDb.Execute (strSQL)
        strSQL = "UPDATE " & strBu & " SET " & Score & " = 2 WHERE [" & Need & "]='Never' AND [" & Trend & "]='Improving'"
        CurrentDb.Execute (strSQL)
        strSQL = "UPDATE " & strBu & " SET " & Score & " = 1 WHERE [" & Need & "]='Never' AND [" & Trend & "]='Same'"
        CurrentDb.Execute (strSQL)
        strSQL = "UPDATE " & strBu & " SET " & Score & " = 1 WHERE [" & Need & "]='Never' AND [" & Trend & "]='Getting Worse'"
        CurrentDb.Execute (strSQL)
        
        If valQuestionCount - I > 3 Then
            strSQL = "UPDATE " & strBu & " SET " & Rel & " = 'H' WHERE " & Rel & " = 'Extremely'"
            CurrentDb.Execute (strSQL)
            strSQL = "UPDATE " & strBu & " SET " & Rel & " = 'M' WHERE " & Rel & " = 'Somewhat'"
            CurrentDb.Execute (strSQL)
            strSQL = "UPDATE " & strBu & " SET " & Rel & " = 'L' WHERE " & Rel & " = 'Not At All'"
            CurrentDb.Execute (strSQL)
            strSQL = "UPDATE " & strBu & " SET " & Rel & " = ' ' WHERE " & Rel & " = 'Not Applicable'"
            CurrentDb.Execute (strSQL)
        End If
    Next
    rs.MoveNext
    Loop

Else
    MsgBox "Something is wrong"
End If
rs.Close
Set rs = Nothing


MsgBox "Complete"

Set db = Nothing
DoCmd.Close

   
End Sub

If anyone could help me with this problem it would be much appreciated.

Cheers

Dennisk · Aug 24, 2007

use the debug.print statement to display the contents of strSQL to see exactly what the problem is.

I do a lot of conversion of user written dbs and most of them do not have a proper coding system fo lookups.

I use the following steps

1) create a query to list the text with th unique property set to yes.
2) convert this to a maketable query and run.
3) add an autonumber to the table.
4) Create a number field in the main table to hold the key.
4) use an update query linking the text from the main table to the lookup table and set the PK from the lookup table in the main table.

this does away with any code so long as you use autonumbers.

rsmonkey · Aug 24, 2007

Thanks for the quick reply my sql just decided to be abit funny with brackets... It should have been:

Code:

strSQL = "UPDATE [" & strBu & "] SET [" & Score & "] = 10 WHERE [" & Need & "]='Always' AND [" & Trend & "]='Improving'"
        CurrentDb.Execute (strSQL)

Rather than

Code:

strSQL = "UPDATE " & strBu & " SET " & Score & " = 10 WHERE [" & Need & "]='Always' AND [" & Trend & "]='Improving'"
        CurrentDb.Execute (strSQL)

Again thanks Dennisk I've copied and saved your method for future reference.

Survey Stats Converter Update Issues. (1 Viewer)

rsmonkey

Registered User.

Dennisk

AWF VIP

rsmonkey

Registered User.

Similar threads

Users who are viewing this thread