By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,327 Members | 1,806 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,327 IT Pros & Developers. It's quick & easy.

access sql error

P: 5
Hi All
Can any one please help. I get an error code 3141 when I try run this sql statement. I have tried it from an HTA App and from Acess and get the error from both.

"Select top 1,* from tblCustomers where CustomerId >= " & Cstr(CurrentID)

Any help would be good

Ta in advance
Sep 2 '07 #1
Share this Question
Share on Google+
8 Replies


FishVal
Expert 2.5K+
P: 2,653
Hi All
Can any one please help. I get an error code 3141 when I try run this sql statement. I have tried it from an HTA App and from Acess and get the error from both.

"Select top 1,* from tblCustomers where CustomerId >= " & Cstr(CurrentID)

Any help would be good

Ta in advance
Hi, there.
  • are you sure tblCustomers.CustomerId is numeric type field ?
  • are you sure Cstr(CurrentID) doesn't evaluate to empty string ?
  • add terminating ";" to the expression
Sep 2 '07 #2

P: 5
Hi FishVal

Yeah to all questions and have also added ; terminator
Sep 2 '07 #3

P: 5
Hi, there.
  • are you sure tblCustomers.CustomerId is numeric type field ?
  • are you sure Cstr(CurrentID) doesn't evaluate to empty string ?
  • add terminating ";" to the expression
The full error report read:
The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect

I have read the access help files and acording to it, my statement is correct.
Sep 2 '07 #4

FishVal
Expert 2.5K+
P: 2,653
The full error report read:
The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect

I have read the access help files and acording to it, my statement is correct.
According to Access TOP N predicate has not been separated with comma from fieldslist, but anyway int runs with comma as well as w/o. :)
Did you try to gather in debug mode actual sql string content and run it in query builder?
Sep 2 '07 #5

P: 5
yeah and it still has the same error

if it will help here is my code that uses the sql

Sub NextCustomer()
dim objConn, strConnection, objRS, strQuery, TheForm
set objConn = CreateObject("ADODB.Connection")
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\order entry\orders.mdb'"
objConn.Open strConnection
set objRS = CreateObject("ADODB.RecordSet")
CurrentNum = CurrentNum + 1
if CurrentNum <NumRecords then
CurrentID = CurrentID + 1
strQuery = "Select top 1,* from tblCustomers where CustomerId >= " & Cstr(CurrentID)
objRS.Open strQuery, objConn,3,1
CurrentID = objRS("CustomerId")

set TheForm = Document.Forms("EditCustomersForm")
TheForm.id.value = objRS("CustomerId")
TheForm.FirstName.Value = objRS("FirstName")
TheForm.LastName.Value = objRS("LastName")
TheForm.Street.Value = objRS("Street")
TheForm.City.Value = objRS("City")
TheForm.State.Value = objRS("State")
TheForm.Zip.Value = objRS("Zip")
TheForm.Phone.Value = objRS("Phone")
TheForm.Email.Value = objRS("Email")
TheForm.Notes.Value = objRS("Notes")
objRS.Close
end if
objConn.Close
set objRS = nothing
set objConn = nothing

end sub
Sep 2 '07 #6

FishVal
Expert 2.5K+
P: 2,653
yeah and it still has the same error

if it will help here is my code that uses the sql

Sub NextCustomer()
dim objConn, strConnection, objRS, strQuery, TheForm
set objConn = CreateObject("ADODB.Connection")
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\order entry\orders.mdb'"
objConn.Open strConnection
set objRS = CreateObject("ADODB.RecordSet")
CurrentNum = CurrentNum + 1
if CurrentNum <NumRecords then
CurrentID = CurrentID + 1
strQuery = "Select top 1,* from tblCustomers where CustomerId >= " & Cstr(CurrentID)
objRS.Open strQuery, objConn,3,1
CurrentID = objRS("CustomerId")

set TheForm = Document.Forms("EditCustomersForm")
TheForm.id.value = objRS("CustomerId")
TheForm.FirstName.Value = objRS("FirstName")
TheForm.LastName.Value = objRS("LastName")
TheForm.Street.Value = objRS("Street")
TheForm.City.Value = objRS("City")
TheForm.State.Value = objRS("State")
TheForm.Zip.Value = objRS("Zip")
TheForm.Phone.Value = objRS("Phone")
TheForm.Email.Value = objRS("Email")
TheForm.Notes.Value = objRS("Notes")
objRS.Close
end if
objConn.Close
set objRS = nothing
set objConn = nothing

end sub
Add space between "," and "*"
"Select top 1, * from tblCustomers where CustomerId >= " & Cstr(CurrentID)
;)

And the comma is not needed, and Cstr is not needed (& operator concatenates variables of all types with string), and, G..d save us, add terminating semicolon ";".
"Select top 1 * from tblCustomers where CustomerId >= " & CurrentID & ";"

Regards

Fish
Sep 2 '07 #7

P: 5
Ta very much (Blush) . It's working now. Strange how one can overlook such simple things. Just shows you two pairs of eyes are better that one
Sep 2 '07 #8

FishVal
Expert 2.5K+
P: 2,653
Ta very much (Blush) . It's working now. Strange how one can overlook such simple things. Just shows you two pairs of eyes are better that one
Not a problem. You are welcome.

Regards

Fish
Sep 2 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.