Formula In A Query

[richard]

Hi

I have a query in which I am trying to create a formula to check if the date
in a record is in the same month as the current date, if this is true to
return the months name, if not to return another value. The formula does not
fall over but only returns the OR value. The OR value is meaningless at the
moment as I am just seeing if the formula is actually doing something
Could someone have a look at this

MonthNo: IIf([Date SubmittedtoLab]=Month(Date()),MonthName(Month([Date
SubmittedtoLab])),5)

Thanks

Richard

 

[Tom Wickerath]

Hi Richard,

You can use the Immediate Window (open with <Ctrl><G>) to test portions of
your expression. For example:

?Date() is returning: 01/31/2008 today.
?Month(Date()) returns 1

So, your test is essentially:

IIf([Date SubmittedtoLab]=1

which means that the Date Submitted would have to be December 31, 1899 in
order to be true. You can see this by using the Type Conversion function,
CDate:

?CDate(1) returns: 12/31/1899

Does this work for you?

MonthNo: IIf(Month([Date
SubmittedtoLab])=Month(Date()),MonthName(Month([Date SubmittedtoLab])),5)



Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[BruceM]

If DateSubmittedToLab is a date field, it will not equal the month from the
current date. Try:
MonthNo: IIf(Month([Date SubmittedtoLab])=Month(Date()), _
MonthName(Month([DateSubmittedtoLab])),5)
Note that the underscore is an attempt to make the expression readable in a
newsreader. Remove it and the carriage return for your query.

 

[Wayne-I-M]

I did an other an swer to your other post - -

But also to follow on from Bruce's comment I would not use just the month
number (unless you only have one year's record in the table),

Use
Year(Date()),Month(Date())
To check the year as well.