Access VBA Query Criteria

[Coleman984]

I've found many threads on the internet that have similar questions; however I have not yet been able to adapt any of other users problems to suit my needs as I am just trying to learn Access VBA, I've become decent with excel VBA but that is not helping me very much here.

The problem I've having is I'm trying to create a query with VBA when a command button is clicked; that uses several criteria. Each day should be filtered Sunday through Monday and only entries that contain 1 of 7 values should be displayed. I initially tried to use excel entirely for this project but wasn't able to get what I wanted done. And since I was basically trying to create a database with excel I decided to just use Access (using Excel still to pass data to Access; that was easy). I really don't know what else to say to make it clear what I'm trying to accomplish.

Query name is:
Query_Weekof17July2011

The database contains the following columns from left to right:
EmployeName
EmployeeID
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday


I will include what I've found.

Private Sub cmbSubmit_Click()
    Dim TheDbPath As String
    Dim QueryName As String
    Dim SQLStr As String
    'First we'll set some variables:

    strSQL = "SELECT * FROM Me.cBoxTableNames.Value WHERE (Sunday = ABS)"
  
    TheDbPath = "C:\Documents and Settings\randell.graybill\My Documents\Attendance.mdb"
    QueryName = Me.cBoxTableNames.Value & " Query"
    SQLStr = "SELECT * FROM " & Me.cBoxTableNames.Value
    Call CreateAccessQuery(TheDbPath, QueryName, SQLStr)
    

DoCmd.Close acForm, Me.Name

DoCmd.OpenForm "AttendanceForm"

End Sub

Sub CreateAccessQuery(ByVal TheDbPath As String, QueryName As String, SQLStr As String)
     
     '  1/2/06 - Carl for : Oz
     '  requies references : MS Active X Data Objects libs and MS Active X Ext
     
    Dim cn As ADODB.Connection
    Dim cat As ADOX.Catalog
    Dim cmd As ADODB.Command
    Set cn = New ADODB.Connection
    
    
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=" & TheDbPath & ";"
     
    If cn.State <> 1 Then
        MsgBox ("Problem with connection")
        Exit Sub
    End If
     
    Set cat = New ADOX.Catalog
    Set cmd = New ADODB.Command
     
    cat.ActiveConnection = cn
     
    cmd.CommandText = SQLStr
    On Error Resume Next
    
    cat.Views.Append QueryName, cmd
    
    If Err.Number = -2147217816 Then
    Err.Clear
    MsgBox "This Query already exists!"
    End If
     
    Set cat = Nothing
    Set cmd = Nothing
    cn.Close
    Set cn = Nothing
     

End Sub

 

[Coleman984]

Using something I found on this forum I was able to get a different error message. See below for the new code I've tried.

Private Sub cmbSubmit_Click()
    Dim TheDbPath As String
    Dim QueryName As String
    Dim SQLStr As String
    Dim Crt1 As String
    
    Crt1 = "ABS"
    'First we'll set some variables:

    strSQL = "SELECT  Weekof17July2011.EmployeeName, Weekof17July2011.EmployeeID, Weekof17July2011.Sunday," & _
"Weekof17July2011.Monday, Weekof17July2011.Tuesday, Weekof17July2011.Wednesday, Weekof17July2011.Thursday" & _
"Weekof17July2011.Friday,Weekof17July2011.Saturday" & _
"FROM Weekof17July2011" & _
"WHERE (((Weekof17July2011.Sunday) Crt1))"

  
    TheDbPath = "C:\Documents and Settings\randell.graybill\My Documents\Attendance.mdb"
    QueryName = Me.cBoxTableNames.Value & " Query"
    'SQLStr = "SELECT * FROM " & Me.cBoxTableNames.Value
  
      Dim cn As ADODB.Connection
    Dim cat As ADOX.Catalog
    Dim cmd As ADODB.Command
    Set cn = New ADODB.Connection
    
    
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=" & TheDbPath & ";"
     
    If cn.State <> 1 Then
        MsgBox ("Problem with connection")
        Exit Sub
    End If
     
    Set cat = New ADOX.Catalog
    Set cmd = New ADODB.Command
     
    cat.ActiveConnection = cn
     
    cmd.CommandText = SQLStr
    'On Error Resume Next
    
    cat.Views.Append QueryName, cmd
    
    If Err.Number = -2147217816 Then
    Err.Clear
    MsgBox "This Query already exists!"
    End If
     
    Set cat = Nothing
    Set cmd = Nothing
    cn.Close
    Set cn = Nothing

    

DoCmd.Close acForm, Me.Name

DoCmd.OpenForm "AttendanceForm"

End Sub

 

[spikepl]

Your basic problem seems to be that you are trying to recreate an Excel look on data in your data structure, and hence the need for making queries as you are attempting to do.
Read about data normalization, http://www.r937.com/Relational.html.

Data of same type is normally stored with the value and some identifier

tblMyvalues
-------------
myValueID
TheValue
MyDate


this would allow you to make one query only, and pick up the desired values according to some specified parameters, instead of having to create new query each time.

 

[Coleman984]

Your basic problem seems to be that you are trying

this would allow you to make one query only, and pick up the desired values according to some specified parameters, instead of having to create new query each time.

I'm not sure if I was clear in that a new query would be needed on a per week basis. Or is this what you are talking about? Is my brain so stuck in "excel" that I'm not truly grasping what I should be attempting to do in access?

 

[plog]

Take a step back and forget about VBA and SQL for a minute. Give a specific example of the rows in your table and what you data your query should return.

 

[Coleman984]

Take a step back and forget about VBA and SQL for a minute. Give a specific example of the rows in your table and what you data your query should return.

Well the purpose of this database will be to track attendance on a week by week basis. With employee name and ID being tracked along with any marks against attendance.

 

[spikepl]

#4 Yes. In Excel data are displayed AND stored in the same way. In Access you can present data in the same way as in Excel. But that does not mean the data should be stored in the same way as in Excel. Rather the opposite.

If a query needs to point at a different table or at different colums, just because some other range of similar data is required, then the data structure needs rework. Check the link provided in #3

Also, search for Attendance using the Search link at the top of this web page, or by using google and restricting the search to this site. You are not the first with such problems

 

[plog]

Well the purpose of this database will be to track attendance on a week by week basis. With employee name and ID being tracked along with any marks against attendance.

Actually, from the table structure you initially listed it sounds like its day-to-day (Sunday, Monday, Tuesday...). If that is what you want to do and nothing else, then you need 2 different tables: 1 table with fields for employee name and employee id; and another table with a field for employee id and a date field that holds when an employee was absent.