Results 1 to 10 of 10
  1. #1
    Shakenaw is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2011
    Posts
    12

    CASE STATEMENT or IF-ELSE-THEN? HELP

    I have about 70 letters (each letter has a certain number) according to their number they could fall into 3 different categories, I am trying to do a “If – Else Statement”, but I don’t want to do 70 individual If Else Statements for each letter, and was wondering if there’s a way that I can have “3” different If statements according to category, and just include the Letter number as a OR, AND, something to that affect, and then if there’s a new letter that comes about that I don’t have, it returns the result that is in that field from original file.

    FYI, The Original File only separates one of the 3 categories, every category thereafter is BLANK, this is why I need a If-Else-Then Statement to input it in my
    data…

    (I know I can do 70 different If-Else-Then statements, but that’s a lot) and way over 70 letters
    For example,
    IIf([LETTER_NO] IS "2243" Or "2244" Or “4678” Or “5633”,"Mainframe",
    IIf([LETTER_NO] IS "1234" Or "3256" Or “6829” Or “7202”,"LOCAL",
    IIf([LETTER_NO] IS "4256" Or "7892","EMAIL", [LETTER_NO])

    The File once imported in Access looks like this (in the original File it only identifies the “E” (Email) and doesn’t specify the other letter type it leaves it blank
    Letter_# - Letter_TYPE


    2243
    2244
    4678
    5633
    1234
    3256
    6829
    7202
    4256 - E
    7892 - E
    2212 - E
    3684 - E




    Ultimately I want it to look like this
    MainFrame - Local - Email
    2243 -1234 - 4256
    2244 - 3256 - 7892
    4678 - 6829 - 2212
    5633 - 7202 - 3684





    Someone informed me to try a “Case Statement” I don’t know how that goes?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    With 70 items, and what I assume is the possibility of more being added, I'd probably have a table that had the letters in one field and the categories in another. Then you can get the category with a simple lookup or SQL join. It's also more flexible and maintainable than having it in code.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Shakenaw is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2011
    Posts
    12
    How do you do a Lookup in Access, or Excel. In access I can just update the table (decode) that list all the letter types but once updated, how do i get it to update my spreadsheet in excell with the new letters found including the names of the letter

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Not clear on how Excel is involved. I would only store the data in one place. In Access you can use DLookup or simply join the table containing these values to the data tables in a query to get the equivalent categories.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Shakenaw is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2011
    Posts
    12
    I ended up Joining the Tables in Access at the append Qry. In Excell, i just did a pivot table for a spreadsheet, so it can look good, however it brings in the Letter Numbers and count of letters sent, but the Name of the letters I have to type in manually, and wanted to know if a letter gets added, how to keep that CELL (name of that particular letter) from moving. I noticed that i would have for example 5 rows of letters with their name, but if a 6th letter came through it would add a row automatically, but the Name that i manually inputed is now off and not matching up with the letter number.

    I guess how to create a "Vlookup" in EXCEL

  6. #6
    Shakenaw is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2011
    Posts
    12
    I found out how to do the VLOOKUP in EXCELL...it's pretty cool and may use it often for automatic updating...

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad you got it sorted out.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    Shakenaw is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2011
    Posts
    12
    One quick Questions: I'm using 2007 Access and Excel, I know that in excell, if i want to bring over a query or table i can do so, but is there a way of bring over a "CROSSTAB" Query? I say this, becuase when i go to the external data source and it list all my tables and queries, all are there except for the Crosstabs.

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Not sure. I typically work from Access. I know you can "push" a crosstab query from Access to Excel; not sure about "pulling" from Excel.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    ahoneycutt is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Posts
    18
    Create a qry that is based on the crosstab query.
    Use that qry to export to excel.

    Hope that helps someone else considering this post is 3 years old.

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

Similar Threads

  1. Print a Closed case
    By lyndon.pace in forum Reports
    Replies: 1
    Last Post: 04-11-2011, 11:35 AM
  2. Print ONE particular case
    By lyndon.pace in forum Programming
    Replies: 2
    Last Post: 04-11-2011, 07:53 AM
  3. Case or Switch in an update statement
    By allenjasonbrown@gmail.com in forum Queries
    Replies: 7
    Last Post: 11-17-2010, 01:49 AM
  4. Case Statement Syntax
    By allenjasonbrown@gmail.com in forum Programming
    Replies: 1
    Last Post: 11-16-2010, 07:18 PM
  5. Use Case Scenarios
    By scott.weppler in forum Access
    Replies: 0
    Last Post: 05-31-2009, 12:15 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