Date manipulation

[Guest]

This works in a query design view column:

WeekOf:
IIf((Weekday([Date])=1),([Date]-6),(DateAdd("d",-(Weekday([DATE])-2),[DATE])))


It looks at the date field and assigns it to the 'WeekOf' which is always
the preceeding Monday.

But in the next query I apply a Criteria to the WeekOf field to range it
between date start and date end fields that exist in a form. The form format
is definitely date. But I get a data mismatch error when I attempt to apply
the criteria.

It leads me to believe that my manipulation of the date with the Iif has
changed its status to not being recognized as a date.

Would welcome advice.

 

[Guest]

I bet that you are right. In that case, wrap the CDate function around it
like so. Watch out for word wrapping:

WeekOf: CDate(IIf((Weekday([Date])=1),
([Date]-6),(DateAdd("d",-(Weekday([DATE])-2),[DATE]))))

There's also a slight possibility that the DATE field is getting mixed up
with the Date() function. Shouldn't be as you have [ ] around it. But in the
future try to avoid using reserved words in table or field names. Here's more
on that subject:
http://support.microsoft.com/kb/286335/

 

[Guest]

Is [DATE] a field in your table or are you trying to use the Date() function?
If it is a field, you really need to change its name. Date is an Access
reserved word and can cause Access confusion. I know the rule says if you
enclose it in brackets it will resolve the problem; however, I once ran
accross a situation where even with the brackets it was returning incorrect
values. In this case it was
IIf([Date] < Date, "foo", "bah")

Because I was not allowed to change the field name, the only way I could get
it to work was:
IIf([Date] < VBA.Date, "foo", "bah")

 

[Guest]

thanks to both. wrapping in CDate in particular....

As to the word DATE being reserved; I understand and agree. These dbs get
started by lay people and it really isn't feasible to expect them to know
that such a commonly used business term can not be used.

MS really needs change their reservation to acDate or something else less
probable...reserving the word Date is almost inviting end user
problems...particularly if the [ ] brackets is not going to shield this issue

but much thanks....
--
NTC

Is [DATE] a field in your table or are you trying to use the Date() function?
If it is a field, you really need to change its name. Date is an Access
reserved word and can cause Access confusion. I know the rule says if you
enclose it in brackets it will resolve the problem; however, I once ran
accross a situation where even with the brackets it was returning incorrect
values. In this case it was
IIf([Date] < Date, "foo", "bah")

Because I was not allowed to change the field name, the only way I could get
it to work was:
IIf([Date] < VBA.Date, "foo", "bah")
--
Dave Hargis, Microsoft Access MVP

This works in a query design view column:

WeekOf:
IIf((Weekday([Date])=1),([Date]-6),(DateAdd("d",-(Weekday([DATE])-2),[DATE])))


It looks at the date field and assigns it to the 'WeekOf' which is always
the preceeding Monday.

But in the next query I apply a Criteria to the WeekOf field to range it
between date start and date end fields that exist in a form. The form format
is definitely date. But I get a data mismatch error when I attempt to apply
the criteria.

It leads me to believe that my manipulation of the date with the Iif has
changed its status to not being recognized as a date.

Would welcome advice.