MS Access Query criteria Problem

[Niroshana]

I have upsized my Access 2007 database to SQL server 2005 database (Front end
Access Database and Back end SQL server Database). Following MS Access query
does not run after upsized the database. Therefore, please help me to solve
the problem.
SELECT *
FROM DONATIONS
WHERE (((DONATIONS.PAY_DATE)>=[forms]![report].[STR_DATE] And
(DONATIONS.PAY_DATE)<=[forms]![report].[END_DATE]));

 

[Duane Hookom]

If this is a query in Access, Pay_Date is a date field, and the form is open
with actual dates entered, it should work.

When you state "does not run", what do you mean? What happens? Do you get an
error message?

What happens if you replace the form control references with actual date
values?

What is the format of dates in your region?

 

[vanderghast]

MS SQL Server does not solve for you the parameter having the syntax
FORMS!formName!ControlName.

If you use a mdb file with linked tables to MS SQL Server, in the front end,
then Jet should be able to resolve these parameters before getting the
linked tables.


So, I imagine that you are using an adp file, then you have to either use a
stored procedure and pass the arguments, or, maybe similar, an SQL function
returning tables, or even an ad hoc query as a string representing the SQL
statement, where the arguments will be embedded in the string, maybe
something like:

"SELECT * FROM donations WHERE pay_date >= CONVERT(datetime, '" &
FORMS!report!str_date & "', 112) AND pay_date <= CONVERT(datetime, '" &
FORMS!report!end_date & "', 112) "


Note that I used the MS SQL Server function CONVERT to cast the string as a
date, assuming a format 112, which stands for an ISO format, 4 digits for
the year; it may not be right for your case; see MS SQL Server doc.

Note that ALL CAPS words as key words to type as they are; datetime is also
a keyword, here.


The right choice (stored proc, or string of an SQL statement) depends on
your actual context, WHERE it has to be used.



Vanderghast, Access MVP