# SolvedExpression too complex in query expression 'Switch' (1 Viewer)

#### saledo2000

##### Registered User.
Hi everyone,
I am trying to write a query in Access 2013 to get Age for children from Zero to Seventeen years old, but getting an error "Expression too complex in query expression 'Switch'".
SELECT
Rt.*,
Switch(
Rt.Age = 0, [Zero],
Rt.Age = 1, [One],
Rt.Age = 2, [Two],
Rt.Age = 3, [Three],
Rt.Age = 4, [Four],
Rt.Age = 5, [Five],
Rt.Age = 6, [Six],
Rt.Age = 7, [Seven],
Rt.Age = 8, [Eight],
Rt.Age = 9, [Nine],
Rt.Age = 10, [Ten],
Rt.Age = 11, [Eleven],
Rt.Age = 12, [Twelve],
Rt.Age = 13, [Thirteen],
Rt.Age = 14, [Fourteen],
Rt.Age = 15, [Fiften],
Rt.Age = 16, [Sixteen],
Rt.Age = 17, [Seventeen],
) AS [Range]
FROM (
SELECT
ID_Number,
DoB,
IIf(
IsDate([DoB]),
Year(Date()) - Year([DoB]) + (Date() < DateSerial(Year(Date()), Month([DoB]), Day([DoB]))),
Null
) AS Age
FROM tblReg
) Rt;

When I move field names for Age (Zero, One ... Seventeen) from query then is OK, but I need these names to create report in Access. Is there any other solution for my problem.
Thanks.

#### theDBguy

##### I’m here to help
Staff member
Hi. Not sure if this will help, but also check out the Choose() function in place of using Switch(), in this case. Cheers!

#### saledo2000

##### Registered User.
Hi. Not sure if this will help, but also check out the Choose() function in place of using Switch(), in this case. Cheers!
Thank you for quick reply.
Choose also gives the same error.

#### theDBguy

##### I’m here to help
Staff member
Okay, looking at your expression, maybe the problem is with using Null. Try using -1 instead.

#### saledo2000

##### Registered User.
Okay, looking at your expression, maybe the problem is with using Null. Try using -1 instead.
It does not work again. I think problem is that query engine cannot calculate that much information.
Thanks

#### Attachments

• 10.2 KB Views: 4

#### MickJav

##### AWF VIP
are these all fields [Zero] ??
not seen it written like that before

#### theDBguy

##### I’m here to help
Staff member
are these all fields [Zero] ??
not seen it written like that before
Hi saledo. Mick has a good point. Can you please clarify that? Thanks.

#### saledo2000

##### Registered User.
are these all fields [Zero] ??
not seen it written like that before
DoB is field in tblReg where query checks difference to Date() and set value in unbound box named Age. Need to make temp table Rt for every child to count how many children by Age. I have used 'One' quotes and gives me the same result. Then I changed to [One], but result is the same.

#### theDBguy

##### I’m here to help
Staff member
DoB is field in tblReg where query checks difference to Date() and set value in unbound box named Age Need to make temp table for every child to count how many children by Age.
But your subquery only has an [Age] field. I/We don't see any fields named [Zero], [One], etc...

Maybe your expression could be something more like?
Code:
``Choose(Nz([Age],0),"[Zero]","[One]",etc...)``

#### MajP

##### You've got your good things, and you've got mine.
Forget about the switch. Make a table and join on age

tblNumbers
NumVal NumText
0 Zero
1 One
....

I thought I provided a working solution already for this.

#### MickJav

##### AWF VIP
could you post a screen shot of the form/report if you have started it

mick

#### saledo2000

##### Registered User.
But your subquery only has an [Age] field. I/We don't see any fields named [Zero], [One], etc...

Maybe your expression could be something more like?
Code:
``Choose(Nz([Age],0),"[Zero]","[One]",etc...)``
could you post a screen shot of the form/report if you have started it

mick
No I did not start because will create report from this query.

#### MickJav

##### AWF VIP
Would you be trying to create a crosstab sort of report?

#### saledo2000

##### Registered User.
Forget about the switch. Make a table and join on age

tblNumbers
NumVal NumText
0 Zero
1 One
....

I thought I provided a working solution already for this.
Yes you posted yesterday. I forgot on that and did not try your solution yet. Thank you for the answer on my previous thread.

