Access msg Too complex

[DDonnachie]

Hi folks,

The following SQL statment works

SELECT StaffHoursTable.EngName, StaffHoursTable.JobNumberShadow, [MonNorm]+[TueNorm]+[WedNorm]+[ThurNorm]+[FriNorm]+[SatNorm]+[SuNorm] AS Norm_Hours, [MonOT]+[TueOT]+[WedOT]+[ThurOT]+[FriOT]+[SatOT]+[SunOT] AS OT_Hours, wktodate(StaffHoursTable.WeekNo,StaffHoursTable.Year) AS WeekDate, Jobs.ProjectType, Jobs.ProjectDescription, wktodate([StaffHoursTable].[WeekNo],[StaffHoursTable].[Year]) 
FROM (StaffHoursTable INNER JOIN Jobs ON StaffHoursTable.JobNumberShadow = Jobs.JobNumber) INNER JOIN EngStaff ON StaffHoursTable.EngName = EngStaff.Eng_Staff 
WHERE StaffHoursTable.EngName In (SELECT [EngStaff]![Eng_Staff] FROM EngStaff) AND 
StaffHoursTable.JobNumberShadow Not In (SELECT [ExcludeJobs]![Exc_Job] FROM ExcludeJobs) AND 
Jobs.ProjectType Not Like "Engineering" 
ORDER BY StaffHoursTable.EngName; 
 

But if I add in the condition

 wktodate([StaffHoursTable].[WeekNo],[StaffHoursTable].[Year]) Between Cdbl([Forms]![frmDatesWBAllStaffRpt]![TxtDtStrt]) And Cdbl([Forms]![frmDatesWBAllStaffRpt]![TxtDtEnd]) 
 

-------------
the function WkToDate is

Function WkToDate(Wk As Integer, Yr As Integer) As Double
'Function WkToDate(Wk As Integer, Yr As Integer) As Date 
Dim Res As Date 
If Wk * 7 < 270 Then 
Res = Format("5/4/" & Yr, "dd/MM/yyyy") 
Res = Res + (Wk * 7) 
Else 
Res = Format("5/4/" & Yr - 1, "dd/MM/yyyy") 
Res = Res + (Wk * 7) 
End If 
Debug.Print Res 
WkToDate = Res 
End Function 
 

Bit of background might help (might not).
This is from a company database developed before I joined and like most small companies its just grown with the company. I was asked to make it exclude certain Projects/Jobs and only include Specified personnel. The person who wrote this used macros extensively and zero code (except auto generated obviously).
This is not the original query, it was a query of a query and to be honest I'm not entirely sure it done what it was supposed to. This query is effectively both queries brought together. The WkToDate is a function I introduced to remove expressions from the original query.

Should probably also mention that if I use the condition

wktodate([StaffHoursTable].[WeekNo],[StaffHoursTable].[Year]) <> to any kind of single value then it gives a type mismatch error. This may well be why it generates the Too complex error but I don't really know.

I have posted the same question on the Mr Excell web forum for Access as well
http://www.mrexcel.com/board2/viewtopic.php?p=1386854#1386854
In that forum it was suggested that using the Cdbl conversion and the function returning a Double may help, unfortunately I get the same result.

Spent all day trawling net and trying various things to get this working. So any help u can provide will be appreciated

Using Access 2003 on XP.
_________________
Thanks

David Donnachie

[FishVal]

Hi folks,

The following SQL statment works

SELECT StaffHoursTable.EngName, StaffHoursTable.JobNumberShadow, [MonNorm]+[TueNorm]+[WedNorm]+[ThurNorm]+[FriNorm]+[SatNorm]+[SuNorm] AS Norm_Hours, [MonOT]+[TueOT]+[WedOT]+[ThurOT]+[FriOT]+[SatOT]+[SunOT] AS OT_Hours, wktodate(StaffHoursTable.WeekNo,StaffHoursTable.Year) AS WeekDate, Jobs.ProjectType, Jobs.ProjectDescription, wktodate([StaffHoursTable].[WeekNo],[StaffHoursTable].[Year]) 
FROM (StaffHoursTable INNER JOIN Jobs ON StaffHoursTable.JobNumberShadow = Jobs.JobNumber) INNER JOIN EngStaff ON StaffHoursTable.EngName = EngStaff.Eng_Staff 
WHERE StaffHoursTable.EngName In (SELECT [EngStaff]![Eng_Staff] FROM EngStaff) AND 
StaffHoursTable.JobNumberShadow Not In (SELECT [ExcludeJobs]![Exc_Job] FROM ExcludeJobs) AND 
Jobs.ProjectType Not Like "Engineering" 
ORDER BY StaffHoursTable.EngName; 
 

