Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2011
    Posts
    2

    Post Unanswered: Data mismatch in SQL - report data environment

    My SQL statement in source data of my VB^ report data environment works well to extract records between #05/30/2011# and #06/05/2011# in American format.

    SELECT WBTkt, DLNo, DDate, DTimeIN,DTimeOUT, RegNo, EAmount
    FROM WBBasE WHERE DDate BETWEEN #05/30/2011# AND #06/05/2011#
    ORDER BY DDate, DTimeOUT

    My problem is when I use a variable or input textbox name from the form (frmRpWBT), which calls the report I get, "Data type mismatch in criteria expression." Where am I going wrong?

    Dim vrStDt as Date, vrEdDt as Date
    -------------
    vrStDt = CDATE(txtStDt.Text)
    vrEdDt = CDATE(txtEdDt.Text)
    ---------
    All my SQL alternative statements give the same error;
    WHERE DDate BETWEEN ' & # & vrStDt & # & ' AND ' & # & vrEdDt & # & '
    or WHERE DDate BETWEEN ' & # & Forms!frmRpWBT!txtStDt & # & ' AND ' & # & Forms!frmRpWBT!txtEdDt & # & '

    Please assist?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You're not including the "#" signs in your string:

    example:
    WHERE DDate BETWEEN ' & # & vrStDt & # & ' AND ' & # & vrEdDt & # & '

    should be:
    WHERE DDate BETWEEN #' & vrStDt & '# AND #' & vrEdDt & '# '


    Where are you calling this from? To track this down in the future I would recommend setting a breakpoint in your code and examining the sql you ended up creating before you send it to the database. It makes funny quote issues like this easy to spot.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jul 2011
    Posts
    2

    Post Data Type Mismatch in SQL - report data environment

    Thanks Teddy for your attention & response. Let me clarify your direction;
    You wrote - should be:
    WHERE DDate BETWEEN #' & vrStDt & '# AND #' & vrEdDt & '# '
    Is the quote at the end of this statement relevant?
    To respond to your query, "Where are you calling this from?" This is a SQL statement within the source of data of my report data environment (deWBTrns1b). I'm calling the report - rptWBTpdly from the form - frmRpWBT.
    I also forgot to ask, how can I have these two dates appearing as from: & to: in my report?

Posting Permissions

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