Access, Calender dates, Strings and SQL

db , vba United States
  • 12 years ago

    I am using Access to write a form that will utilize a calendar control to get a date and past that in a sql string that will get the corresponding's date information.

    This is not working for me.  There are no rows returned.  When using

    strSQL = "Select * from Table Where sDate = ""09/23/2008"""

    I get the necessary information

    However, when using

    Dim dtDate as Date 

    dtDate = Me.Calendar0.Value

    strSQL = "Select * from Table Where sDate = ""

    strSQL = strSQL & CStr(dtDate) & """

    I get no rows returned.  Even when I take out the CStr(), the same no rows is returned.

    I have written an If...Then statement to test and dtDate = "09/23/2008" is not true nor with CStr().

     Any help with getting the value of a calendar control into a SQL string inwhich the database will recognize is great.  Even if a parameterized query would be good; I would need to be instructed with the course of action to pass the value in since I was unsuccessful before. 

    Thank you,

    P Dunn


  • 12 years ago

     Access usually likes dates to be surrounded by hash symbols in coded queries. You also seemed to have a few extra quote marks. Try something like this:

    strSQL = "Select * from Table Where sDate = "

    strSQL = strSQL & "#" & dtDate & "#;"

    Hope this helps!

Post a reply

Enter your message below

Sign in or Join us (it's free).


Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“It works on my machine.” - Anonymous