But if I add in the condition

 wktodate([StaffHoursTable].[WeekNo],[StaffHoursTable].[Year]) Between Cdbl([Forms]![frmDatesWBAllStaffRpt]![TxtDtStrt]) And Cdbl([Forms]![frmDatesWBAllStaffRpt]![TxtDtEnd]) 
 

-------------
the function WkToDate is

Function WkToDate(Wk As Integer, Yr As Integer) As Double
'Function WkToDate(Wk As Integer, Yr As Integer) As Date 
Dim Res As Date 
If Wk * 7 < 270 Then 
Res = Format("5/4/" & Yr, "dd/MM/yyyy") 
Res = Res + (Wk * 7) 
Else 
Res = Format("5/4/" & Yr - 1, "dd/MM/yyyy") 
Res = Res + (Wk * 7) 
End If 
Debug.Print Res 
WkToDate = Res 
End Function 
 

Bit of background might help (might not).
This is from a company database developed before I joined and like most small companies its just grown with the company. I was asked to make it exclude certain Projects/Jobs and only include Specified personnel. The person who wrote this used macros extensively and zero code (except auto generated obviously).
This is not the original query, it was a query of a query and to be honest I'm not entirely sure it done what it was supposed to. This query is effectively both queries brought together. The WkToDate is a function I introduced to remove expressions from the original query.

Should probably also mention that if I use the condition

wktodate([StaffHoursTable].[WeekNo],[StaffHoursTable].[Year]) <> to any kind of single value then it gives a type mismatch error. This may well be why it generates the Too complex error but I don't really know.

I have posted the same question on the Mr Excell web forum for Access as well
http://www.mrexcel.com/board2/viewtopic.php?p=1386854#1386854
In that forum it was suggested that using the Cdbl conversion and the function returning a Double may help, unfortunately I get the same result.

Spent all day trawling net and trying various things to get this working. So any help u can provide will be appreciated

Using Access 2003 on XP.
_________________
Thanks

David Donnachie

Hi, David.

Try the following.

 WkToDate([StaffHoursTable].[WeekNo],[StaffHoursTable].[Year]) Between CDate([Forms]![frmDatesWBAllStaffRpt]![TxtDtStrt]) And CDate([Forms]![frmDatesWBAllStaffRpt]![TxtDtEnd]) 

Function WkToDate(ByVal Wk As Integer, ByVal Yr As Integer) As Date
 
    If Wk * 7 >= 270 Then Yr = Yr - 1
    WkToDate = DateAdd("w", Wk, DateSerial(Yr, 4, 5))
 
End Function
 

[DDonnachie]

Hi FishVal,

still getting the same error, don't know what's causing this but I know its to do with the comparison on this date field (really wish the original designer had included the date field on the record instead of just the Week and Year)


Thanks anyway

Will keep trying.....

[FishVal]

Hi FishVal,

still getting the same error, don't know what's causing this but I know its to do with the comparison on this date field (really wish the original designer had included the date field on the record instead of just the Week and Year)


Thanks anyway

Will keep trying.....

Ok.

Move criteria fields to SELECT clause of the query to see whether they are being retrieved correctly.

[DDonnachie]

Sorry I don't understand what you mean ?

If I remove the criteria from the dat field then the query works fine. If I remove everything execpt the calculation fields then it still doesn't work.

I reduced the query to

