Too few parameters. Expected 1.

[Jarryd]

Hi,

Does anyone know what is wrong with the following query?:

db.Execute ("INSERT INTO BIll ([bill].[pickup_dt], Inv_num,
[bill].[Inv_dt], ship_num, ship_ref_1, net_chg, fuel_net_chg, VAT,
Consignee_Name, Consignee_City, ship_ref_2)" _
& "VALUES( #" & pck_dt & "#, '" & Inv_num & "', #" & inv_dt & "#, '" &
ship_num & "', '" & ship_r_1 & "', " & nchg & ", " & nfuel & ", " & VAT & ",
" & con_nm & ", " & con_cty & ", '" & dept & "');")

I am getting this error: Too few parameters. Expected 1.

I have copied the the field names out of a select query that I created in
the query builder (to avoid type-o's). I am sure it is something simple. I
just not spotting it.

TIA,

Jarryd

 

[TC]

Jarryd, it's hard to wade through actual code, when there's a simpler
way to see what's happening. Instead of putting the SQL string directly
into the Execute call, store it into a string variable first. Then you
can print it to the debug window, which usually makes it easier to see
what is actually being executed:

dim s as string
s = "INSERT INTO etc. etc. etc.
debug.print s ' <==
db.execute s

Then look at the outpout in the debug window.

My bet is, a string quote error

HTH,
TC [MVP Access]

 

[Jarryd]

Hi TC,

I did that.

Dim s As String
s = ("INSERT INTO BIll ([bill].[pickup_dt], Inv_num, [bill].[Inv_dt],
ship_num, ship_ref_1, net_chg, fuel_net_chg, VAT, Consignee_Name,
Consignee_City, ship_ref_2)" _
& "VALUES( #" & pck_dt & "#, '" & Inv_num & "', #" & inv_dt & "#, '" &
ship_num & "', '" & ship_r_1 & "', " & nchg & ", " & nfuel & ", " & VAT & ",
" & con_nm & ", " & con_cty & ", '" & dept & "');")
Debug.Print s '<==

It didn't do anything... at all. What does it mean?

TIA,

Jarryd

 

[BruceM]

I don't think there should be a parentheses before the first double quote or
after the last one. If you are adding fields to the string you need to
insert a double quote to end the literal text before you insert the field
name. If you are adding two fields to the string you need to concatenate
them together:
Me.ship_num & " " & Me.ship_ref will give you the field values for ship_num
and ship_ref with a space between them, assuming that ship_num and ship_ref
are fields in the record source.
Which brings me to something that puzzles me: you said this code is part of
a query, but the string would seem to be part of VBA code, which means it is
associated with a form or report. How does the object in which the code
appears fit into the picture? Where in the query were you trying to place
the code originally?
Also, there are some single quotes that I take it are supposed to be part of
the string, but they could be confounding things. When I need a literal
quote or apostrophe to appear I tend to use chr(34) and chr(39):

"#, " & chr(39) should give you a number sign, a comma, a space, and an
apostrophe.

Remember, that is only if you need a quote mark or apostrophe in the text
string, not for the quotes marks that surround literal text values.

Hi TC,

I did that.

Dim s As String
s = ("INSERT INTO BIll ([bill].[pickup_dt], Inv_num, [bill].[Inv_dt],
ship_num, ship_ref_1, net_chg, fuel_net_chg, VAT, Consignee_Name,
Consignee_City, ship_ref_2)" _
& "VALUES( #" & pck_dt & "#, '" & Inv_num & "', #" & inv_dt & "#, '" &
ship_num & "', '" & ship_r_1 & "', " & nchg & ", " & nfuel & ", " & VAT &
", " & con_nm & ", " & con_cty & ", '" & dept & "');")
Debug.Print s '<==

It didn't do anything... at all. What does it mean?

TIA,

Jarryd

Jarryd, it's hard to wade through actual code, when there's a simpler
way to see what's happening. Instead of putting the SQL string directly
into the Execute call, store it into a string variable first. Then you
can print it to the debug window, which usually makes it easier to see
what is actually being executed:

dim s as string
s = "INSERT INTO etc. etc. etc.
debug.print s ' <==
db.execute s

Then look at the outpout in the debug window.

My bet is, a string quote error

HTH,
TC [MVP Access]

 

[Guest]

Using single qoutes inside double qoutes for SQL strings is perfectly valid.
The parentheses are for the Execute() method, also valid.
Basically, I don't see an syntax error. The parameter error in this case
means that one of the fields identified in the VALUES list either does not
exist or is misspelled. I am not positive there is no syntax error, but I
don't see it.

I don't think there should be a parentheses before the first double quote or
after the last one. If you are adding fields to the string you need to
insert a double quote to end the literal text before you insert the field
name. If you are adding two fields to the string you need to concatenate
them together:
Me.ship_num & " " & Me.ship_ref will give you the field values for ship_num
and ship_ref with a space between them, assuming that ship_num and ship_ref
are fields in the record source.
Which brings me to something that puzzles me: you said this code is part of
a query, but the string would seem to be part of VBA code, which means it is
associated with a form or report. How does the object in which the code
appears fit into the picture? Where in the query were you trying to place
the code originally?
Also, there are some single quotes that I take it are supposed to be part of
the string, but they could be confounding things. When I need a literal
quote or apostrophe to appear I tend to use chr(34) and chr(39):

