criteria between two dates

[margaret]

I have a form .. frmForm that has several fields ... id; bkdate; wkdate. I
have a query and I want to limit the data between 9/26/2008 and the date that
is in the field [wkdate]. As follows:

Between #9/26/2008# And [forms]![frmdate]![wkdate]

so my query looks like
WHERE ((([TBL Tickets].CurrentDate) Between #9/26/2008# And
[forms]![frmdate]![wkdate]));

For some strange reason, after working like a champ for years, it's not
working. I keep getting messages that it doesn't recorgnize [forms] ... if I
take out [forms] it tells me it doesn't recognize [frmdate]. I've even tried
using the table that the form is built upon ...

tables!tblform!wkdate

No cigar.

However, if I change the "w" to a "b" for bkdate, everthing works. But, of
course, the information is wrong because it's selecting the wrong date.

Any idea?

 

[Lord Kelvan]

are you sure wkdate is the name of a textbox or something on your form
double check you could also try defining value
ie
Between #9/26/2008# And [forms]![frmdate]![wkdate].[value]

Regards
Kelvan

 

[margaret]

I'm pretty sure I have the right field name (since it's in a query there is
no text box). I've used the build button and pulled the field name from
there.

There value didn't work I got the same message ...

Microsoft does not recognize [forms]![frmdate]![wkdate].[value as a valid
field ]name or expression.

 

[OssieMac]

Hi Margaret,

If it has been working previously then suggests some type of corruption. If
Kelvan's suggestion doesn't work then I would try the following.

Compact and repair.
Delete the problem form field and recreate.

If above doesn't work then I have fixed a problem by the following method.
Close the database and rename it to old or something.
Open a new database and name it as per your original name.
Import all into the new database. When importing you can select all in all
the tabs and import it all in one go. Don't forget to go into Options and
check all three boxes under Import box. (Tables and Queries defaults should
be OK. ) doesn't matter if nothing to import under all three Import options;
you simply get a message accordingly.)
From memory if you have any VBA code then you might have to open the VBA
editor and go into Tools -> References if you had any additional references
in the original database.

I will be interested just for future reference in how you fix the problem
whether it be one of the above or not.

 

[John Spencer]

Check the NAME of the control on the form. That is what you are referring to
when you say Forms!frmDate!wkDate. Is it possible that you renamed the
control? As noted elsewhere, it is possible that the form itself has been
corrupted and as noted elsewhere, you may need to rebuild the form (or even
the database).

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County