Help losing the plot

[Dazza666]

Hi,

I've been working on loads of queries for the past few weeks and have finally lost the plot.

I can't get the simplest queries to work anymore, please help!

I'm using Query by form but even replacing the form field with a static date I can't get the below to work.

New Referrals 16-18: IIf([Startdate]>6/5/2009,1,0)

regardless of the persons startDate before or after the above date access places a '1' in all records. I can get this to work by replacing the date with the number '45000'... wtf? When i do this some records will have 1 and some will have 0 (which is effectively what i want). But when I use a static date it puts a one in every record...

Please help because this doesn't make any sense to me


cheers

 

[namliam]

A date is actually a number with a format on it. The number represents the number of days that have passed since 31-dec-1899 (I think)

If you want to enter dates, you have to put # around the date. Much like you have to use " around text.

IIf([Startdate]>#6/5/2009#,1,0)

Also make sure that this is June 5, the date is in AMERICAN format MM/DD/YYYY...
Instead if you want to be 100% sure use Dateserial:
IIf([Startdate]>Dateserial(2009,5,6),1,0)

Which will never break! Dateserial(year,month,day)

Good luck finding your brain, I know I have these times as well.... Your brain just seems to be broken and/or lost!

 

[Dazza666]

A date is actually a number with a format on it. The number represents the number of days that have passed since 31-dec-1899 (I think)

If you want to enter dates, you have to put # around the date. Much like you have to use " around text.

IIf([Startdate]>#6/5/2009#,1,0)

Also make sure that this is June 5, the date is in AMERICAN format MM/DD/YYYY...
Instead if you want to be 100% sure use Dateserial:
IIf([Startdate]>Dateserial(2009,5,6),1,0)

Which will never break! Dateserial(year,month,day)

Good luck finding your brain, I know I have these times as well.... Your brain just seems to be broken and/or lost!

lol great

It's always something silly I find.

thanks for saving the day!!!