SQL in VBA - error message

[Tom]

I am recieving the following runtime error message when I
run the code below: "The number of query values and
destination fields are not the same".

Dim strSQL As String
Dim strClaimNumber As String
Dim strInjuryType1 As String
Dim strInjuryType2 As String

strSQL = "INSERT INTO tblInjuriesperClaimECA([ClaimNumber],
[InjuryType1],[InjuryType2])VALUES (strClaimNumber &
strInjuryType1 & strInjuryType2);"

CurrentDb.Execute strSQL

From what I see, the number of values are the same, so I
don't know why that error message is coming up. The field
types are also the same. Any ideas?

Thank you for your help

 

[Patrick]

HI!!
Try replacing the "&" in the following sentence
VALUES(strClaimNumber & strInjuryType1 & strInjuryType2)

by "," like this:
VALUES (strClaimNumber ,strInjuryType1 , strInjuryType2)

This should take care of the problem..
PAtrick

 

[Cheryl Fischer]

Tom,

Using the Ampersand in (VALUES (strClaimNumber & strInjuryType1 &
strInjuryType2)) has turned your three values into one by contatenation.
Try running your SQL as follows:

strSQL = "INSERT INTO tblInjuriesperClaimECA([ClaimNumber],
[InjuryType1],[InjuryType2])VALUES (strClaimNumber,
strInjuryType1, strInjuryType2);"

hth,

 

[Roger Carlson]

You are trying to assign the literal string "strClaimNumber" to the field,
NOT the value held in the variable. To see what is really happening, put
the following:

Debug.Print strSQL

before the execute line. It will display what Access really sees.
To correct it, you have to do something like this:

strSQL = "INSERT INTO tblInjuriesperClaimECA([ClaimNumber],
[InjuryType1],[InjuryType2])VALUES (" & strClaimNumber & "," &
strInjuryType1 & "," & strInjuryType2 & ");"

This assumes all values are numbers. If any are text, you have to delimit
the values with apostrohes or TWO quotes. Example where InjuryType1 and
InjuryType2 are text:

strSQL = "INSERT INTO tblInjuriesperClaimECA([ClaimNumber],
[InjuryType1],[InjuryType2])VALUES (" & strClaimNumber & ",'" &
strInjuryType1 & "','" & strInjuryType2 & "');"

 

[Tom]

Thanks for your reply, I changed the sql statement to
what you wrote below (by the way they are text fields) and
now I'm getting the following message:

"Runtime error '3061'. Too few parameters. Expected 1"

Why is it only expecting one. Here is my new sql
statement:

strSQL = "INSERT INTO tblInjuriesperClaimECA([ClaimNumber],
[InjuryType1],[InjuryType2])VALUES (" & strClaimNumber
& ",'" & strInjuryType1 & "','" & strInjuryType2 & "');"

Thanks again for your help.

-----Original Message-----
You are trying to assign the literal

string "strClaimNumber" to the field,

NOT the value held in the variable. To see what is really happening, put
the following:

Debug.Print strSQL

before the execute line. It will display what Access really sees.
To correct it, you have to do something like this:

strSQL = "INSERT INTO tblInjuriesperClaimECA ([ClaimNumber],
[InjuryType1],[InjuryType2])VALUES (" & strClaimNumber & "," &
strInjuryType1 & "," & strInjuryType2 & ");"

This assumes all values are numbers. If any are text, you have to delimit
the values with apostrohes or TWO quotes. Example where InjuryType1 and
InjuryType2 are text:

strSQL = "INSERT INTO tblInjuriesperClaimECA ([ClaimNumber],
[InjuryType1],[InjuryType2])VALUES (" & strClaimNumber & ",'" &
strInjuryType1 & "','" & strInjuryType2 & "');"

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

I am recieving the following runtime error message when I
run the code below: "The number of query values and
destination fields are not the same".

Dim strSQL As String
Dim strClaimNumber As String
Dim strInjuryType1 As String
Dim strInjuryType2 As String

CurrentDb.Execute strSQL

From what I see, the number of values are the same, so I
don't know why that error message is coming up. The field
types are also the same. Any ideas?

Thank you for your help

.

 

[Tom]

Thanks for your reply Cheryl,

When I replaced the Ampersand with "," , I now get the
following error message:

"Runtime error "3061". Too few parameters. Expected 3."

Why is not finding the three parameters?

Thanks again.

-----Original Message-----
Tom,

Using the Ampersand in (VALUES (strClaimNumber & strInjuryType1 &
strInjuryType2)) has turned your three values into one by contatenation.
Try running your SQL as follows:

strSQL = "INSERT INTO tblInjuriesperClaimECA ([ClaimNumber],
[InjuryType1],[InjuryType2])VALUES (strClaimNumber,
strInjuryType1, strInjuryType2);"

hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

I am recieving the following runtime error message when I
run the code below: "The number of query values and
destination fields are not the same".

Dim strSQL As String
Dim strClaimNumber As String
Dim strInjuryType1 As String
Dim strInjuryType2 As String

strSQL = "INSERT INTO tblInjuriesperClaimECA ([ClaimNumber],
[InjuryType1],[InjuryType2])VALUES (strClaimNumber &
strInjuryType1 & strInjuryType2);"

CurrentDb.Execute strSQL

From what I see, the number of values are the same, so I
don't know why that error message is coming up. The field
types are also the same. Any ideas?

Thank you for your help

.

 

[Cheryl Fischer]

