Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2016
    Posts
    6

    Answered: Data type mismatch in criteria expression.

    Can anyone help me to filter my crystal report using one textbox for the MEM_CODES field and two datetimepicker for my SDATE field.
    I need to filter the report by entering member's codes and date from , date to using datetime picker.


    My current codes are :
    Dim fechadel As String = Format(DateTimePicker1.Value, "M/dd/yyyy H:mm:ss")
    Dim fechal As String = Format(DateTimePicker2.Value, "M/dd/yyyy H:mm:ss")

    report("SELECT * FROM DTR_REC WHERE SDATE >='#" & fechadel & "#'AND SDATE <= '#" & fechal & "#' ", "CrystalReport1")
    i've encounter this error: " Data type mismatch in criteria expression."

    SDATE is datetime datatype

    I hope you can help me guys with my errors... Thanks

  2. Best Answer
    Posted by healdem

    "so what are the actual values of the variable / control

    with a data mismatch its nearly always a wrong data type

    Code:
    report("SELECT * FROM DTR_REC WHERE SDATE >='#" & fechadel & "#' AND SDATE <= '#" & fechal & "#' ", "CrystalReport1")
    probably should read

    Code:
    report("SELECT * FROM DTR_REC WHERE SDATE >= #" & fechadel & "# AND SDATE <= #" & fechal & "#;", "CrystalReport1")
    you could use a between predicate, assuming that the first value is less than or equal to the second value

    Code:
    report("SELECT * FROM DTR_REC WHERE SDATE >= #" & fechadel & "# between #" & fechal & "#;", 
    "CrystalReport1")
    I guess that by quoting the value of the control you are coercing it to string and the sql parser treats it as such

    of course you could get round this problem altogether by using the control directly (as it knows the underlying datatype
    Code:
    report("SELECT * FROM DTR_REC WHERE SDATE between " & DateTimePicker1 & " AND  " & DateTimePicker2, "CrystalReport1")
    Im not aware that the time picker control allows you to set a timen with it so the time element of the formatting is I suspect wasted

    if your data has a time element in it then you need to do something more.
    ..the lower limit is fine, the upper limit could be a problem as if you have time data in a datetime field then you need to add time data tot eh upper limit

    ferisntance
    say you have a value of sdate which is 22 June 2016 10:31:56 UTC
    if your upper limit is 22 June 2016 then the above value will not be returned as the implicit time if not specified is 00:00:00

    so the option is to either use your current logic
    Code:
    report("SELECT * FROM DTR_REC WHERE SDATE >= " & DateTimePicker1 & " AND  sdate < " & dateadd("d",1,DateTimePicker2), "CrystalReport1")
    the similar code using the between syntax is a bit more complicated
    or using the between syntax add 23:59:59 to the upper limit to allow for any times on that date
    Code:
    report("SELECT * FROM DTR_REC WHERE SDATE between " & DateTimePicker1 & " AND  " & dateadd("s",-1,dateadd("d",1,DateTimePicker2)), "CrystalReport1")
    but if your data doesn't have a time component then don't sweat it. "


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what are the actual values of the variable / control

    with a data mismatch its nearly always a wrong data type

    Code:
    report("SELECT * FROM DTR_REC WHERE SDATE >='#" & fechadel & "#' AND SDATE <= '#" & fechal & "#' ", "CrystalReport1")
    probably should read

    Code:
    report("SELECT * FROM DTR_REC WHERE SDATE >= #" & fechadel & "# AND SDATE <= #" & fechal & "#;", "CrystalReport1")
    you could use a between predicate, assuming that the first value is less than or equal to the second value

    Code:
    report("SELECT * FROM DTR_REC WHERE SDATE >= #" & fechadel & "# between #" & fechal & "#;", 
    "CrystalReport1")
    I guess that by quoting the value of the control you are coercing it to string and the sql parser treats it as such

    of course you could get round this problem altogether by using the control directly (as it knows the underlying datatype
    Code:
    report("SELECT * FROM DTR_REC WHERE SDATE between " & DateTimePicker1 & " AND  " & DateTimePicker2, "CrystalReport1")
    Im not aware that the time picker control allows you to set a timen with it so the time element of the formatting is I suspect wasted

    if your data has a time element in it then you need to do something more.
    ..the lower limit is fine, the upper limit could be a problem as if you have time data in a datetime field then you need to add time data tot eh upper limit

    ferisntance
    say you have a value of sdate which is 22 June 2016 10:31:56 UTC
    if your upper limit is 22 June 2016 then the above value will not be returned as the implicit time if not specified is 00:00:00

    so the option is to either use your current logic
    Code:
    report("SELECT * FROM DTR_REC WHERE SDATE >= " & DateTimePicker1 & " AND  sdate < " & dateadd("d",1,DateTimePicker2), "CrystalReport1")
    the similar code using the between syntax is a bit more complicated
    or using the between syntax add 23:59:59 to the upper limit to allow for any times on that date
    Code:
    report("SELECT * FROM DTR_REC WHERE SDATE between " & DateTimePicker1 & " AND  " & dateadd("s",-1,dateadd("d",1,DateTimePicker2)), "CrystalReport1")
    but if your data doesn't have a time component then don't sweat it.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Jun 2016
    Posts
    6
    it works. I removed the single quote. Thank you so much.

    Dim fechadel As String = Format(DateTimePicker1.Value, "M/dd/yyyy H:mm:ss")
    Dim fechal As String = Format(DateTimePicker2.Value, "M/dd/yyyy H:mm:ss")
    report("SELECT * FROM DTR_REC WHERE SDATE >=#" & fechadel & "# AND SDATE <=#" & fechal & "# ", "CrystalReport1")

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •