Results 1 to 7 of 7

Thread: [RESOLVED] Type mismatch on date in Where statement

  1. Apr 13th, 2008, 01:36 AM #1
    Scaleman
    Scaleman is offline

    Thread Starter
    New Member
    Join Date
    Mar 2008
    Posts
    13

    Resolved [RESOLVED] Type mismatch on date in Where statement

    Have a problem with error Data type Mismatch in criteria expression
    Code:
    " WHERE Data.[date tested] = '2/16/2007/';"
    checked table and field is data type Date/Time Tried all different formats
    12/12/2001 #12/12/2001# *12/12/2001* not sure if there is not a bug .
    using access 2000 with ADO
    Reply With Quote Reply With Quote
  2. Apr 13th, 2008, 04:07 AM #2
    westconn1
    westconn1 is offline
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Type mismatch on date in Where statement

    Code:
    " WHERE Data.[date tested] = #2/16/2007# ;"
    this should be correct i believe
    Last edited by westconn1; Apr 13th, 2008 at 05:03 AM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete
    Reply With Quote Reply With Quote
  3. Apr 13th, 2008, 04:34 AM #3
    Siddharth Rout
    Siddharth Rout is offline
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: Type mismatch on date in Where statement

    " WHERE Data.[date tested] = '2/16/2007/';"
    You have an extra slash at the end of the date

    try this

    " WHERE Data.[date tested] = '02/16/2007';"

    Hope this helps...
    Last edited by Siddharth Rout; Apr 13th, 2008 at 04:42 AM. Reason: spell check
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero
    Reply With Quote Reply With Quote
  4. Apr 13th, 2008, 09:07 PM #4
    Scaleman
    Scaleman is offline

    Thread Starter
    New Member
    Join Date
    Mar 2008
    Posts
    13

    Re: Type mismatch on date in Where statement

    Yes you are right
    Code:
    " WHERE Data.[date tested] = #2/16/2007# ;"
    Now Im trying to get the date from the form Text Box
    Keep geting error " Data type mismatch in criteria expression"
    BeginDate and EndDate text box's I have tryed add# on end with
    Dim bdate as string
    set bdate = "#" BeginDate "#"
    Then put var in where sta. '" & bdate & "'

    still no luck!

    even dim as other type long,inger,varent don't know.
    Code:
    Data.[date tested] > '" & BeginDate & "' And Data.[date tested] < '" & EndDate & "'
    Reply With Quote Reply With Quote
  6. Apr 13th, 2008, 10:46 PM #5
    NeoDan
    NeoDan is offline
    Lively Member
    Join Date
    May 2007
    Posts
    70

    Re: Type mismatch on date in Where statement

    Quote Originally Posted by Scaleman
    Yes you are right
    Code:
    " WHERE Data.[date tested] = #2/16/2007# ;"
    Now Im trying to get the date from the form Text Box
    Keep geting error " Data type mismatch in criteria expression"
    BeginDate and EndDate text box's I have tryed add# on end with
    Dim bdate as string
    set bdate = "#" BeginDate "#"
    Then put var in where sta. '" & bdate & "'

    still no luck!

    even dim as other type long,inger,varent don't know.
    Code:
    Data.[date tested] > '" & BeginDate & "' And Data.[date tested] < '" & EndDate & "'


    Hey there...
    I think the main problem is you are using strings in the criteria ...eg. you have used ' before closing the qry string and then ' again before starting the query string...

    ...d] > '" & BeginDate & "' And...

    the SQL whill interpret the dates as strings and not dates... just put the # in place of it eg...

    ...d] > #" & BeginDate & "# And...

    and don't use them in your begindate and enddate variables. You want the SQl to look like this when it is submitted by your program...

    Data.[date tested] > #mm/dd/yyyy# And Data.[date tested] < #mm/dd/yyyy#

    and not this

    Data.[date tested] > '#mm/dd/yyyy#' And Data.[date tested] < '#mm/dd/yyyy#'


    incidentally you could rewrite the sql as

    data.[date tested] between #mm/dd/yyyy# and #mm/dd/yyyy#

    cheers
    Reply With Quote Reply With Quote
  7. Apr 14th, 2008, 04:40 AM #6
    westconn1
    westconn1 is offline
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Type mismatch on date in Where statement

    try to print out your query to the immediate window so you can see it looks like it is correct
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete
    Reply With Quote Reply With Quote
  8. Apr 14th, 2008, 08:02 PM #7
    Scaleman
    Scaleman is offline

    Thread Starter
    New Member
    Join Date
    Mar 2008
    Posts
    13

    Re: Type mismatch on date in Where statement

    Hey Guy's
    That did the trick!
    Thanks alot for the help
    hope i can be of some help someday
    Scamenan
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

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

Forum Rules


Click Here to Expand Forum to Full Width