Why am I getting this Access SQL error - BC30287: '.' expected. ?

When I try to run the page that contains this code, I get a compilation error that points to the line delimited by ***. I don't understand the error. Could someone please help me correct it?

Thank you!
Tim

    Private Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs) Handles Me.Load
 
      Dim dbPath As String = Server.MapPath("/currentDB/CVGWebsite.mdb")
        Dim strConnection As String = _
            ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath & _
             ";Persist Security Info=False;")

        Dim strSQLFeatured As String = "SELECT DISTINCT [Web Price]," & _
            " [Web Notes], Make, Model, Year, [Stock Number]" & _
            " FROM WebData" & _
            " WHERE [Web Special] = Yes" & _
      ***  " AND [Web Start] <= " & Date() & _  ***
            " AND [Web Stop] >= " & Date() & _
            " ORDER BY Make ASC, Model ASC"

        Dim conn As New OleDbConnection(strConnection)
        Dim daFeatured As New OleDbDataAdapter(strSQLFeatured, conn)

        Dim ds As New DataSet()
        daFeatured.Fill(ds, "FeaturedVehicles")
       
        If ds.Tables("FeaturedVehicles").Rows.Count > 0 Then
            featuredVehicleRepeater.Visible = True
        Else
            featuredVehicleRepeater.Visible = False
        End If

    End Sub
bigmoxyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
try this:
Dim strSQLFeatured As String = "SELECT DISTINCT [Web Price]," & _
            " [Web Notes], Make, Model, Year, [Stock Number]" & _
            " FROM WebData" & _
            " WHERE [Web Special] = -1 & _
            " AND [Web Start] <= " & Date() & _  
            " AND [Web Stop] >= " & Date() & _
            " ORDER BY Make ASC, Model ASC"

Open in new window

0
TextReportCommented:
missing " and if you are passing dates then you need to ensure they are in MM/DD/YYYY format with # around.
Cheers, Andrew
Dim strSQLFeatured As String = "SELECT DISTINCT [Web Price]," & _
            " [Web Notes], Make, Model, Year, [Stock Number]" & _
            " FROM WebData" & _
            " WHERE [Web Special] = -1" & _
            " AND [Web Start] <= #" & Format(Date(),"mm/dd/yyyy") & "#" & _  
            " AND [Web Stop] >= #" & Format(Date(),"mm/dd/yyyy") & "#" & _
            " ORDER BY Make ASC, Model ASC"

Open in new window

0
bigmoxyAuthor Commented:
I get the same error at the same place with both of your suggested solutions.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

bigmoxyAuthor Commented:
Here is my code for your review.
Index.aspx.txt
0
dqmqCommented:
I think your problem is here:

Dim strConnection As String = _
            ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath & _
             ";Persist Security Info=False;")

Remove the parens.
0
bigmoxyAuthor Commented:
I understand your point but that same connection string is used without problems in other places. Removing the parens didn't make a difference.
0
TextReportCommented:
I suspect it doesn't line DATE(), have you tried hardcoding a date to check the syntax

     " AND [Web Start] <= #02/03/2008#" & _

Cheers, Andrew
0
dqmqCommented:
Are you certain it's a compliation error?

Does this help:

           " WHERE [Web Special] = ""Yes""" & _
0
bigmoxyAuthor Commented:
TextReport:
Hard coding the date does work however that cannot be the permanent solution. I need to be comparing the dates in the db to the current date.

dqmq:
Yes, it's a compilation error. It's pretty obvious. See attached screen print. Also, treating Yes as a string would not be correct. Web Special is a boolean/bit field with values Yes/No. WHERE [Web Special] = Yes and [Web Special] = -1 both work.

BC30287-error.png
0
TextReportCommented:
Can you use the DATE() within the SQL statement, I suspect it doesn't like DATE()

     " AND [Web Start] <= DATE()" & _

