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

SQL Query to VBA Syntax

P: n/a
p
I'm trying to put a query into VBA code and its giving me difficulties.
I would like to put the following query:

SELECT tbl_Workload.Priority, tbl_Workload.Number AS Num,
tbl_Workload.Name, tbl_Workload.Comment AS Comment, tbl_Workload.EmpID,
tbl_Workload.TaskID, tbl_Jobs.ID
FROM tbl_Workload LEFT JOIN tbl_Jobs ON tbl_Workload.Number =
tbl_Jobs.Number
WHERE (((tbl_Workload.EmpID)=[Forms].[frm_Main].[Employee]))
ORDER BY tbl_Workload.Priority, tbl_Workload.Number;

into the following VBA equivalent.
RsSql = "SELECT tbl_Workload.Priority, tbl_Workload.Number As Num,
tbl_Workload.Name, tbl_Workload.Commend, tbl_Workload.EmpID,
tbl_Workload.TaskID, tbl_Jobs.ID" _
& "FROM tblWorkload LEFT JOIN tbl_Jobs on tbl_Workload.Number =
tbl_Jobs.Number" _
& "WHERE (tbl_Workload.EmpID) =" & Me.[Employee]

Could someone have a look?

Dec 3 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a
"Giving me difficulties" is not very specific. Could you clarify for us what
you were expecting and what happens that is different from what you were
expecting, and the context in which you encountered the apparent anomaly?
Have you used Debug.Print to see if you are, in fact, generating the exact
SQL statement that you expected to generate?

A couple of SWAGs: If Field EmpID is text, then you need to have some
quotation marks generated around the value you obtain from Me.Employee. I'd
also include a space before the FROM and before the WHERE... the Query
builder performs some formatting, but in the code, what you code is what you
get.

Larry Linson
Microsoft Access MVP
"p" <p.******@gmail.comwrote in message
news:11**********************@79g2000cws.googlegro ups.com...
I'm trying to put a query into VBA code and its giving me difficulties.
I would like to put the following query:

SELECT tbl_Workload.Priority, tbl_Workload.Number AS Num,
tbl_Workload.Name, tbl_Workload.Comment AS Comment, tbl_Workload.EmpID,
tbl_Workload.TaskID, tbl_Jobs.ID
FROM tbl_Workload LEFT JOIN tbl_Jobs ON tbl_Workload.Number =
tbl_Jobs.Number
WHERE (((tbl_Workload.EmpID)=[Forms].[frm_Main].[Employee]))
ORDER BY tbl_Workload.Priority, tbl_Workload.Number;

into the following VBA equivalent.
RsSql = "SELECT tbl_Workload.Priority, tbl_Workload.Number As Num,
tbl_Workload.Name, tbl_Workload.Commend, tbl_Workload.EmpID,
tbl_Workload.TaskID, tbl_Jobs.ID" _
& "FROM tblWorkload LEFT JOIN tbl_Jobs on tbl_Workload.Number =
tbl_Jobs.Number" _
& "WHERE (tbl_Workload.EmpID) =" & Me.[Employee]

Could someone have a look?

Dec 3 '06 #2

P: n/a
p
Ok. I'm using a subform to display a query. The recordsource of the
query is an actual query object but i'm outputting the contents of this
subform into excel so I need to use VBA to generate the SQL because
there are FORM elements as a part of the WHERE clause.

I realised that I had no spaces in front of the &"FROM and &"WHERE
parts, but a new error has come up:

***
run-time error '3135':

Syntax error in JOIN operation

***

I've examined the code (Below) and cannot find where this would be
wrong.

RsSql = "SELECT tbl_Workload.Priority, tbl_Workload.Number As Num,
tbl_Workload.Name, tbl_Workload.Commend, tbl_Workload.EmpID,
tbl_Workload.TaskID, tbl_Jobs.ID" _
& " FROM tblWorkload LEFT JOIN tbl_Jobs on tbl_Workload.Number
= tbl_Jobs.Number" _
& " WHERE (tbl_Workload.EmpID) =" & Me.[Employee]