"#, " & chr(39) should give you a number sign, a comma, a space, and an
apostrophe.

Remember, that is only if you need a quote mark or apostrophe in the text
string, not for the quotes marks that surround literal text values.

Hi TC,

I did that.

Dim s As String
s = ("INSERT INTO BIll ([bill].[pickup_dt], Inv_num, [bill].[Inv_dt],
ship_num, ship_ref_1, net_chg, fuel_net_chg, VAT, Consignee_Name,
Consignee_City, ship_ref_2)" _
& "VALUES( #" & pck_dt & "#, '" & Inv_num & "', #" & inv_dt & "#, '" &
ship_num & "', '" & ship_r_1 & "', " & nchg & ", " & nfuel & ", " & VAT &
", " & con_nm & ", " & con_cty & ", '" & dept & "');")
Debug.Print s '<==

It didn't do anything... at all. What does it mean?

TIA,

Jarryd

Jarryd, it's hard to wade through actual code, when there's a simpler
way to see what's happening. Instead of putting the SQL string directly
into the Execute call, store it into a string variable first. Then you
can print it to the debug window, which usually makes it easier to see
what is actually being executed:

dim s as string
s = "INSERT INTO etc. etc. etc.
debug.print s ' <==
db.execute s

Then look at the outpout in the debug window.

My bet is, a string quote error

HTH,
TC [MVP Access]

 

[BruceM]

I realize that single quotes inside double quotes are valid, but I also know
I have had problems getting them to work as expected. It's difficult enough
for me to figure out three or four double quotes strung together without
compounding the confusion by trying to add a literal quote.
As for the parentheses, s is defined as a string, but the string starts with
a parentheses. I would have thought that if the string is being used for
the Execute method, the string needs to start with a quote and the
parentheses be left out. I freely admit I am unfamiliar with the Execute
method (and could find no help whatever in Help), but a string starting with
a parentheses just didn't look right to me. I would have expected the
parentheses to be part of the Execute method, and the string to replace what
comes between the parentheses.

Perhaps if I had a clearer idea of what was being attempted I would have
either avoided jumping in or would have provided something less speculative.
But thanks for your observations. I am learning a bit more each day.

Using single qoutes inside double qoutes for SQL strings is perfectly
valid.
The parentheses are for the Execute() method, also valid.
Basically, I don't see an syntax error. The parameter error in this case
means that one of the fields identified in the VALUES list either does not
exist or is misspelled. I am not positive there is no syntax error, but I
don't see it.

I don't think there should be a parentheses before the first double quote
or
after the last one. If you are adding fields to the string you need to
insert a double quote to end the literal text before you insert the field
name. If you are adding two fields to the string you need to concatenate
them together:
Me.ship_num & " " & Me.ship_ref will give you the field values for
ship_num
and ship_ref with a space between them, assuming that ship_num and
ship_ref
are fields in the record source.
Which brings me to something that puzzles me: you said this code is part
of
a query, but the string would seem to be part of VBA code, which means it
is
associated with a form or report. How does the object in which the code
appears fit into the picture? Where in the query were you trying to
place
the code originally?
Also, there are some single quotes that I take it are supposed to be part
of
the string, but they could be confounding things. When I need a literal
quote or apostrophe to appear I tend to use chr(34) and chr(39):

"#, " & chr(39) should give you a number sign, a comma, a space, and an
apostrophe.

Remember, that is only if you need a quote mark or apostrophe in the text
string, not for the quotes marks that surround literal text values.

Hi TC,

I did that.

Dim s As String
s = ("INSERT INTO BIll ([bill].[pickup_dt], Inv_num, [bill].[Inv_dt],
ship_num, ship_ref_1, net_chg, fuel_net_chg, VAT, Consignee_Name,
Consignee_City, ship_ref_2)" _
& "VALUES( #" & pck_dt & "#, '" & Inv_num & "', #" & inv_dt & "#, '" &
ship_num & "', '" & ship_r_1 & "', " & nchg & ", " & nfuel & ", " & VAT
&
", " & con_nm & ", " & con_cty & ", '" & dept & "');")
Debug.Print s '<==

It didn't do anything... at all. What does it mean?

TIA,

Jarryd
Jarryd, it's hard to wade through actual code, when there's a simpler
way to see what's happening. Instead of putting the SQL string
directly
into the Execute call, store it into a string variable first. Then you
can print it to the debug window, which usually makes it easier to see
what is actually being executed:

dim s as string
s = "INSERT INTO etc. etc. etc.
debug.print s ' <==
db.execute s

Then look at the outpout in the debug window.

My bet is, a string quote error

HTH,
TC [MVP Access]

 

[Guest]

I see your point about the s. I was looking at the original post, not the
follow up.
The parentheses are not necessary, but it still works. I tried it in the
immediate window.