Cheers, Andrew
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TextReportCommented:
If this is in ASP.NET so have you tried rightNow.ToString("MM/DD/YYYY") instead of DATE()
Cheers, Andrew
0
bigmoxyAuthor Commented:
Exception Details: System.Data.OleDb.OleDbException: Invalid use of '.', '!', or '()'. in query expression '[Web Special] = Yes AND [Web Start] <= Now.ToString("MM/DD/YYYY") AND [Web Stop] >= Now.ToString("MM/DD/YYYY"'.

rightNow is not a defined function so I used "Now". This is my SQL

        Dim strSQLFeatured As String = "SELECT DISTINCT [Web Price]," & _
            " [Web Notes], Make, Model, Year, [Stock Number]" & _
            " FROM WebData" & _
            " WHERE [Web Special] = Yes" & _
            " AND [Web Start] <= Now.ToString(""MM/DD/YYYY"")" & _
            " AND [Web Stop] >= Now.ToString(""MM/DD/YYYY"")" & _
            " ORDER BY Make ASC, Model ASC"
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Try this:

        Dim strSQLFeatured As String = "SELECT DISTINCT [Web Price]," & _
            " [Web Notes], Make, Model, Year, [Stock Number]" & _
            " FROM WebData" & _
            " WHERE [Web Special] = Yes" & _
            " AND [Web Start] <=#" &  Now.ToString(""MM/DD/YYYY"")" & "#" _
            " AND [Web Stop] >=#"& Now.ToString(""MM/DD/YYYY"")" & "#" _
            " ORDER BY Make ASC, Model ASC"
0
TextReportCommented:
Now.ToString is an ASP function and therefore has to be outside your SQL string so you need to do the following.
YES is not a defined constant in the JET Engine and you will need to compart a BIT field with 0 or -1 but the true definition of true is <> 0 this is then transportable between JET where -1 is true and SQL Server where 1 is true.

Cheers, Andrew
        Dim strSQLFeatured As String = "SELECT DISTINCT [Web Price]," & _
            " [Web Notes], Make, Model, Year, [Stock Number]" & _
            " FROM WebData" & _
            " WHERE [Web Special] <> 0" & _
            " AND [Web Start] <= #" & Now.ToString("MM/DD/YYYY") & "#" & _
            " AND [Web Stop] >= #" & Now.ToString("MM/DD/YYYY") & "#" & _
            " ORDER BY Make ASC, Model ASC"

Open in new window

0
bigmoxyAuthor Commented:
I get several syntax errors in VS 2008, see screen print - syntax-error.png
syntax-error.png
0
TextReportCommented:
You have moved the Now.ToString to outside your SQL String therefore you don't need 2 " you need just 1
Cheers, Andrew
0
bigmoxyAuthor Commented:
Sorry wasn't thinking... Here's my code now and a new error.

        Dim strSQLFeatured As String = "SELECT DISTINCT [Web Price]," & _
            " [Web Notes], Make, Model, Year, [Stock Number]" & _
            " FROM WebData" & _
            " WHERE [Web Special] = Yes" & _
            " AND [Web Start] <=#" & Now.ToString("MM/DD/YYYY") & "#" & _
            " AND [Web Stop] >=#" & Now.ToString("MM/DD/YYYY") & "#" & _
            " ORDER BY Make ASC, Model ASC"

query-error.png
0
bigmoxyAuthor Commented:
I overlooked this suggestion but that is the one that works! Thank you very much for staying with me on this!

Regards,
Tim
0
TextReportCommented:
Just to finish off teh Now.ToString it looks like the flexibility of this was our downfall. It looks like it is case dependant so you can try. I would suggest it is probably worth checking this out as you may need it in ASP.Net anyway.

Now.ToString("MM/dd/yyyy")

Cheers, Andrew
0
bigmoxyAuthor Commented:
Yes, that works too! Thank you Andrew!

Regards,
Tim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.