I have not tried Debug.Print before, but will find out and use it right
now.

Thanks for your prompt response and in advance for your advice.
Larry Linson wrote:
"Giving me difficulties" is not very specific. Could you clarify for us what
you were expecting and what happens that is different from what you were
expecting, and the context in which you encountered the apparent anomaly?
Have you used Debug.Print to see if you are, in fact, generating the exact
SQL statement that you expected to generate?

A couple of SWAGs: If Field EmpID is text, then you need to have some
quotation marks generated around the value you obtain from Me.Employee. I'd
also include a space before the FROM and before the WHERE... the Query
builder performs some formatting, but in the code, what you code is what you
get.

Larry Linson
Microsoft Access MVP
"p" <p.******@gmail.comwrote in message
news:11**********************@79g2000cws.googlegro ups.com...
I'm trying to put a query into VBA code and its giving me difficulties.
I would like to put the following query:

SELECT tbl_Workload.Priority, tbl_Workload.Number AS Num,
tbl_Workload.Name, tbl_Workload.Comment AS Comment, tbl_Workload.EmpID,
tbl_Workload.TaskID, tbl_Jobs.ID
FROM tbl_Workload LEFT JOIN tbl_Jobs ON tbl_Workload.Number =
tbl_Jobs.Number
WHERE (((tbl_Workload.EmpID)=[Forms].[frm_Main].[Employee]))
ORDER BY tbl_Workload.Priority, tbl_Workload.Number;

into the following VBA equivalent.
RsSql = "SELECT tbl_Workload.Priority, tbl_Workload.Number As Num,
tbl_Workload.Name, tbl_Workload.Commend, tbl_Workload.EmpID,
tbl_Workload.TaskID, tbl_Jobs.ID" _
& "FROM tblWorkload LEFT JOIN tbl_Jobs on tbl_Workload.Number =
tbl_Jobs.Number" _
& "WHERE (tbl_Workload.EmpID) =" & Me.[Employee]

Could someone have a look?
Dec 3 '06 #3

P: n/a
p
Ok after a quick debug i've realised that the SQL Statement only gets
to this point:

"SELECT tbl_Workload.Priority, tbl_Workload.Number As Num,
tbl_Workload.Name, tbl_Workload.Commend, tbl_Workload.EmpID,
tbl_Workload.TaskID, tbl_Jobs.ID

FROM tblWorkload LEFT JOIN tbl_Jobs on tbl_Workload.Number =
tbl_Jobs.Number