I think I found the problem and it is parentheses. This one is unmatched:
Consignee_Name, Consignee_City, ship_ref_2)"

As to the single quotes, we will have to agree it is a matter of style. I
find
"SELECT * FROM tblSomething WHERE SOME_FIELD = '" & Me.txtSomeValue & "';"
much easier to read than
"SELECT * FROM tblSomething WHERE SOME_FIELD = '''" & Me.txtSomeValue & "''';"
I have a hard time even counting how many quotes are there much less knowing
whether 3 or 4 is the correct number to use.

I realize that single quotes inside double quotes are valid, but I also know
I have had problems getting them to work as expected. It's difficult enough
for me to figure out three or four double quotes strung together without
compounding the confusion by trying to add a literal quote.
As for the parentheses, s is defined as a string, but the string starts with
a parentheses. I would have thought that if the string is being used for
the Execute method, the string needs to start with a quote and the
parentheses be left out. I freely admit I am unfamiliar with the Execute
method (and could find no help whatever in Help), but a string starting with
a parentheses just didn't look right to me. I would have expected the
parentheses to be part of the Execute method, and the string to replace what
comes between the parentheses.

Perhaps if I had a clearer idea of what was being attempted I would have
either avoided jumping in or would have provided something less speculative.
But thanks for your observations. I am learning a bit more each day.

Using single qoutes inside double qoutes for SQL strings is perfectly
valid.
The parentheses are for the Execute() method, also valid.
Basically, I don't see an syntax error. The parameter error in this case
means that one of the fields identified in the VALUES list either does not
exist or is misspelled. I am not positive there is no syntax error, but I
don't see it.

I don't think there should be a parentheses before the first double quote
or
after the last one. If you are adding fields to the string you need to
insert a double quote to end the literal text before you insert the field
name. If you are adding two fields to the string you need to concatenate
them together:
Me.ship_num & " " & Me.ship_ref will give you the field values for
ship_num
and ship_ref with a space between them, assuming that ship_num and
ship_ref
are fields in the record source.
Which brings me to something that puzzles me: you said this code is part
of
a query, but the string would seem to be part of VBA code, which means it
is
associated with a form or report. How does the object in which the code
appears fit into the picture? Where in the query were you trying to
place
the code originally?
Also, there are some single quotes that I take it are supposed to be part
of
the string, but they could be confounding things. When I need a literal
quote or apostrophe to appear I tend to use chr(34) and chr(39):

"#, " & chr(39) should give you a number sign, a comma, a space, and an
apostrophe.

Remember, that is only if you need a quote mark or apostrophe in the text
string, not for the quotes marks that surround literal text values.



Hi TC,

I did that.

Dim s As String
s = ("INSERT INTO BIll ([bill].[pickup_dt], Inv_num, [bill].[Inv_dt],
ship_num, ship_ref_1, net_chg, fuel_net_chg, VAT, Consignee_Name,
Consignee_City, ship_ref_2)" _
& "VALUES( #" & pck_dt & "#, '" & Inv_num & "', #" & inv_dt & "#, '" &
ship_num & "', '" & ship_r_1 & "', " & nchg & ", " & nfuel & ", " & VAT
&
", " & con_nm & ", " & con_cty & ", '" & dept & "');")
Debug.Print s '<==

It didn't do anything... at all. What does it mean?

TIA,

Jarryd
Jarryd, it's hard to wade through actual code, when there's a simpler
way to see what's happening. Instead of putting the SQL string
directly
into the Execute call, store it into a string variable first. Then you
can print it to the debug window, which usually makes it easier to see
what is actually being executed:

dim s as string
s = "INSERT INTO etc. etc. etc.
debug.print s ' <==
db.execute s

Then look at the outpout in the debug window.

My bet is, a string quote error

HTH,
TC [MVP Access]

 

[BruceM]

I agree that the first SELECT is easier to read, but I still don't quite get
the whole business with quotes. I don't know which double quote goes with
the one before SELECT (assuming they need to be in pairs). For that matter,
I'm not quite sure which is the apostrophe and which is the double quote. I
was under the impression that the closing quote that goes with the one
before SELECT is the first double quote that is not preceded by an
apostrophe, but even if I am correct I still don't quite get it. Then there
is the whole business of text fields and number fields using a different
arrangement of quotes. I need to study VBA more thoroughly. So far it has
been rather piecemeal. My point about chr(34) or chr(39), however, was just
that if I need a double or single quote within a text string I find it
easier to use chr() then a string of quote marks.
Thanks again for the reply. If you could shed any more light on my
confusion about quotes, I would really appreciate it.

I see your point about the s. I was looking at the original post, not the
follow up.
The parentheses are not necessary, but it still works. I tried it in the
immediate window.

I think I found the problem and it is parentheses. This one is unmatched:
Consignee_Name, Consignee_City, ship_ref_2)"

As to the single quotes, we will have to agree it is a matter of style. I
find
"SELECT * FROM tblSomething WHERE SOME_FIELD = '" & Me.txtSomeValue & "';"
much easier to read than
"SELECT * FROM tblSomething WHERE SOME_FIELD = '''" & Me.txtSomeValue &
"''';"
I have a hard time even counting how many quotes are there much less
knowing
whether 3 or 4 is the correct number to use.

