Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Posts
    214

    Unanswered: formating issues

    All, using access 2003 frontend w/sql server 2008 backend. I am running a query. Some of the date fields are formatted so they would appear in the result as mmddyy and mmmmyyyy instead of SQLs yyyymmdd. This runs fine and returns the records until I want to put a parameter on the date: between[enter start date] and [enter end date]. Then when I run it; no records are returned. Can someone tell me why this is happening?
    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Dates and text columns don't mix... They are pretty much the definition of "oh crud!" for DBAs, programmers, and geeks in general.

    1. Create a date column.
    2. Convert the text into dates
    3. Live happily ever after!


    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jul 2004
    Posts
    214
    Thanks for replying. Do you mean create another date column in my query? I am sorry. I do not follow. Where do I put the parameter: between[enter start date] and [enter end date]? Thanks

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use datetime datatypes for your date / time values
    then you can use the date time functions to manipulate the data
    do not use strings for dates
    use the appropriate syntax to identify a date time literal
    where ever possible use columns of the datetime datatype

    the format of a column has little or no meaning the format is the value used for display purposes

    ferisntance consider the value 1234567890.1234
    that could be displayed (formatted) as
    1,234,567,890.1234 for most non European countries and the UK & Ireland
    1.234.567.890,1234 for most European countries
    as a currency value it would probably be expressed as
    1,234,567,890.12
    it can be truncated to 1,234,567,890 as an integer
    but the underlying value in the database is still 1234567890.1234

    your main problem with dates is when using literals
    as said before you need to expressly indicate that the literal is a date value by expressing it in US format mm/dd/yyyy and encaspulate it with the # symbol. EG "#05/08/2012#". there is some flexibility in Access/JET dates, it will accpet ISO date #yyyy/mm/dd# I think it will accept alph month eg #03/May/2012# but it will get its knickers in a twist if you try to use a sensibale date format dd/mm/yyyy

    now when using pass through queries you would need to check how SQL server handles date literals
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jul 2004
    Posts
    214
    Got it. Thanks so much.

Posting Permissions

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