Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

Access SQL

Posted on 2016-09-03
12
Medium Priority
?
80 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +1
12 Comments
 
LVL 31

Expert Comment

by:hnasr
ID: 41783374
SELECT is missing in the query.
0
 

Author Comment

by:shieldsco
ID: 41783662
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 40

Expert Comment

by:als315
ID: 41783681
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:shieldsco
ID: 41783688
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
ID: 41783690
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 40

Accepted Solution

by:
als315 earned 1500 total points
ID: 41783702
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
 

Author Comment

by:shieldsco
ID: 41783713
error
0
 
LVL 40

Expert Comment

by:als315
ID: 41783727
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 40

Expert Comment

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

Expert Comment

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

Expert Comment

by:Helen Feddema
ID: 41783756
Yes, I think we need to see the field names and data types.
0
 

Author Comment

by:shieldsco
ID: 41783787
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

649 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