regarding database filter

Posted on 2012-08-28
Last Modified: 2012-08-28
i have a database which captures date and time in the following format
08/26/2012 5:58:42 PM
i need to filter all the data created on 08/26/2012
i have a datepicker control in VB
i need to use the datepicker control to select the date which i will be using to filter the data.
how to do that coz if i am using the query "Select * from table where datefield =#" & datepicker1.value & "#"
nothing is returned as it will search for exact value of date picker that would be 08/26/2012  but the actual value of the table is 08/26/2012 5:58:42 PM. how to give the query for filtering this situation. please help
Question by:anbuvec
    LVL 2

    Expert Comment

    you have to give the time in your query

    "Select * from table where datefield between '" & datepicker1.value.ToString("yyyy MMM DD") & " 00:00'" to '" & datepicker1.value.ToString("yyyy MMM DD") & " 23:59'"

    Author Comment

    .tostring is not an attribute or function and more over the database field type for date value is date/time field. i have tried the following and it gave me object required error
    "Select tablefield from table where datefield between '" & DTPicker1.Value.ToString("MM DD yyyy") & " 00:00' to '" & DTPicker1.Value.ToString("MM DD yyyy") & " 23:59'".
    LVL 1

    Accepted Solution


    You want to go ahead and use the SQL "LIKE" command. So if wanted to pick all the records from the table that were from 08/26/2012 i would do the following (amend the date formatting as required for your database)

    SELECT * FROM TABLE WHERE date LIKE '%08/26/2102%'

    LVL 2

    Expert Comment

    did you can take the date part from the datepicker ?

    Author Closing Comment

    Thank you so much owenacampbell... i was using the like command but i have missed the syntax i was using # instead of ' as that was a date field that is why i wanted to use = instead of like... it is just a syntax... but very quick and thank you for ur response... :D

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now