access sql error

[shaunFish]

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

[FishVal]

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

[shaunFish]

Hi FishVal

Yeah to all questions and have also added ; terminator

[shaunFish]

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.

[FishVal]

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?

[shaunFish]

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

[FishVal]

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

[shaunFish]

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

[FishVal]

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