SELECT wktodate(StaffHoursTable.WeekNo,StaffHoursTable.Year) AS WeekDate
FROM StaffHoursTable
WHERE (((wktodate([StaffHoursTable].[WeekNo],[StaffHoursTable].[Year])) Between CDate([Forms]![frmDatesWBAllStaffRpt]![TxtDtStrt]) And CDate([Forms]![frmDatesWBAllStaffRpt]![TxtDtEnd])));
 

and now get this message
'Data Type Mismatch in criteria expression'

[FishVal]

Sorry I don't understand what you mean ?

If I remove the criteria from the dat field then the query works fine. If I remove everything execpt the calculation fields then it still doesn't work.

I reduced the query to

SELECT wktodate(StaffHoursTable.WeekNo,StaffHoursTable.Year) AS WeekDate
FROM StaffHoursTable
WHERE (((wktodate([StaffHoursTable].[WeekNo],[StaffHoursTable].[Year])) Between CDate([Forms]![frmDatesWBAllStaffRpt]![TxtDtStrt]) And CDate([Forms]![frmDatesWBAllStaffRpt]![TxtDtEnd])));
 

and now get this message
'Data Type Mismatch in criteria expression'

I mean the following. Retrive criteria fields w/o including them in query criteria. Just to see what you are comparing.

SELECT wktodate(StaffHoursTable.WeekNo,StaffHoursTable.Year) AS WeekDate, CDate([Forms]![frmDatesWBAllStaffRpt]![TxtDtStrt]) AS StartDate, CDate([Forms]![frmDatesWBAllStaffRpt]![TxtDtEnd]) AS EndDate
FROM StaffHoursTable;

additionally you can run the following query to see whether the types of the values being compared are adequate

SELECT wktodate(StaffHoursTable.WeekNo,StaffHoursTable.Year) AS WeekDate, Typename(wktodate(StaffHoursTable.WeekNo,StaffHoursTable.Year)) AS WeekDateType, CDate([Forms]![frmDatesWBAllStaffRpt]![TxtDtStrt]) AS StartDate, TypeName(CDate([Forms]![frmDatesWBAllStaffRpt]![TxtDtStrt])) AS StartDateType, CDate([Forms]![frmDatesWBAllStaffRpt]![TxtDtEnd]) AS EndDate, TypeName(CDate([Forms]![frmDatesWBAllStaffRpt]![TxtDtEnd])) AS EndDateType
FROM StaffHoursTable;

And BTW is the form "frmDatesWBAllStaffRpt" opened when you run the query?

You've localized error to be in WHERE clause, now it will be nice to localize what particular instruction throws it.

[DDonnachie]

I mean the following. Retrive criteria fields w/o including them in query criteria. Just to see what you are comparing.

SELECT wktodate(StaffHoursTable.WeekNo,StaffHoursTable.Year) AS WeekDate, CDate([Forms]![frmDatesWBAllStaffRpt]![TxtDtStrt]) AS StartDate, CDate([Forms]![frmDatesWBAllStaffRpt]![TxtDtEnd]) AS EndDate
FROM StaffHoursTable;

additionally you can run the following query to see whether the types of the values being compared are adequate

SELECT wktodate(StaffHoursTable.WeekNo,StaffHoursTable.Year) AS WeekDate, Typename(wktodate(StaffHoursTable.WeekNo,StaffHoursTable.Year)) AS WeekDateType, CDate([Forms]![frmDatesWBAllStaffRpt]![TxtDtStrt]) AS StartDate, TypeName(CDate([Forms]![frmDatesWBAllStaffRpt]![TxtDtStrt])) AS StartDateType, CDate([Forms]![frmDatesWBAllStaffRpt]![TxtDtEnd]) AS EndDate, TypeName(CDate([Forms]![frmDatesWBAllStaffRpt]![TxtDtEnd])) AS EndDateType
FROM StaffHoursTable;

And BTW is the form "frmDatesWBAllStaffRpt" opened when you run the query?

You've localized error to be in WHERE clause, now it will be nice to localize what particular instruction throws it.

