10 Replies - 4844 Views - Last Post: 19 August 2011 - 01:33 AM Rate Topic: -----

#1 stevthym  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 17-August 11

VB6 Between Dates Query

Posted 17 August 2011 - 05:02 AM

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


Is This A Good Question/Topic? 0
  • +

Replies To: VB6 Between Dates Query

#2 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 572
  • View blog
  • Posts: 2,985
  • Joined: 19-May 09

Re: VB6 Between Dates Query

Posted 17 August 2011 - 07:40 AM

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.
Was This Post Helpful? 0
  • +
  • -

#3 stevthym  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 17-August 11

Re: VB6 Between Dates Query

Posted 17 August 2011 - 11:31 PM

View PostBobRodes, 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!
Was This Post Helpful? 0
  • +
  • -

#4 renmee  Icon User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 155
  • Joined: 20-July 09

Re: VB6 Between Dates Query

Posted 18 August 2011 - 12:32 AM

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

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

Was This Post Helpful? 0
  • +
  • -

#5 stevthym  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 17-August 11

Re: VB6 Between Dates Query

Posted 18 August 2011 - 02:11 AM

View Postrenmee, on 18 August 2011 - 12:32 AM, said:

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


how should I do that?
Was This Post Helpful? 0
  • +
  • -

#6 renmee  Icon User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 155
  • Joined: 20-July 09

Re: VB6 Between Dates Query

Posted 18 August 2011 - 02:29 AM

View Poststevthym, on 18 August 2011 - 02:11 AM, said:

View Postrenmee, 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.
Was This Post Helpful? 0
  • +
  • -

#7 stevthym  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 17-August 11

Re: VB6 Between Dates Query

Posted 18 August 2011 - 06:01 AM

View Postrenmee, on 18 August 2011 - 02:29 AM, said:

View Poststevthym, on 18 August 2011 - 02:11 AM, said:

View Postrenmee, 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?
Was This Post Helpful? 0
  • +
  • -

#8 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 572
  • View blog
  • Posts: 2,985
  • Joined: 19-May 09

Re: VB6 Between Dates Query

Posted 18 August 2011 - 04:57 PM

View Poststevthym, on 18 August 2011 - 07:31 AM, said:

View PostBobRodes, 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.
Was This Post Helpful? 0
  • +
  • -

#9 renmee  Icon User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 155
  • Joined: 20-July 09

Re: VB6 Between Dates Query

Posted 18 August 2011 - 05:02 PM

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.
Was This Post Helpful? 0
  • +
  • -

#10 Patric  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 11
  • Joined: 27-July 09

Re: VB6 Between Dates Query

Posted 19 August 2011 - 12:21 AM

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
Was This Post Helpful? 0
  • +
  • -

#11 renmee  Icon User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 155
  • Joined: 20-July 09

Re: VB6 Between Dates Query

Posted 19 August 2011 - 01:33 AM

View PostBobRodes, on 18 August 2011 - 04:57 PM, said:

View Poststevthym, on 18 August 2011 - 07:31 AM, said:

View PostBobRodes, 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!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1