Solved

Access SQL

Posted on 2016-09-03
12
40 Views
Last Modified: 2016-09-04
ErrorI'm using the code below and get a runtime error 3078 cannot find input table or query, Can you help?

Set rst = CurrentDb.OpenRecordset("tblUserMaster.[OpDiv], tblUserMaster.[Fname], tblUserMaster.[Lname], tblUserMaster.[Level], tblUserMaster.[Approver] From tblUserMaster WHERE (((tblUserMaster.Active = 'Yes' And ((tblUserMaster.OpDiv) = 'CDC')GROUP BY tblUserMaster.[OpDiv], tblUserMaster.[Level]ORDER BY tblUserMaster.[OpDiv]")

Open in new window

0
Comment
Question by:shieldsco
  • 4
  • 4
  • 2
  • +1
12 Comments
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
SELECT is missing in the query.
0
 

Author Comment

by:shieldsco
Comment Utility
Syntax error

Error
Set rst = CurrentDb.OpenRecordset("Select tblUserMaster.[OpDiv], tblUserMaster.[Fname], tblUserMaster.[Lname], tblUserMaster.[Level], tblUserMaster.[Approver] From tblUserMaster WHERE (((tblUserMaster.Active = 'Yes') And ((tblUserMaster.OpDiv) = 'CDC')) GROUP BY tblUserMaster.[OpDiv], tblUserMaster.[Fname], tblUserMaster.Lname, tblUserMaster.[Level], tblUserMaster.[Approver] ORDER BY tblUserMaster.[Level]")

Open in new window

0
 
LVL 39

Expert Comment

by:als315
Comment Utility
Always count an opening and closing parenthesis. In this query you have 5 opening and only 4 closing. Try to remove first one:
Set rst = CurrentDb.OpenRecordset("Select tblUserMaster.[OpDiv], tblUserMaster.[Fname], tblUserMaster.[Lname], tblUserMaster.[Level], tblUserMaster.[Approver] From tblUserMaster WHERE ((tblUserMaster.Active = 'Yes') And ((tblUserMaster.OpDiv) = 'CDC')) GROUP BY tblUserMaster.[OpDiv], tblUserMaster.[Fname], tblUserMaster.Lname, tblUserMaster.[Level], tblUserMaster.[Approver] ORDER BY tblUserMaster.[Level]")

Open in new window

You can also remove extra pair from last part of where:
Set rst = CurrentDb.OpenRecordset("Select tblUserMaster.[OpDiv], tblUserMaster.[Fname], tblUserMaster.[Lname], tblUserMaster.[Level], tblUserMaster.[Approver] From tblUserMaster WHERE (((tblUserMaster.Active = 'Yes') And (tblUserMaster.OpDiv = 'CDC')) GROUP BY tblUserMaster.[OpDiv], tblUserMaster.[Fname], tblUserMaster.Lname, tblUserMaster.[Level], tblUserMaster.[Approver] ORDER BY tblUserMaster.[Level]")

Open in new window

0
 

Author Comment

by:shieldsco
Comment Utility
Capture.JPG
Set rst = CurrentDb.OpenRecordset("Select tblUserMaster.[OpDiv], tblUserMaster.[Fname], tblUserMaster.[Lname], tblUserMaster.[Level], tblUserMaster.[Approver] From tblUserMaster WHERE (((tblUserMaster.Active = 'Yes') And (tblUserMaster.OpDiv = 'CDC')) GROUP BY tblUserMaster.[OpDiv], tblUserMaster.[Fname], tblUserMaster.Lname, tblUserMaster.[Level], tblUserMaster.[Approver] ORDER BY tblUserMaster.[Level]")

Open in new window

0
 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
It is very hard to debug a long SQL statement in a line of code -- I recommend saving it as a temporary query, which is then used to create a recordset.  That makes it much easier to examine and correct any errors in the SQL.  Here are some examples (using the CreateAndTestQuery procedure at the of the code):

