Confusing Dates Query

[fagash]

I am trying to set up a database for an accounts firm I am working for using Access XP. The database is being used to keep track of clients’ records and the progress of their accounts.

The database consists of a number of tables recording various pieces of information:

Clients Table:
- Client Code
- Client Name
- Trading Name
- Client Type (Lookup from another table [Sole Trader, Partnership etc]
- Year End Date (dd/mm)

Records In:
- Client Code
- Year End Date (dd/mm/yy)
- Date Received
- Various other non-relevant fields
- Date Returned
- Accounts Status [Records Received, Accounts in Progress, Problems, Review etc]

Clients send in there accounting records (invoices, bank statements etc) every year which are booked in by the user in the Booking In form. The accounts are reviewed and audited and the progress is recorded as and when the status changes. When the accounts are completed, the status is set to “Compeleted” and finally when the records are returned, the Date Returned field is filled in.

Everything is working fine so far and I am able to draw up all the relevant reports needed. However, I am having a bit of a problem writing a query that tells me when records may be due in.

Clients get a bit forgetful and often don’t send their records in until they have been reminded they are due. This always falls after their year end every year. I would like a query that says whether or not we need to ask them for their records for this year.

The problem I am encountering is that their year end is the same every year and so a dd/mm format is the only information needed in the Clients table. However, the fact that we audit their accounts every year means the Records In table is recording the year end in dd/mm/yy format.

The query needs to look at the client’s year end dd/mm and match it against the records received dd/mm/yy and today’s date to work out if we need to ask for their records.

i.e. Today’s date: 24/08/04
Client Year End: 05/04
Accounts Completed: 05/04/02 and 05/04/03

Need the query to flag that the records for 05/04/04 are due.

As one final point, if the query could be simplified by assuming that a client will always send in their records within one year of their year end and there will be no records required dating back to say y/e 05/04/02 then that is not a problem. In reality this will not be the case but human judgement can deal with that. If it is possible to check exact year end dates though then that will be great.

If anyone can aid with this problem then I would be most grateful. A copy of the database with sample data can be provided.

 

[Brianwarnock]

The Format of the date/time field only controls the display not what is stored, the year end fields data will contain a year , namely that of the year it was entered.
Brian

 

[fagash]

I did fear that much. Is there any way to remove the year part of the date in the Clients Database otherwise in a years time or so the data is all going to be messed up.

 

[Brianwarnock]

You cannot remove the year in a date field , you will need to carry this info in a text field if entered with a date separator eg 05/04 then this can be converted to a valid date value containing the current year using the function Datevalue eg above would be converted tp 05/04/2004 this year and 05/04/2005 next year.

I don't know how to convert your existing data to text.
Brian

 

[Brianwarnock]

Actually you could convert your existing data by using
Day(yearendfld)&"/"&Month(yearendfld)
Add a text field to your table and then run an Update query on the field with the above in the UpdateTo box
Brian

 

[fagash]

Hmm, I see. That could solve the problem.....

Enter date as text and use a query to convert it to date using current year.

Then my query would work as I could ask to match year end date with this converted date as long as the date falls within the last 365 days. That should make sense. Will have to work on that a bit later so if anyone has any other ideas for the moment then keep replying.

Cheers Brian

 

[Brianwarnock]

Hope you get it working

 

[fagash]

I'm nearly there with:

SELECT Clients.[Client Code], Clients.[Client Name], Clients.[Trading As Name], Clients.[Client Type], 
IIf((Now()>DateValue([Year End (dd/mm)])),DateValue([Year End (dd/mm)]),(DateValue([Year End (dd/mm)])-365)) AS [Year end],
[Records Received].[Year End Date]

Although I'm sure that is a hugely inefficient code! There could also be a potential problem at leap years so if anyone can tell me how to get round that? [EDIT: this is already causing problems so needs a fix to take away one year rather than 365 days]

However, it's not completely perfect as I need to build another expression in there and I can't get my head around it.

Each client exists in the client table as a single entry. From this they can have multiple year ends 05/04/02, 05/04/03, 05/04/04 which is entered in the "Records In" table. I need my query to look at the most recent "Year End" on "Records In" and see whether that falls within the last year/365 days. If it does then we do not require any records for the time being.

I'm sure that little bit is very, very easy but I can't seem to get it to work.

The relationship between the tables is currently:

[Clients] {1 - many} [Records In]

 

[Rich]

I did fear that much. Is there any way to remove the year part of the date in the Clients Database otherwise in a years time or so the data is all going to be messed up.

Why? you can use the DateDiff function to see if Accounts are due/overdue

 

[fagash]

I've got the query working mostly apart from what I said in my last post about how it can't check the current year end against the most recent records received, i.e. If I have two sets of records for a client with a year end 5th April, one for year end 05/04/03 and one for 05/04/04 and then delete the year end date in the Records In table for the 05/04/03 records, the query is returning that I need records for 05/04/04 but I don't, I need them for the previous year. Obviously this error only occurs when I have created a reference to the client in the Records In table where no year end date is entered but it's still an error that can occur.

Also, the "minus one year" rather than "minus 365 days" part of the code needs changing. This is more important though as I can just make the Records In table require Year End Date which will end the problem. I know this must be a very simple thing to change.

 

[fagash]

Me posting again

Ok, I've decided the best way around this problem is for the Booking In table (data entered from a form) to require the field for year end date. What I want it to do is simply automatically put in the year end for the records in (which can then be overwritten if needed) once a client code has been entered, thus creating a record.

I can use the

IIf((Now()>DateValue([Year End (dd/mm)])),DateValue([Year End (dd/mm)]),(DateValue([Year End (dd/mm)])-365)) AS [Year end],

bit of code to create the relevant year end but can't seem to work out how to get it to automtically enter in the field.

Can anyone help with this and and the "-1 year" rather than "-365 days" problem and then I will be out your hair! I appreciate all the help so far.

Cheers!

 

[Brianwarnock]

Back in today so lets tackle each question

1st replace (DateValue([Year End (dd/mm)])-365)) by DateAdd("yyyy",-1,(DateValue([Year End]))) to go to previous year.

