locked
Using CDate in a SQL WHERE clause For Access database RRS feed

  • 问题

  • I have an Access Database table in which I store dates as strings in field VisitDate in a Short Date format "09/29/2020". There is another String field 'ApName' In this table. If I create a where clause like:

    dim WHEREClause as string = String.Empty

    dim strDt as string = "01/01/2020"

    dim endDt as string = "10/29/2020"

    dim LLName as string = "Elk Creek Apt."

    WHEREClause = " WHERE ApName = '" & LLName  & "' And CDate(VisitDate) BETWEEN #" & strDt & "# And #" & endDt & "#" 

    and run an SQL SELECT call. it returns all the correct entries.

    If I use a where clause like:

    WHEREClause = " WHERE  CDate(VisitDate) BETWEEN #" & strDt & "# And #" & endDt & "#" 

    and run an SQL SELECT call, it returns an error 'Data type mismatch in criteria expression.' What am I doing wrong here? Thanks for any help you can give me. I've been struggling with this problem for over a year now.

    2020年10月29日 18:16

全部回复

  • What data type is VisitDate? 

    I assume that VisitDate is a text column?

    The CDate is failing since the value in VisitDate is not a valid date format.

    You have to add this:

      CDate(VisitDate) BETWEEN #" & strDt & "# And #" & endDt & "#   AND IsDate(VisitDate) = true " 

    So, you have a column of data (as text), and there are text, numbers or who knows what in that column. 

    So, you need to add that "isDate()" test/check, and it will thus not include rows that have a bad date, and thus the CDate() function will not fail.

    Also, Cdate() will much assume that your date format is:

    MM/DD/YYYY

    or

    YYYY-MM-DD

    If the text date column is anything other then the above two, then there is some risk that you get incorrect date values (the month and day value will be flipped on you - and regional settings could effect this).

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)

    Edmonton, Alberta Canada




    2020年10月29日 19:22
  • Hello,

    • When possible, place code for data operations in a class.
    • NEVER use string concatenation for SQL.

    The following code sample is a basic code sample showing how to do a WHERE condition with a string and dates between.

    Ignore 

           

    Imports BaseConnectionLibrary.ConnectionClasses

    Namespace Classes

        Public Class DatabaseOperations
    Inherits AccessConnection

    Which are from a NuGet package of mine for connection strings, concentrate on the code.

    Imports System.Data.OleDb
    Imports System.IO
    Imports BaseConnectionLibrary.ConnectionClasses
    
    Namespace Classes
    
        Public Class DatabaseOperations
            Inherits AccessConnection
    
            ''' <summary>
            ''' Default our connection to a database in the executable folder when not using a password
            ''' </summary>
            ''' <remarks>
            ''' Not used in the code sample but this is how to do a connection not encrypted.
            ''' </remarks>
            Public Sub New()
                DefaultCatalog = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb")
            End Sub
    
            Public Function LoadOrders(companyName As String, startDate As Date, endDate As Date) As DataTable
    
    
                Using cn As New OleDbConnection(ConnectionString)
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = <SQL>
                            SELECT Orders.OrderID, Orders.Identifier, Orders.OrderDate, Customers.CompanyName
                            FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID_OLD
                            WHERE (((
                                Customers.CompanyName)=? AND Orders.OrderDate BETWEEN ? AND ?
                        ));
                        </SQL>.Value
    
                        cmd.Parameters.AddWithValue("?", companyName)
                        cmd.Parameters.AddWithValue("?", startDate)
                        cmd.Parameters.AddWithValue("?", endDate)
    
                        Dim dt As New DataTable With {.TableName = "Customer"}
    
                        dt.Columns.Add(New DataColumn() With {.ColumnName = "Process", .DataType = GetType(Boolean), .DefaultValue = False})
                        dt.Columns("Process").SetOrdinal(0)
    
                        Try
                            cn.Open()
                            dt.Load(cmd.ExecuteReader)
    
    
                        Catch ex As Exception
                            mHasException = True
                            mLastException = ex
                        End Try
    
                        Return dt
    
                    End Using
                End Using
            End Function
        End Class
    End Namespace


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    2020年10月29日 21:29
  • Although Microsoft with VB is very much tailored around the worldwide seen very strange American English date format, can you not use a VB function as CDate inside your SQL statement. 

    Therefore I would use for all four a parameter clause, where the Visitdate parameter then is. 

    Param2 = CDate(VisitDate)


    Success
    Cor


    2020年10月29日 22:56
  • As a follow up?

    Why did your first one work, and not the 2nd one?

    Well, the issue is that BOTH should work if the VisitDate column in question did not have BAD data.

    So the first one works, because the additonal condition you have (ApName = '" & LLName  & "' ) JUST OH so happened to restrict the data list ENOUGH that the bad dates you have "some where" in that data were skipped.

    In the 2nd example, you have a much LESS data where restriction of data, and thus some of those "bad" dates now are part of the query, and hence it errors out. 

    As noted, you can/should find those bad dates, but for the time being you can use my other post/solution here.

    (include a where with IsDate(columnName) = true)

    And as noted, while is a good tip to use parameters, as such, parameters alone will not fix this issue/problem. The simple issue is that your 2nd data query is a larger data set and in that larger set is some bad date values in that column. You could obtain a list of "bad dates" with this query:

    SELECT * from thatTable where isdate(VisitDate) = false.

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)

    Edmonton, Alberta Canada


    2020年10月30日 0:23
  • Hi SequimMike,

    How is the question going? If your question has been answered then please click the "Mark as Answer" Link at the bottom of the correct post(s), so that it will help other members to find the solution quickly if they face a similar issue.

    Besides, if you need further assistance, please let me know. 

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2020年11月2日 7:45