Help with code syntax

  • Thread starter Thread starter Alain
  • Start date Start date
A

Alain

Hi to All,

I am getting the following error in my SELECT statement:
Expected: line number or label or statement or end of statement, I have many
combination of quotes mark where I thing they should go but still getting
the same error but on different lenght of my Select statement.
(It looks like this is one of the things that is very difficult for me to
catch up or maybe Access make ot very difficult to code :-))) )

Any help understanging this will be greatly appreciated

Public Sub NewRefNo(ref As Integer)
Dim db As DAO.Database
'Dim rst As DAO.Recordset
Dim temp As Integer
Set db = CurrentDb

temp = CurrentDb.Execute_
"SELECT Max(Right([ReferenceNo]),3) FROM tblProperty WHERE
Left([ReferenceNo], 3)=" & ref "",
dbFailOnError

End Sub

Alain
 
You're missing a space between "Execute" and the underscore character, you
need "CurrentDb.Execute _" instead of "CurrentDb.Execute_".

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Not sure if this is exactly what you're doing, but try this:

Dim rst As DAO.Recordset
Set rst = db.OpenRecordset _
("SELECT Max(Right([ReferenceNo]),3) FROM " & _
"tblProperty WHERE Left([ReferenceNo], 3)='" & _
ref & "'", dbOpenDynaset)
temp = rst.Fields(0).Value
rst.Close
Set rst = Nothing

--

Ken Snell
<MS ACCESS MVP>


If you want to get a value from the
 
Hi Brendan,

I have already tried this before posting and it gave me a syntax error on
both lines


Brendan Reynolds said:
You're missing a space between "Execute" and the underscore character, you
need "CurrentDb.Execute _" instead of "CurrentDb.Execute_".

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Alain said:
Hi to All,

I am getting the following error in my SELECT statement:
Expected: line number or label or statement or end of statement, I have
many combination of quotes mark where I thing they should go but still
getting the same error but on different lenght of my Select statement.
(It looks like this is one of the things that is very difficult for me to
catch up or maybe Access make ot very difficult to code :-))) )

Any help understanging this will be greatly appreciated

Public Sub NewRefNo(ref As Integer)
Dim db As DAO.Database
'Dim rst As DAO.Recordset
Dim temp As Integer
Set db = CurrentDb

temp = CurrentDb.Execute_
"SELECT Max(Right([ReferenceNo]),3) FROM tblProperty WHERE
Left([ReferenceNo], 3)=" & ref "",
dbFailOnError

End Sub

Alain
Click to expand...
Click to expand...
 
Hi Ken

I vave tried your code and it give me the following :
Object variable or With block variable not set
I do not quite understand where the object variable come from

Alain

Ken Snell said:
Not sure if this is exactly what you're doing, but try this:

Dim rst As DAO.Recordset
Set rst = db.OpenRecordset _
("SELECT Max(Right([ReferenceNo]),3) FROM " & _
"tblProperty WHERE Left([ReferenceNo], 3)='" & _
ref & "'", dbOpenDynaset)
temp = rst.Fields(0).Value
rst.Close
Set rst = Nothing

--

Ken Snell
<MS ACCESS MVP>


If you want to get a value from the
Alain said:
Hi to All,

I am getting the following error in my SELECT statement:
Expected: line number or label or statement or end of statement, I have many
combination of quotes mark where I thing they should go but still getting
the same error but on different lenght of my Select statement.
(It looks like this is one of the things that is very difficult for me to
catch up or maybe Access make ot very difficult to code :-))) )

Any help understanging this will be greatly appreciated

Public Sub NewRefNo(ref As Integer)
Dim db As DAO.Database
'Dim rst As DAO.Recordset
Dim temp As Integer
Set db = CurrentDb

temp = CurrentDb.Execute_
"SELECT Max(Right([ReferenceNo]),3) FROM tblProperty WHERE
Left([ReferenceNo], 3)=" & ref "",
dbFailOnError

End Sub

Alain
Click to expand...
Click to expand...
 
Hi Ken,

just fixed the previous error, my mistake,
Just to let you know that your code is generating a error regarding wrong
argument in the following :
Max(Right([ReferenceNo]),3)
I changed it to the following
Max(Right([ReferenceNo],3)) and it works good

Thanks for the help

Alain



Ken Snell said:
Not sure if this is exactly what you're doing, but try this:

Dim rst As DAO.Recordset
Set rst = db.OpenRecordset _
("SELECT Max(Right([ReferenceNo]),3) FROM " & _
"tblProperty WHERE Left([ReferenceNo], 3)='" & _
ref & "'", dbOpenDynaset)
temp = rst.Fields(0).Value
rst.Close
Set rst = Nothing

--

Ken Snell
<MS ACCESS MVP>


If you want to get a value from the
Alain said:
Hi to All,

I am getting the following error in my SELECT statement:
Expected: line number or label or statement or end of statement, I have many
combination of quotes mark where I thing they should go but still getting
the same error but on different lenght of my Select statement.
(It looks like this is one of the things that is very difficult for me to
catch up or maybe Access make ot very difficult to code :-))) )

Any help understanging this will be greatly appreciated

Public Sub NewRefNo(ref As Integer)
Dim db As DAO.Database
'Dim rst As DAO.Recordset
Dim temp As Integer
Set db = CurrentDb

temp = CurrentDb.Execute_
"SELECT Max(Right([ReferenceNo]),3) FROM tblProperty WHERE
Left([ReferenceNo], 3)=" & ref "",
dbFailOnError

End Sub

Alain
Click to expand...
Click to expand...
 
That typo was in your original code.... but I should have caught it
nonetheless. Glad it's working.

--

Ken Snell
<MS ACCESS MVP>

Alain said:
Hi Ken,

just fixed the previous error, my mistake,
Just to let you know that your code is generating a error regarding wrong
argument in the following :
Max(Right([ReferenceNo]),3)
I changed it to the following
Max(Right([ReferenceNo],3)) and it works good

Thanks for the help

Alain



Ken Snell said:
Not sure if this is exactly what you're doing, but try this:

Dim rst As DAO.Recordset
Set rst = db.OpenRecordset _
("SELECT Max(Right([ReferenceNo]),3) FROM " & _
"tblProperty WHERE Left([ReferenceNo], 3)='" & _
ref & "'", dbOpenDynaset)
temp = rst.Fields(0).Value
rst.Close
Set rst = Nothing

--

Ken Snell
<MS ACCESS MVP>


If you want to get a value from the
Alain said:
Hi to All,

I am getting the following error in my SELECT statement:
Expected: line number or label or statement or end of statement, I have many
combination of quotes mark where I thing they should go but still getting
the same error but on different lenght of my Select statement.
(It looks like this is one of the things that is very difficult for me to
catch up or maybe Access make ot very difficult to code :-))) )

Any help understanging this will be greatly appreciated

Public Sub NewRefNo(ref As Integer)
Dim db As DAO.Database
'Dim rst As DAO.Recordset
Dim temp As Integer
Set db = CurrentDb

temp = CurrentDb.Execute_
"SELECT Max(Right([ReferenceNo]),3) FROM tblProperty WHERE
Left([ReferenceNo], 3)=" & ref "",
dbFailOnError

End Sub

Alain
Click to expand...
Click to expand...
Click to expand...
 
Back
Top