When I run the query without any of the criteria, then the values are all as expected. The WeekDate field and the Result of WkToDate are both displayed as Date fields or atleast look like it.
If I remove the criteria from WeekDate field only then the query runs fine, also If i remove all other criteria except fot the WeekDate check then the query will still fail.
One of my test consisted of removing all other fields from the expression except for WeekDate and its search criteria, this query failed due to a 'Type Mismatch'.
Oh yeah sorry for not stating this earlier, but the Form where the dates are entered must be open for the query to execute.

The problem is definitely caused by the comparison of the date field.
Giacomo from the other site says he thinks it might be related to the way Access treats all dates as US Format and mine will be in a UK format. At the time I agreed that this could be the cause, but now I've thought about it, that shuold only cause incorrect data to come out. Also I've tried replacing the criteria limits with hard coded values in all formats and it still returns data mismatch.

hope this helps.

[FishVal]

When I run the query without any of the criteria, then the values are all as expected. The WeekDate field and the Result of WkToDate are both displayed as Date fields or atleast look like it.
If I remove the criteria from WeekDate field only then the query runs fine, also If i remove all other criteria except fot the WeekDate check then the query will still fail.
One of my test consisted of removing all other fields from the expression except for WeekDate and its search criteria, this query failed due to a 'Type Mismatch'.
Oh yeah sorry for not stating this earlier, but the Form where the dates are entered must be open for the query to execute.

The problem is definitely caused by the comparison of the date field.
Giacomo from the other site says he thinks it might be related to the way Access treats all dates as US Format and mine will be in a UK format. At the time I agreed that this could be the cause, but now I've thought about it, that shuold only cause incorrect data to come out. Also I've tried replacing the criteria limits with hard coded values in all formats and it still returns data mismatch.

hope this helps.

Well, Donnachie.

I think I should take a look at your db. Send me a copy of the db having remove info which you don't want let other to see. Leave some relevant records to run query on. I will temporarily open my vCard for download.

[DDonnachie]

Well, Donnachie.

I think I should take a look at your db. Send me a copy of the db having remove info which you don't want let other to see. Leave some relevant records to run query on. I will temporarily open my vCard for download.

Thanks FishVal,

looks like it'll be tomorrow before I can finish cleaning the DB ofthe companies info, I appreciate you taking the time to do this.

[FishVal]

Thanks FishVal,

looks like it'll be tomorrow before I can finish cleaning the DB ofthe companies info, I appreciate you taking the time to do this.

Hi, David.

• 1. You are right Access from some unknown reasons refuses to run it with messages like “type mismatch” or “expression too complex”.
  
• 2. Date comparison works well until table “StaffHoursTable” is joined with table “EngStaff”, so I think the problem is not in date comparison itself.
  
• 3. Now good news:
  
  3.1. It is much more suitable to convert StartDate and EndDate input into corresponding Week-Year couples and pass their values into the query. Criteria like (StaffHoursTable.WeekNo> 1 AND StaffHoursTable.Year>2001) AND (StaffHoursTable.WeekNo<10 AND StaffHoursTable.Year<2004) works fine and, you know, having got rid from VBA invoking in the query, the query will fly like a rocket.
  
  3.2. Plz clarify the logic of (Date) to [WeekNo, Year] conversion. From you code I can guess that “year” starts at #5/4/yyyy#, but week doesn’t start at this date each year. How it is treated this case? So I’d like you to specify the logic very-very clearly. Don’t worry about technical details, it is on my responsibility.
  
• 4. What concerning the query itself.
  
  4.1 Criteria “WHERE ((StaffHoursTable.EngName) In (SELECT [EngStaff]![Eng_Staff] FROM EngStaff))” does the same as Join (StaffHoursTable INNER JOIN EngStaff ON StaffHoursTable.EngName = EngStaff.Eng_Staff). There is no reason to implement both in the same query. INNER JOIN supposed to perform better than IN, so I recommend to get rid of the latter.
  
  4.2 Field EngStaff.Eng_Staff is not supposed to be unique. So far it doesn't affect query output, but you should think to set StaffHoursTable.EngName to be FK of EngStaff.ID.

[DDonnachie]

Hi, David.

