SQL syntax error

C

Chace

Can someone please help to spot the syntax error in the following. The query
works fine outside of VBA, but it will not make it past this step in my VBA
code.

strSQL = "SELECT Sum(tbl_IPLs.[IPL Credit]) AS [SumOfIPL Credit] " & _
"FROM (tbl_IPLHazTypeApplicability INNER JOIN tbl_HazIPLs ON " & _
"tbl_IPLHazTypeApplicability.HazIPLID = tbl_HazIPLs.HazIPLID) " & _
"INNER JOIN tbl_IPLs ON tbl_HazIPLs.IPLID = tbl_IPLs.IPLID " & _
"WHERE (((tbl_HazIPLs.HazIPLID)=" & intHazIPLID & " AND
((tbl_HazIPLs.Exist)=-1) " & _
"AND ((tbl_IPLHazTypeApplicability.HazTypeID)=" & intHazTypeID & "));"

Set rs = CurrentDb.OpenRecordset(strSQL)

Thanks in advance,
Chace
 
A

Allan Murphy

Chace

Is ((tbl_HazIPLs.Exist)=-1) " & _ supposed to be on a separate line? If so
you to add " & _ on the end of the previous line.
 
C

Chace

No, it just posted that way.

Allan Murphy said:
Chace

Is ((tbl_HazIPLs.Exist)=-1) " & _ supposed to be on a separate line? If so
you to add " & _ on the end of the previous line.


--
Allan Murphy
Email: (e-mail address removed)

Chace said:
Can someone please help to spot the syntax error in the following. The query
works fine outside of VBA, but it will not make it past this step in my VBA
code.

strSQL = "SELECT Sum(tbl_IPLs.[IPL Credit]) AS [SumOfIPL Credit] " & _
"FROM (tbl_IPLHazTypeApplicability INNER JOIN tbl_HazIPLs ON " & _
"tbl_IPLHazTypeApplicability.HazIPLID = tbl_HazIPLs.HazIPLID) " & _
"INNER JOIN tbl_IPLs ON tbl_HazIPLs.IPLID = tbl_IPLs.IPLID " & _
"WHERE (((tbl_HazIPLs.HazIPLID)=" & intHazIPLID & " AND
((tbl_HazIPLs.Exist)=-1) " & _
"AND ((tbl_IPLHazTypeApplicability.HazTypeID)=" & intHazTypeID & "));"

Set rs = CurrentDb.OpenRecordset(strSQL)

Thanks in advance,
Chace
Click to expand...
Click to expand...
 
J

John Griffiths

Hi Chace

To check that what you get in strSQL what you expect add a debug print.

strSQL = "SELECT Sum(t.....and so on.... "));"

Debug.Print strSQL

Set rs = CurrentDb.OpenRecordset(strSQL)

If there is still problem you can post the actual SQL to be looked at.

Regards John
 
C

Chace

I had already done that but still could not see the problem. However here is
the debug print result I got......Do you see anything wrong? I almost wonder
if I am missing a reference file or something??

SELECT tbl_HazardConsequences.*, tbl_HazardConsequences.HazardID,
tbl_HazardConsequences.HazardTypeID FROM tbl_HazardConsequences WHERE
(((tbl_HazardConsequences.HazardID])=[Forms]![frm_Hazards]![HazardID]) AND
((tbl_HazardConsequences.HazardTypeID)=5));


John Griffiths said:
Hi Chace

To check that what you get in strSQL what you expect add a debug print.

strSQL = "SELECT Sum(t.....and so on.... "));"

Debug.Print strSQL

Set rs = CurrentDb.OpenRecordset(strSQL)

If there is still problem you can post the actual SQL to be looked at.

Regards John

Chace said:
Can someone please help to spot the syntax error in the following. The query
works fine outside of VBA, but it will not make it past this step in my VBA
code.

