Results 1 to 8 of 8
  1. #1
    zurek is offline Novice
    Windows XP Access 2003
    Join Date
    May 2009
    Posts
    2

    Thumbs down Use of COLLATE statement in SELECT clause

    I need to make SQL queries which would allow me to order the data in a table by different alphabets (sort orders). I have one table with text data and I need to make 2 queries. The First query would order the data by the standard English alphabet (I suspect it is called Latin1 in computer usage). But now, I need to use the same table and make a second query which would order the same data according to the different alphabet (Czech alphabet - I suspect it is called CP1250 in computer usage) - however, in both cases, I do not know the correct collation names for these alphabets for use in Access. If I am not mistaken, I think there should exist a COLLATE statement in the SELECT clause, but I cannot find the correct syntax for the COLLATE statement. Whenever I use the word COLLATE in a SELECT, Access tells me that there is incorrect syntax near this word.
    For English I was trying to do this:
    SELECT Data


    FROM Table
    ORDER BY Data
    COLLATE SQL_Latin1_General_CP1_CS_AS;
    Could you please help me?

  2. #2
    thhui is offline Competent Performer
    Windows XP Access 2002 (version 10.0)
    Join Date
    Feb 2009
    Posts
    235
    It seems that you are using the idea of Mysql in access.
    However, this is not quite feasible dealing with different collating order in access database.

    Because, access database table must be created with a single collating order.
    Afterwards, it could not be changed in select query.
    So the sql statement in query could only be done in one of a predefined fixed collating order.

    My answer is that you should try to export the same table to a different database but using different collating order.
    Then you can run the query in another collating order now.

  3. #3
    zurek is offline Novice
    Windows XP Access 2003
    Join Date
    May 2009
    Posts
    2

    How to export table programmatically?

    Well, I feared that it be impossible in Access to use the COLLATE clause in the SELECT statement. Ï know that it is very possible to change the collate order in the Menu of Access. I understand that having two identical databases having the first set the collation to CZECH and the other to the GENERAL would be a solution.
    OK, let us have the one (the Czech) database as the "primary" database, the other (the English) would be the "secondary" database. The user would be allowed to change the data in the primary database only. Then, each time he changes the data, the export of the table would be necessary into the "secondary" database to maintain that the databases are always the same. Nevertheless, I need to make the export of the table into the other database somehow programmatically without the need of manual exporting each time. Is this possible? Could you supply a short sample code how to export the table into different database after each change of data in the source table?

  4. #4
    thhui is offline Competent Performer
    Windows XP Access 2002 (version 10.0)
    Join Date
    Feb 2009
    Posts
    235
    In my opinion, it would be better exporting your table only when you are in need, ie. only when you must execute the query or report so as to get the Czech order.
    Last edited by thhui; 05-21-2009 at 11:58 PM.

  5. #5
    thhui is offline Competent Performer
    Windows XP Access 2002 (version 10.0)
    Join Date
    Feb 2009
    Posts
    235
    Program Steps are refined as follows:
    1)Copy your original database file DB1 to another access file named DB2
    2)Change the collating order to Czech for DB2.
    3)Compact the database DB2 so that it is commited in Czech Order.
    4)Run the query in DB2 and it would be in Czech collating order now.

    NB: You must have two different access files in order to have two different collating order querys.

  6. #6
    thhui is offline Competent Performer
    Windows XP Access 2002 (version 10.0)
    Join Date
    Feb 2009
    Posts
    235
    Another option is -
    1)Create a blank Access Database DB2 in Czech colloating order first.
    2)Then link all tables in it from DB1.
    3)Copy all queries, reports, macros, modules from DB1 into DB2
    4)Run query and report in DB2 after you have inputted all data in DB1 and now it would be in Czech Collating Order (and in English Collating Order when you run same query and report in DB1)

  7. #7
    channel66 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2011
    Posts
    2
    HI.

    How Change the collating order to an database access file to latin.
    Thanks

  8. #8
    thhui is offline Competent Performer
    Windows XP Access 2002
    Join Date
    Feb 2009
    Posts
    235
    See the folowing link
    http://msdn.microsoft.com/en-us/library/Aa140014

    Set the access database collating order to 1033 for Latin General

Please reply to this thread with any new information or opinions.

Similar Threads

  1. how to use IF then statement
    By ronnie4 in forum Access
    Replies: 1
    Last Post: 03-17-2009, 11:32 PM
  2. update in where clause
    By mikensu in forum Access
    Replies: 0
    Last Post: 03-16-2009, 07:19 AM
  3. FROM Clause error message
    By skylite in forum Queries
    Replies: 2
    Last Post: 10-23-2008, 05:47 AM
  4. IIF clause
    By Peljo in forum Queries
    Replies: 2
    Last Post: 02-05-2008, 11:22 AM
  5. Help on WHERE clause
    By QBCM in forum Programming
    Replies: 1
    Last Post: 12-19-2005, 08:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums