Results 1 to 7 of 7

Thread: Date & SQL

  1. #1
    Join Date
    Apr 2004
    Location
    Michigan
    Posts
    44

    Angry Unanswered: Date & SQL

    Hi guys,

    I have a search interface form, where the user selects the date to search for the date on that record and forwards.

    Now, the date table field is Date Format: Short Date
    and so as for the field in the form is short Date Format

    Now, when the user clicks search it runs a code, here is the portion of it:

    varwhere = "[tblProcessInfo].[DateDropped] > '" & Me.Date.Value & "'"

    DoCmd.OpenReport "rpReportCreateIJET", acViewPreview, , varwhere

    but then then it gives me error saying: "Data Type Mismatchin criteria expression"

    So I was wondering why it says that, though both of the fields are in date format.

  2. #2
    Join Date
    Apr 2004
    Location
    Michigan
    Posts
    44

    nevermind

    I found out why, a sec after I posted that..

    this can be used without quotes to add a variable into sql statement

    "[tblProcessInfo].[DateDropped] = DateEntered"

  3. #3
    Join Date
    Mar 2004
    Posts
    7

    Data type mismatch is criteria expression

    I using Delphi 7 to query access 2000 database by date. When I put my search as a string like:
    Search := 'Select * from Article Where Article_Date > '+ ''''+datetostr (TD1.date)+'''';

    I get the following error message : Data type mismatch is criteria expression

    Someone said that for date to work properly I should remove the quotes around TD1.date. I did that like this:
    Search := 'Select * from Article Where Article_Date > '+ datetostr(TD1.date);

    I get the wrong results. Data type in access is date and format is short date. My datetime picker kind in delphi properties is dtkDate.

    Can anyone help?

    Motho

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    try
    > #" & Me.Date.Value & "#"

    nothing for numbers
    ' for text
    # for dates

    ignore date format - it's for display only: all access dates are doubles (days-point-fractionaldays)

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    and if datetostr means what i think it means, you need to rethink. access dates are not strings. either you need to get the access datetype into string for the compare or you need to get your TD1.date into access datetype format.

    you will be overjoyed to learn that:
    access datetype = 0.00 represents the datetime 30/12/1899 00:00:00
    access datetype = 0.25 represents the datetime 30/12/1899 06:00:00

    calculate the number of days between 30 Dec 1899 and your TD1.date and you have an access datetype value for your date.

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Mar 2004
    Posts
    7

    Data type mismatch in criteria expression

    I changed my search to :
    Search := 'Select * from Article Where Article_Date >= #'' & TD1.Date & ''# ';
    And now I get the following error: Syntax error in date query expression.

    Can you help??

    ta,

    Motho

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    sorry - last msg was completely confused... maybe i shouldnt try to post while i'm talkng on the phone!


    still slightly confused about who should understand your code: access or delphi?


    anyhow, since i can't offer any comment on delphi, here is what i should have said earlier for access:

    38225 is the number of days to today since 30th December 1899, so if TDI.Date is such a number...
    SQL = "SELECT mydates.datemine FROM mydates WHERE mydates.datemine < " & 38225 & ";"
    or in your case ...< " & TD1.Date & ";"


    if TD1.Date is a string or you make it a string with datetostr(), then
    SQL = "SELECT mydates.datemine FROM mydates WHERE mydates.datemine < #" & 8/26/2004 & "#;"

    or in your case ...< #" & datetostr(TD1.Date) & "#;"

    ...in your last post you have access-unusual usage of single quotes.

    and watch out for US/ROW date format... access often decides to speak US whatever settings you think you have.



    i have no idea if this will make sense to delphi tho. this is access-speak.



    izy

    typo: was our is out
    Last edited by izyrider; 08-26-04 at 12:27.
    currently using SS 2008R2

Posting Permissions

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