Solved

Access SQL

Posted on 2016-09-03
12
45 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
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 39

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
 

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 39

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:shieldsco
ID: 41783713
error
0
 
LVL 39

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 39

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

920 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

15 Experts available now in Live!

Get 1:1 Help Now