strSQL = "SELECT Sum(tbl_IPLs.[IPL Credit]) AS [SumOfIPL Credit] " & _
"FROM (tbl_IPLHazTypeApplicability INNER JOIN tbl_HazIPLs ON " & _
"tbl_IPLHazTypeApplicability.HazIPLID = tbl_HazIPLs.HazIPLID) " & _
"INNER JOIN tbl_IPLs ON tbl_HazIPLs.IPLID = tbl_IPLs.IPLID " & _
"WHERE (((tbl_HazIPLs.HazIPLID)=" & intHazIPLID & " AND
((tbl_HazIPLs.Exist)=-1) " & _
"AND ((tbl_IPLHazTypeApplicability.HazTypeID)=" & intHazTypeID & "));"

Set rs = CurrentDb.OpenRecordset(strSQL)

Thanks in advance,
Chace
Click to expand...
Click to expand...
 
D

Dirk Goldgar

Chace said:
I had already done that but still could not see the problem. However
here is the debug print result I got......Do you see anything wrong?
I almost wonder if I am missing a reference file or something??

SELECT tbl_HazardConsequences.*, tbl_HazardConsequences.HazardID,
tbl_HazardConsequences.HazardTypeID FROM tbl_HazardConsequences WHERE
(((tbl_HazardConsequences.HazardID])=[Forms]![frm_Hazards]![HazardID])
AND ((tbl_HazardConsequences.HazardTypeID)=5));
Click to expand...

That's not the SQL you originally posted. What's the story there?

With regard to the SQL above, if you try to open a recordset on that
using
Set rs = CurrentDb.OpenRecordset(strSQL)
Click to expand...

.... you'll get an error due to the missing "parameter" that is
[Forms]![frm_Hazards]![HazardID] . That form/control reference is
understood and resolved by Access when opening a query, but not by DAO.
Since you're building this SQL in code, build the value of the control
into the SQL string, in place of the form reference.
 
C

Chace

Thanks for your help and persistance.......You are right I modified the
original SQL (sorry I did not point that out before), but as you can see the
debug print looks okay, other than your comment regarding the reference to
the form control. However, that does not seem to be the problem either,
because in my troubleshooting, I replaced the form control with a simple
number (i.e. 1) and I still get the error. BTW this code is inside a
function subroutine.

The error is: "There may have been an error evaluating the function, event
or macro"

Dirk Goldgar said:
Chace said:
I had already done that but still could not see the problem. However
here is the debug print result I got......Do you see anything wrong?
I almost wonder if I am missing a reference file or something??

SELECT tbl_HazardConsequences.*, tbl_HazardConsequences.HazardID,
tbl_HazardConsequences.HazardTypeID FROM tbl_HazardConsequences WHERE
(((tbl_HazardConsequences.HazardID])=[Forms]![frm_Hazards]![HazardID])
AND ((tbl_HazardConsequences.HazardTypeID)=5));
Click to expand...

That's not the SQL you originally posted. What's the story there?

With regard to the SQL above, if you try to open a recordset on that
using
Set rs = CurrentDb.OpenRecordset(strSQL)
Click to expand...

.... you'll get an error due to the missing "parameter" that is
[Forms]![frm_Hazards]![HazardID] . That form/control reference is
understood and resolved by Access when opening a query, but not by DAO.
Since you're building this SQL in code, build the value of the control
into the SQL string, in place of the form reference.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
D

Dirk Goldgar

Chace said:
Thanks for your help and persistance.......You are right I modified
the original SQL (sorry I did not point that out before), but as you
can see the debug print looks okay, other than your comment regarding
the reference to the form control. However, that does not seem to be
the problem either, because in my troubleshooting, I replaced the
form control with a simple number (i.e. 1) and I still get the error.
BTW this code is inside a function subroutine.

The error is: "There may have been an error evaluating the function,
event or macro"
Click to expand...

Hmm, then I don't think we've got the full story yet. Have you done an
explicit compile of your project (Debug -> Compile, on the VB Editor
menu), to see if any coding errors are highlighted? If so, that would
be something to address. If not, please post the complete current code
for the function in question, indicating exactly which line is
highlighted at run time as the source of the error.
 
