[stevthym]

Hi! I am almost ready to break my head onto the wall! I am really stacked with an SQL query and wish of some help!

I have a Database where one table is called OrderHead. There are various fields where two of them is OrderTotalAmount and DateOfOrder. I just need to filter my orders via Dates and get the relevant Total Amounts. I am using the following query and get error Run Time 3464 (Data mismatch).

txtFrom and txtTo are two text fields where the user can enter the date range.

Here is the query:

strSQL = "SELECT Sum(OrderTotalAmount) from OrderHead where  DateOfOrder between '" & Format(txtFrom.text, "dd/mm/yyyy") & "' AND '" & Format(txtTo.text, "dd/mm/yyyy") & "'"

Please help!

This post has been edited by modi123_1: 17 August 2011 - 06:53 AM
Reason for edit:: please use code tags

[BobRodes]

You probably have your SQL Server instance set up to do American date format. At least, that would cause the problem, as it would be expecting a mm/dd/yyyy format. Try turning that around in your format function and see if it solves the problem.

[stevthym]

BobRodes, on 17 August 2011 - 07:40 AM, said:

You probably have your SQL Server instance set up to do American date format. At least, that would cause the problem, as it would be expecting a mm/dd/yyyy format. Try turning that around in your format function and see if it solves the problem.

No. this is not the problem as I am using the right format!

[renmee]

stevthym, why don't you use dtpicker instead of textbox.

This post has been edited by renmee: 18 August 2011 - 12:51 AM

[stevthym]

renmee, on 18 August 2011 - 12:32 AM, said:

stevthym, why don't you use dtpicker instead of textbox.

how should I do that?

[renmee]

stevthym, on 18 August 2011 - 02:11 AM, said:

renmee, on 18 August 2011 - 12:32 AM, said:

stevthym, why don't you use dtpicker instead of textbox.

how should I do that?

go to reference and tick on "Microsoft Windows Common Control -2 6.0(SP6).
the tool will be available in your toolbar.

[stevthym]

renmee, on 18 August 2011 - 02:29 AM, said:

stevthym, on 18 August 2011 - 02:11 AM, said:

renmee, on 18 August 2011 - 12:32 AM, said:

stevthym, why don't you use dtpicker instead of textbox.

how should I do that?

go to reference and tick on "Microsoft Windows Common Control -2 6.0(SP6).
the tool will be available in your toolbar.

any other idea? Is my code ok?

[BobRodes]

stevthym, on 18 August 2011 - 07:31 AM, said:

BobRodes, on 17 August 2011 - 07:40 AM, said:

You probably have your SQL Server instance set up to do American date format. At least, that would cause the problem, as it would be expecting a mm/dd/yyyy format. Try turning that around in your format function and see if it solves the problem.

No. this is not the problem as I am using the right format!

You're saying that you know for a fact that your SQL Server is formatted for UK dates? Ok. Well, here's the documentation for the error. http://msdn.microsof...office.12).aspx . The problem doesn't appear to be in the code you have shared with us.

[renmee]

As what i see, your code is ok.
try to get txtFrom.text and txtTo.text before going to strSQL.
if you do get the correct data then the problem would be in your SQL Table itself.

[Patric]

Hi stevthym,

You could try to declare your textbox value as DateTime variable 1st.
(Make sure you add in validation first so it wont prompt you error message for invalid date type variable)

Dim dFrom As DateTime
Dim dTo As DateTime 

dFrom = txtFrom.Text 
dTo = txtTo.Text 

strSQL = "SELECT Sum(OrderTotalAmount) from OrderHead where  DateOfOrder between '" & Format(dFrom, "yyyy-MM-dd HH:mm:ss") & "' AND '" & Format(txtTo.text, "yyyy-MM-dd HH:mm:ss") & "'"

Hope that help.

Regards,
Patric

[renmee]

BobRodes, on 18 August 2011 - 04:57 PM, said:

stevthym, on 18 August 2011 - 07:31 AM, said:

BobRodes, on 17 August 2011 - 07:40 AM, said:

You probably have your SQL Server instance set up to do American date format. At least, that would cause the problem, as it would be expecting a mm/dd/yyyy format. Try turning that around in your format function and see if it solves the problem.

No. this is not the problem as I am using the right format!

You're saying that you know for a fact that your SQL Server is formatted for UK dates? Ok. Well, here's the documentation for the error. http://msdn.microsof...office.12).aspx . The problem doesn't appear to be in the code you have shared with us.

I agree with bob!