Possibly the stupidest thing ever programmed (1 Viewer)

[BamaColtsFan]

Ok, so I'm trying to use nested IIf statements to determine the value of one column in a query. For some reason, I can't get the query to accept the third statement. Naturally, I'm using Access 2007 which, rather than actually tell me what the error is, it simply reverts back to the previously saved statement when it doesn't like what I type so I have no clue what the problem actually is. Whoever decided that little trick should be included in Access needs to be beaten severly...

Anyway, I can't figure out what is wrong with the following:

Reason: IIf([Name] In (SELECT [Trouble Tickets].Name 
			FROM [Trouble Tickets] 
			WHERE  [Trouble Tickets]![Timecard Stop Date] = [CombinedTimecards]![Timecard Stop Date] 
			And [Trouble Tickets]![Name] = [CombinedTimecards]![Name] 
			And [Trouble Tickets]![Date Closed] is NULL ),'TICKET', 


	(IIf([Name] In (SELECT [Deployed Soldiers].[Deployed_Name] 
			FROM [Deployed Soldiers] 
			WHERE [Deployed Soldiers].[Deployed] = True 
			And [Deployed Soldiers].[Deployed_Name] = [CombinedTimecards]![Name] 
			And [CombinedTimecards].[Timecard Stop Date] BETWEEN [Deployed Soldiers].[Depart_Date] 
			And [Deployed Soldiers].[Return_Date]),'DEPLOYED/LWOP', 

	(IIf([Name] In (SELECT [qryDelinquentBatch].Name 
			FROM [qryDelinquentBatch] 
			WHERE [qryDelinquentBatch]![Timecard Stop Date] = [CombinedTimecards]![Timecard Stop Date]
			And [qryDelinquentBatch]![Name] = [CombinedTimecards]![Name] ), 'Batch',

	' '))))

Any advice would be appreciated!

THNX!

Oh by the way - I forgot to mention that it works perfectly with just two nested IIf statements...

 

[Vassago]

Ok, so I'm trying to use nested IIf statements to determine the value of one column in a query. For some reason, I can't get the query to accept the third statement. Naturally, I'm using Access 2007 which, rather than actually tell me what the error is, it simply reverts back to the previously saved statement when it doesn't like what I type so I have no clue what the problem actually is. Whoever decided that little trick should be included in Access needs to be beaten severly...

Anyway, I can't figure out what is wrong with the following:

Reason: IIf([COLOR="Red"]1[/COLOR][Name] In (SELECT [Trouble Tickets].Name 
			FROM [Trouble Tickets] 
			WHERE  [Trouble Tickets]![Timecard Stop Date] = [CombinedTimecards]![Timecard Stop Date] 
			And [Trouble Tickets]![Name] = [CombinedTimecards]![Name] 
			And [Trouble Tickets]![Date Closed] is NULL ),'TICKET', 


	([COLOR="red"]2[/COLOR]IIf([COLOR="red"]3[/COLOR][Name] In (SELECT [Deployed Soldiers].[Deployed_Name] 
			FROM [Deployed Soldiers] 
			WHERE [Deployed Soldiers].[Deployed] = True 
			And [Deployed Soldiers].[Deployed_Name] = [CombinedTimecards]![Name] 
			And [CombinedTimecards].[Timecard Stop Date] BETWEEN [Deployed Soldiers].[Depart_Date] 
			And [Deployed Soldiers].[Return_Date]),'DEPLOYED/LWOP', 

	([COLOR="red"]4[/COLOR]IIf([COLOR="red"]5[/COLOR][Name] In (SELECT [qryDelinquentBatch].Name 
			FROM [qryDelinquentBatch] 
			WHERE [qryDelinquentBatch]![Timecard Stop Date] = [CombinedTimecards]![Timecard Stop Date]
			And [qryDelinquentBatch]![Name] = [CombinedTimecards]![Name] ), 'Batch',

	' ')))) [COLOR="red"]<--- Only 4 Closed[/COLOR]

Any advice would be appreciated!

THNX!

Oh by the way - I forgot to mention that it works perfectly with just two nested IIf statements...

Well, for one, because you have open ended parenthesis around the nested if statements, it looks like you are missing one ")". Look above in red.

 

[boblarson]

Also, NAME should be in square brackets EVERYWHERE because it is an Access Reserved Word and should NOT be used as the name of a field or object. But since you have that, it needs to be in square brackets.

 

[BamaColtsFan]

Well, for one, because you have open ended parenthesis around the nested if statements, it looks like you are missing one ")". Look above in red.

Well, for the love of God.... all it had to do was give me a little error message and let me fix it! Thanks a million Vassago... Works great now!

 

[boblarson]

Make sure you get .Name in square brackets too, or it may fail at some other point down the road.

 

[BamaColtsFan]

Also, NAME should be in square brackets EVERYWHERE because it is an Access Reserved Word and should NOT be used as the name of a field or object. But since you have that, it needs to be in square brackets.

Oops... I guess I knew that Bob, but I clearly overlooked it... I will look into changing it when I can... THNX!

 

[Vassago]

Well, for the love of God.... all it had to do was give me a little error message and let me fix it! Thanks a million Vassago... Works great now!

No problem! Glad it worked out for you.