I realize that single quotes inside double quotes are valid, but I also
know
I have had problems getting them to work as expected. It's difficult
enough
for me to figure out three or four double quotes strung together without
compounding the confusion by trying to add a literal quote.
As for the parentheses, s is defined as a string, but the string starts
with
a parentheses. I would have thought that if the string is being used for
the Execute method, the string needs to start with a quote and the
parentheses be left out. I freely admit I am unfamiliar with the Execute
method (and could find no help whatever in Help), but a string starting
with
a parentheses just didn't look right to me. I would have expected the
parentheses to be part of the Execute method, and the string to replace
what
comes between the parentheses.

Perhaps if I had a clearer idea of what was being attempted I would have
either avoided jumping in or would have provided something less
speculative.
But thanks for your observations. I am learning a bit more each day.

Using single qoutes inside double qoutes for SQL strings is perfectly
valid.
The parentheses are for the Execute() method, also valid.
Basically, I don't see an syntax error. The parameter error in this
case
means that one of the fields identified in the VALUES list either does
not
exist or is misspelled. I am not positive there is no syntax error,
but I
don't see it.

:

I don't think there should be a parentheses before the first double
quote
or
after the last one. If you are adding fields to the string you need
to
insert a double quote to end the literal text before you insert the
field
name. If you are adding two fields to the string you need to
concatenate
them together:
Me.ship_num & " " & Me.ship_ref will give you the field values for
ship_num
and ship_ref with a space between them, assuming that ship_num and
ship_ref
are fields in the record source.
Which brings me to something that puzzles me: you said this code is
part
of
a query, but the string would seem to be part of VBA code, which means
it
is
associated with a form or report. How does the object in which the
code
appears fit into the picture? Where in the query were you trying to
place
the code originally?
Also, there are some single quotes that I take it are supposed to be
part
of
the string, but they could be confounding things. When I need a
literal
quote or apostrophe to appear I tend to use chr(34) and chr(39):

"#, " & chr(39) should give you a number sign, a comma, a space, and
an
apostrophe.

Remember, that is only if you need a quote mark or apostrophe in the
text
string, not for the quotes marks that surround literal text values.



Hi TC,

I did that.

Dim s As String
s = ("INSERT INTO BIll ([bill].[pickup_dt], Inv_num,
[bill].[Inv_dt],
ship_num, ship_ref_1, net_chg, fuel_net_chg, VAT, Consignee_Name,
Consignee_City, ship_ref_2)" _
& "VALUES( #" & pck_dt & "#, '" & Inv_num & "', #" & inv_dt & "#, '"
&
ship_num & "', '" & ship_r_1 & "', " & nchg & ", " & nfuel & ", " &
VAT
&
", " & con_nm & ", " & con_cty & ", '" & dept & "');")
Debug.Print s '<==

It didn't do anything... at all. What does it mean?

TIA,

Jarryd
Jarryd, it's hard to wade through actual code, when there's a
simpler
way to see what's happening. Instead of putting the SQL string
directly
into the Execute call, store it into a string variable first. Then
you
can print it to the debug window, which usually makes it easier to
see
what is actually being executed:

dim s as string
s = "INSERT INTO etc. etc. etc.
debug.print s ' <==
db.execute s

Then look at the outpout in the debug window.

My bet is, a string quote error

HTH,
TC [MVP Access]

 

[Graham Mandeno]

Hi Jarryd

The error means that there is some text in your SQL string which is being
interpreted by SQL as a field name, and since that field does not exist it
figures it must be a parameter.

