SQL Query to pull records of exception entry

Question

I am new to Excel VBA. I have a UserForm in which I am trying to populate records of exception entered by employee for the current month. I have a database called Ofc. Under which I have a table Codeusage.

Primary key is Sno. Here is Name of fields in table Codeusage details the structure and Records saved.

Objective: For example if I want to see all exceptions 43243312 has updated for the month of Oct then it should show three records.

The contents of the Codeusage table:

Sno PeoplesoftId    Date of exception   Start of exception  End of exception    Exception time in minutes   Code used   Reason for code usage   Authorized By      
36  43243312    10/7/2015   12:10:00 AM 12:15:00 AM 05  Code 11 - Coaching  fg  Maddala    
37  43243312    10/7/2015   4:20:40 AM  4:35:40 AM  15  Code 3 - Team Meeting   lsdfj   Abhishek       
38  43243309    10/7/2015   1:00:00 AM  1:15:00 AM  15  code 19 - onetoone  kwjke   Atanu      
39  43243312    10/6/2015   12:00:00 AM 12:10:00 AM 10  Code 11 - Coaching  sajjkh  Maddala  

Design view of table

Sno                             AutoNumber      
PeoplesoftId                        Text
Date of exception                   Date/Time
Start of exception                  Date/Time
End of exception                    Date/Time
Exception time in minutes           Text
Code used                           Text
Reason for code usage               Text
Authorized By                       Text

This is the sql statement written:

SQL1 = "SELECT * FROM CodeUsage where PeoplesoftId=" & "'" & var & "'" & " or format(Date of exception,""MM"")=" & "'" & Format(Now, "MM") & "'" & ""

But is giving an error.

Answer 1

A couple of points:

1. I'd suggest you parameterise your query. You only need one daft so-and-so to rename his ID "0; DROP TABLE Codeusage" or some such hilarious prank and unsanitised SQL text commands can give you a whole world of hurt.
2. Column names with spaces are ripe for causing trouble and are usually considered poor design, so I'd remove those spaces if you possibly can. If not then you'll need to quote your column names. You'll have to check what works for your particular system but straight brackets would be a good first bet.

You'll have to adjust the code below to meet the specific requirements of your database (you don't mention which one), but this is what I would do for something like SQL Server or SQLite. Note, I've referenced Microsoft ActiveX Data Objects 6.1 Library via Tools -> References):

Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim rs As ADODB.Recordset
Dim firstOfMonth As Date
Dim lastOfMonth As Date
Dim PeoplesoftId As String

'Open the database connection
Set conn = New ADODB.Connection
conn.CursorLocation = adUseClient
conn.Open "[Your connection string goes here]"

'Define your query parameters
PeoplesoftId = "43243312"
firstOfMonth = DateSerial(Year(Now), Month(Now), 1)
lastOfMonth = DateSerial(Year(firstOfMonth), Month(firstOfMonth) + 1, 0)

'Create your command
Set cmd = New ADODB.Command
cmd.ActiveConnection = conn

'Command text
cmd.CommandText = "SELECT * FROM CodeUsage" & _
                  " WHERE PeoplesoftId=?" & _
                  " AND Date_of_exception>=?" & _
                  " AND Date_of_exception<=?" 'note I've replaced your spaces with underscore

cmd.CommandType = adCmdText

'Command parameters
Set prm = cmd.CreateParameter(, adChar, adParamInput, Len(PeoplesoftId), PeoplesoftId)
cmd.Parameters.Append prm

Set prm = cmd.CreateParameter(, adDate, adParamInput, , firstOfMonth)
cmd.Parameters.Append prm

Set prm = cmd.CreateParameter(, adDate, adParamInput, , lastOfMonth)
cmd.Parameters.Append prm

'Execute the command - returns a RecordSet
Set rs = cmd.Execute

'Close the connection
conn.Close