0

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?

Improve this question
6
  • if the DB column is a date type use dtp_startdate.Value because dates do not have a format. if it is not a date it should be if you want to do comparisons like >, = etc. Otherwise you are doing a text compare where "9" is always greater than "12" Commented Mar 16, 2015 at 15:04
  • Thanks for the response, upon doing this I have the following results. Changing the dtp to .value instead of text gives the same result, if I remove the # from the query I get the following message show: Syntax error (missing operator) in query expression '([incident date] > 01/03/2015 15:15:29)'. Commented Mar 16, 2015 at 15:18
  • depending on what and how incident_date is stored, you might want dtp.Value.Date in the query to pass just the Date and not Time portion. Commented Mar 16, 2015 at 15:23
  • tried and still shows results for anything dated after 3rd of jan, this is really strange. Commented Mar 16, 2015 at 15:51
  • the formats have little (if anything) to do with it because dates do not have formats - they are simply a value (a number of Ticks). Formatting is how the date is only how they are displayed. If you are passing strings to the DB on INSERT, the DB can convert/misinterpret. If there is no data after 1/3/2015 then you will get nothing back. Very hard to tell from here Commented Mar 16, 2015 at 16:06

1 Answer 1

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

apc thats worked wonders thankyou! I have tried to pass this as a parameter but that is also throwing all sorts of errors at me also so my plan was get it working for now and get the parameters done later, thanks for posting
I have to ask as I cannot figure this out at all, how do I set the value as a parameter? I have tried the following command command.Parameters.AddWithValue("@startdate", dtp_startdate.Value.Date.ToString("yyyy-MM-dd")) and im met with data type mismatch errors everytime, do you know what Im doing wrong?
In SQL server (not sure about Access though I would think it is the same) select * from incident where [incident date] > @startdate command.Parameters.AddWithValue("@startdate", dtp_startdate.Value.Date)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.