Query / Form Relationsship

[BobRich99]

I recently updated from Access 2000 to Access 2007

In the past I have been able to make a form with start date and end date
text boxes and then put a statement like :

Between [forms]![FormName]![textboxname] and [forms]![FormName]![textboxname]

in the criteria box in the query, and this would work.

In fact, if I run an database that I built using Access 2000 it still works
fine.

Additionally, I could put a combo box on the form, to select a job ( or
product or customer) , with an asterisk as the default character so they can
select ALL jobs, or select a specific job for the report.

New databases that I build now are not working properly using this method.

For the most part I am still using ACCESS 2000 or ACCESS 2003 formats,
because that what my client are still using.

Any suggestions that anyone could give me would be most helpful.

 

[vanderghast]

The date data type seems to fail to be automatically asserted right. Try

Between CDate(forms![FormName]![textboxname]) and CDate(
forms![FormName]![textboxname] )


or explicitly define the two parameters data type in the query.


Vanderghast, Access MVP

 

[BobRich99]

The date data type seems to fail to be automatically asserted right. Try

Between CDate(forms![FormName]![textboxname]) and CDate(
forms![FormName]![textboxname] )


or explicitly define the two parameters data type in the query.


Vanderghast, Access MVP

I recently updated from Access 2000 to Access 2007

In the past I have been able to make a form with start date and end date
text boxes and then put a statement like :

Between [forms]![FormName]![textboxname] and
[forms]![FormName]![textboxname]

in the criteria box in the query, and this would work.

In fact, if I run an database that I built using Access 2000 it still
works
fine.

Additionally, I could put a combo box on the form, to select a job ( or
product or customer) , with an asterisk as the default character so they
can
select ALL jobs, or select a specific job for the report.

New databases that I build now are not working properly using this method.

For the most part I am still using ACCESS 2000 or ACCESS 2003 formats,
because that what my client are still using.

Any suggestions that anyone could give me would be most helpful.

Thanks for your answer. When I try what you suggested, I get an error that the expression is to complex.

Im really having trouble understanding why this method works fine on older
database builds but not on new database builds. This particular database
build is ACCESS 2000, because that is what the client is using.

 

[vanderghast]

There have been an increase of security between these two versions and some
syntax are not valid anymore, although I suspect this is not necessary the
case, here, there may have been lot of other modifications too (after all,
that is what service-updates are for).

You said you get an error about an expression being too complex, which
occurs in case where parenthesis are not matched, in syntax. Can you post
the whole SQL statement?




Vanderghast, Access MVP

 

[BobRich99]

There have been an increase of security between these two versions and some
syntax are not valid anymore, although I suspect this is not necessary the
case, here, there may have been lot of other modifications too (after all,
that is what service-updates are for).

You said you get an error about an expression being too complex, which
occurs in case where parenthesis are not matched, in syntax. Can you post
the whole SQL statement?




Vanderghast, Access MVP

Ok,

I have been testing the problem I am having and it seems that the date range
criteria if its the only criteria, works fine. It works unmodified as in my
initial post AND it works using the suggestion you provided.

The problem is when I add the 2nd criteria.

I have a combo box on my form, indexed to a job table. The default value of
the combo box is set to an "*".

In the past my users could leave the asterisk after selecting a date range,
to get a report of all jobs for that date range ( or customers, or products,
or whatever... ) or they could select a specific item to base and print the
report on.

If I select a job, the query selects that job and works fine. The query has
records with that job and the correct date range.

If I leave the asterisk, I get an empty query, even though I expect to get a
query with the correct date range and ALL of the jobs.

It would seem the error I got before was caused by a typo I made.

 

[BobRich99]

Apparently this was a bug that was corrected in SP1.

Thank you for taking the time to answer my question