Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Hi,
    I've created a MS Query in Excel, and I'm wanting to add a new column, not a column from the file I'm querying. For example, below is the data from the file, and the far right column (New Period) is the data I want to create with a formula:

    Company Nature Period Amount New Period
    75 4000000 JAN09 1,000.00 JAN09B

    I want to define a new column (the New Period above), by putting a formula in the field heading area.
    I've used formulas such as:
    left(Period,3) and the result would be JAN, and this works just fine.

    Unlike Exel, MS Query will not let me use the = sign. Likewise, it doesn't like the & sign, which is what I tried to do by using this formula:
    Period&"B" to create the New Period called JAN09B

    Does anyone know how I could create this inside the MS Query, as opposed to having the formula in Excel AFTER the data has been downloaded. I'm trying to eliminate formulas in Excel, plus I'd simply like to know how far I can take the formula's inside MS Query. I'd love to know if an IF Then formula inside MS Query works as well.

    Any & all ideas are appreciated!

    Thanks!!
    Lana

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In MS Query, select Records | Add Column...
    In the Field box, enter the formula without an =, and use single quotes around a string value. In your example:

    Period & 'B'

    Enter the name you want to give the new field in the Column Header box. In your example: New Period.

    You can't use Excel worksheet functions in MS Query, but you can use(some) VBA functions in expressions. For example, the equivalent of Excel's IF function is IIf. For example:

    IIf(Amount > 1000, 'Large', 'Small')

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thanks Hans... I'm still having trouble with the formula's. It always gives me these messages:

    Error for when I try using the > sign is this one....
    SQL0104 - Token > was not valid. Valid tokens: ),.

    Error for when I try using the & sign is this one...
    SQL0104 - Token & was not valid. Valid tokens: +- AS <IDENTIFIER>.

    Now if I just enter 'B' in the field, then the letter B shows up down the whole column, but I'm trying to JOIN the period and the letter B. The If statement you gave me gives the 1st error message above.

    Any other ideas??

    Thanks!
    Lana

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Can you select View | SQL or click the SQL button on the toolbar?

    If so, could you copy the SQL instruction and paste it into a reply?

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts
    I already tried to just type it into the SQL area (see below), but it didn't work... thought I'd give it a whirl. I also tried using the word AND instead of the & sign, and that didn't work either. The field called BSNBCD is the Period, so I'm trying to join the BSNBCD field with the letter B (as seen in the SQL below... which didn't work).


    SELECT left(BSJ6CD,2), YABSREP.BSJ6CD, YABSREP.BSBPCD, YABSREP.BSJ7CD, YABSREP.BSBQCD, YABSREP.BSBEVA, YABSREP.BSNBCD, YABSREP.BSGRCD, YABSREP.BSJ9CD, YABSREP.BSGPCD, YABSREP.BSGQCD, YABSREP.BSOMCD, LEFT(BSJCNB,4), YABSREP.BSBEVA, BSNBCD & 'B'
    FROM SCAR.AMFLIB.YABSREP YABSREP
    WHERE (YABSREP.BSJCNB Between 200700 And 200912)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'm afraid I don't know why it doesn't work. The syntax looks correct to me, and similar expressions work OK for me, without error messages.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Where do the data come from? An Access or SQL Server database? If so, you could create a query in the database, and get the data from that query instead of the table.

  8. #8
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts
    The data is coming from our AS400. The software we use is called MAPICS.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I have no experience with MAPICS, so I have no idea what is possible with it and what isn't. Perhaps someone else will have a suggestion.

Posting Permissions

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