#### saledo2000

##### Registered User.
Would you be trying to create a crosstab sort of report?
Yes that is my plan.

#### MickJav

##### AWF VIP
Thought so I'm just of to bed but I think a code sulution will work better unless others have a better Idear if not I'll try and sort something tomorrow

mick

#### saledo2000

##### Registered User.
Thought so I'm just of to bed but I think a code sulution will work better unless others have a better Idear if not I'll try and sort something tomorrow

mick
Thank you Mick.

#### MickJav

##### AWF VIP
You could use a cross tab query for a sub report

You said you were displaying totals for an age are there more than 1 row like girls, boys?

#### cheekybuddha

##### AWF VIP
Use a UDF. Switch() and Choose() probably have a limit on how many arguments they can take.

In a standard module:
Code:
``````Function NumberToWord(iNum As Integer) As String
' This only works for 0 -17!!!
Dim strRet As String

Select Case iNum
Case 0: strRet = "Zero"
Case 1: strRet = "One"
Case 2: strRet = "Two"
Case 3: strRet = "Three"
Case 4: strRet = "Four"
Case 5: strRet = "Five"
Case 6: strRet = "Six"
Case 7: strRet = "Seven"
Case 8: strRet = "Eight"
Case 9: strRet = "Nine"
Case 10: strRet = "Ten"
Case 11: strRet = "Eleven"
Case 12: strRet = "Twelve"
Case 13: strRet = "Thirteen"
Case 14: strRet = "Fourteen"
Case 15: strRet = "Fifteen"
Case 16: strRet = "Sixteen"
Case 17: strRet = "Seventeen"
End Select

NumberToWord = "[" & strRet & "]"

End Function``````
Then, in your SQL:
Code:
``````SELECT
Rt.*,
NumberToWord(Rt.Age) AS [Range]
FROM (
SELECT
ID_Number,
DoB,
IIf(
IsDate([DoB]),
Year(Date()) - Year([DoB]) + (Date() < DateSerial(Year(Date()), Month([DoB]), Day([DoB]))),
Null
) AS Age
FROM tblReg
) Rt;``````
(Or use MajP's table driven solution!)

hth,

d

#### MajP

##### You've got your good things, and you've got mine.
Is there any other solution for my problem
. If you build the UDF NumberToWord then you can use in calculated field as well. You can avoid the query all together.
=NumberToWord([DOB])

I see that what I wrote originally was definitely not clear. I was suggesting you could wrap the two functions. But to be clear I would make a table to do this and not a function except for the age.
Code:
``````Function NumberToWord(DOB As Date) As String
' This only works for 0 -17!!!
Dim strRet As String
Dim INum As Long
INum = AgeYears(DOB)
Select Case INum
Case 0: strRet = "Zero"
Case 1: strRet = "One"
Case 2: strRet = "Two"
Case 3: strRet = "Three"
Case 4: strRet = "Four"
Case 5: strRet = "Five"
Case 6: strRet = "Six"
Case 7: strRet = "Seven"
Case 8: strRet = "Eight"
Case 9: strRet = "Nine"
Case 10: strRet = "Ten"
Case 11: strRet = "Eleven"
Case 12: strRet = "Twelve"
Case 13: strRet = "Thirteen"
Case 14: strRet = "Fourteen"
Case 15: strRet = "Fifteen"
Case 16: strRet = "Sixteen"
Case 17: strRet = "Seventeen"
End Select
NumberToWord = strRet
End Function
Public Function AgeYears(ByVal datBirthDate As Date) As Integer
' Comments: Returns the age in years
' Params  : datBirthDate    Date to check
' Returns : Number of years
' Source  : Total Visual SourceBook
On Error GoTo PROC_ERR

Dim intYears As Integer

intYears = Year(Now) - Year(datBirthDate)

If DateSerial(Year(Now), Month(datBirthDate), Day(datBirthDate)) > Now Then
' Subtract a year if birthday hasn't arrived this year
intYears = intYears - 1
End If

AgeYears = intYears

PROC_EXIT:
Exit Function

PROC_ERR:
MsgBox "Error: " & Err.Number & ". " & Err.Description, , "modDateTime.AgeYears"
Resume PROC_EXIT
End Function``````
As pointed out this is a more accurate age in years.

Last edited: