April 8, 2011 at 4:13 pm
This query is throwing an exception and I do not know why yet. The logic is there I just need to implement it properly and I do not know what is wrong yet.
Option Compare Database
Public Sub TextsPerDayByUser()
If IsNull(DLookup("[Name]", "[MSysObjects]", "[Type] IN (1, 4, 6) AND [Name] = '" & "TextsPerHourByUser" & "'")) Then
CreateTable_TextsPerHourByUser ("TextsPerHourByUser")
Else
DoCmd.DeleteObject acTable, "TextsPerHourByUser"
CreateTable_TextsPerHourByUser ("TextsPerHourByUser")
End If
Dim dbs As DAO.Database
Set dbs = CurrentDb
Dim strHour(23) As String
strHour(0) = "00:00:00"
strHour(1) = "01:00:00"
strHour(2) = "02:00:00"
strHour(3) = "03:00:00"
strHour(4) = "04:00:00"
strHour(5) = "05:00:00"
strHour(6) = "06:00:00"
strHour(7) = "07:00:00"
strHour(8) = "08:00:00"
strHour(9) = "09:00:00"
strHour(10) = "10:00:00"
strHour(11) = "11:00:00"
strHour(12) = "12:00:00"
strHour(13) = "13:00:00"
strHour(14) = "14:00:00"
strHour(15) = "15:00:00"
strHour(16) = "16:00:00"
strHour(17) = "17:00:00"
strHour(18) = "18:00:00"
strHour(19) = "19:00:00"
strHour(20) = "20:00:00"
strHour(21) = "21:00:00"
strHour(22) = "22:00:00"
strHour(23) = "23:00:00"
strHour(23) = "23:00:00"
Dim rstDistinctUsers As DAO.Recordset2
Set rstDistinctUsers = dbs.OpenRecordset("SELECT DISTINCT Usr FROM Main_Table")
Do While Not rstDistinctUsers.EOF
Dim curuser As String
curuser = rstDistinctUsers("Usr")
For a_cnt = 0 To 23
Dim dHour As Date
dHour = strHour(a_cnt)
Dim strSQL As String
strSQL = "SELECT * FROM Main_Table WHERE Usr='" & curuser & "' AND Tme >='" & CDate(strHour(a_cnt)) & "' AND Tme <='" & CDate(strHour(a_cnt + 1)) & "'"
Dim rstTextsThisHour As DAO.Recordset2
Set rstTextsThisHour = dbs.OpenRecordset(strSQL)
Next a_cnt
rstDistinctUsers.MoveNext
Loop
End Sub
Function CreateTable_TextsPerHourByUser(strTableName As String) As Integer
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Dim fld As DAO.Field2
'Get the database
Set dbs = CurrentDb
'Table does not exist
'Create the colors table
Set tdf = dbs.CreateTableDef(strTableName)
'Create the field
Set fld = tdf.CreateField("ID", dbLong)
fld.Attributes = dbAutoIncrField
tdf.Fields.Append fld
Set fld = Nothing
'Create the field
Set fld = tdf.CreateField("Usr", dbText, 255)
tdf.Fields.Append fld
Set fld = Nothing
'Create the field
Set fld = tdf.CreateField("Dte", dbText, 255)
tdf.Fields.Append fld
Set fld = Nothing
'Create the field
Set fld = tdf.CreateField("Texts", dbInteger)
tdf.Fields.Append fld
Set fld = Nothing
'Create the Primary Key
Set ind = tdf.CreateIndex("PrimaryKey")
With ind
.Fields.Append .CreateField("ID")
.Primary = True
End With
tdf.Indexes.Append ind
'Append the table to the database
dbs.TableDefs.Append tdf
'Cleanup
Set fld = Nothing
Set tdf = Nothing
Set dbs = Nothing
End Function
April 28, 2011 at 10:10 am
I would step through the code to capture the results of strSQL and execute in Query Analyzer.
Then check the error and narrow down the problem.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 28, 2011 at 10:13 am
It's <almost> always the date that's causing the problem.
definitely go the copy / paste to query analyser or ssms and check for exact problems there.
If you have the error message and post it then maybe we can help further.
July 10, 2011 at 12:19 pm
I take it that you resolved you problem since it has been months from your initial post? 😀
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply