If Statement in query

[nick243]

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

[aytee111]

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?

[aytee111]

Why does your statement end abruptly, is that a typo or is that Access telling you that your sentence is too long!

[nick243]

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.

[nick243]

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.

[aytee111]

Either you will have to separate it out into multiple fields or else do it in VBA.

[nick243]

Ok, thanks, I will try what you are saying.

[ssanfu]

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.

[nick243]

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.

[ssanfu]

Oh...
Well..... sorry... you have your work cut out for yourself.


Good luck...