Vb.net 2010 MS Access 2013 search date error

Question

4.00/5 (1 vote)

See more:

I am using VS2010 and Microsoft Access Database 2013. I have a query to search the action start data. Depending on which date I select on the datetimepicker I get no results or limited results without any error message during application debugging.

My code is below.

VB

Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
    
        Dim Search As Date = ActionStartDateDateTimePicker.Value
        Me.ActionsLogTableAdapter.FilterBYActionStartDate(Me.GSKactionsDBDataSet.ActionsLog, Search)

        Form48.MdiParent = Form27
        Form48.Show()
        Form48.ActionsLogTableAdapter.FilterBYActionStartDate(Form48.GSKactionsDBDataSet.ActionsLog, Search)

    End Sub

And my Query is as follows:

Method Name: FilterBYActionStartDate

SQL

SELECT        ID, ActionReferance, ActionDescription, ActionSite, ActionCategory, ActionImportance, ActionAccountability, ActionProgressStatus, ActionClosurestatus, ActionDoneBy,
                         ActionComment, ActionYear, ActionStartDate, PlannedClosureDate, RealClosureDate, ActualClosureDuration, PlannedClosureDuration
FROM            ActionsLog
WHERE        (ActionStartDate LIKE ?)

Thanks for any help.

Posted 12-Sep-15 7:24am

Sherif Adely

Updated 12-Sep-15 7:29am

Wendelius

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2015-09-12T07:34:00

Solution 1

As far as I can see the problem is that you seem to store the ActionStartDate in a character column. The situation should be corrected by using a datetime column instead and then normal operators such as =, >, < and so on to compare the datetime column with the value from the user interface. LIKE operator is meant to be used when searching with partial data from a string data.

For more information about the data types, see SQL Data Types[^]

Posted 12-Sep-15 7:34am

Wendelius

Updated 12-Sep-15 7:35am

v2

Comments

Maciej Los 12-Sep-15 14:18pm

+5!

Wendelius 13-Sep-15 1:18am

Thanks!

Sherif Adely 12-Sep-15 14:21pm

Hi Mika,
Thank you for your prompt response
I have changed the operators to =, > but I still get no results or limited results

Wendelius 13-Sep-15 0:29am

As said, it's not enough to change the operator. If the data type of the column is character, you need to change it to datetime.

Maciej Los · Answer 2 · 2015-09-12T08:20:00

Solution 2

In addition to soultion 1 by Mika Wendelius[^], i would add some extra links:
How to store, calculate, and compare Date/Time data in Microsoft Access [^]
Examples of using dates as criteria in Access queries[^]

Posted 12-Sep-15 8:20am

Maciej Los