Solved Expression too complex in query expression 'Switch' (1 Viewer)

saledo2000

Registered User.
Local time
Today, 11:55
Joined
Jan 21, 2013
Messages
94
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
Local time
Today, 04:55
Joined
Oct 29, 2018
Messages
21,357
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.
Local time
Today, 11:55
Joined
Jan 21, 2013
Messages
94
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
Local time
Today, 04:55
Joined
Oct 29, 2018
Messages
21,357
Okay, looking at your expression, maybe the problem is with using Null. Try using -1 instead.
 

saledo2000

Registered User.
Local time
Today, 11:55
Joined
Jan 21, 2013
Messages
94
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

  • Switch error.png
    Switch error.png
    10.2 KB · Views: 361

saledo2000

Registered User.
Local time
Today, 11:55
Joined
Jan 21, 2013
Messages
94
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
Local time
Today, 04:55
Joined
Oct 29, 2018
Messages
21,357
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.
Local time
Today, 07:55
Joined
May 21, 2018
Messages
8,463
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.
 

Dreamweaver

Well-known member
Local time
Today, 11:55
Joined
Nov 28, 2005
Messages
2,466
could you post a screen shot of the form/report if you have started it

mick
 

saledo2000

Registered User.
Local time
Today, 11:55
Joined
Jan 21, 2013
Messages
94
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.
 

saledo2000

Registered User.
Local time
Today, 11:55
Joined
Jan 21, 2013
Messages
94
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.
 

Dreamweaver

Well-known member
Local time
Today, 11:55
Joined
Nov 28, 2005
Messages
2,466
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
 

Dreamweaver

Well-known member
Local time
Today, 11:55
Joined
Nov 28, 2005
Messages
2,466
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
Local time
Today, 11:55
Joined
Jul 21, 2014
Messages
2,237
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.
Local time
Today, 07:55
Joined
May 21, 2018
Messages
8,463
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:

Users who are viewing this thread

Top Bottom