• 1. You are right Access from some unknown reasons refuses to run it with messages like “type mismatch” or “expression too complex”.
  
• 2. Date comparison works well until table “StaffHoursTable” is joined with table “EngStaff”, so I think the problem is not in date comparison itself.
  
• 3. Now good news:
  
  3.1. It is much more suitable to convert StartDate and EndDate input into corresponding Week-Year couples and pass their values into the query. Criteria like (StaffHoursTable.WeekNo> 1 AND StaffHoursTable.Year>2001) AND (StaffHoursTable.WeekNo<10 AND StaffHoursTable.Year<2004) works fine and, you know, having got rid from VBA invoking in the query, the query will fly like a rocket.
  
  3.2. Plz clarify the logic of (Date) to [WeekNo, Year] conversion. From you code I can guess that “year” starts at #5/4/yyyy#, but week doesn’t start at this date each year. How it is treated this case? So I’d like you to specify the logic very-very clearly. Don’t worry about technical details, it is on my responsibility.
  
• 4. What concerning the query itself.
  
  4.1 Criteria “WHERE ((StaffHoursTable.EngName) In (SELECT [EngStaff]![Eng_Staff] FROM EngStaff))” does the same as Join (StaffHoursTable INNER JOIN EngStaff ON StaffHoursTable.EngName = EngStaff.Eng_Staff). There is no reason to implement both in the same query. INNER JOIN supposed to perform better than IN, so I recommend to get rid of the latter.
  
  4.2 Field EngStaff.Eng_Staff is not supposed to be unique. So far it doesn't affect query output, but you should think to set StaffHoursTable.EngName to be FK of EngStaff.ID.

Sorry for takin so long to reply................. really starting to hate this DB.
ok problem not solved.....merely worked around.
I decided to give it a try just doing the date limits using Week and Year, for some reason it really didn't like getting values from the input form, it would either interpret them as a diferent value or just wouldn't recognise them at all.

I was able to make it give the error again just trying to get it to convert a date to Week and Year, accidentally put a letter in with the numbers. It only stopped giving me the error when I exited and reloaded the form.

So I am now going back to using VB to get the results of the query (without the date criteria), apply the criteria in the code and send results to Excel, this is normally the option I use if I think the query is to complex to do in a single step.... and yes I came to this from an Excel background. anyway I now get the results I want and the managers wanted to copy the data to Excel anyway so everyone is relatively happy.

to answer some of your questions -

#2 - yup an oversite on my part, hadn't got round to tidying up the query at that point.

#3.2 - thats what I thought, even thought it would be wrong for the financial year. I was wrong, HMRC say 5 April is ALWAYS the end of the tax year and 6th is always the start of the new one.

#4.2 - EngStaff.Eng_Staff is actually unique, I wrote the form where the names get added, simple list boxes with double click to add and remove enties.

Thanks for the help FishVal, there is definitely something wierd with this database, hopefully at some point I'll be allowed to re-write it.

[FishVal]

Sorry for takin so long to reply................. really starting to hate this DB.
ok problem not solved.....merely worked around.
I decided to give it a try just doing the date limits using Week and Year, for some reason it really didn't like getting values from the input form, it would either interpret them as a diferent value or just wouldn't recognise them at all.

I was able to make it give the error again just trying to get it to convert a date to Week and Year, accidentally put a letter in with the numbers. It only stopped giving me the error when I exited and reloaded the form.

So I am now going back to using VB to get the results of the query (without the date criteria), apply the criteria in the code and send results to Excel, this is normally the option I use if I think the query is to complex to do in a single step.... and yes I came to this from an Excel background. anyway I now get the results I want and the managers wanted to copy the data to Excel anyway so everyone is relatively happy.

to answer some of your questions -

#2 - yup an oversite on my part, hadn't got round to tidying up the query at that point.

#3.2 - thats what I thought, even thought it would be wrong for the financial year. I was wrong, HMRC say 5 April is ALWAYS the end of the tax year and 6th is always the start of the new one.

#4.2 - EngStaff.Eng_Staff is actually unique, I wrote the form where the names get added, simple list boxes with double click to add and remove enties.