Do as TC suggests, and check out what is printed in the Immediate window
just below your code (you can also set a breakpoint on the db.Execute line,
and type:
?s
in the Immediate window when the code stops at the breakpoint.

You may not be able to see easily what the problem is, so you can select and
copy the SQL string from the Immediate window and paste it into the SQL
window of a new query. Then try to run the query and it will prompt you for
a value for the offending "parameter".

Some other things to check:

1. The way you have written this SQL code, the only text field is
ship_ref_2. Is this correct? It looks to me from the field names that
Consignee_Name and Consignee_City are also text fields, in which case their
values should be enclosed in quotes.

2. The fact you have a field named VAT suggests you might be from the UK and
so you would not use North American date formats (mm/dd/yyyy). VBA will
always convert a date to a string using your Windows regional settings, but
SQL understands only North American date formats. Therefore, you must
explicitly specify the format when you include a date in a SQL string. For
example:
Format( pck_dt, "\#mm\/dd\/yyyy\#" )
Note that this format includes the # delimiters as well, and the buit that
looks like a "V" is a back-slash followed by a forward-slash.

 

[TC]

Hi Jarryd, I ran into my posting limit & could not reply.

Run the code, then press Ctrl-g to display the so-called "debug
window". In that window, you'll see the result of the debug.print
statement. This will show you the actual sql that you tried to execute.
I'm somewhat surprised that no-one else jumped in, to explain this!

You'll probably find it easier to find the error in your code, by
looking at the sql statement that was generated by your code - rather
than by looking at the code itself. If you can't see the error, in the
debug window output, just cust & paste that output into a new post in
thread, & someone else will see it for you.

Cheers,
TC [MVP Access]

 

[TC]

I don't think there should be a parentheses before the first double quote or
after the last one.

That just changes the parameter from "call by refrerence", to "call by
value". In some circumstances, that would cause a problem. Here, it
doesn't.

dim v
msgbox v ' <- call by reference
call msgbox (v) ' <- call by reference
msgbox (v) ' <- call by value

I don't recall if there is much on this in VBA help. But you would
certainly find tons of explanations of those two phrases, on the web.
They are two of the three different ways that parameters can be passed
to subroutines (or methods or whatever) in any programming language.
(The third method being "call by name".)

HTH,
TC [MVP Access]

 

[BruceM]

Thanks for the comments. There is little to nothing in VBA help (shocking,
no?), so I will study the matter further on the web. I thought that a
string is always started by a double quote. It's news to me that it can
start with a parentheses. Clearly there is even more to learn than I had
thought.

I don't think there should be a parentheses before the first double quote
or
after the last one.

That just changes the parameter from "call by refrerence", to "call by
value". In some circumstances, that would cause a problem. Here, it
doesn't.

dim v
msgbox v ' <- call by reference
call msgbox (v) ' <- call by reference
msgbox (v) ' <- call by value

I don't recall if there is much on this in VBA help. But you would
certainly find tons of explanations of those two phrases, on the web.
They are two of the three different ways that parameters can be passed
to subroutines (or methods or whatever) in any programming language.
(The third method being "call by name".)

HTH,
TC [MVP Access]

 

[Douglas J Steele]

The parentheses aren't starting the string. They're surrounding a string
that's properly started and terminated with quotes.

TC's point was that when you're calling a function or sub, you can surround
a parameter with parentheses, and it changes how the parameter is passed to
the routine. As he stated, It shouldn't matter in this particular case.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for the comments. There is little to nothing in VBA help (shocking,
no?), so I will study the matter further on the web. I thought that a
string is always started by a double quote. It's news to me that it can
start with a parentheses. Clearly there is even more to learn than I had
thought.

I don't think there should be a parentheses before the first double quote
or
after the last one.

That just changes the parameter from "call by refrerence", to "call by
value". In some circumstances, that would cause a problem. Here, it
doesn't.

dim v
msgbox v ' <- call by reference
call msgbox (v) ' <- call by reference
msgbox (v) ' <- call by value

I don't recall if there is much on this in VBA help. But you would
certainly find tons of explanations of those two phrases, on the web.
They are two of the three different ways that parameters can be passed
to subroutines (or methods or whatever) in any programming language.
(The third method being "call by name".)

HTH,
TC [MVP Access]

 

[Guest]

