Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Error Message (2002)

    I have just converted from Windows 2000 to Windows XP. At the same time, I got Office XP with Access 2002. I have an Access 2000 database that I have been using for almost two years. Each month I delete certain linked tables and link to the current months tables that have the same name. One of the tables has a field named sub-account-nbr which is a 6 digit field. I need a 5 digit number to relate to field in another table.
    I have a query set up that I have used since the beginning that makes a table based on the linked table and includes a field that is a 5 digit number. This morning, it doesn't work. <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>
    The error message I get is "Invalid Argument". The only thing this query does is Select the entire table and add an alias.
    the SQL for the 5 digit field is: Select Left([sub-account-nbr],5) as Account Number.
    The linked table is Read Only, so changing a field name is not an option.

    Does anyone know what has changed and what I need to do?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Error Message (2002)

    The first thing to do is check your references. I've seen some very strange behavior with a broken reference. Go into the VB editor (Alt+F11 will get you there), make sure the immediate window is visible, and in the immediate window, type something like "? Date()" without the quotes and see if it triggers an error.

    I don't understand what you mean by "The only thing this query does is Select the entire table and add an alias". Do you mean that you're aliasing the field? A select query doesn't make a table, so give us the rest of the SQL you're using.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error Message (2002)

    Thank you Charlotte
    No the ? Date() that you suggested just morphed into Print date. No error message.
    Well okay, I misstated the situation. The query Selects the linked table, selects all fields in the table including [SL Balances}, [sub-account-nbr], Left ([sub-account-nbr],5) AS [Account Number]
    The last command is INTO Sub Balances FROM SL Balances.

    Only now it has changed it's mind about the nature of the problem. Since going into the VB module, when I attempt to run the query, the error message is "Compile error in query expression Left ([sub-account-nbr],5)'.

    There is no '. in the SQL --

    <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

    .

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Error Message (2002)

    No, the single quote gets put in the error message. Ignore it. Try adding the table designation to the Left() expression. And could you post the entire SQL statement. Trying to diagnose a problem that is related to the SQL is very difficult if you can't look at the entire statement.
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error Message (2002)

    Ok, here is the entire thing.

    SELECT [SL BAlances].date, [SL Balances].[sub-account-nbr], Left([sub-account-nbr],5) AS [Account Number], [SL Balances].[appropriation-amt], [SL Balances].[income-amt], [SL Balances].[expenditure-amt], [SL Balances].[encumbrance-amt], [SL Balances].<transfer-amt>, [SL Balances].[free-balance-amt], [SL Balances].[overhead-fytd-amt], [SL Balances].[overhead-fytd-taken], [SL Balances].[overdraft-amt-allowed], [SL Balances].[overdraft-reason], [SL Balances].[overdraft-flag], [SL Balances].[overdraft-expiration-date-cymd], [SL Balances].[date-entered-cymd] INTO SubBalances From [SL Balances]

    In the design view, this shows a column for both sub-account-nbr, and Account Number.

  6. #6
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error Message (2002)

    <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> Whoa Charlotte! You rock! I tried inserting the tbl name between Left( and the field name, and it worked. Thank you very much. Such a simple fix, but I would never have found it.

Posting Permissions

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