| Author |
 Topic  |
|
|
withanhdammit
Junior Member
USA
236 Posts |
 Posted - 05 July 2005 : 14:27:02
|
DBType = Access 2003
Tables = tblType, tblOwners, tblStoreData
tblType has TypeID, TypeName
tblOwners has OwnerID, OwnerName
tblStoreData has TypeID, OwnerID, plus too many others to list, they're not
important here
I have an inner join that's giving me fits. It is:
strSQL = "SELECT * FROM (tblStoreData " _
& "INNER JOIN tblOwners ON tblStoreData.OwnerID = tblOwners.OwnerID) " _
& "INNER JOIN tblType ON tblStoreData.TypeID = tblType.TypeID "
If I take the 2nd INNER JOIN out, it works fine, if I take out the 1st INNER JOIN it breaks, so it's obviously the 2nd INNER JOIN that's the problem.
When I run the report that calls this SQL statement, it tells me "The
specified field 'TypeID' could refer to more than one table listed in the FROM clause of your SQL statement."
Does anyone have any ideas for me?
Thanks!
Darhl
edited to fix spelling typo |
I reject your reality and substitute my own. |
Edited by - withanhdammit on 05 July 2005 15:12:22 |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 06 July 2005 : 05:52:24
|
| not sure about the parentheses - try removing them. your join conditions look fine to me. |
 |
|
|
withanhdammit
Junior Member
USA
236 Posts |
Posted - 06 July 2005 : 12:12:18
|
Rui,
Yes, it is when I run this in Access when I get the error.
Pdrq, the parentheses need to be there. Without them, I get a syntax error: "Syntax error (missing operator) in query expression 'tblStoreData.OwnerID = tblOwners.OwnerID INNER JOIN tblType on tblStoreData.TypeID = tblType.TypeID'.
And, I've done some more research and findings. This query is built into a report, the report is called by a form. The form has two option groups to apply filters. It is only when the "type" option group is filtered that it gives me fits. If the "type" filter is not applied then it works fine, it even works fine when the other filter "include" is applied. I will copy the forms apply filter sub.
Thanks!
h
Private Sub Apply_Filter()
strFilter = Null
Me.tglNonTraditional.Enabled = False
Me.tglUpcomingStores.Enabled = False
If Me.cboOwner <> "" Or Not IsNull(Me.cboOwner) Then
strFilter = "OwnerID = " & Me.cboOwner
End If
Select Case Me.ogType
Case 1
Me.tglNonTraditional.Enabled = False
Me.ogReports = 1
Case 2
Me.tglNonTraditional.Enabled = False
Me.ogReports = 1
If Not IsNull(strFilter) Then strFilter = strFilter & " And "
strFilter = strFilter & "TypeID = 1"
Case 3
Me.tglNonTraditional.Enabled = True
Me.ogReports = 4
If Not IsNull(strFilter) Then strFilter = strFilter & " And "
strFilter = strFilter & "TypeID <> 1"
End Select
Select Case Me.ogInclude
Case 1
Me.tglUpcomingStores.Enabled = False
If Me.ogType <> 3 Then Me.ogReports = 1 Else Me.ogReports = 4
Case 2
Me.tglUpcomingStores.Enabled = False
If Me.ogType <> 3 Then Me.ogReports = 1 Else Me.ogReports = 4
If Not IsNull(strFilter) Then strFilter = strFilter & " And "
strFilter = strFilter & "StoreOpen = True"
Case 3
Me.tglUpcomingStores.Enabled = True
Me.ogReports = 5
If Not IsNull(strFilter) Then strFilter = strFilter & " And "
strFilter = strFilter & "StoreOpen = False"
End Select
If IsNull(strFilter) Then
Me.FilterOn = False
Else
Me.Filter = strFilter
Me.FilterOn = True
End If
If IsNull(Me.cboOwner) Then
Me.cmdClear.Enabled = False
Else
Me.cmdClear.Enabled = (Me.cboOwner <> "")
End If
End Sub
Edited to include syntax error text. |
I reject your reality and substitute my own. |
Edited by - withanhdammit on 06 July 2005 12:18:04 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 06 July 2005 : 13:21:01
|
| It's clearly the filter that is giving you the problems. Wherever you have TypeID specified as a filter criteria, specify the table as well, say as, tblStoreData.TypeID, instead of using TypeID alone. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
withanhdammit
Junior Member
USA
236 Posts |
Posted - 06 July 2005 : 17:52:03
|
quote:
Originally posted by ruirib
It's clearly the filter that is giving you the problems. Wherever you have TypeID specified as a filter criteria, specify the table as well, say as, tblStoreData.TypeID, instead of using TypeID alone.
So, the code for the Select Case Me.ogType should be:Select Case Me.ogType
Case 1
Me.tglNonTraditional.Enabled = False
Me.ogReports = 1
Case 2
Me.tglNonTraditional.Enabled = False
Me.ogReports = 1
If Not IsNull(strFilter) Then strFilter = strFilter & " And "
strFilter = strFilter & "tblStoreData.TypeID = 1"
Case 3
Me.tglNonTraditional.Enabled = True
Me.ogReports = 4
If Not IsNull(strFilter) Then strFilter = strFilter & " And "
strFilter = strFilter & "tblStoreData.TypeID <> 1"
End Select |
I reject your reality and substitute my own. |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 07 July 2005 : 06:06:03
|
| sorry, my Jet/Access days are a bit behind me now, but let me reassure you your join conditions in your original post are fine SQL, even if Access gets shirty about them ;-) |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
withanhdammit
Junior Member
USA
236 Posts |
Posted - 07 July 2005 : 12:14:22
|
quote:
Originally posted by pdrg
sorry, my Jet/Access days are a bit behind me now, but let me reassure you your join conditions in your original post are fine SQL, even if Access gets shirty about them ;-)
Thanks, I thought I had the joins done properly!
h |
I reject your reality and substitute my own. |
|
|
|
withanhdammit
Junior Member
USA
236 Posts |
Posted - 07 July 2005 : 12:17:09
|
quote:
Originally posted by ruirib
I guess that should fix it.
Thanks Rui, I'll give it a go and let you know the results.
h |
I reject your reality and substitute my own. |
Edited by - withanhdammit on 07 July 2005 12:17:36 |
|
|
|
withanhdammit
Junior Member
USA
236 Posts |
Posted - 09 July 2005 : 00:52:22
|
quote:
Originally posted by withanhdammit
quote:
Originally posted by ruirib
I guess that should fix it.
Thanks Rui, I'll give it a go and let you know the results.
h
Thanks a lot for your suggestion Rui, that did the trick. I actually found another query when I was selecting different options on my form that I fixed the same way.
Your help and support is highly appreciated!
h |
I reject your reality and substitute my own. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
| |
Topic |
|
Topic Locked
.