Results 1 to 10 of 10
  1. #1
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185

    If Statement in query

    I am trying to edit this query



    Hrs Work: IIf([B-B Assocaite Tracking]![Lunch Start] Is Null Or [B-B Assocaite Tracking]![Lunch End] Is Null,IIf([B-B Assocaite Tracking]![End Time]<[B-B Assocaite Tracking]![Start Time],DateAdd("d",1,[B-B Assocaite Tracking]![End Time]),[B-B Assocaite Tracking]![End Time])-[B-B Assocaite Tracking]![Start Time],IIf([B-B Assocaite Tracking]![End Time]<[B-B Assocaite Tracking]![Start Time],DateAdd("d",1,[B-B Assocaite Tracking]![End Time]),[B-B Assocaite Tracking]![End Time])-[B-B Assocaite Tracking]![Start

    Whenever I try to edit the query it starts giving me an error when I try to add anything to the statement.

    The error is
    "Cannot perform Join, Group, Sort, or Indexed restriction. A value being searched or sorted on is too long."

    Is there any work around for this?

    Thanks,
    Nick

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Your IIf's need to be contained within brackets - IIf(....IIf(...))
    Your second one doesn't have a closing bracket

    Are those "!"s correct? Don't remember as far back as 2002! Are those fields on a table?

  3. #3
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Why does your statement end abruptly, is that a typo or is that Access telling you that your sentence is too long!

  4. #4
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    Quote Originally Posted by aytee111 View Post
    Why does your statement end abruptly, is that a typo or is that Access telling you that your sentence is too long!
    Yes, When I go into expression builder and try to finish the statement it gives me the above error in quotations.

  5. #5
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    Quote Originally Posted by aytee111 View Post
    Your IIf's need to be contained within brackets - IIf(....IIf(...))
    Your second one doesn't have a closing bracket

    Are those "!"s correct? Don't remember as far back as 2002! Are those fields on a table?
    Yes, those are fields on a table. Wen I try to add in the field name and close the () it also gives me issues.

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Either you will have to separate it out into multiple fields or else do it in VBA.

  7. #7
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    Ok, thanks, I will try what you are saying.

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I would also suggest you develop a better naming convention.
    Why
    do you have spaces in the object names? NO one but a programmer would/should see the object names so "Proper English" is not needed.

    I know it is hard to resist using spaces, but FIGHT the urge! Don't give in! Win the battle!


    Examples:
    Table name: [B-B Assocaite Tracking] --> bad because of the spaces and the special character.
    Better would be "BB_Assocaite_Tracking".
    Best would be "BBAssocaiteTracking"

    Field name: [Lunch Start] --> bad because of the space.
    Better would be "Lunch_Start".
    Best would be "LunchStart"



    Object names should be letters and numbers. No spaces, punctuation or special characters (exception is the underscore) in names.

  9. #9
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    Quote Originally Posted by ssanfu View Post
    I would also suggest you develop a better naming convention.
    Why
    do you have spaces in the object names? NO one but a programmer would/should see the object names so "Proper English" is not needed.

    I know it is hard to resist using spaces, but FIGHT the urge! Don't give in! Win the battle!


    Examples:
    Table name: [B-B Assocaite Tracking] --> bad because of the spaces and the special character.
    Better would be "BB_Assocaite_Tracking".
    Best would be "BBAssocaiteTracking"

    Field name: [Lunch Start] --> bad because of the space.
    Better would be "Lunch_Start".
    Best would be "LunchStart"


    Object names should be letters and numbers. No spaces, punctuation or special characters (exception is the underscore) in names.
    This code was made by a previous employee. I am just trying to fix it.

  10. #10
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Oh...
    Well..... sorry... you have your work cut out for yourself.


    Good luck...

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

Similar Threads

  1. Can anyone help with my SQL statement query
    By fluffyvampirekitten in forum Access
    Replies: 1
    Last Post: 08-20-2015, 09:53 PM
  2. if statement in sql statement, query builder
    By 54.69.6d.20 in forum Access
    Replies: 4
    Last Post: 09-11-2012, 07:38 AM
  3. Replies: 3
    Last Post: 07-10-2012, 05:23 AM
  4. If statement in Query
    By Jojojo in forum Queries
    Replies: 5
    Last Post: 11-18-2011, 03:02 PM
  5. Iif Statement in query
    By evanhughes in forum Queries
    Replies: 1
    Last Post: 11-03-2011, 08:55 AM

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