Finding nearest Date :S:S

[Pharcyde]

Students in my DB have certain dates where they can be accredited with passing their exams.

Once all 3 are passed (which is sorted - ta mailman) they are accredited with it on a certain date(these dates are held in a separate table).

I would like to calculate the nearest next accreditation date to the date the last exam was passed, and when that date arrives, asign a flag(or a true/false value) to show that they have been accredited...

Any ideas boys and girls???

Ta, Lee

 

[charityg]

Could you post the structures of both tables?

Where do you store the StudentID and the Date they are actually accredited?

 

[namliam]

Yah cool, whats the logic behind the accred date?

 

[Pharcyde]

Heres the db. THe logic behind the accDate?? Well, its just a table of dates really. The next available accDate needs to be found, and as soon as current date reaches that (if all exams are passed) then the db will accredit them automatically. ( I know hoe to figure that one out) so this is the missing piece of the jigsaw i guess....


is that ok? Cheers, Lee

 

[Mile-O]

You should really start using naming conventions in your database; you have tables, queries, forms, and reports all with the same name.

 

[Pharcyde]

yeah i know. Am not the best at design, naming and organisation stuff Mile! I kinda just muddle my way thru it all. Any suggestions on the naming then, or maybe a link would be easier for u?

 

[Mile-O]

There's a number of conventions, these - for example - are what I use:

For a table tblName
For a query qryName
Form: frm
Report: rpt
Module: bas
Class Module: C

Integer: Int
Single: sng
Double: dbl
Long Integer: lng
Boolean: boo
Currency: cur
String: str
Date: dte

Textbox: txt
Listbox: lst
Combobox: cbo
Checkbox: chk
Toggle Button: tgl
ActiveX Control: ctl
Label: lbl
Command Button: cmd

 

[Pharcyde]

Oh yeah, this is what I'm using to try and get the next date. It looks logical enough, and does return a date, but it isnt the correct one, as there is a closer date then the one returned.

NearestAccDate: Min(IIf([Date]>=[Ass-Assessments]![ResultDate],[Date],"False"))

where Result date is gruped by Max...


This is Driving me Mad!!!!!

 

[Mile-O]

Use a query to sort your field so that the dates are in order.

Now, this is why you need naming conventions. Your line of code doesn't know whether to look up the table or the query of that name.

Date is a reserved word and shouldn't be used as a field name.