Propoer VBA sytax for SQL

  • Thread starter Thread starter Alp Bekisoglu
  • Start date Start date
A

Alp Bekisoglu

Hi All,

Can someone please help me out on this one? I'd like to correctly place the
following SQL in VBA code:

SELECT TOP 1 q_txable_s.person_id, q_txable_s.asessment_year, [2002].from,
[2002].to, [2002].first, [2002].next, [2002].tax, [2002].rate
FROM 2002, q_txable_s
WHERE ((([2002].from)<[q_txable_s]![Taxable_s]) AND
([q_txable_s]![Taxable_s]<=[2002]![to]));

where I'd like to use the following as well as the resulting code(open to
suggestions, advice,opinion):
Dim tNm, sSQL As String
tNm = Forms!yr!cur_y 'cur_y is a textbox on form yr
'below is where I need guidance in achieving the correct sytax, punctuation
'as can be seen, I'm trying to replace the table name with the tNm variable
sSQL = SELECT TOP 1 q_txable_s.person_id, q_txable_s.asessment_year,
tNm.from, tNm.to, tNm.first, tNm.next, tNm.tax, tNm.rate
FROM tNm, q_txable_s
WHERE (((tNm.from)<[q_txable_s]![Taxable_s]) AND
([q_txable_s]![Taxable_s]<=tNm![to]));

I thank everyone in advance for any guidance.

Sincerely,

Alp
 
Hi,


Your SQL statement miss a ORDER BY statement (generally we need one when
using TOP ).

Since this is not a VBA statement, it is not VBA code, you handle it
through a string:


sSQL="SELECT ..."


Finally, in SQL, the syntax is TableName.FieldName, not TableName!FieldName



Hoping it may help,
Vanderghast, Access MVP
 
Thank you Michael. Since I'm trying desperately to conclude this coding
problem, I do hope you could have a bit more time for me.

I have been trying it in code, but unsuccessfully. Thus I thought I am
making some serious mistakes in VBA'lizing the SQL statement from a query
and am now asking for guidance in the right direction.

As I indicated in my original question, one of the issues is to be able to
bind the name of a table used in this SQL to a varible, reason being the
fact that I can not alter the source table name within a query at runtime
(maybe I do not know how to accomplish that).

When I made an attempt to code it, I think it went into a continuous loop
and hence Access stopped responding.
Here's the code"
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL, year, table As String
table = Me!tbnm
year = Me!yr
Set dbs = CurrentDb()
strSQL = "SELECT * FROM " & table
strSQL = strSQL & " WHERE " & tablo & ".year=" & year
strSQL = strSQL & ";"
Me!Text6 = strSQL 'with this, I am just attempting to see the resulting
sql as text
Set rst = dbs.OpenRecordset(strSQL) 'could this be the culprit? Should
I use something else?
If (rst.EOF) Then
**** here will be some message indicating there is no data for the
year
Else
Me!Text6 = strSQL 'as indicated above
'here it should run the SQL
End If

' Close the recordset and the database.
rst.Close
dbs.Close

Or am I totally in the wrong direction?

Thank you for your time and help.

Sincerely,

Alp

Michel Walsh said:
Hi,


Your SQL statement miss a ORDER BY statement (generally we need one when
using TOP ).

Since this is not a VBA statement, it is not VBA code, you handle it
through a string:


sSQL="SELECT ..."


Finally, in SQL, the syntax is TableName.FieldName, not TableName!FieldName



Hoping it may help,
Vanderghast, Access MVP


Alp Bekisoglu said:
Hi All,

Can someone please help me out on this one? I'd like to correctly place the
following SQL in VBA code:

SELECT TOP 1 q_txable_s.person_id, q_txable_s.asessment_year, [2002].from,
[2002].to, [2002].first, [2002].next, [2002].tax, [2002].rate
FROM 2002, q_txable_s
WHERE ((([2002].from)<[q_txable_s]![Taxable_s]) AND
([q_txable_s]![Taxable_s]<=[2002]![to]));

where I'd like to use the following as well as the resulting code(open to
suggestions, advice,opinion):
Dim tNm, sSQL As String
tNm = Forms!yr!cur_y 'cur_y is a textbox on form yr
'below is where I need guidance in achieving the correct sytax, punctuation
'as can be seen, I'm trying to replace the table name with the tNm variable
sSQL = SELECT TOP 1 q_txable_s.person_id, q_txable_s.asessment_year,
tNm.from, tNm.to, tNm.first, tNm.next, tNm.tax, tNm.rate
FROM tNm, q_txable_s
WHERE (((tNm.from)<[q_txable_s]![Taxable_s]) AND
([q_txable_s]![Taxable_s]<=tNm![to]));

