Error when counting data by date

80 views Asked by At

This is the error:

Additional information: Syntax error (missing operator) 
in query expression 'count(rev1) FORMAT(Now(),'mm/dd/yyyy')'.

This is the code:

 Dim con As New OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\sheetlog.mdb;Jet OLEDB:Database Password = 'password';")
 Dim cmd, cmd1 As OleDbCommand
 Dim dbcmd As String
 Dim dbcmd2 As String

 con.Open()

 dbcmd2 = "SELECT DISTINCT empname FROM sheet"
 dbcmd = "SELECT count(new), count(rev1) FORMAT(Now(),'mm/dd/yyyy') As PerDate FROM sheet"

 cmd = New OleDbCommand(dbcmd, con)
 cmd1 = New OleDbCommand(dbcmd2, con)

 ListView1.Clear()
 ListView1.GridLines = True
 ListView1.FullRowSelect = True
 ListView1.View = View.Details
 ListView1.MultiSelect = False
 ListView1.Columns.Add("Employee Name", 200)
 ListView1.Columns.Add("New", 80)
 ListView1.Columns.Add("Revision1", 80)
 ListView1.Columns.Add("Revision2", 80)
 ListView1.Columns.Add("Revision3", 80)
 ListView1.Columns.Add("Revision4", 80)
 ListView1.Columns.Add("Revision5", 80)


 Dim Reader As OleDbDataReader = cmd1.ExecuteReader()
 Dim Reader1 As OleDbDataReader = cmd.ExecuteReader()

 While Reader.Read And Reader1.Read
     With ListView1.Items.Add(Reader("empname"))
                   .subitems.add(Reader1("new"))
                   .subitems.add(Reader1("rev1"))
               '   .subitems.add(Reader1("crev2"))
                '  .subitems.add(Reader1("crev3"))
                '  .subitems.add(Reader1("crev4"))
                 ' .subitems.add(Reader1("crev5"))
     End With
  End While

  con.Close()

End Sub
0

There are 0 answers