Results 1 to 13 of 13
  1. #1
    Drew101 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    22

    If then with parameters

    What is wrong with this statement and how do I fix it. in SQL View

    [code][IIf([transaction_activity].[type]="No",[transaction_activity].[Description],transaction_activity].[type])][code]

    Basically what I am saying if in the [Type] field there is the word "NO" Then look in the [Description] field and bring over that value, and everything else in the [Type] field stays the same. I am changing only the "NO" to what ever the first 30 characters states in the [Description] field. I need it not to go over 30 characters

  2. #2
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Firstly lose square bracket at beginning and end.

  3. #3
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    If you've put that code in the designer like before you are saying if type is exactly "no", put traction_activity there, if type isn't exactly no then put type there

  4. #4
    Drew101 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    22
    So basically I am unable to produce all of that, if then statement, in the same field. I would need to divide it out like the Debit and Credit and then combine the data possibly in another query?

  5. #5
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    You could put that all in a calculated field in your query

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If Description can be more than 30 characters, try:

    IIf([type]="No", Left([Description],30), [type])
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Drew101 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    22
    Click image for larger version. 

Name:	Design View 2.JPG 
Views:	13 
Size:	17.0 KB 
ID:	28895Is that before Type or after Type I am getting errors

  8. #8
    Drew101 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    22
    First 30 Characters only, or <30 or was that a typo in your sentence

  9. #9
    Drew101 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    22
    Were you meaning to say should not be more than 30 Characters

  10. #10
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051

    If then with parameters

    Are you trying to replace data?

    June's method should be fine but it will be a new column. (You shouldn't change data in your table, just calculate new ones)

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    FYI, both description and type are reserved words (as are many others such as desc, date, time). Using them as field names can cause unexpected and misleading errors.

  12. #12
    Drew101 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    22
    Yes, only If the data in [type] says the words "NO" Then grab or look into [Description] and give me the first 30 characters. aka Replace

  13. #13
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    In a new column type expr1: (then type the code from June in #6)

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

Similar Threads

  1. Asking For Parameters Twice
    By batowl in forum Reports
    Replies: 3
    Last Post: 03-08-2012, 03:01 PM
  2. Parameters
    By butl3111 in forum Access
    Replies: 1
    Last Post: 04-14-2011, 10:29 PM
  3. Parameters
    By Alaska1 in forum Access
    Replies: 1
    Last Post: 03-30-2011, 02:05 PM
  4. Too few parameters
    By jgelpi16 in forum Programming
    Replies: 1
    Last Post: 12-23-2010, 10:28 AM
  5. Too Few Parameters
    By jgelpi16 in forum Programming
    Replies: 8
    Last Post: 09-27-2010, 01:46 PM

Tags for this Thread

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