CODEWITHSUNDEEP
vbams-access
Bast
Bast

Reputation: 3

Insert into with variable numbers

I have made this function in Access vba : 

Function test_zeroSQL()

Dim nsemaine As Single
Dim var11 As Single
Dim var50 As Single
Dim var60 As Single
Dim var11b As Single
Dim var50b As Single
Dim var60b As Single

nsemaine = DatePart("ww", Date, 2, 2) - 1
If zero11() = 0 Then
    var11 = 1
    var11b = 1
Else
    var11 = zero11()
    var11b = zero11b()
End If

If zero50() = 0 Then
    var50 = 1
    var50b = 1
Else
    var50 = zero50()
    var50b = zero50b()
End If

If zero60() = 0 Then
    var60 = 1
    var60b = 1
Else
    var60 = zero60()
    var60b = zero60b()
End If

test_zeroSQL = "INSERT INTO [Taux de Service] ( Semaine, [Sortie B60], [Nc B60], [Sortie B50], [Nc B50], [Sortie B11], [Nc B11] )"
test_zeroSQL = test_zeroSQL & vbCrLf & "VALUES (" & nsemaine & "," & var60 & "," & var60b & "," & var50 & "," & var50b & "," & var11 & " ," & var11b & " )"
End Function

But it doesnt work, i have this message : "the number of requests values ​​must coincide with the number of destination fields"

I think it comes from the attribut of my variables because it works when i remplace single by integer. Nevertheless i have checked my fields are "Single", as well. I dont understand where it could come from...

Can you help me please ?

Upvotes: 0

Views: 45

Answers (1)

litelite
litelite

Reputation: 2851

The number of column specified (the parenthesis after [Taux de Service]) is not the same as the number of value inside the values.

You are using decimal numbers and a french local (which use , as a decimal separator), so it messes up the query because access always uses , as a column separator.

There are two solutions for this problem :

  1. Use query parameters which is also the best practice as it help preventing SQL injection attacks and also just user accidentally using special caracters and blowing-up the query.

  2. Replace the , by . in every decimal number like this : Replace(myString, ",",".")

Upvotes: 2

Related Questions