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.EffDate. 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.Value & "#")

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

[Audrey Abbey]

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

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.Value & "#")

[dsatino]

Sorry, try this:

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

[Audrey Abbey]

Same error, unfortunately.

Syntax error in date in query expression 'tblCapacity.EffDate = #'

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.cboEffDate, "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_YourFormName.cboEffDate.value

Obviously you need to change the "YourFormName" 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.

[Audrey Abbey]

Ok, I did that and it returned "Null" - so I assume that means it is not seeing the value that I selected from the drop down list.

Since I am selecting a date from a combo box, does that mean the event is firing before my selection is recognized?

Should I move this to a different event, or did I miss a step?

[Audrey Abbey]

Ok, I have resolved the issue and everything seems to be working correctly. Finally.

The Bound Column on the combo box was changed to "2" at some point (probably trying to resolve a different issue).

Changing it to 1 allows the code to run perfectly. I do not know why. If anyone has a short and sweet explanation they would like to share, that would be awesome. Fixing a problem is great. Understanding why the fixed worked...prevents more problems in the future!

Thank you both for your time and assistance (and patience)!

Here is what the code ended up

[NeoPa]

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.

This is so missing the point. Unfortunately this is often misunderstood as I mentioned earlier. Certainly I am from the UK, but code should be portable even if you're from the USA. I repeat this over and over again but no-one seems to notice as the code seems to work when run in the USA.

Access is not USA specific, but SQL, as a standard, is. The SQL standard for dates (in both ANSI-89 & ANSI-92) is m/d/yyyy. It is probably not a coincidence that it matches the US format, but it is a SQL standard for the whole world. Therefore, code written using the local format is always incorrect. It may work in the US, but it's not good practice, and should be formatted specifically for SQL if it's to be reliable, portable code. Does that make sense?

Moving on. I left out the reference to the ComboBox in my earlier post by mistake. It should have read Format(Me.cboEffDate, "m/d/yyyy"). It doesn't need a .Value after it, as that is the default property and it certainly doesn't need to be a reference to the form's class (Form_blahblahblah). The Me. approach works properly and there's no need for such a clumsy fix.

Audrey, your last post indicates you intended to post your code, but it's not there. If you'd like to post it I will happily run my eyes over it for you. There are a number of items that can easily be wrong even though testing is unlikely to show it up. It's better to catch these now, than later on when those rare occurrences cause a problem.

[NeoPa]

Audrey:
Changing it to 1 allows the code to run perfectly. I do not know why.

This indicates which column of the ComboBox is considered to reflect its .Value. As mentioned before, the .Value is the default property. If the Bound Column is set to 1 then I expect this is the column that contains the date. We have no knowledge of any other columns in your ComboBox, so we cannot say what is in column #2, but it is unlikely to be the date. It may even be without a second column, in which case the Null value would always be returned, and this would explain the empty string between the hashes (#) in your SQL string.

[Audrey Abbey]

Oops. I must have gotten in a hurry. Here is what I finally ended up with (there are many more lines as I was just using a small set of fields to test with):

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]=#" & Me.cboEffDate.Value & "#"
    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]
    Me.txtT2Start = rs![T2 Start Date]
    Me.txtT2End = rs![T2 End Date]
    Me.txtT2Inlet = rs![T2 Inlet Gas]
    Me.txtT2Residue = rs![T2 Residue Design]
    Me.txtT2Fuel = rs![T2 Fuel]
    Me.txtT3Start = rs![T3 Start Date]
    Me.txtT3End = rs![T3 End Date]
    Me.txtT3Inlet = rs![T3 Inlet Gas]
    Me.txtT3Residue = rs![T3 Residue Design]
    Me.txtT3Fuel = rs![T3 Fuel]
End Sub
 

[NeoPa]

Thanks for posting Audrey.

For lines #8 through #11 I would reiterate that simply using the date value as returned, formatted to the local date format, is not a reliable or portable approach. It will work if the PC is set up for USA and the date format chosen is the standard one, but there are various situations where it may fail. Access is pretty clever at hiding such problems from you, and will determine what you mean with many dates even when incorrectly specified, but reliable it is not.

Line #12 proved very helpful in the debugging stage but is no longer required and can be removed without fear of causing any problems.

Good luck with your project.

[Audrey Abbey]

Thanks again, NeoPa. I really appreciate the help.

I will fix the stuff you mentioned. Now that I understand what you are doing with the date format, I am going to use that everywhere. An ounce of prevention is worth a pound of cure!

Besides this database is for an international company. We are in the US but you never know when they may try to use the database somewhere else.

[dsatino]

Audrey, let me start by saying. Always listen to NeoPa because he's always right.

Now let me get to where I was going with all of this, and I'm not the expert that NeoPa is, but what follows is based on experience.

The date format he gave you is the SQL standard as it applies to the whole world and Access will always recognize it in that format. But if you ever start using Access to to open SQL recordsets on other types of database servers, they may not recognize this format. (i.e. the backend database is not Access). For instance, my company has a Sybase server that can't interpret the 'm/d/yyyy' format. I have no idea why, but it can't.

As for the .Value reference, it's certainly not necessary because it's the default but I think if you're new to code it leads your thought process down a road that will cause you to wonder/explore other properties that are available for the control. You may not need them in this instance, but you'll know that there are other properties that can be modified/controlled programmatically.

As for the 'form_' class reference, I wasn't suggesting that you substitute it for the 'Me' keyword, rather you'd use it in the immediate window since the 'Me' keyword doesn't work in the immediate window. The immediate window is your friend.

[NeoPa]

DSatino:
Always listen to NeoPa because he's always right.

This is going to sound strange, but I was very impressed by this post generally. OK The quoted bit may be a bit of a strectch, though I try to avoid mistakes where possible, but the rest of it is all sound. Good stuff.