CODEWITHSUNDEEP
vb.netms-access-2013
Boneyt
Boneyt

Reputation: 73

custom date format causing query error

Wondering if anyone can explain what's causing this query to return incorrect data when getting information from my datetimepicker form

On form load the following command takes place

Private Sub frm_3_viewincident_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    'clears the start date dtp box
    dtp_startdate.Visible = True
    dtp_startdate.Format = DateTimePickerFormat.Custom
    dtp_startdate.CustomFormat = " "
end sub

This makes the datetimepicker blank as intended,

When the value is changed I have the following command taking place

Private Sub dtp_startdate_ValueChanged(sender As Object, e As EventArgs) Handles dtp_startdate.ValueChanged
    'changes format to dd/mm/yyyy
    dtp_startdate.Format = DateTimePickerFormat.Custom
    dtp_startdate.CustomFormat = "dd/MM/yyyy"
End Sub

This then displays the selected date as 15/10/2014 for example. Now if I enter the date 1st of March 2015 as 01/03/2015 and run the following query

select * from incident where [incident date] > #" & dtp_startdate.Text & "#

instead of returning any results from 1st of march onwards it's returning all results from the 3rd of january onwards. The database is in the short date format dd/mm/yyyy same as the dtp format so I am unsure whats causing this. Can anyone advise?

Upvotes: 0

Views: 226

Answers (1)

apc
apc

Reputation: 5566

Try using

"select * from incident where [incident date] > #" & dtp_startdate.Value.ToString("yyyy-MM-dd") & "# "

as this date format is less ambigous then dd/MM/yyyy or MM/dd/yyyy

You should also consider using parameters in you query instead of string concaternation.

Upvotes: 1

Related Questions