Private Sub cmdFilter_Click()
'Created by Helen Feddema 10-Aug-2010
'Last modified by Helen Feddema 10-Aug-2010

On Error GoTo ErrorHandler

   Dim dbs As DAO.Database
   Dim intColumn As Integer
   Dim intColumns As Integer
   Dim intCount As Integer
   Dim intIndex As Integer
   Dim intRow As Integer
   Dim intRows As Integer
   Dim lngCount As Long
   Dim lst As Access.ListBox
   Dim strFilter As String
   Dim strData As String
   Dim strPrompt As String
   Dim strQuery As String
   Dim strSQL As String
   Dim strTest As String
   Dim strTitle As String
   Dim varItem As Variant
   
   strFilter = ""
   Set lst = Me![lstStates]
      
   'Check that at least one state has been selected
   If lst.ItemsSelected.Count = 0 Then
      strTitle = "No items selected"
      strPrompt = "Please select at least one state"
      MsgBox prompt:=strPrompt, _
         buttons:=vbInformation + vbOKOnly, _
         Title:=strTitle
      lst.SetFocus
      GoTo ErrorHandlerExit
   End If
   
   intColumns = lst.ColumnCount
   intRows = lst.ItemsSelected.Count
   
   'Create concatenated filter string
   For Each varItem In lst.ItemsSelected
      strData = Nz(lst.Column(0, varItem))
      strFilter = strFilter & "[LocationState] = " & Chr(39) _
         & strData & Chr(39) & " Or "
   Next varItem

   'Trim last "Or" from filter string
   strFilter = Left(strFilter, Len(strFilter) - 4)
   Debug.Print "Filter string: " & strFilter
   
   'Create filtered recordset for other subform
   strQuery = "qryFilteredProjects"
   Set dbs = CurrentDb
   strSQL = "SELECT * FROM tblProjects WHERE " & strFilter _
      & " ORDER BY [LocationState];"
   Debug.Print "SQL for " & strQuery & ": " & strSQL
   lngCount = CreateAndTestQuery(strQuery, strSQL)
   Debug.Print "No. of items found: " & lngCount
   Parent![subFilteredProjects].Form.RecordSource = strQuery
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
Comment Utility
Sorry, it was my mistake in last code sample (4 opening and only 3 closing parenthesis):
Set rst = CurrentDb.OpenRecordset("Select tblUserMaster.[OpDiv], tblUserMaster.[Fname], tblUserMaster.[Lname], tblUserMaster.[Level], tblUserMaster.[Approver] From tblUserMaster WHERE ((tblUserMaster.Active = 'Yes') And (tblUserMaster.OpDiv = 'CDC')) GROUP BY tblUserMaster.[OpDiv], tblUserMaster.[Fname], tblUserMaster.Lname, tblUserMaster.[Level], tblUserMaster.[Approver] ORDER BY tblUserMaster.[Level]")

Open in new window

0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:shieldsco
Comment Utility
error
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
Now you should check type of data in two fields:
tblUserMaster.Active = 'Yes' (May be it is boolean field?)
and
tblUserMaster.OpDiv = 'CDC' (Is it text field?)
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
If first field is boolean (Yes/No field is boolean), you can use:
tblUserMaster.Active = True
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
It will greatly help if you upload a sample database demonstrating the issue.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
Yes, I think we need to see the field names and data types.
0
 

Author Comment

by:shieldsco
Comment Utility
I figured out.... see code below.

Set rst = CurrentDb.OpenRecordset("SELECT tblUserMaster.OpDiv, tblUserMaster.Fname, tblUserMaster.Lname, tblUserMaster.Level, tblUserMaster.Approver FROM tblUserMaster WHERE (((tblUserMaster.Active) = Yes) And ((tblUserMaster.OpDiv) = 'CDC')) GROUP BY tblUserMaster.OpDiv, tblUserMaster.Fname, tblUserMaster.Lname, tblUserMaster.Level, tblUserMaster.Approver")

Open in new window

0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now