correct code please

[Ken]

I apologise if this is considered a repost, I posted below about the method
of Object... I got a response and spent the past 4 hours trying every
combination to not get that error and the only way is to omit the criteria.
This is the code I have that I think should return one record and is giving
me the error.
CntStr =
"Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=C:\Traffic\trafficdata.mdb"
SqlStr ="SELECT * FROM orders WHERE ((Section)=" & Chr(34) & Me!Section &
Chr(34)& ");"
Cnt.Open CntStr
Rst.Open SqlStr, Cnt

I checked to see if for some reason the unbound form would have the data
file locked and it doesn't, It would actually make me feel better if I got
the same error with no criteria.
I'm about to start bouncing my head off the desk, any help is greatly
appreciated.

 

[Alex Dybenko]

Ken,
if Section is number field - then you don't need to put value in quotes

 

[Ken]

it is text, and I've tried no quotes, single and double quotes.

 

[Brendan Reynolds]

It's the field name. I get the same result if I add a field named 'Section'
to the Orders table in the Northwind database. The error disappears if I
change the field name or put square brackets around it in the SQL string ...

SqlStr = "SELECT * FROM orders WHERE ((Orders.[Section])='123.45');"

'Section' must be a reserved word.

 

[Brendan Reynolds]

Yep, it's a reserved word alright. See under 'S' at the following URL ...

http://support.microsoft.com/default.aspx?scid=kb;en-us;321266

--
Brendan Reynolds
Access MVP

It's the field name. I get the same result if I add a field named
'Section' to the Orders table in the Northwind database. The error
disappears if I change the field name or put square brackets around it in
the SQL string ...

SqlStr = "SELECT * FROM orders WHERE ((Orders.[Section])='123.45');"

'Section' must be a reserved word.

--
Brendan Reynolds
Access MVP

it is text, and I've tried no quotes, single and double quotes.

 

[Ken]

Thank you sooooo much, I wish you could have been part of the dance around
the office.

It's the field name. I get the same result if I add a field named 'Section'
to the Orders table in the Northwind database. The error disappears if I
change the field name or put square brackets around it in the SQL string ...

SqlStr = "SELECT * FROM orders WHERE ((Orders.[Section])='123.45');"

'Section' must be a reserved word.

--
Brendan Reynolds
Access MVP

it is text, and I've tried no quotes, single and double quotes.

 

[Ken Snell \(MVP\)]

To add to Brendan's correct information, here are three KB articles that
tell of reserved words and symbols:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763

--

Ken Snell
<MS ACCESS MVP>

Yep, it's a reserved word alright. See under 'S' at the following URL ...

http://support.microsoft.com/default.aspx?scid=kb;en-us;321266

--
Brendan Reynolds
Access MVP

It's the field name. I get the same result if I add a field named
'Section' to the Orders table in the Northwind database. The error
disappears if I change the field name or put square brackets around it in
the SQL string ...

SqlStr = "SELECT * FROM orders WHERE ((Orders.[Section])='123.45');"

'Section' must be a reserved word.

--
Brendan Reynolds
Access MVP

it is text, and I've tried no quotes, single and double quotes.

:

Ken,
if Section is number field - then you don't need to put value in quotes

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


I apologise if this is considered a repost, I posted below about the
method
of Object... I got a response and spent the past 4 hours trying every
combination to not get that error and the only way is to omit the
criteria.
This is the code I have that I think should return one record and is
giving
me the error.
CntStr =
"Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=C:\Traffic\trafficdata.mdb"
SqlStr ="SELECT * FROM orders WHERE ((Section)=" & Chr(34) &
Me!Section &
Chr(34)& ");"
Cnt.Open CntStr
Rst.Open SqlStr, Cnt

I checked to see if for some reason the unbound form would have the
data
file locked and it doesn't, It would actually make me feel better if
I got
the same error with no criteria.
I'm about to start bouncing my head off the desk, any help is greatly
appreciated.

 

[Brendan Reynolds]

LOL! Sounds like fun, sorry I missed it.

It was a team effort, though - I might not have thought to test the field
name, if Alex hadn't eliminated the other possibilities.

--
Brendan Reynolds
Access MVP

Thank you sooooo much, I wish you could have been part of the dance around
the office.

It's the field name. I get the same result if I add a field named
'Section'
to the Orders table in the Northwind database. The error disappears if I
change the field name or put square brackets around it in the SQL string
...

SqlStr = "SELECT * FROM orders WHERE ((Orders.[Section])='123.45');"

'Section' must be a reserved word.

--
Brendan Reynolds
Access MVP

it is text, and I've tried no quotes, single and double quotes.

:

Ken,
if Section is number field - then you don't need to put value in
quotes

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


I apologise if this is considered a repost, I posted below about the
method
of Object... I got a response and spent the past 4 hours trying
every
combination to not get that error and the only way is to omit the
criteria.
This is the code I have that I think should return one record and is
giving
me the error.
CntStr =
"Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=C:\Traffic\trafficdata.mdb"
SqlStr ="SELECT * FROM orders WHERE ((Section)=" & Chr(34) &
Me!Section
&
Chr(34)& ");"
Cnt.Open CntStr
Rst.Open SqlStr, Cnt

I checked to see if for some reason the unbound form would have the
data
file locked and it doesn't, It would actually make me feel better if
I
got
the same error with no criteria.
I'm about to start bouncing my head off the desk, any help is
greatly
appreciated.