I thank everyone in advance for any guidance.

Sincerely,

Alp
Click to expand...
Click to expand...
 
While continueing to get it right, I have reached here:
Public Sub DoSQL()

Dim SQLbir, s_TblName As String
s_TblName = DLookup("asessment_year", "ya_table", "")

SQLbir = "SELECT TOP 1 q_txable_s.person_id, q_txable_s.asessment_year,
[" & s_TblName & "].from, [" & s_TblName & "].to, [" & s_TblName & "].first,
[" & s_TblName & "].next, [" & s_TblName & "].tax, [" & s_TblName & "].rate
" & _
"FROM " & s_TblName & ", q_txable_s" & _
"WHERE ((([" & s_TblName & "].from)<[q_txable_s].[Taxable_s]) AND
([q_txable_s].[Taxable_s]<=[" & s_TblName & "].[to]));"

DoCmd.RunSQL SQLbir

End Sub

Now I get this error when I try DoSQL in the immediate window:
Run-time error 3131
Sytax error in FROM clause

Before, I was getting 'missing operator' error until I included the [ & ] in
select.

Looks like getting somewhere, but still too far to go...

Alp
 
HI,


You have the right approach, but some typo, at least, as I see it, and
potential problems with reserved words, like year.

strSQL = strSQL & " WHERE " & tablo & ".year=" & year

should be table, not tablo:

strSQL = strSQL & " WHERE " & table & ".[year]=" & Me!yr


Note that
Dim strSQL, year, table As String

declare two variants and one string. Should be:

Dim strSQL As String, table As String

and avoid hidding the function year( ) by declaring a variable with the same
name. It is useless, in any cases, here, unless Me!Yr can be NULL, then:

strSQL = strSQL & ( " WHERE " & table & ".[year]=" + Me!yr )


is probably prefered ( + propagates the NULL, & doesn't )


Set rst = dbs.OpenRecordset(strSQL)


is fine in itself, even if personnaly, I prefer to specify something for the
defaults parameters.




Hoping it may help,
Vanderghast, Access MVP


Alp Bekisoglu said:
Thank you Michael. Since I'm trying desperately to conclude this coding
problem, I do hope you could have a bit more time for me.

I have been trying it in code, but unsuccessfully. Thus I thought I am
making some serious mistakes in VBA'lizing the SQL statement from a query
and am now asking for guidance in the right direction.

As I indicated in my original question, one of the issues is to be able to
bind the name of a table used in this SQL to a varible, reason being the
fact that I can not alter the source table name within a query at runtime
(maybe I do not know how to accomplish that).

When I made an attempt to code it, I think it went into a continuous loop
and hence Access stopped responding.
Here's the code"
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL, year, table As String
table = Me!tbnm
year = Me!yr
Set dbs = CurrentDb()
strSQL = "SELECT * FROM " & table
strSQL = strSQL & " WHERE " & tablo & ".year=" & year
strSQL = strSQL & ";"
Me!Text6 = strSQL 'with this, I am just attempting to see the resulting
sql as text
Set rst = dbs.OpenRecordset(strSQL) 'could this be the culprit? Should
I use something else?
If (rst.EOF) Then
**** here will be some message indicating there is no data for the
year
Else
Me!Text6 = strSQL 'as indicated above
'here it should run the SQL
End If

' Close the recordset and the database.
rst.Close
dbs.Close

Or am I totally in the wrong direction?

Thank you for your time and help.

Sincerely,

Alp

Michel Walsh said:
Hi,


Your SQL statement miss a ORDER BY statement (generally we need one when
using TOP ).

Since this is not a VBA statement, it is not VBA code, you handle it
through a string:


sSQL="SELECT ..."


Finally, in SQL, the syntax is TableName.FieldName, not TableName!FieldName



Hoping it may help,
Vanderghast, Access MVP


Alp Bekisoglu said:
Hi All,

Can someone please help me out on this one? I'd like to correctly
Click to expand...
place
the
following SQL in VBA code:

SELECT TOP 1 q_txable_s.person_id, q_txable_s.asessment_year, [2002].from,
[2002].to, [2002].first, [2002].next, [2002].tax, [2002].rate
FROM 2002, q_txable_s
WHERE ((([2002].from)<[q_txable_s]![Taxable_s]) AND
([q_txable_s]![Taxable_s]<=[2002]![to]));

where I'd like to use the following as well as the resulting code(open to
suggestions, advice,opinion):
Dim tNm, sSQL As String
tNm = Forms!yr!cur_y 'cur_y is a textbox on form yr
'below is where I need guidance in achieving the correct sytax, punctuation
'as can be seen, I'm trying to replace the table name with the tNm variable
sSQL = SELECT TOP 1 q_txable_s.person_id, q_txable_s.asessment_year,
tNm.from, tNm.to, tNm.first, tNm.next, tNm.tax, tNm.rate
FROM tNm, q_txable_s
WHERE (((tNm.from)<[q_txable_s]![Taxable_s]) AND
([q_txable_s]![Taxable_s]<=tNm![to]));

