Need a little help.

[DemonBob]

Aight guys i'm new to VBA and access programming, more tuned to mySQL, php, and c#. I'm trying to make a database for my father. He is a mechanic for a local company and gets sent out alot on service calls. And wants an easy way to keep track of his work. So I figured a database would be the best idea.

Using Access 2003:

The database has one table.

Called: "MachineLogTableOne"

Field Names: ID, Segment, WorkDate, WorkOrderNumber, EquipmentNumber, SerialNumber, ArrangementNumber, Hours, WorkPreformed

Has 2 forms:

Form 1: "MachineLogTableOneForm" - Used to enter data
Form 2: "SearchDate" - search by date

Has 1 query:

Query: "qryMachineQuery"

Has 1 Switchboard:

Called Switchboard

The problem I am having is with SearchDate and the VBA. The form consist of just a input box and an ok button. When the button is pushed it is suppose to query all the revelent entries of that date. Every time I click ok it gives me a run-time error '2001'. The code is below.

The Code

Private Sub cmdOK_Click()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Set db = CurrentDb
    Set qdf = db.QueryDefs("qryMachineQuery")
    strSQL = "SELECT MachineLogTableOne.* " & _
             "FROM MachineLogTableOne " & _
             "WHERE MachineLogTableOne.WorkDate='" & Me.mlDate.Value & "';"
    qdf.SQL = strSQL
    
     If Application.SysCmd(acSysCmdGetObjectState, acQuery, "qryMachineQuery") = acObjStateOpen Then
        DoCmd.Close acQuery, "qryMachineQuery"
    End If
    DoCmd.OpenQuery "qryMachineQuery"
    DoCmd.Close acForm, Me.Name
    Set qdf = Nothing
    Set db = Nothing
End Sub

The debugger is saying that this line is the culprit.

DoCmd.OpenQuery "qryMachineQuery"

I've done tried adding the extra arugemtns acView and such, still gives the same error though.

 

[pbaldy]

Try this for starters:

"WHERE MachineLogTableOne.WorkDate=#" & Me.mlDate.Value & "#;"

 

[DemonBob]

Thanks man, i figured it would be something like that. It works now, flawlessly

 

[pbaldy]

HTH. The basic rule is that date/time values are surrounded by #, text values by ', and numeric values not by anything.