If A Date Is Greater Than Another Then... 1

[dominicdunmow]

I'm pretty new to Access and I'm trying what I thought was a relatively simple IIF statement...but it won't work!!

I'm trying to make the return in a field dependant on whether a date is less than the date in another field. The line I have used is:-

IIf([AmendTermStart]<=#01/10/2003#,([Cost (US)]/[TermLength]),"x")

Access doesn't seem to be able to compare the date in [AmendTermStart] with 01/10/2003 despite being the same format. So even for dates greater than 01/10/2003 it still returns the

([Cost (US)]/[TermLength])

Calculation.

Help!!

 

[KenReay]

hi

are you in uk (or at least using none usa format dates?

is this in an sql string?

Access / Jet expects dates in SQL strings to be in a limited number of formats try

IIf([AmendTermStart]<=#2003/10/01#,([Cost (US)]/[TermLength]),"x")


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[dominicdunmow]

Thanks very much, it works!!!

I didn't consider changing the date format as the date I am comparing to [AmendTermStart] is in the Eur format of dd/mm/yyyy.

Guess it's something I need to get used to.

Many thanks

Dominic

 

[KenReay]

Hi

Yes afraid so, I take it you are in the UK?

You can use either american style mm/dd/yyyy or the yyyy/mm/dd I suggested

Remember the date is always STORED in the same way, the format is just the visual presentation of it

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[dominicdunmow]

...Yes I am in the UK.

Rather interestingly, having run the query for a couple of hours now (using the yyyy/mm/dd format), Access of its own accord has changed the date format in SQL to the US version as you mention above mm/dd/yyyy.

It still works, but befuddled me for a moment as I could have sworn that wasn't the format that I'd entered.

At least it still works.

Dominic