Need help with syntax (missing operator?)

[Audrey Abbey]

I have a piece of code (below) that is giving me fits. I copied this code from elsewhere on the net, and I have been fiddling with the quotations in the Where clause, as that seems to be the problem. Depending on what I change, I get compile errors, syntax errors, etc.

I have an unbound combo box (cboEffDate). The recordsource for this box is a simple query that shows the effective dates stored in tblCapacity.Eff Date. What I want to happen is for the user to select an Effective Date from the drop down, then have my text boxes on the form populate with the field values associated with the effective date. Once those fields are populated with the correct set of variables, there are some calculations.

The error is in the "where" clause.
Also, the field [EffDate] is a short date type in the table. The cboEffDate is also formated for short date.

Private Sub cboEffDate_AfterUpdate()
    ' Pull info out of SQL View.

    Dim db As Database
    Dim rs As Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * " & _
    "FROM tblCapacity " & _
    "WHERE [EffDate] = " & Me.cboEffDate.Value & "")
    Me.txtFieldCap = rs![Field Inlet Capacity]
    Me.txtT1Start = rs![T1 Start Date]
    Me.txtT1End = rs![T1 End Date]
    Me.txtT1Inlet = rs![T1 Inlet Gas]
    Me.txtT1Residue = rs![T1 Residue Design]
    Me.txtT1Fuel = rs![T1 Fuel]
End Sub

[dsatino]

"WHERE [EffDate] = #" & Me.cboEffDate.V alue & "#")

You need to enclose dates with the # character for Access queries.

[Audrey Abbey]

I typed:
"WHERE [EffDate] = #" & Me.cboEffDate.V alue & "#")

Now I get runtime 3075:
Syntax error in date in query expression 'EffDate = #'

I wonder if I have an extra space somewhere that I should not.

[Audrey Abbey]

Now I am getting a different error:

RunTime 3075
Syntax error in date in query expression '[EffDate] = #'

here is what I have currently:
"WHERE [EffDate] = #" & Me.cboEffDate.V alue & "#")

[dsatino]

Sorry, try this:

"WHERE tblCapacity.EffDate = #" & Me.cboEffDate.Value & "#")

[Audrey Abbey]

Same error, unfortunately.

Syntax error in date in query expression 'tblCapacity.Ef fDate = #'

Any other ideas/approaches are appreciated!

[NeoPa]

Audrey,

This is a problem many have trouble with, and few understand fully. The hashes (#) are important, but not the only issue when dealing with date literals. See Literal DateTimes and Their Delimiters (#) for the full explanation.

To help with ypour specific problem (should you still need it after reviewing the article) it would help if you were to post the SQL itself, rather than the VBA which creates the SQL indirectly. To get this replace your lines #8 through #10 with :

strSQL = "SELECT * " & _
         "FROM [tblCapacity] " & _
         "WHERE ([EffDate]=#" & Format(, "m/d/yyyy") & "#)"
Debug.Print strSQL
Set rs = db.OpenRecordset(strSQL)

and also add this line after #5 :

Dim strSQL As String

I assume your error is occurring at line #8. This is always well worth including in the description of your problem of course.

[Audrey Abbey]

NeoPa - Thank you for taking the time to help me.

I did as you suggested, and now I get a compile error.

Here is the code as it is currently (with your changes):

Private Sub cboEffDate_AfterUpdate()
    ' Pull info out of SQL View.

    Dim db As Database
    Dim rs As Recordset
    Dim strSQL As String

    Set db = CurrentDb
    strSQL = "SELECT * " & _
             "FROM [tblCapacity] " & _
             "WHERE ([EffDate]=#" & Format(, "m/d/yyyy") & "#)"
    Debug.Print strSQL
    Set rs = db.OpenRecordset(strSQL)
    Me.txtFieldCap = rs![Field Inlet Capacity]
    Me.txtT1Start = rs![T1 Start Date]
    Me.txtT1End = rs![T1 End Date]
    Me.txtT1Inlet = rs![T1 Inlet Gas]
    Me.txtT1Residue = rs![T1 Residue Design]
    Me.txtT1Fuel = rs![T1 Fuel]
End Sub

The error is:
Compile Error: Argument not optional

The line:

   strSQL = "SELECT * " & _
is highlighted in yellow.

There is nothing in the "immediate" box when I do a control G either.

[dsatino]

your format function is missing the item it's supposed to be formatting. You need to reference the date you want to format.

[Audrey Abbey]

Ok that makes sense.

However, the date will be selected from a combo box that is already formated to the short date. Perhaps the point of NeoPa's code was to make sure it is in the proper form.

Not sure where to put the reference to the combo box though. After the formating code?

[Audrey Abbey]

Tried to reference the cboEffDate, but still getting the compile error.

[Audrey Abbey]

Ok, I think I am getting somewhere.

Here is the code as it is currently:

Private Sub cboEffDate_AfterUpdate()
    ' Pull info out of SQL View.

    Dim db As Database
    Dim rs As Recordset
    Dim strSQL As String

    Set db = CurrentDb
    strSQL = "SELECT * " & _
             "FROM [tblCapacity] " & _
             "WHERE ([EffDate]=#" & Format(Me.cboEffDate.Value, "m/d/yyyy") & "#)"
    Debug.Print strSQL
    Set rs = db.OpenRecordset(strSQL)
    Me.txtFieldCap = rs![Field Inlet Capacity]
    Me.txtT1Start = rs![T1 Start Date]
    Me.txtT1End = rs![T1 End Date]
    Me.txtT1Inlet = rs![T1 Inlet Gas]
    Me.txtT1Residue = rs![T1 Residue Design]
    Me.txtT1Fuel = rs![T1 Fuel]
End Sub

What is being captured in the Immediate window is this:

SELECT * FROM [tblCapacity] WHERE ([EffDate]=##)

[Audrey Abbey]

If I am reading this correctly (and I may not be) it appears that my code:

"WHERE ([EffDate]=#" & Format(Me.cboEf fDate, "m/d/yyyy") & "#)"

is being interpreted as:
WHERE ([EffDate]=##)

Apparently, it is ignoring everything between the two hash marks. I don't understand why, however.

[dsatino]

So it's not seeing a value in your field. Or maybe your field name is wrong. Or maybe you're misusing the Me keyword.

Type this into the immediate window:

?Form_YourFormN ame.cboEffDate. value

Obviously you need to change the "YourFormNa me" part.

[dsatino]

Oh, and I think NeoPa's point of formatting your date despite it already being a date is because he's from the UK where date's are formatted d/m/yyyy and Access (being an American product) doesn't recognize this as a valid date. So if your date field was 21/10/2010, Access wouldn't see this as a valid date.