Solved

Access SQL

Posted on 2016-09-03
12
63 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 30

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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 500 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 30

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

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.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

738 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