C

Chace

Okay, I compiled the following code with no problems identified, however it
is still getting hung up at the line designated below. Thanks so much for
the help....this is driving me crazy.

Function IPLHazApplicability(ctl As Control, intHazIPLID As Integer) As
Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strSQL As String
Dim strSQL2 As String
Dim strHazardType As String
Dim intHazTypeID As Integer
Dim intHazTypeIPLsum As Integer
Dim intHazardID As Integer

Set db = CurrentDb

strHazardType = ctl.Name
intHazardID = [Forms]![frm_Hazards]![HazardID]


Select Case strHazardType
Case "chk_FL"
intHazTypeID = 1
Case "chk_EX"
intHazTypeID = 2
Case "chk_MD"
intHazTypeID = 3
Case "chk_EN"
intHazTypeID = 4
Case "chk_RX"
intHazTypeID = 5
Case "chk_TX"
intHazTypeID = 6
Case "chk_IN"
intHazTypeID = 7
End Select

If ctl = -1 Then

Set rs = CurrentDb.OpenRecordset("tbl_IPLHazTypeApplicability")

rs.AddNew
rs![HazIPLID] = intHazIPLID
rs![HazTypeID] = intHazTypeID
rs.Update
rs.Close
Set rs = Nothing

Else
strSQL = "Select * from tbl_IPLHazTypeApplicability where HazIPLID=" &
intHazIPLID & " AND HazTypeID=" & intHazTypeID
Set rs = CurrentDb.OpenRecordset(strSQL)
Debug.Print strSQL

rs.MoveFirst
rs.Delete
rs.Close
Set rs = Nothing

End If

intHazTypeIPLsum = DSum("[IPL Credit]", "[Query4]", "([HazID]=
[Forms]![frm_Hazards]![HazardID] And [Exist])=-1 And [HazTypeID]=" &
intHazTypeID)


strSQL2 = "SELECT tbl_HazardConsequences.*, tbl_HazardConsequences.HazardID,
" & _
"tbl_HazardConsequences.HazardTypeID FROM tbl_HazardConsequences " & _
"WHERE (((tbl_HazardConsequences.HazardID])=" & intHazardID & ") AND " & _
"((tbl_HazardConsequences.HazardTypeID)=" & intHazTypeID & "));"

'Code will not go past line below, although it does NOT highlight yellow
Set rs2 = CurrentDb.OpenRecordset(strSQL2)

rs2![MitigFreq] = [Forms]![frm_Hazards]![cbo_InitFreq] * 10 ^
-intHazTypeIPLsum
rs2.Update
rs2.Close
Set rs2 = Nothing
[Forms]![frm_Hazards]![subfrm_HazardConsequences].Requery

End Function
 
D

Dirk Goldgar

Chace said:
Okay, I compiled the following code with no problems identified,
however it is still getting hung up at the line designated below.
Thanks so much for the help....this is driving me crazy.

Function IPLHazApplicability(ctl As Control, intHazIPLID As Integer)
As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strSQL As String
Dim strSQL2 As String
Dim strHazardType As String
Dim intHazTypeID As Integer
Dim intHazTypeIPLsum As Integer
Dim intHazardID As Integer

Set db = CurrentDb

strHazardType = ctl.Name
intHazardID = [Forms]![frm_Hazards]![HazardID]


Select Case strHazardType
Case "chk_FL"
intHazTypeID = 1
Case "chk_EX"
intHazTypeID = 2
Case "chk_MD"
intHazTypeID = 3
Case "chk_EN"
intHazTypeID = 4
Case "chk_RX"
intHazTypeID = 5
Case "chk_TX"
intHazTypeID = 6
Case "chk_IN"
intHazTypeID = 7
End Select

If ctl = -1 Then

Set rs = CurrentDb.OpenRecordset("tbl_IPLHazTypeApplicability")

