469,593 Members | 1,984 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,593 developers. It's quick & easy.

Access msg Too complex

Hi folks,

The following SQL statment works
Expand|Select|Wrap|Line Numbers
  1. 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]) 
  2. FROM (StaffHoursTable INNER JOIN Jobs ON StaffHoursTable.JobNumberShadow = Jobs.JobNumber) INNER JOIN EngStaff ON StaffHoursTable.EngName = EngStaff.Eng_Staff 
  3. WHERE StaffHoursTable.EngName In (SELECT [EngStaff]![Eng_Staff] FROM EngStaff) AND 
  4. StaffHoursTable.JobNumberShadow Not In (SELECT [ExcludeJobs]![Exc_Job] FROM ExcludeJobs) AND 
  5. Jobs.ProjectType Not Like "Engineering" 
  6. ORDER BY StaffHoursTable.EngName; 
  7.  
But if I add in the condition

Expand|Select|Wrap|Line Numbers
  1.  wktodate([StaffHoursTable].[WeekNo],[StaffHoursTable].[Year]) Between Cdbl([Forms]![frmDatesWBAllStaffRpt]![TxtDtStrt]) And Cdbl([Forms]![frmDatesWBAllStaffRpt]![TxtDtEnd]) 
  2.  
-------------
the function WkToDate is
Expand|Select|Wrap|Line Numbers
  1. Function WkToDate(Wk As Integer, Yr As Integer) As Double
  2. 'Function WkToDate(Wk As Integer, Yr As Integer) As Date 
  3. Dim Res As Date 
  4. If Wk * 7 < 270 Then 
  5. Res = Format("5/4/" & Yr, "dd/MM/yyyy") 
  6. Res = Res + (Wk * 7) 
  7. Else 
  8. Res = Format("5/4/" & Yr - 1, "dd/MM/yyyy") 
  9. Res = Res + (Wk * 7) 
  10. End If 
  11. Debug.Print Res 
  12. WkToDate = Res 
  13. End Function 
  14.  
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
Aug 15 '07 #1
13 2003
FishVal
2,653 Expert 2GB
Hi folks,

The following SQL statment works
Expand|Select|Wrap|Line Numbers
  1. 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]) 
  2. FROM (StaffHoursTable INNER JOIN Jobs ON StaffHoursTable.JobNumberShadow = Jobs.JobNumber) INNER JOIN EngStaff ON StaffHoursTable.EngName = EngStaff.Eng_Staff 
  3. WHERE StaffHoursTable.EngName In (SELECT [EngStaff]![Eng_Staff] FROM EngStaff) AND 
  4. StaffHoursTable.JobNumberShadow Not In (SELECT [ExcludeJobs]![Exc_Job] FROM ExcludeJobs) AND 
  5. Jobs.ProjectType Not Like "Engineering" 
  6. ORDER BY StaffHoursTable.EngName; 
  7.  
But if I add in the condition

Expand|Select|Wrap|Line Numbers
  1.  wktodate([StaffHoursTable].[WeekNo],[StaffHoursTable].[Year]) Between Cdbl([Forms]![frmDatesWBAllStaffRpt]![TxtDtStrt]) And Cdbl([Forms]![frmDatesWBAllStaffRpt]![TxtDtEnd]) 
  2.  
-------------
the function WkToDate is
Expand|Select|Wrap|Line Numbers
  1. Function WkToDate(Wk As Integer, Yr As Integer) As Double
  2. 'Function WkToDate(Wk As Integer, Yr As Integer) As Date 
  3. Dim Res As Date 
  4. If Wk * 7 < 270 Then 
  5. Res = Format("5/4/" & Yr, "dd/MM/yyyy") 
  6. Res = Res + (Wk * 7) 
  7. Else 
  8. Res = Format("5/4/" & Yr - 1, "dd/MM/yyyy") 
  9. Res = Res + (Wk * 7) 
  10. End If 
  11. Debug.Print Res 
  12. WkToDate = Res 
  13. End Function 
  14.  
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.

Expand|Select|Wrap|Line Numbers
  1.  WkToDate([StaffHoursTable].[WeekNo],[StaffHoursTable].[Year]) Between CDate([Forms]![frmDatesWBAllStaffRpt]![TxtDtStrt]) And CDate([Forms]![frmDatesWBAllStaffRpt]![TxtDtEnd]) 
Expand|Select|Wrap|Line Numbers
  1. Function WkToDate(ByVal Wk As Integer, ByVal Yr As Integer) As Date
  2.  
  3.     If Wk * 7 >= 270 Then Yr = Yr - 1
  4.     WkToDate = DateAdd("w", Wk, DateSerial(Yr, 4, 5))
  5.  
  6. End Function
  7.  
Aug 15 '07 #2
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.....
Aug 15 '07 #3
FishVal
2,653 Expert 2GB
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.
Aug 15 '07 #4
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

Expand|Select|Wrap|Line Numbers
  1. SELECT wktodate(StaffHoursTable.WeekNo,StaffHoursTable.Year) AS WeekDate
  2. FROM StaffHoursTable
  3. WHERE (((wktodate([StaffHoursTable].[WeekNo],[StaffHoursTable].[Year])) Between CDate([Forms]![frmDatesWBAllStaffRpt]![TxtDtStrt]) And CDate([Forms]![frmDatesWBAllStaffRpt]![TxtDtEnd])));
  4.  
and now get this message
'Data Type Mismatch in criteria expression'
Aug 15 '07 #5
FishVal
2,653 Expert 2GB
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