All text values have to be enclosed in quotes when passed to SQL. The
variable, control, or field names cannot be. If you say:
"SELECT * FROM SomeTable WHERE SomeField = Me.txtFoo;"
Jet will look for a numeric value and of course will get an error because
the literal string "Me.txtFoo" can't be evaluated to a number.
If you say:
"SELECT * FROM SomeTable WHERE SomeField = 'Me.txtFoo';"
Jet will look for the literal text value of "Me.txtFoo" and will not find it.
To pass the value in Me.txtFoo, it has to be included in the string, but
outside the quotes for VBA to evaluate it.
"SELECT * FROM SomeTable WHERE SomeField = '" & Me.txtFoo & "';"
It would be the same as
"SELECT * FROM SomeTable WHERE SomeField = " & chr(34) & Me.txtFoo & _
chr(34) &";"
And the same as
"SELECT * FROM SomeTable WHERE SomeField = """ & Me.txtFoo & """;"

To see the result, try all three in the immediate window. The result will be:
SELECT * FROM SomeTable WHERE SomeField = "Bonzo";

That is what Jet will see.

So, the rule is to include a quote in a string, you use "". The third quote
is to terminate the string.

And
Text values must be enclosed with quotes
Numberic values must not be enclosed in anything
Date/Time values must be enclosed with #

I agree that the first SELECT is easier to read, but I still don't quite get
the whole business with quotes. I don't know which double quote goes with
the one before SELECT (assuming they need to be in pairs). For that matter,
I'm not quite sure which is the apostrophe and which is the double quote. I
was under the impression that the closing quote that goes with the one
before SELECT is the first double quote that is not preceded by an
apostrophe, but even if I am correct I still don't quite get it. Then there
is the whole business of text fields and number fields using a different
arrangement of quotes. I need to study VBA more thoroughly. So far it has
been rather piecemeal. My point about chr(34) or chr(39), however, was just
that if I need a double or single quote within a text string I find it
easier to use chr() then a string of quote marks.
Thanks again for the reply. If you could shed any more light on my
confusion about quotes, I would really appreciate it.

I see your point about the s. I was looking at the original post, not the
follow up.
The parentheses are not necessary, but it still works. I tried it in the
immediate window.

I think I found the problem and it is parentheses. This one is unmatched:
Consignee_Name, Consignee_City, ship_ref_2)"

As to the single quotes, we will have to agree it is a matter of style. I
find
"SELECT * FROM tblSomething WHERE SOME_FIELD = '" & Me.txtSomeValue & "';"
much easier to read than
"SELECT * FROM tblSomething WHERE SOME_FIELD = '''" & Me.txtSomeValue &
"''';"
I have a hard time even counting how many quotes are there much less
knowing
whether 3 or 4 is the correct number to use.

I realize that single quotes inside double quotes are valid, but I also
know
I have had problems getting them to work as expected. It's difficult
enough
for me to figure out three or four double quotes strung together without
compounding the confusion by trying to add a literal quote.
As for the parentheses, s is defined as a string, but the string starts
with
a parentheses. I would have thought that if the string is being used for
the Execute method, the string needs to start with a quote and the
parentheses be left out. I freely admit I am unfamiliar with the Execute
method (and could find no help whatever in Help), but a string starting
with
a parentheses just didn't look right to me. I would have expected the
parentheses to be part of the Execute method, and the string to replace
what
comes between the parentheses.

Perhaps if I had a clearer idea of what was being attempted I would have
either avoided jumping in or would have provided something less
speculative.
But thanks for your observations. I am learning a bit more each day.

Using single qoutes inside double qoutes for SQL strings is perfectly
valid.
The parentheses are for the Execute() method, also valid.
Basically, I don't see an syntax error. The parameter error in this
case
means that one of the fields identified in the VALUES list either does
not
exist or is misspelled. I am not positive there is no syntax error,
but I
don't see it.

:

I don't think there should be a parentheses before the first double
quote
or
after the last one. If you are adding fields to the string you need
to
insert a double quote to end the literal text before you insert the
field
name. If you are adding two fields to the string you need to
concatenate
them together:
Me.ship_num & " " & Me.ship_ref will give you the field values for
ship_num
and ship_ref with a space between them, assuming that ship_num and
ship_ref
are fields in the record source.
Which brings me to something that puzzles me: you said this code is
part
of
a query, but the string would seem to be part of VBA code, which means
it
is
associated with a form or report. How does the object in which the
code
appears fit into the picture? Where in the query were you trying to
place
the code originally?
Also, there are some single quotes that I take it are supposed to be
part
of
the string, but they could be confounding things. When I need a
literal
quote or apostrophe to appear I tend to use chr(34) and chr(39):

"#, " & chr(39) should give you a number sign, a comma, a space, and
an
apostrophe.

Remember, that is only if you need a quote mark or apostrophe in the
text
string, not for the quotes marks that surround literal text values.



Hi TC,

I did that.

Dim s As String
s = ("INSERT INTO BIll ([bill].[pickup_dt], Inv_num,
[bill].[Inv_dt],
ship_num, ship_ref_1, net_chg, fuel_net_chg, VAT, Consignee_Name,
Consignee_City, ship_ref_2)" _
& "VALUES( #" & pck_dt & "#, '" & Inv_num & "', #" & inv_dt & "#, '"
&
ship_num & "', '" & ship_r_1 & "', " & nchg & ", " & nfuel & ", " &
VAT
&
", " & con_nm & ", " & con_cty & ", '" & dept & "');")
Debug.Print s '<==

It didn't do anything... at all. What does it mean?

TIA,

Jarryd
Jarryd, it's hard to wade through actual code, when there's a
simpler
way to see what's happening. Instead of putting the SQL string
directly
into the Execute call, store it into a string variable first. Then
you
can print it to the debug window, which usually makes it easier to
see
what is actually being executed:

dim s as string
s = "INSERT INTO etc. etc. etc.
debug.print s ' <==
db.execute s

Then look at the outpout in the debug window.

My bet is, a string quote error

HTH,
TC [MVP Access]

 

[BruceM]

Please stay with me just a bit more here. As I have mentioned, part of the
difficulty with quotes is knowing which are single and which are double. By
using the arrow key I was able to determine which are single and which are
double in your examples. I will separate them with spaces in the first
example:

"SELECT * FROM SomeTable WHERE SomeField = ' " & Me.txtFoo & " ';"

But in your next example you said:

It would be the same as
"SELECT * FROM SomeTable WHERE SomeField = " & chr(34) & Me.txtFoo & _
chr(34) &";"

In that example the double quote comes first, while in the first example the
single quote comes first (in the marks that surround & Me.txtFoo). Was that
your intention? Also, chr(34) is the double quote, and chr(39) the single.
I would not use chr() in a SELECT statement, by the way, but rather in a
text string that is to appear in the body of an e-mail (created with
SendObject) or a message box or something like that where a literal quote
may be part of the text that the user sees in the message.

Considering what you said about why 'Me.txtFoo' would not work, it seems to
me that the single quotes in the first example I referenced above serve to
place "& Me.txtFoo" (which includes a reference to a field) outside of the
quotes but still included in the string. I hope that's right, because it
finally makes sense.
In the third example you gave, the way I am looking at it the first of the
three quotes closes the string that starts with SELECT, then the middle one
opens a new string, which is closed by the middle quote in the second set of
three, and finally the semicolon by itself is enclosed in quotes.
Thanks again for the time you have put into helping me understand this.

All text values have to be enclosed in quotes when passed to SQL. The
variable, control, or field names cannot be. If you say:
"SELECT * FROM SomeTable WHERE SomeField = Me.txtFoo;"
Jet will look for a numeric value and of course will get an error because
the literal string "Me.txtFoo" can't be evaluated to a number.
If you say:
"SELECT * FROM SomeTable WHERE SomeField = 'Me.txtFoo';"
Jet will look for the literal text value of "Me.txtFoo" and will not find
it.
To pass the value in Me.txtFoo, it has to be included in the string, but
outside the quotes for VBA to evaluate it.
"SELECT * FROM SomeTable WHERE SomeField = '" & Me.txtFoo & "';"
It would be the same as
"SELECT * FROM SomeTable WHERE SomeField = " & chr(34) & Me.txtFoo & _
chr(34) &";"
And the same as
"SELECT * FROM SomeTable WHERE SomeField = """ & Me.txtFoo & """;"

To see the result, try all three in the immediate window. The result will
be:
SELECT * FROM SomeTable WHERE SomeField = "Bonzo";

That is what Jet will see.

So, the rule is to include a quote in a string, you use "". The third
quote
is to terminate the string.

And
Text values must be enclosed with quotes
Numberic values must not be enclosed in anything
Date/Time values must be enclosed with #

I agree that the first SELECT is easier to read, but I still don't quite
get
the whole business with quotes. I don't know which double quote goes
with
the one before SELECT (assuming they need to be in pairs). For that
matter,
I'm not quite sure which is the apostrophe and which is the double quote.
I
was under the impression that the closing quote that goes with the one
before SELECT is the first double quote that is not preceded by an
apostrophe, but even if I am correct I still don't quite get it. Then
there
is the whole business of text fields and number fields using a different
arrangement of quotes. I need to study VBA more thoroughly. So far it
has
been rather piecemeal. My point about chr(34) or chr(39), however, was
just
that if I need a double or single quote within a text string I find it
easier to use chr() then a string of quote marks.
Thanks again for the reply. If you could shed any more light on my
confusion about quotes, I would really appreciate it.

I see your point about the s. I was looking at the original post, not
the
follow up.
The parentheses are not necessary, but it still works. I tried it in
the
immediate window.

I think I found the problem and it is parentheses. This one is
unmatched:
Consignee_Name, Consignee_City, ship_ref_2)"

As to the single quotes, we will have to agree it is a matter of style.
I
find
"SELECT * FROM tblSomething WHERE SOME_FIELD = '" & Me.txtSomeValue &
"';"
much easier to read than
"SELECT * FROM tblSomething WHERE SOME_FIELD = '''" & Me.txtSomeValue &
"''';"
I have a hard time even counting how many quotes are there much less
knowing
whether 3 or 4 is the correct number to use.

:

I realize that single quotes inside double quotes are valid, but I
also
know
I have had problems getting them to work as expected. It's difficult
enough
for me to figure out three or four double quotes strung together
without
compounding the confusion by trying to add a literal quote.
As for the parentheses, s is defined as a string, but the string
starts
with
a parentheses. I would have thought that if the string is being used
for
the Execute method, the string needs to start with a quote and the
parentheses be left out. I freely admit I am unfamiliar with the
Execute
method (and could find no help whatever in Help), but a string
starting
with
a parentheses just didn't look right to me. I would have expected the
parentheses to be part of the Execute method, and the string to
replace
what
comes between the parentheses.

Perhaps if I had a clearer idea of what was being attempted I would
have
either avoided jumping in or would have provided something less
speculative.
But thanks for your observations. I am learning a bit more each day.

Using single qoutes inside double qoutes for SQL strings is
perfectly
valid.
The parentheses are for the Execute() method, also valid.
Basically, I don't see an syntax error. The parameter error in this
case
means that one of the fields identified in the VALUES list either
does
not
exist or is misspelled. I am not positive there is no syntax error,
but I
don't see it.

:

I don't think there should be a parentheses before the first double
quote
or
after the last one. If you are adding fields to the string you
need
to
insert a double quote to end the literal text before you insert the
field
name. If you are adding two fields to the string you need to
concatenate
them together:
Me.ship_num & " " & Me.ship_ref will give you the field values for
ship_num
and ship_ref with a space between them, assuming that ship_num and
ship_ref
are fields in the record source.
Which brings me to something that puzzles me: you said this code
is
part
of
a query, but the string would seem to be part of VBA code, which
means
it
is
associated with a form or report. How does the object in which the
code
appears fit into the picture? Where in the query were you trying
to
place
the code originally?
Also, there are some single quotes that I take it are supposed to
be
part
of
the string, but they could be confounding things. When I need a
literal
quote or apostrophe to appear I tend to use chr(34) and chr(39):

"#, " & chr(39) should give you a number sign, a comma, a space,
and
an
apostrophe.

Remember, that is only if you need a quote mark or apostrophe in
the
text
string, not for the quotes marks that surround literal text values.



Hi TC,

I did that.

Dim s As String
s = ("INSERT INTO BIll ([bill].[pickup_dt], Inv_num,
[bill].[Inv_dt],
ship_num, ship_ref_1, net_chg, fuel_net_chg, VAT, Consignee_Name,
Consignee_City, ship_ref_2)" _
& "VALUES( #" & pck_dt & "#, '" & Inv_num & "', #" & inv_dt & "#,
'"
&
ship_num & "', '" & ship_r_1 & "', " & nchg & ", " & nfuel & ", "
&
VAT
&
", " & con_nm & ", " & con_cty & ", '" & dept & "');")
Debug.Print s '<==

It didn't do anything... at all. What does it mean?

TIA,

Jarryd
Jarryd, it's hard to wade through actual code, when there's a
simpler
way to see what's happening. Instead of putting the SQL string
directly
into the Execute call, store it into a string variable first.
Then
you
can print it to the debug window, which usually makes it easier
to
see
what is actually being executed:

dim s as string
s = "INSERT INTO etc. etc. etc.
debug.print s ' <==
db.execute s

Then look at the outpout in the debug window.

My bet is, a string quote error

HTH,
TC [MVP Access]

 

[Douglas J Steele]

In the third example you gave, the way I am looking at it the first of the
three quotes closes the string that starts with SELECT, then the middle one
opens a new string, which is closed by the middle quote in the second set of
three, and finally the semicolon by itself is enclosed in quotes.

FWIW, there's no need to put the semi-colon at the end of the SQL statement.

 

[BruceM]

That's good to know. Is my understanding of this business with quote marks
on the right track?

 

[Douglas J. Steele]

More or less. I'm not sure I follow what you're saying in "In the third
example you gave, the way I am looking at it the first of the three quotes
closes the string that starts with SELECT, then the middle one opens a new
string, which is closed by the middle quote in the second set of three, and
finally the semicolon by itself is enclosed in quotes.".

Looking at it piece by piece,

"SELECT * FROM SomeTable WHERE SomeField = "

is a string.

That's followed by a pair of double quotes (""), which VBA translates to a
single double quote (").

You then concatenate the contents of text box txtFoo to that string.

Finally, you've got """;". The first quote open the string, which consists
of "";. Again, VBA translates the pair of double quotes to a single double
quote. You've then got the semi-colon, and the last quote closes the string.