rs.AddNew
rs![HazIPLID] = intHazIPLID
rs![HazTypeID] = intHazTypeID
rs.Update
rs.Close
Set rs = Nothing

Else
strSQL = "Select * from tbl_IPLHazTypeApplicability where
HazIPLID=" & intHazIPLID & " AND HazTypeID=" & intHazTypeID
Set rs = CurrentDb.OpenRecordset(strSQL)
Debug.Print strSQL

rs.MoveFirst
rs.Delete
rs.Close
Set rs = Nothing

End If

intHazTypeIPLsum = DSum("[IPL Credit]", "[Query4]", "([HazID]=
[Forms]![frm_Hazards]![HazardID] And [Exist])=-1 And [HazTypeID]=" &
intHazTypeID)


strSQL2 = "SELECT tbl_HazardConsequences.*,
tbl_HazardConsequences.HazardID, " & _
"tbl_HazardConsequences.HazardTypeID FROM tbl_HazardConsequences " & _
"WHERE (((tbl_HazardConsequences.HazardID])=" & intHazardID & ") AND
" & _ "((tbl_HazardConsequences.HazardTypeID)=" & intHazTypeID & "));"

'Code will not go past line below, although it does NOT highlight
yellow Set rs2 = CurrentDb.OpenRecordset(strSQL2)

rs2![MitigFreq] = [Forms]![frm_Hazards]![cbo_InitFreq] * 10 ^
-intHazTypeIPLsum
rs2.Update
rs2.Close
Set rs2 = Nothing
[Forms]![frm_Hazards]![subfrm_HazardConsequences].Requery

End Function
Click to expand...

I see two problems with that SQL statement, and I wonder if one of them
could be the problem :
strSQL2 = "SELECT tbl_HazardConsequences.*,
tbl_HazardConsequences.HazardID, " & _
"tbl_HazardConsequences.HazardTypeID FROM tbl_HazardConsequences " & _
"WHERE (((tbl_HazardConsequences.HazardID])=" & intHazardID & ") AND
" & _ "((tbl_HazardConsequences.HazardTypeID)=" & intHazTypeID & "));"
Click to expand...

First, there's an extraneous, unmatched right quare bracket (]) on the
end of
"WHERE (((tbl_HazardConsequences.HazardID])
Click to expand...

My guess is that's the culprit, but also you have the fields HazardID
and HazardTypeID included twice in the result set. Try this:

strSQL2 = _
"SELECT tbl_HazardConsequences.* " & _
"FROM tbl_HazardConsequences " & _
"WHERE (HazardID=" & intHazardID & ") AND (" & _
HazardTypeID=" & intHazTypeID & ");"
 
C

Chace

At first your suggested code did NOT work. I then tried to open the table
"tbl_HazardConsequences" manually and got an error that it was "being
modified by another user, open as read only?" This could have been related
to a subform on the main form that has its recordsource as a query based on
this table (and others).....maybe it had it locked?? Or it could be related
to the fact that I was "timed out" the other day during design (citrix
issue) and it could have left this table in some type of locked state.

I somehow got the table to open manually without getting the error above.
Now your suggested code DOES work as well as some of my other varieties that
I have tested before.

Not sure what happened in the end, but everything seems fine for now.

Thanks so much for your help.
Chace

Dirk Goldgar said:
Chace said:
Okay, I compiled the following code with no problems identified,
however it is still getting hung up at the line designated below.
Thanks so much for the help....this is driving me crazy.

Function IPLHazApplicability(ctl As Control, intHazIPLID As Integer)
As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strSQL As String
Dim strSQL2 As String
Dim strHazardType As String
Dim intHazTypeID As Integer
Dim intHazTypeIPLsum As Integer
Dim intHazardID As Integer

Set db = CurrentDb

strHazardType = ctl.Name
intHazardID = [Forms]![frm_Hazards]![HazardID]


