Confusing Date Query

[fagash]

Firstly, sorry for the length of this post but I am trying to be as descriptive as possible.

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, Completed 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.

 

[SkipVought]

Hi,

Your Clients YearEndDate is NOT a date -- different form than RecordsIn.

Be advised that Date/Time values are JUST NUMBERS, like right now is 38223.32363. This can be FORMATTED any number of ways to look like a Date with or without Time or Time alone.

I believe that what you want is the Year End is either May 4 or April 5 of a specified year or the current year.

So if you were to enter the STRING "05/04" into [Year End], meaning month/day, then

ClientYearEndDate = DateSerial(Year(Date), Left([Year End],2), Right([Year End],2))

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[fagash]

I know about the numbers thing. Same in excel etc.

So does that code remove the year element entirely or just change the formatting to look like the yeaar has gone? I'm not very good with codes you see, more of a WYSIWYG person.