I take it you then want to store this value in a table?? You will be aware that storing calculated values is against all the rules of database design but I'm not able to suggest how you should solve your problem so here goes, For the input form with Year End , by the way avoid spaces in field names it causes problems, bound to the table then in the before update event of the form code Me.[Year End]= IIf((Date()>DateValue([Year End (dd/mm)])),DateValue([Year End (dd/mmDateAdd("yyyy",-1,(DateValue([Year End]))))

I think that should do the trick.

What worries me is that there is something basically wrong with the approach to this which I am missing.

Brian

 

[fagash]

Thanks

I've got the query working, after a few adjustments as you've missed out a couple of little bits. No probs though as it's working. Just thought I'd mention it incase anyone else is looking at this thread or looks at it in future.

The final code for the query is:

IIf((Now()>DateValue([Year End (dd/mm)])),DateValue([Year End (dd/mm)]),DateAdd("yyyy",-1,(DateValue([Year End (dd/mm)])))) AS [Year End]

Can't seem to get that second bit to work though. I'm currently entering:

Me.[Year End]= IIf((Date()>DateValue([Year End (dd/mm)])),DateValue([Year End (dd/mm)],DateAdd("yyyy",-1,(DateValue([Year End]))))

but the field doesn't update when I go back into for view and add a new record and then when I go back into design view it has deleted the event field. I've tried it as both an expression and a code.

Would you like a copy of the database to have a look at where it is going wrong?

I realise that a lot is probably wrong as this is only the second database I have ever built, the first being an unfinished college project about 4 years ago! If I update the field names in the table will the queries automatically adjust themselves or do I have to go through and amend them all myself?

 

[Brianwarnock]

Yes compact the database then post it and I'll take a look, as a parttimer I'm off tomorrow so today would be good.

Brian

 

[fagash]

Attachment

Attached with a very limited amount of sample data.

Thanks.

 

[Brianwarnock]

Ok the 1st thing to do was to own up to the fact that my code was crap it should have been VBA not sql thus it needs an If Then Else staement .
I guess I'm getting too old to do 2 things at the same time
The code will be placed in the Forms before update not the year end control's.

I'm having a bit of trouble with the fact that Year End [dd/mm] is in a sub form and year end date is on the main.
As to your question regarding field names , yes I believe the names in the queries etc , infact everywhere but VBA code will be changed automatically if you change the name in the tables.

Brian

 

[fagash]

Well I'll change the field names at some time then to remove all the spaces and hopefuly that can stop any potential problems. Might also get rid of the (dd/mm) in the Year End (dd/mm) field name too as that is getting a bit confusing as well.

Oh yeah, I forgot to mention the subform I didn't realise that would be causing any problems though as it is just a simple lookup thing to make sure people have entered the right client code. Anyway, good luck! I don't do easy!

Are you going to sort it out and attach the amended database or was your post a hint to stop being lazy and do it myself?

 

[Brianwarnock]

Ok finally sussed it i think I will post back your database with code if you want but what you need is pasted below, I hope I've got the logic you require correct.


Private Sub Year_End_Date_GotFocus()
mydate = Date 'set to system date
yearend = Forms![booking in]![booking in lookup sub]![year end (dd/mm)]
If mydate < DateValue(yearend) Then
Me.Year_End_Date = DateValue(yearend)
Else: Me.Year_End_Date = DateAdd("yyyy", -1, DateValue(yearend))
End If
End Sub

You may also like to put the code in the before uodate event of the form incase a user does not tab through the year end date control.


Private Sub Form_BeforeUpdate(Cancel As Integer)
mydate = Date 'set to system date
yearend = Forms![booking in]![booking in lookup sub]![year end (dd/mm)]
If mydate < DateValue(yearend) Then
Me.Year_End_Date = DateValue(yearend)
Else: Me.Year_End_Date = DateAdd("yyyy", -1, DateValue(yearend))
End If

End Sub

Note that I have not included any error checking eg for null [year end (dd/mm)]

It really would be worthwhile reviewing your naming if you have time as you can see to save typo errors I said yearend= etc

Brian

 

[Brianwarnock]

Just read your response, ignored all emails whilst working
The only problem with the subform was that I had not worked with one in this sitaution before and the Me. type syntax did not seem to work thus I had to work out the method of getting at the info. It's all good clean fun
Brian

 

[fagash]

Cheers!

Might be better if you just post the amended database since you have it working. Saves me hassle!