Solved Expression too complex in query expression 'Switch'

[saledo2000]

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]

Hi. Not sure if this will help, but also check out the Choose() function in place of using Switch(), in this case. Cheers!

 

[saledo2000]

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]

Okay, looking at your expression, maybe the problem is with using Null. Try using -1 instead.

 

[saledo2000]

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

 

[Dreamweaver]

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

 

[theDBguy]

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]

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]

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?

Choose(Nz([Age],0),"[Zero]","[One]",etc...)

 

[MajP]

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.

Solved - Insert into statement from unbound text box to a table.

Wow! I really must need some more coffee today! :coffee: I missed out a comma after the 't.*' See if this works: SELECT t.*, Switch( t.Age < 4, '0 - 3', t.Age < 12, '4 - 12', t.Age < 13, '12 - 13', t.Age < 17, '13 - 17', t.Age < 30, '18 - 30', t.Age >= 30, '30 plus'...

www.access-programmers.co.uk

 

[Dreamweaver]

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

mick

 

[saledo2000]

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?

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.

 

[Dreamweaver]

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

 

[saledo2000]

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.

Solved - Insert into statement from unbound text box to a table.

Wow! I really must need some more coffee today! :coffee: I missed out a comma after the 't.*' See if this works: SELECT t.*, Switch( t.Age < 4, '0 - 3', t.Age < 12, '4 - 12', t.Age < 13, '12 - 13', t.Age < 17, '13 - 17', t.Age < 30, '18 - 30', t.Age >= 30, '30 plus'...

www.access-programmers.co.uk

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]

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

Yes that is my plan.

 

[Dreamweaver]

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]

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.

 

[Dreamweaver]

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]

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

In a standard module:

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:

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]

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.

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.