Expand|Select|Wrap|Line Numbers
  1. SELECT wktodate(StaffHoursTable.WeekNo,StaffHoursTable.Year) AS WeekDate
  2. FROM StaffHoursTable
  3. WHERE (((wktodate([StaffHoursTable].[WeekNo],[StaffHoursTable].[Year])) Between CDate([Forms]![frmDatesWBAllStaffRpt]![TxtDtStrt]) And CDate([Forms]![frmDatesWBAllStaffRpt]![TxtDtEnd])));
  4.  
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT wktodate(StaffHoursTable.WeekNo,StaffHoursTable.Year) AS WeekDate, CDate([Forms]![frmDatesWBAllStaffRpt]![TxtDtStrt]) AS StartDate, CDate([Forms]![frmDatesWBAllStaffRpt]![TxtDtEnd]) AS EndDate
  2. FROM StaffHoursTable;
additionally you can run the following query to see whether the types of the values being compared are adequate
Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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.
Aug 15 '07 #6
I mean the following. Retrive criteria fields w/o including them in query criteria. Just to see what you are comparing.
Expand|Select|Wrap|Line Numbers
  1. SELECT wktodate(StaffHoursTable.WeekNo,StaffHoursTable.Year) AS WeekDate, CDate([Forms]![frmDatesWBAllStaffRpt]![TxtDtStrt]) AS StartDate, CDate([Forms]![frmDatesWBAllStaffRpt]![TxtDtEnd]) AS EndDate
  2. FROM StaffHoursTable;
additionally you can run the following query to see whether the types of the values being compared are adequate
Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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.
Aug 16 '07 #7
FishVal
2,653 Expert 2GB
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.
Aug 16 '07 #8
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.
Aug 16 '07 #9
FishVal
2,653 Expert 2GB
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.
Aug 17 '07 #10
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.
Aug 20 '07 #11
FishVal
2,653 Expert 2GB
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
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM (StaffHoursTable INNER JOIN Jobs ON StaffHoursTable.JobNumberShadow = Jobs.JobNumber) INNER JOIN EngStaff ON StaffHoursTable.EngName = EngStaff.Eng_Staff
  3. WHERE (((StaffHoursTable.JobNumberShadow) Not In (SELECT [ExcludeJobs]![Exc_Job] FROM ExcludeJobs)) AND ((Jobs.ProjectType) Not Like "Engineering"))
  4. ORDER BY StaffHoursTable.EngName;
  5.  
qryOutput
Expand|Select|Wrap|Line Numbers
  1. SELECT qryBeforeDateFiltering.*
  2. FROM qryBeforeDateFiltering
  3. WHERE ((([WeekNo]+[Year]*1000)>=[Forms]![frmDatesWBAllStaffRpt]![txtStartWeek]+[Forms]![frmDatesWBAllStaffRpt]![txtStartYear]*1000 And ([WeekNo]+[Year]*1000)<=[Forms]![frmDatesWBAllStaffRpt]![txtEndWeek]+[Forms]![frmDatesWBAllStaffRpt]![txtEndYear]*1000));
  4.  
Forms:

frmDatesWBAllStaffRpt
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnRunQuery_Click()
  2.     DoCmd.OpenQuery "qryOutput"
  3. End Sub
  4.  
  5. Private Sub TxtDtEnd_AfterUpdate()
  6.  
  7.     With Me
  8.         If IsNull(.TxtDtEnd) Then
  9.             .txtEndYear = Null
  10.             .txtEndWeek = Null
  11.             .btnRunQuery.Enabled = False
  12.         Else
  13.             .txtEndYear = GetFinYear(.TxtDtEnd)
  14.             .txtEndWeek = GetFinWeek(.TxtDtEnd)
  15.             If Not IsNull(.TxtDtStrt) Then .btnRunQuery.Enabled = True
  16.         End If
  17.     End With
  18.  
  19. End Sub
  20.  
  21. Private Sub TxtDtStrt_AfterUpdate()
  22.  
  23.     With Me
  24.         If IsNull(.TxtDtStrt) Then
  25.             .txtStartYear = Null
  26.             .txtStartWeek = Null
  27.             .btnRunQuery.Enabled = False
  28.         Else
  29.             .txtStartYear = GetFinYear(.TxtDtStrt)
  30.             .txtStartWeek = GetFinWeek(.TxtDtStrt)
  31.             If Not IsNull(.TxtDtEnd) Then .btnRunQuery.Enabled = True
  32.         End If
  33.  
  34.     End With
  35.  
  36. End Sub
  37.  
  38. Private Function GetFinYear(ByVal dteDate As Date) As Long
  39.  
  40.     dteDate = DateAdd("m", -3, dteDate)
  41.     dteDate = DateAdd("d", -5, dteDate)
  42.     GetFinYear = Year(dteDate)
  43.  
  44. End Function
  45.  
  46. Private Function GetFinWeek(ByVal dteDate As Variant) As Long
  47.  
  48.     Dim dteStart As Date
  49.  
  50.     dteStart = DateAdd("d", -Weekday(DateSerial(GetFinYear(dteDate), 4, 5)) + 1, _
  51.         DateSerial(GetFinYear(dteDate), 4, 5))
  52.     GetFinWeek = DateDiff("w", dteStart, dteDate, vbMonday) + 1
  53.  
  54. End Function
  55.  
  56.  
Aug 22 '07 #12
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.
Aug 24 '07 #13
FishVal
2,653 Expert 2GB
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.
Aug 24 '07 #14

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

49 posts views Thread by Yannick Turgeon | last post: by
55 posts views Thread by AnandaSim | last post: by
25 posts views Thread by Nudge | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.