Calculation question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All,

I have a database which lists all our vessels and when they're due to be
inspected. I would like to be able to write a report which lists all the
vessels due for inspection within the next three months. Currently the
re-inspection date is entered.

I think I need to write this in a query but I'm not sure how to structure
the formula or even where it should go. I currently have the vessel name,
location, tag no. and inspection date displayed in the query. I would like
to write an expression which goes along the lines of:

if the inspection date month - this month is <= 3, display Y, else display N

and have the report then list all the vessels with Y in that column, and
their inspection dates and how many months it is until the new inspection is
due. I think I'll need a couple of columns to do that.

I hope I'm being clear, any help would be appreciated, I have forgotten a
lot of the things I learnt at a training course last year.

I am using Access 2002. Could someone also please provide the link to the
FAQ page or its equivalent?

Thanks,
Fiona
 
Hi:

Try this column in a query where YourDateField is your date field --

MonthDiff: IIf(Datediff("M",Month(Date()),[YourDateField])<=3,"Y","N")

Regards,

Naresh Nichani
Microsoft Access MVP
 
Thank you Naresh. I finally worked out how to utilise it! I must have
forgotten more than I thought...

Unfortunately, the column returns "N" even though some next inspections are
due in May (month 5) and we are in February (month 2). Could this be linked
to US dates (mm/dd/yy) as opposed to what I use in Australia (dd/mm/yy)?

Fiona

Naresh Nichani MVP said:
Hi:

Try this column in a query where YourDateField is your date field --

MonthDiff: IIf(Datediff("M",Month(Date()),[YourDateField])<=3,"Y","N")

Regards,

Naresh Nichani
Microsoft Access MVP

fiona said:
Hi All,

I have a database which lists all our vessels and when they're due to be
inspected. I would like to be able to write a report which lists all the
vessels due for inspection within the next three months. Currently the
re-inspection date is entered.

I think I need to write this in a query but I'm not sure how to structure
the formula or even where it should go. I currently have the vessel name,
location, tag no. and inspection date displayed in the query. I would like
to write an expression which goes along the lines of:

if the inspection date month - this month is <= 3, display Y, else display N

and have the report then list all the vessels with Y in that column, and
their inspection dates and how many months it is until the new inspection is
due. I think I'll need a couple of columns to do that.

I hope I'm being clear, any help would be appreciated, I have forgotten a
lot of the things I learnt at a training course last year.

I am using Access 2002. Could someone also please provide the link to the
FAQ page or its equivalent?

Thanks,
Fiona
Click to expand...
Click to expand...
 
Thankyou,

I figured out the answer after reading this post! It gave me an invaluable
push in the right direction. I got it to work by using the following code:

IIf(DateDiff("m",Date(),[NextInspectionDate])<=3,"Y","N")

and set the display criteria to "Y"

and in the next column I calculated the number of months and set the display
criteria to >= 0.

Somebody has kept all the inspection dates even if they're in the past. I
am udgrading someone else's creation - they are using access like a
spreadsheet! (Grr).

Thanks again for your help.

Fiona

Naresh Nichani MVP said:
Hi:

Try this column in a query where YourDateField is your date field --

MonthDiff: IIf(Datediff("M",Month(Date()),[YourDateField])<=3,"Y","N")

Regards,

Naresh Nichani
Microsoft Access MVP

fiona said:
Hi All,

I have a database which lists all our vessels and when they're due to be
inspected. I would like to be able to write a report which lists all the
vessels due for inspection within the next three months. Currently the
re-inspection date is entered.

I think I need to write this in a query but I'm not sure how to structure
the formula or even where it should go. I currently have the vessel name,
location, tag no. and inspection date displayed in the query. I would like
to write an expression which goes along the lines of:

if the inspection date month - this month is <= 3, display Y, else display N

and have the report then list all the vessels with Y in that column, and
their inspection dates and how many months it is until the new inspection is
due. I think I'll need a couple of columns to do that.

I hope I'm being clear, any help would be appreciated, I have forgotten a
lot of the things I learnt at a training course last year.

I am using Access 2002. Could someone also please provide the link to the
FAQ page or its equivalent?

Thanks,
Fiona
Click to expand...
Click to expand...
 
Back
Top