I thank everyone in advance for any guidance.

Sincerely,

Alp
Click to expand...
Click to expand...
Click to expand...
 
Hi,


You can DoCmd.RunSQL an "action" statement (DELETE, INSERT, ALTER, ...) but
not a statement that retrieve data (SELECT).



Hoping it may help,
Vanderghast, Access MVP
 
Thanks a lot for your help Michael. I'm a few steps ahead now. I'll make the
suggested corrections.

Sincerely,

Alp

Michel Walsh said:
HI,


You have the right approach, but some typo, at least, as I see it, and
potential problems with reserved words, like year.

strSQL = strSQL & " WHERE " & tablo & ".year=" & year

should be table, not tablo:

strSQL = strSQL & " WHERE " & table & ".[year]=" & Me!yr


Note that
Dim strSQL, year, table As String

declare two variants and one string. Should be:

Dim strSQL As String, table As String

and avoid hidding the function year( ) by declaring a variable with the same
name. It is useless, in any cases, here, unless Me!Yr can be NULL, then:

strSQL = strSQL & ( " WHERE " & table & ".[year]=" + Me!yr )


is probably prefered ( + propagates the NULL, & doesn't )


Set rst = dbs.OpenRecordset(strSQL)


is fine in itself, even if personnaly, I prefer to specify something for the
defaults parameters.




Hoping it may help,
Vanderghast, Access MVP


Alp Bekisoglu said:
Thank you Michael. Since I'm trying desperately to conclude this coding
problem, I do hope you could have a bit more time for me.

I have been trying it in code, but unsuccessfully. Thus I thought I am
making some serious mistakes in VBA'lizing the SQL statement from a query
and am now asking for guidance in the right direction.

As I indicated in my original question, one of the issues is to be able to
bind the name of a table used in this SQL to a varible, reason being the
fact that I can not alter the source table name within a query at runtime
(maybe I do not know how to accomplish that).

When I made an attempt to code it, I think it went into a continuous loop
and hence Access stopped responding.
Here's the code"
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL, year, table As String
table = Me!tbnm
year = Me!yr
Set dbs = CurrentDb()
strSQL = "SELECT * FROM " & table
strSQL = strSQL & " WHERE " & tablo & ".year=" & year
strSQL = strSQL & ";"
Me!Text6 = strSQL 'with this, I am just attempting to see the resulting
sql as text
Set rst = dbs.OpenRecordset(strSQL) 'could this be the culprit? Should
I use something else?
If (rst.EOF) Then
**** here will be some message indicating there is no data for the
year
Else
Me!Text6 = strSQL 'as indicated above
'here it should run the SQL
End If

' Close the recordset and the database.
rst.Close
dbs.Close

Or am I totally in the wrong direction?

Thank you for your time and help.

Sincerely,

Alp

Michel Walsh said:
Hi,


Your SQL statement miss a ORDER BY statement (generally we need
Click to expand...
one
when
using TOP ).

Since this is not a VBA statement, it is not VBA code, you handle it
through a string:


sSQL="SELECT ..."


Finally, in SQL, the syntax is TableName.FieldName, not TableName!FieldName



Hoping it may help,
Vanderghast, Access MVP


Hi All,

Can someone please help me out on this one? I'd like to correctly place
the
following SQL in VBA code:

SELECT TOP 1 q_txable_s.person_id, q_txable_s.asessment_year, [2002].from,
[2002].to, [2002].first, [2002].next, [2002].tax, [2002].rate
FROM 2002, q_txable_s
WHERE ((([2002].from)<[q_txable_s]![Taxable_s]) AND
([q_txable_s]![Taxable_s]<=[2002]![to]));

where I'd like to use the following as well as the resulting
Click to expand...
code(open
to
suggestions, advice,opinion):
Dim tNm, sSQL As String
tNm = Forms!yr!cur_y 'cur_y is a textbox on form yr
'below is where I need guidance in achieving the correct sytax,
punctuation
'as can be seen, I'm trying to replace the table name with the tNm
variable
sSQL = SELECT TOP 1 q_txable_s.person_id, q_txable_s.asessment_year,
tNm.from, tNm.to, tNm.first, tNm.next, tNm.tax, tNm.rate
FROM tNm, q_txable_s
WHERE (((tNm.from)<[q_txable_s]![Taxable_s]) AND
([q_txable_s]![Taxable_s]<=tNm![to]));

I thank everyone in advance for any guidance.

Sincerely,

Alp
Click to expand...
Click to expand...
Click to expand...
 
Thanks again. Could sound a pretty odd question but I'll ask it anyway; what
should I use to run the select sql then? Execute is not for select as well.

Alp

Michel Walsh said:
Hi,


You can DoCmd.RunSQL an "action" statement (DELETE, INSERT, ALTER, ...) but
not a statement that retrieve data (SELECT).



Hoping it may help,
Vanderghast, Access MVP


Alp Bekisoglu said:
While continueing to get it right, I have reached here:
Public Sub DoSQL()

Dim SQLbir, s_TblName As String
s_TblName = DLookup("asessment_year", "ya_table", "")

SQLbir = "SELECT TOP 1 q_txable_s.person_id, q_txable_s.asessment_year,
[" & s_TblName & "].from, [" & s_TblName & "].to, [" & s_TblName & "].first,
[" & s_TblName & "].next, [" & s_TblName & "].tax, [" & s_TblName & "].rate
" & _
"FROM " & s_TblName & ", q_txable_s" & _
"WHERE ((([" & s_TblName & "].from)<[q_txable_s].[Taxable_s]) AND
([q_txable_s].[Taxable_s]<=[" & s_TblName & "].[to]));"

