Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2006
    Posts
    2

    Unanswered: MS Access and Advantage Database issue

    Hi there,

    I am trying to run some SQL statements through MS Access 2000 queries on an Advantage 6.11. The ODBC link seems to work fine as I can see all the tables (.ADT, .ADI, .ADM). The problems seems to be that a simple SQL statement such as WHERE [table.date] BETWEEN #--/--/--# and #--/--/--# brings me data that does not comply with the WHERE statement (out of the date range). I cannot understand why is doing that..
    ANy ideas anybody?

    Very grateful to any help
    Regards

    PS: I am running XP Pro on the client and Server 2000 on the server, Advantage is Medisoft 's database.

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,423
    Provided Answers: 8
    welcome

    what format is the #--/--/--# in is should be in USA also

    a problem I have found with Advantage Frontend is the Computer running Advantage Frontend has to also be set to USA date format
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Jan 2006
    Posts
    2
    Hi Stephan,

    The SQL format is the one compatible with MS Access in fact I tried something different and worked fine. Instead of linking the table I imported the table and run the following WHERE statement: WHERE [mwtrn.date from] BETWEEN #1/1/2005# and #12/31/2005#. It brought me the correct data, however if I link the table it brings me data out that range.
    Now linking the table or importing the table uses the same ODBC driver however the query runs differently and I suspect that is WHERE the problem resides.
    I am still interested in linking the tables.

    Thanks for your help,

    Adrian

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,423
    Provided Answers: 8
    just test the 08/01/05 that is is aug not Jan
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If you are linking the tables, that could be your problem. JET does things in odd ways, its possible that by linking you are then using the JET SQL parser rather than the ODBC parser.

    I know using the #date# is meant to be a safe way of passing dates to and from SQL, just wondering out aloud how JET has handled the column defintion. It may be worth checking to see how JET has interpreted the table defintion - open the table in design mode and see how the columns are defined - I'd want to check the data types are as expected. If its perceived as a non date column hten using the between clause may cause very odd results,

Posting Permissions

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