WHERE (tbl_Workload.Ep"

So it's being cut off at the where clause - HOWEVER there is no
formatting around the end of this SQL query and I don't see why this
would be the issue. And how come it has chosen to run tbl_Workload.Ep,
when it clearly is trying to call tbl_Workload.Emp (it cuts out the
'm').. Craziness!

p wrote:
Ok. I'm using a subform to display a query. The recordsource of the
query is an actual query object but i'm outputting the contents of this
subform into excel so I need to use VBA to generate the SQL because
there are FORM elements as a part of the WHERE clause.

I realised that I had no spaces in front of the &"FROM and &"WHERE
parts, but a new error has come up:

***
run-time error '3135':

Syntax error in JOIN operation

***

I've examined the code (Below) and cannot find where this would be
wrong.

RsSql = "SELECT tbl_Workload.Priority, tbl_Workload.Number As Num,
tbl_Workload.Name, tbl_Workload.Commend, tbl_Workload.EmpID,
tbl_Workload.TaskID, tbl_Jobs.ID" _
& " FROM tblWorkload LEFT JOIN tbl_Jobs on tbl_Workload.Number
= tbl_Jobs.Number" _
& " WHERE (tbl_Workload.EmpID) =" & Me.[Employee]

I have not tried Debug.Print before, but will find out and use it right
now.

Thanks for your prompt response and in advance for your advice.
Larry Linson wrote:
"Giving me difficulties" is not very specific. Could you clarify for us what
you were expecting and what happens that is different from what you were
expecting, and the context in which you encountered the apparent anomaly?
Have you used Debug.Print to see if you are, in fact, generating the exact
SQL statement that you expected to generate?

A couple of SWAGs: If Field EmpID is text, then you need to have some
quotation marks generated around the value you obtain from Me.Employee. I'd
also include a space before the FROM and before the WHERE... the Query
builder performs some formatting, but in the code, what you code is what you
get.

Larry Linson
Microsoft Access MVP
"p" <p.******@gmail.comwrote in message
news:11**********************@79g2000cws.googlegro ups.com...
I'm trying to put a query into VBA code and its giving me difficulties.
I would like to put the following query:
>
SELECT tbl_Workload.Priority, tbl_Workload.Number AS Num,
tbl_Workload.Name, tbl_Workload.Comment AS Comment, tbl_Workload.EmpID,
tbl_Workload.TaskID, tbl_Jobs.ID
FROM tbl_Workload LEFT JOIN tbl_Jobs ON tbl_Workload.Number =
tbl_Jobs.Number
WHERE (((tbl_Workload.EmpID)=[Forms].[frm_Main].[Employee]))
ORDER BY tbl_Workload.Priority, tbl_Workload.Number;
>
into the following VBA equivalent.
>
>
RsSql = "SELECT tbl_Workload.Priority, tbl_Workload.Number As Num,
tbl_Workload.Name, tbl_Workload.Commend, tbl_Workload.EmpID,
tbl_Workload.TaskID, tbl_Jobs.ID" _
& "FROM tblWorkload LEFT JOIN tbl_Jobs on tbl_Workload.Number =
tbl_Jobs.Number" _
& "WHERE (tbl_Workload.EmpID) =" & Me.[Employee]
>
Could someone have a look?
>
Dec 3 '06 #4

P: n/a
What, exactly, do you mean when you say 'the SQL Statement only gets
to this point"? Did you put a Debug.Print for RsSQL immediately after the
statement that puts the value into RsSQL, or are you using some other
method?

It is difficult to follow, when we don't have all the details -- your
database is right in front of you, but we are dependent on what you describe
to us in trying to do remote debugging.

I agree... no matter where you have the Debug.Print, or other "glimpse" of
the constructed string, it looks strange that it terminates at that point,
and that the "m" should be dropped. Almost appears that something overlaid
the last part of the SQL statement after it was constructed but before it
was printed/displayed.

Larry Linson
Microsoft Access MVP
"p" <p.******@gmail.comwrote in message
news:11**********************@16g2000cwy.googlegro ups.com...
Ok after a quick debug i've realised that the SQL Statement only gets
to this point:

"SELECT tbl_Workload.Priority, tbl_Workload.Number As Num,
tbl_Workload.Name, tbl_Workload.Commend, tbl_Workload.EmpID,
tbl_Workload.TaskID, tbl_Jobs.ID

FROM tblWorkload LEFT JOIN tbl_Jobs on tbl_Workload.Number =
tbl_Jobs.Number

WHERE (tbl_Workload.Ep"

So it's being cut off at the where clause - HOWEVER there is no
formatting around the end of this SQL query and I don't see why this
would be the issue. And how come it has chosen to run tbl_Workload.Ep,
when it clearly is trying to call tbl_Workload.Emp (it cuts out the
'm').. Craziness!

p wrote:
>Ok. I'm using a subform to display a query. The recordsource of the
query is an actual query object but i'm outputting the contents of this
subform into excel so I need to use VBA to generate the SQL because
there are FORM elements as a part of the WHERE clause.

I realised that I had no spaces in front of the &"FROM and &"WHERE
parts, but a new error has come up:

***
run-time error '3135':

Syntax error in JOIN operation

***

I've examined the code (Below) and cannot find where this would be
wrong.

RsSql = "SELECT tbl_Workload.Priority, tbl_Workload.Number As Num,
tbl_Workload.Name, tbl_Workload.Commend, tbl_Workload.EmpID,
tbl_Workload.TaskID, tbl_Jobs.ID" _
& " FROM tblWorkload LEFT JOIN tbl_Jobs on tbl_Workload.Number
= tbl_Jobs.Number" _
& " WHERE (tbl_Workload.EmpID) =" & Me.[Employee]

I have not tried Debug.Print before, but will find out and use it right
now.

Thanks for your prompt response and in advance for your advice.
Larry Linson wrote:
"Giving me difficulties" is not very specific. Could you clarify for us
what
you were expecting and what happens that is different from what you
were
expecting, and the context in which you encountered the apparent
anomaly?
Have you used Debug.Print to see if you are, in fact, generating the
exact
SQL statement that you expected to generate?

A couple of SWAGs: If Field EmpID is text, then you need to have some
quotation marks generated around the value you obtain from Me.Employee.
I'd
also include a space before the FROM and before the WHERE... the Query
builder performs some formatting, but in the code, what you code is
what you
get.

Larry Linson
Microsoft Access MVP
"p" <p.******@gmail.comwrote in message
news:11**********************@79g2000cws.googlegro ups.com...
I'm trying to put a query into VBA code and its giving me
difficulties.
I would like to put the following query:

SELECT tbl_Workload.Priority, tbl_Workload.Number AS Num,
tbl_Workload.Name, tbl_Workload.Comment AS Comment,
tbl_Workload.EmpID,
tbl_Workload.TaskID, tbl_Jobs.ID
FROM tbl_Workload LEFT JOIN tbl_Jobs ON tbl_Workload.Number =
tbl_Jobs.Number
WHERE (((tbl_Workload.EmpID)=[Forms].[frm_Main].[Employee]))
ORDER BY tbl_Workload.Priority, tbl_Workload.Number;

into the following VBA equivalent.
RsSql = "SELECT tbl_Workload.Priority, tbl_Workload.Number As Num,
tbl_Workload.Name, tbl_Workload.Commend, tbl_Workload.EmpID,
tbl_Workload.TaskID, tbl_Jobs.ID" _
& "FROM tblWorkload LEFT JOIN tbl_Jobs on tbl_Workload.Number
=
tbl_Jobs.Number" _
& "WHERE (tbl_Workload.EmpID) =" & Me.[Employee]

Could someone have a look?

Dec 4 '06 #5

P: n/a
Hi,
& " WHERE (tbl_Workload.EmpID) =" & Me.[Employee]
Try & " WHERE (tbl_Workload.EmpID) =" & Me.Employee

without the square brackets. I do believe something is wrong here. How did
your intellisense gave you square brackets? (if Employee is a control name in
your form)
>p wrote:
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200612/1

Dec 4 '06 #6

P: n/a
Pardon me butting in. I have glanced at this thread for the first time. My
guess would be from the info given that somehow a backspace character, and
perhaps other control characters, has been inserted into the character
string. Em<bs>p

I would first try retyping it and try again.
"p" <p.******@gmail.comwrote in message
news:11**********************@16g2000cwy.googlegro ups.com...
Ok after a quick debug i've realised that the SQL Statement only gets
to this point:

"SELECT tbl_Workload.Priority, tbl_Workload.Number As Num,
tbl_Workload.Name, tbl_Workload.Commend, tbl_Workload.EmpID,
tbl_Workload.TaskID, tbl_Jobs.ID

FROM tblWorkload LEFT JOIN tbl_Jobs on tbl_Workload.Number =
tbl_Jobs.Number

WHERE (tbl_Workload.Ep"

So it's being cut off at the where clause - HOWEVER there is no
formatting around the end of this SQL query and I don't see why this
would be the issue. And how come it has chosen to run tbl_Workload.Ep,
when it clearly is trying to call tbl_Workload.Emp (it cuts out the
'm').. Craziness!

p wrote:
>Ok. I'm using a subform to display a query. The recordsource of the
query is an actual query object but i'm outputting the contents of this
subform into excel so I need to use VBA to generate the SQL because
there are FORM elements as a part of the WHERE clause.

I realised that I had no spaces in front of the &"FROM and &"WHERE
parts, but a new error has come up:

***
run-time error '3135':

Syntax error in JOIN operation

***

I've examined the code (Below) and cannot find where this would be
wrong.

RsSql = "SELECT tbl_Workload.Priority, tbl_Workload.Number As Num,
tbl_Workload.Name, tbl_Workload.Commend, tbl_Workload.EmpID,
tbl_Workload.TaskID, tbl_Jobs.ID" _
& " FROM tblWorkload LEFT JOIN tbl_Jobs on tbl_Workload.Number
= tbl_Jobs.Number" _
& " WHERE (tbl_Workload.EmpID) =" & Me.[Employee]

I have not tried Debug.Print before, but will find out and use it right
now.

Thanks for your prompt response and in advance for your advice.
Larry Linson wrote:
"Giving me difficulties" is not very specific. Could you clarify for us
what
you were expecting and what happens that is different from what you
were
expecting, and the context in which you encountered the apparent
anomaly?
Have you used Debug.Print to see if you are, in fact, generating the
exact
SQL statement that you expected to generate?

A couple of SWAGs: If Field EmpID is text, then you need to have some
quotation marks generated around the value you obtain from Me.Employee.
I'd
also include a space before the FROM and before the WHERE... the Query
builder performs some formatting, but in the code, what you code is
what you
get.

Larry Linson
Microsoft Access MVP
"p" <p.******@gmail.comwrote in message
news:11**********************@79g2000cws.googlegro ups.com...
I'm trying to put a query into VBA code and its giving me
difficulties.
I would like to put the following query:

SELECT tbl_Workload.Priority, tbl_Workload.Number AS Num,
tbl_Workload.Name, tbl_Workload.Comment AS Comment,
tbl_Workload.EmpID,
tbl_Workload.TaskID, tbl_Jobs.ID
FROM tbl_Workload LEFT JOIN tbl_Jobs ON tbl_Workload.Number =
tbl_Jobs.Number
WHERE (((tbl_Workload.EmpID)=[Forms].[frm_Main].[Employee]))
ORDER BY tbl_Workload.Priority, tbl_Workload.Number;

into the following VBA equivalent.
RsSql = "SELECT tbl_Workload.Priority, tbl_Workload.Number As Num,
tbl_Workload.Name, tbl_Workload.Commend, tbl_Workload.EmpID,
tbl_Workload.TaskID, tbl_Jobs.ID" _
& "FROM tblWorkload LEFT JOIN tbl_Jobs on tbl_Workload.Number
=
tbl_Jobs.Number" _
& "WHERE (tbl_Workload.EmpID) =" & Me.[Employee]

Could someone have a look?


Dec 4 '06 #7

P: n/a
Sorry for interrupting as I know about access only a thing or two (but
i'm learning...) so my post must be way off but i noticed this
difference:
in the orgininal query there is a field

tbl_Workload.CommenT

while in the VBA the field says

tbl_Workload.CommenD

,i.e., T is substituded with D
i was just wondering if it could cause some trouble as the code might
be unable to pick the field...
David F Cox wrote:
Pardon me butting in. I have glanced at this thread for the first time. My
guess would be from the info given that somehow a backspace character, and
perhaps other control characters, has been inserted into the character
string. Em<bs>p

I would first try retyping it and try again.
"p" <p.******@gmail.comwrote in message
news:11**********************@16g2000cwy.googlegro ups.com...
Ok after a quick debug i've realised that the SQL Statement only gets
to this point:

"SELECT tbl_Workload.Priority, tbl_Workload.Number As Num,
tbl_Workload.Name, tbl_Workload.Commend, tbl_Workload.EmpID,
tbl_Workload.TaskID, tbl_Jobs.ID

FROM tblWorkload LEFT JOIN tbl_Jobs on tbl_Workload.Number =
tbl_Jobs.Number

WHERE (tbl_Workload.Ep"

So it's being cut off at the where clause - HOWEVER there is no
formatting around the end of this SQL query and I don't see why this
would be the issue. And how come it has chosen to run tbl_Workload.Ep,
when it clearly is trying to call tbl_Workload.Emp (it cuts out the
'm').. Craziness!

p wrote:
Ok. I'm using a subform to display a query. The recordsource of the
query is an actual query object but i'm outputting the contents of this
subform into excel so I need to use VBA to generate the SQL because
there are FORM elements as a part of the WHERE clause.

I realised that I had no spaces in front of the &"FROM and &"WHERE
parts, but a new error has come up:

***
run-time error '3135':

Syntax error in JOIN operation

***

I've examined the code (Below) and cannot find where this would be
wrong.

RsSql = "SELECT tbl_Workload.Priority, tbl_Workload.Number As Num,
tbl_Workload.Name, tbl_Workload.Commend, tbl_Workload.EmpID,
tbl_Workload.TaskID, tbl_Jobs.ID" _
& " FROM tblWorkload LEFT JOIN tbl_Jobs on tbl_Workload.Number
= tbl_Jobs.Number" _
& " WHERE (tbl_Workload.EmpID) =" & Me.[Employee]

I have not tried Debug.Print before, but will find out and use it right
now.

Thanks for your prompt response and in advance for your advice.
Larry Linson wrote:
"Giving me difficulties" is not very specific. Could you clarify for us
what
you were expecting and what happens that is different from what you
were
expecting, and the context in which you encountered the apparent
anomaly?
Have you used Debug.Print to see if you are, in fact, generating the
exact
SQL statement that you expected to generate?

A couple of SWAGs: If Field EmpID is text, then you need to have some
quotation marks generated around the value you obtain from Me.Employee.
I'd
also include a space before the FROM and before the WHERE... the Query
builder performs some formatting, but in the code, what you code is
what you
get.

Larry Linson
Microsoft Access MVP
"p" <p.******@gmail.comwrote in message
news:11**********************@79g2000cws.googlegro ups.com...
I'm trying to put a query into VBA code and its giving me
difficulties.
I would like to put the following query:
>
SELECT tbl_Workload.Priority, tbl_Workload.Number AS Num,
tbl_Workload.Name, tbl_Workload.Comment AS Comment,
tbl_Workload.EmpID,
tbl_Workload.TaskID, tbl_Jobs.ID
FROM tbl_Workload LEFT JOIN tbl_Jobs ON tbl_Workload.Number =
tbl_Jobs.Number
WHERE (((tbl_Workload.EmpID)=[Forms].[frm_Main].[Employee]))
ORDER BY tbl_Workload.Priority, tbl_Workload.Number;
>
into the following VBA equivalent.
>
>
RsSql = "SELECT tbl_Workload.Priority, tbl_Workload.Number As Num,
tbl_Workload.Name, tbl_Workload.Commend, tbl_Workload.EmpID,
tbl_Workload.TaskID, tbl_Jobs.ID" _
& "FROM tblWorkload LEFT JOIN tbl_Jobs on tbl_Workload.Number
=
tbl_Jobs.Number" _
& "WHERE (tbl_Workload.EmpID) =" & Me.[Employee]
>
Could someone have a look?
>
Dec 4 '06 #8

P: n/a
Hi austris,

If there aren’t any syntax errors, these are two common problems will appear.

If you use access query and the name of the query field/column is misspelled,
access will prompt you to enter a parameter input for the field/column.

If you open the query from a recordset, a similar error messages “No value
given for one or more required parameters.”
>austris wrote:
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200612/1

Dec 5 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.