Formula In A Query

[richard]

Hi
I am trying to create a formula that checks to see if a date in a record is
from the same month as the current date and if so return a value
The formula is not falling over but always returns the OR value.
Could someone take a look and point out my error

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



Thanks

Richard

 

[Wayne-I-M]

Not sure what you're trying to do with this. I you just want the month
number you could simply use
DatePart("m",[Date SubmittedtoLab])

This is (I think??) what your 1st post was looking for - but I don't
understand

MonthNo: IIf([TableName]![Date SubmittedtoLab] Between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0),Format(Date(),"mmmm"))

To get the month number you could use this - but the simple DatePart is better

OtherExample: IIf([TableName]![Date SubmittedtoLab] Between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0),DatePart("m",Date()),DatePart("m",[Date SubmittedtoLab]))


You could change the TableName and just paste this to see what I mean

SELECT IIf([TableName]![Date SubmittedtoLab] Between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0),Format(Date(),"mmmm")) AS MonthNo,
IIf([TableName]![Date SubmittedtoLab] Between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0),DatePart("m",Date()),DatePart("m",[Date SubmittedtoLab])) AS OtherExample
FROM TableName;


Post back if this is not what you are after.