Thanks for the help FishVal, there is definitely something wierd with this database, hopefully at some point I'll be allowed to re-write it.

Hi, David.

The solution so far looks like.


Queries:

qryBeforeDateFiltering

SELECT StaffHoursTable.EngName, StaffHoursTable.JobNumberShadow, [MonNorm]+[TueNorm]+[WedNorm]+[ThurNorm]+[FriNorm]+[SatNorm]+[SuNorm] AS Norm_Hours, [MonOT]+[TueOT]+[WedOT]+[ThurOT]+[FriOT]+[SatOT]+[SunOT] AS OT_Hours, Jobs.ProjectType AS Expr1, Jobs.ProjectDescription, StaffHoursTable.WeekNo, StaffHoursTable.Year
FROM (StaffHoursTable INNER JOIN Jobs ON StaffHoursTable.JobNumberShadow = Jobs.JobNumber) INNER JOIN EngStaff ON StaffHoursTable.EngName = EngStaff.Eng_Staff
WHERE (((StaffHoursTable.JobNumberShadow) Not In (SELECT [ExcludeJobs]![Exc_Job] FROM ExcludeJobs)) AND ((Jobs.ProjectType) Not Like "Engineering"))
ORDER BY StaffHoursTable.EngName;
 

qryOutput

SELECT qryBeforeDateFiltering.*
FROM qryBeforeDateFiltering
WHERE ((([WeekNo]+[Year]*1000)>=[Forms]![frmDatesWBAllStaffRpt]![txtStartWeek]+[Forms]![frmDatesWBAllStaffRpt]![txtStartYear]*1000 And ([WeekNo]+[Year]*1000)<=[Forms]![frmDatesWBAllStaffRpt]![txtEndWeek]+[Forms]![frmDatesWBAllStaffRpt]![txtEndYear]*1000));
 

Forms:

frmDatesWBAllStaffRpt

Private Sub btnRunQuery_Click()
    DoCmd.OpenQuery "qryOutput"
End Sub
 
Private Sub TxtDtEnd_AfterUpdate()
 
    With Me
        If IsNull(.TxtDtEnd) Then
            .txtEndYear = Null
            .txtEndWeek = Null
            .btnRunQuery.Enabled = False
        Else
            .txtEndYear = GetFinYear(.TxtDtEnd)
            .txtEndWeek = GetFinWeek(.TxtDtEnd)
            If Not IsNull(.TxtDtStrt) Then .btnRunQuery.Enabled = True
        End If
    End With
 
End Sub
 
Private Sub TxtDtStrt_AfterUpdate()
 
    With Me
        If IsNull(.TxtDtStrt) Then
            .txtStartYear = Null
            .txtStartWeek = Null
            .btnRunQuery.Enabled = False
        Else
            .txtStartYear = GetFinYear(.TxtDtStrt)
            .txtStartWeek = GetFinWeek(.TxtDtStrt)
            If Not IsNull(.TxtDtEnd) Then .btnRunQuery.Enabled = True
        End If
 
    End With
 
End Sub
 
Private Function GetFinYear(ByVal dteDate As Date) As Long
 
    dteDate = DateAdd("m", -3, dteDate)
    dteDate = DateAdd("d", -5, dteDate)
    GetFinYear = Year(dteDate)
 
End Function
 
Private Function GetFinWeek(ByVal dteDate As Variant) As Long
 
    Dim dteStart As Date
 
    dteStart = DateAdd("d", -Weekday(DateSerial(GetFinYear(dteDate), 4, 5)) + 1, _
        DateSerial(GetFinYear(dteDate), 4, 5))
    GetFinWeek = DateDiff("w", dteStart, dteDate, vbMonday) + 1
 
End Function
 
 

[DDonnachie]

Thanks FishVal,

just checkedout ur mail today, a working query with no errors, fantastic

I can honestly say, I would never have come up with this solution.

[FishVal]

Thanks FishVal,

just checkedout ur mail today, a working query with no errors, fantastic

I can honestly say, I would never have come up with this solution.

Welcome to TSDN, David.