Select Case strHazardType
Case "chk_FL"
intHazTypeID = 1
Case "chk_EX"
intHazTypeID = 2
Case "chk_MD"
intHazTypeID = 3
Case "chk_EN"
intHazTypeID = 4
Case "chk_RX"
intHazTypeID = 5
Case "chk_TX"
intHazTypeID = 6
Case "chk_IN"
intHazTypeID = 7
End Select

If ctl = -1 Then

Set rs = CurrentDb.OpenRecordset("tbl_IPLHazTypeApplicability")

rs.AddNew
rs![HazIPLID] = intHazIPLID
rs![HazTypeID] = intHazTypeID
rs.Update
rs.Close
Set rs = Nothing

Else
strSQL = "Select * from tbl_IPLHazTypeApplicability where
HazIPLID=" & intHazIPLID & " AND HazTypeID=" & intHazTypeID
Set rs = CurrentDb.OpenRecordset(strSQL)
Debug.Print strSQL

rs.MoveFirst
rs.Delete
rs.Close
Set rs = Nothing

End If

intHazTypeIPLsum = DSum("[IPL Credit]", "[Query4]", "([HazID]=
[Forms]![frm_Hazards]![HazardID] And [Exist])=-1 And [HazTypeID]=" &
intHazTypeID)


strSQL2 = "SELECT tbl_HazardConsequences.*,
tbl_HazardConsequences.HazardID, " & _
"tbl_HazardConsequences.HazardTypeID FROM tbl_HazardConsequences " & _
"WHERE (((tbl_HazardConsequences.HazardID])=" & intHazardID & ") AND
" & _ "((tbl_HazardConsequences.HazardTypeID)=" & intHazTypeID & "));"

'Code will not go past line below, although it does NOT highlight
yellow Set rs2 = CurrentDb.OpenRecordset(strSQL2)

rs2![MitigFreq] = [Forms]![frm_Hazards]![cbo_InitFreq] * 10 ^
-intHazTypeIPLsum
rs2.Update
rs2.Close
Set rs2 = Nothing
[Forms]![frm_Hazards]![subfrm_HazardConsequences].Requery

End Function
Click to expand...

I see two problems with that SQL statement, and I wonder if one of them
could be the problem :
strSQL2 = "SELECT tbl_HazardConsequences.*,
tbl_HazardConsequences.HazardID, " & _
"tbl_HazardConsequences.HazardTypeID FROM tbl_HazardConsequences " & _
"WHERE (((tbl_HazardConsequences.HazardID])=" & intHazardID & ") AND
" & _ "((tbl_HazardConsequences.HazardTypeID)=" & intHazTypeID & "));"
Click to expand...

First, there's an extraneous, unmatched right quare bracket (]) on the
end of
"WHERE (((tbl_HazardConsequences.HazardID])
Click to expand...

My guess is that's the culprit, but also you have the fields HazardID
and HazardTypeID included twice in the result set. Try this:

strSQL2 = _
"SELECT tbl_HazardConsequences.* " & _
"FROM tbl_HazardConsequences " & _
"WHERE (HazardID=" & intHazardID & ") AND (" & _
HazardTypeID=" & intHazTypeID & ");"

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
D

Dirk Goldgar

Chace said:
At first your suggested code did NOT work. I then tried to open the
table "tbl_HazardConsequences" manually and got an error that it was
"being modified by another user, open as read only?" This could have
been related to a subform on the main form that has its recordsource
as a query based on this table (and others).....maybe it had it
locked?? Or it could be related to the fact that I was "timed out"
the other day during design (citrix issue) and it could have left
this table in some type of locked state.

I somehow got the table to open manually without getting the error
above. Now your suggested code DOES work as well as some of my other
varieties that I have tested before.

Not sure what happened in the end, but everything seems fine for now.
Click to expand...

I don't know, either, but if it's working now, I guess we can live with
it.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL VBA -- INNER JOIN Syntax 6
SQL syntax 1
Syntax Error 1
Syntax error baffling me 1
syntax error 1
Problem with Syntax Error (Missing Operator) 2
Repost-Assist with Syntax Error(Missing Op) 1
Syntax error 8

Top