BTW, there is a slight mistake in Klatuu's post. He says:

"SELECT * FROM SomeTable WHERE SomeField = '" & Me.txtFoo & "';"
It would be the same as
"SELECT * FROM SomeTable WHERE SomeField = " & chr(34) & Me.txtFoo & _
chr(34) &";"
And the same as
"SELECT * FROM SomeTable WHERE SomeField = """ & Me.txtFoo & """;"

To see the result, try all three in the immediate window. The result will
be:
SELECT * FROM SomeTable WHERE SomeField = "Bonzo";

That's not true. Only the last two would result in that string. The first
one would result in

SELECT * FROM SomeTable WHERE SomeField = 'Bonzo';

 

[SteveS]

Hi Jarryd

The error means that there is some text in your SQL string which is being
interpreted by SQL as a field name, and since that field does not exist it
figures it must be a parameter.

Do as TC suggests, and check out what is printed in the Immediate window
just below your code (you can also set a breakpoint on the db.Execute line,
and type:
?s
in the Immediate window when the code stops at the breakpoint.

You may not be able to see easily what the problem is, so you can select and
copy the SQL string from the Immediate window and paste it into the SQL
window of a new query. Then try to run the query and it will prompt you for
a value for the offending "parameter".

Some other things to check:

1. The way you have written this SQL code, the only text field is
ship_ref_2. Is this correct? It looks to me from the field names that
Consignee_Name and Consignee_City are also text fields, in which case their
values should be enclosed in quotes.

2. The fact you have a field named VAT suggests you might be from the UK and
so you would not use North American date formats (mm/dd/yyyy). VBA will
always convert a date to a string using your Windows regional settings, but
SQL understands only North American date formats. Therefore, you must
explicitly specify the format when you include a date in a SQL string. For
example:
Format( pck_dt, "\#mm\/dd\/yyyy\#" )
Note that this format includes the # delimiters as well, and the buit that
looks like a "V" is a back-slash followed by a forward-slash.

FWIW, there should be a space between the close parenthesis and VALUES:

---snip----
ship_ref_2)" _
& "VALUES(
---snip---

In the immediate window, it looks like:

"..... ship_ref_2)VALUES(......."


HTH

 

[BruceM]

Thanks, Douglas. The part I wasn't getting was that VBA translates a pair
of double quotes to one double quote. The part of my remark that starts
with "then the middle one opens a new string..." was a misunderstanding.
Your clarification of the results in Klatuu's post helped. I was trying to
find a way for all of the SELECT statements to be identical, and it wasn't
happening. I will save these messages for reference, and will study the
matter further. Thanks again.