update syntax error

[seeker]

The following update query:
strsql = "update tblmember set DateFitnessScreeningSigned = #" &
txtDateofSigning & "#, homephone = '" & txtPhone & _
"', streetaddress = '" & txtStreetAddress & "', city = '" &
txtCity & "', state = '" & txtState & _
"', zip = '" & txtZip & "', gender = '" & cmbGender & "',
beginHeight = '" & txtHeight & "', beginweight = '" & _
txtWeight & "', DateOfBirth = #" & txtDOB & "#, BeginBMI = '"
& txtBMI & "', BeginBP = '" & txtBP & "', BeginRestingHeartRate = '" & _
txtRestingHeart & "', RiskStratification = '" & cmbRisk & "',
BeginFitnessTestResults = '" & txtFitnessTest & _
"', chestpain = '" & cmbChestPain & "', HeartDisease = '" &
cmbHeartDisease & "', EKG = '" & cmbEKG & "', HighBloodPressure = '" & _
cmbHighBloodPressure & "', HighBloodPressureMeds = '" &
cmbMeds & "', diabetes = '" & cmbDiabetes & "', GlucoseLevels = '" & _
cmbBloodGlucose & "', Smoker = '" & cmbSmoke & "', LoseWeight
= '" & cmbLoseWeight & "', LegPain = '" & cmbLegPain & _
"', RapidWeightLossGain = '" & cmbWeightLossGain & "',
PregnantGivenBirth = '" & cmbPregnant & "', surgery = '" & cmbSurgery & _
"', BackJointDisease = '" & cmbJointDisease & "',
FamilyHistoryHeartDisease = '" & cmbFamilyHistory & "', CholesterolKnowledge
= '" & _
cmbCholesterol & "', Cholesterolnumber = '" & txtCholesterol &
"', AdverseHealth = '" & txtOtherHealthIssues & _
"', PresentExerciseProgram = '" & cmbPresentExercise & "',
LastregularPhysicalActivity = '" & txtRegularExercise & _
"', EmergencyContact = '" & txtEmergencyContactPerson & "',
EmergencyPhone = '" & txtEmergencyContactPhone & _
"', BeginWaistCircum = '" & txtWaist & "', BeginAbdomenCircum
= '" & txtAbdomen & "', BeginHipsCircum = '" & txtHips & _
"', BeginBIA = '" & txtBIA & "', variousdiseases = '" &
cmbVariousDisease & "', where membernumber = " & cmbName

with this result in immediate window after ?strsql:
update tblmember set DateFitnessScreeningSigned = #3/2/2009#, homephone =
'(316) 686-0851', streetaddress = '1900 S Rock Rd #2105', city = 'Wichita',
state = 'KS', zip = '67207-', gender = 'Female', beginHeight = '64.5',
beginweight = '208.0', DateOfBirth = #8/1/1957#, BeginBMI = '35.1', BeginBP =
'', BeginRestingHeartRate = '', RiskStratification = 'Medium',
BeginFitnessTestResults = '21.16 ml/kg/min', chestpain = 'No', HeartDisease =
'No', EKG = 'No', HighBloodPressure = 'No', HighBloodPressureMeds = 'No',
diabetes = 'No', GlucoseLevels = 'No', Smoker = 'Yes', LoseWeight = 'Yes',
LegPain = 'No', RapidWeightLossGain = 'No', PregnantGivenBirth = 'No',
surgery = 'No', BackJointDisease = 'No', FamilyHistoryHeartDisease = 'No',
CholesterolKnowledge = 'No', Cholesterolnumber = '', AdverseHealth = '',
PresentExerciseProgram = 'Yes', LastregularPhysicalActivity = '',
EmergencyContact = 'Maxine Keely', EmergencyPhone = '(316) 683-9314',
BeginWaistCircum = '110.5', BeginAbdomenCircum = '131.0', BeginHipsCircum =
'12
4.5', BeginBIA = '42.6', variousdiseases = 'N/A', where membernumber = 16

gives me this error
Syntax error in update statement.

I have some fields that are updated to ' ' but I believe that should be ok.
Can anyone see anything that i am missing. Thank you.

 

[Daryl S]

Seeker -

The syntax error is due to the trailing comma after the SET fieldlist and
before the WHERE near the end of the statement.

Are all the fields text fields? I would think items like beginHeight,
BeginBMI, etc. are numeric fields. These would not need the single-quotes
around the values. In cases where you would have '' for a numeric field, you
can only set it to Null or zero (like BeginBP). Also, the Yes/No fields - if
they are text fields you are OK, otherwise don't add the single quotes.

 

[seeker]

thanks for the second eyes. Myself and a compatriot missed it.