DoCmd.RunSQL SQLbir

End Sub

Now I get this error when I try DoSQL in the immediate window:
Run-time error 3131
Sytax error in FROM clause

Before, I was getting 'missing operator' error until I included the [
Click to expand...
& ]
in
select.

Looks like getting somewhere, but still too far to go...

Alp
Click to expand...
Click to expand...
 
Hi,

You should either open a recordset against it, like you were doing in
your previous code, either assign it as RowSouce of a list box control ( be
sure to set the number of columns right). You can also assign it to as
RecordSource of a form, but then, you have to bind individual control to
each fields, which requires more code (assuming you do it through code, not
through a static design).



Hoping it may help,
Vanderghast, Access MVP




Alp Bekisoglu said:
Thanks again. Could sound a pretty odd question but I'll ask it anyway; what
should I use to run the select sql then? Execute is not for select as well.

Alp

Michel Walsh said:
Hi,


You can DoCmd.RunSQL an "action" statement (DELETE, INSERT, ALTER, ...) but
not a statement that retrieve data (SELECT).



Hoping it may help,
Vanderghast, Access MVP


Alp Bekisoglu said:
While continueing to get it right, I have reached here:
Public Sub DoSQL()

Dim SQLbir, s_TblName As String
s_TblName = DLookup("asessment_year", "ya_table", "")

SQLbir = "SELECT TOP 1 q_txable_s.person_id, q_txable_s.asessment_year,
[" & s_TblName & "].from, [" & s_TblName & "].to, [" & s_TblName & "].first,
[" & s_TblName & "].next, [" & s_TblName & "].tax, [" & s_TblName & "].rate
" & _
"FROM " & s_TblName & ", q_txable_s" & _
"WHERE ((([" & s_TblName & "].from)<[q_txable_s].[Taxable_s]) AND
([q_txable_s].[Taxable_s]<=[" & s_TblName & "].[to]));"

DoCmd.RunSQL SQLbir

End Sub

Now I get this error when I try DoSQL in the immediate window:
Run-time error 3131
Sytax error in FROM clause

Before, I was getting 'missing operator' error until I included the [
Click to expand...
& ]
in
select.

Looks like getting somewhere, but still too far to go...

Alp
Click to expand...
Click to expand...
Click to expand...
 
Back
Top