Why the difference between Access Query Configuration and Tableadapter Query Configuration W
I have found that I can run a query using Access query builder works fine but the same query does not work in the "TableAdapter Query Configuration Wizard". Why is this?
Example:
SELECT * from Rental_Agreement
WHERE DateSerial(year(end_date), month(end_date) , day(end_date) - 5) = Date()
This works in Acess 2003 but not in the "TableAdapter Query Configuration Wizard".
Are you getting an error? If so, what is it?
The primary difference is that there is functionality that is available when executing a query in Access that is not available when using the Jet database engine. A little more detail might help identify what it is.
I was able to successfully use DateSerial in a SQL statement from ADO.NET.
What was the error that was being generated by your app? Are you sure it wasn't related to a bad or null date (such as a data type mismatch)?
I have a value of 3.
Below is doumentation for the problem.......
I enter :
SELECT * from Rental_Agreement
WHERE DateSerial(year(end_date), month(end_date) , day(end_date) - 5) = Date()
And receive error message:
Error in list of function arguments: ')' not recognized.
Unable to parse query text.
I then click the Continue button and receive the error message:
SQL Execution Error.
Executed SQL Statement: SELECT * from Rental_Agreement
WHERE DateSerial([year](end_date), [month](end_date) , [day](end_date) - 5) = Date()
Error Source: Microsoft Jet Database Engine
Error Message: Undefined Function '[Year]' in expresion
It then changed my statement to:
SELECT *
FROM Rental_Agreement
WHERE DateSerial([year](end_date), [month](end_date), [day](end_date) - 5) = DATE ()
Notice it placed square brakets [ ] around 'year' 'month' and 'day'
OK, I see what you mean. I suppose it's my tendency to avoid the GUI tools since I always seem to find limitations, so I typically either manually code the SQL or use the Access QBE to generate the SQL.
It looks to me as if the Query Builder doesn't really adequately support the same features as the Access QBE. However, as I mentioned, that query will run from ADO.NET. In addition, if you just manually enter the SQL into the CommandText property for the SelectCommand (bypass the option to automatically create the others) it should work. You just won't be able to use the Query Builder for this type of statement (although you can use Preview Data from the Data Sources window to see the data).
Couldn't hurt although I wouldn't be surprised if this was "by-design". I'm fairly certain the Microsoft support folks here would see this message.
In any event, the Query Builder is obviously not parsing the SQL statement correctly for Jet OLEDB.