Roger's explanation and solution are right on.

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

Thanks for your reply Cheryl,

When I replaced the Ampersand with "," , I now get the
following error message:

"Runtime error "3061". Too few parameters. Expected 3."

Why is not finding the three parameters?

Thanks again.

-----Original Message-----
Tom,

Using the Ampersand in (VALUES (strClaimNumber & strInjuryType1 &
strInjuryType2)) has turned your three values into one by contatenation.
Try running your SQL as follows:

strSQL = "INSERT INTO tblInjuriesperClaimECA ([ClaimNumber],
[InjuryType1],[InjuryType2])VALUES (strClaimNumber,
strInjuryType1, strInjuryType2);"

hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

I am recieving the following runtime error message when I
run the code below: "The number of query values and
destination fields are not the same".

Dim strSQL As String
Dim strClaimNumber As String
Dim strInjuryType1 As String
Dim strInjuryType2 As String

strSQL = "INSERT INTO tblInjuriesperClaimECA ([ClaimNumber],
[InjuryType1],[InjuryType2])VALUES (strClaimNumber &
strInjuryType1 & strInjuryType2);"

CurrentDb.Execute strSQL

From what I see, the number of values are the same, so I
don't know why that error message is coming up. The field
types are also the same. Any ideas?

Thank you for your help

.

 

[Roger Carlson]

Are you certain you have spelled all of your field and variable names
correctly? This error message can also result from misspelling fields.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Thanks for your reply, I changed the sql statement to
what you wrote below (by the way they are text fields) and
now I'm getting the following message:

"Runtime error '3061'. Too few parameters. Expected 1"

Why is it only expecting one. Here is my new sql
statement:

strSQL = "INSERT INTO tblInjuriesperClaimECA([ClaimNumber],
[InjuryType1],[InjuryType2])VALUES (" & strClaimNumber
& ",'" & strInjuryType1 & "','" & strInjuryType2 & "');"

Thanks again for your help.

-----Original Message-----
You are trying to assign the literal

string "strClaimNumber" to the field,

NOT the value held in the variable. To see what is really happening, put
the following:

Debug.Print strSQL

before the execute line. It will display what Access really sees.
To correct it, you have to do something like this:

strSQL = "INSERT INTO tblInjuriesperClaimECA ([ClaimNumber],
[InjuryType1],[InjuryType2])VALUES (" & strClaimNumber & "," &
strInjuryType1 & "," & strInjuryType2 & ");"

This assumes all values are numbers. If any are text, you have to delimit
the values with apostrohes or TWO quotes. Example where InjuryType1 and
InjuryType2 are text:

strSQL = "INSERT INTO tblInjuriesperClaimECA ([ClaimNumber],
[InjuryType1],[InjuryType2])VALUES (" & strClaimNumber & ",'" &
strInjuryType1 & "','" & strInjuryType2 & "');"

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

I am recieving the following runtime error message when I
run the code below: "The number of query values and
destination fields are not the same".

Dim strSQL As String
Dim strClaimNumber As String
Dim strInjuryType1 As String
Dim strInjuryType2 As String


CurrentDb.Execute strSQL

From what I see, the number of values are the same, so I
don't know why that error message is coming up. The field
types are also the same. Any ideas?

Thank you for your help

.

 

[Roger Carlson]

Sorry, forget the last post.

If they are ALL text fields, then it would look like this:
strSQL = "INSERT INTO tblInjuriesperClaimECA([ClaimNumber],
[InjuryType1],[InjuryType2])VALUES ('" & strClaimNumber
& "','" & strInjuryType1 & "','" & strInjuryType2 & "');"

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Thanks for your reply, I changed the sql statement to
what you wrote below (by the way they are text fields) and
now I'm getting the following message:

"Runtime error '3061'. Too few parameters. Expected 1"

Why is it only expecting one. Here is my new sql
statement:

strSQL = "INSERT INTO tblInjuriesperClaimECA([ClaimNumber],
[InjuryType1],[InjuryType2])VALUES (" & strClaimNumber
& ",'" & strInjuryType1 & "','" & strInjuryType2 & "');"

Thanks again for your help.

-----Original Message-----
You are trying to assign the literal

string "strClaimNumber" to the field,

NOT the value held in the variable. To see what is really happening, put
the following:

Debug.Print strSQL

before the execute line. It will display what Access really sees.
To correct it, you have to do something like this:

strSQL = "INSERT INTO tblInjuriesperClaimECA ([ClaimNumber],
[InjuryType1],[InjuryType2])VALUES (" & strClaimNumber & "," &
strInjuryType1 & "," & strInjuryType2 & ");"

This assumes all values are numbers. If any are text, you have to delimit
the values with apostrohes or TWO quotes. Example where InjuryType1 and
InjuryType2 are text:

strSQL = "INSERT INTO tblInjuriesperClaimECA ([ClaimNumber],
[InjuryType1],[InjuryType2])VALUES (" & strClaimNumber & ",'" &
strInjuryType1 & "','" & strInjuryType2 & "');"

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

I am recieving the following runtime error message when I
run the code below: "The number of query values and
destination fields are not the same".

Dim strSQL As String
Dim strClaimNumber As String
Dim strInjuryType1 As String
Dim strInjuryType2 As String


CurrentDb.Execute strSQL

From what I see, the number of values are the same, so I
don't know why that error message is coming up. The field
types are also the same. Any ideas?

Thank you for your help

.