If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
MS Access Query criteria Problem
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])); |
#2
|
|||
|
|||
MS Access Query criteria Problem
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? -- Duane Hookom Microsoft Access MVP "Niroshana" wrote: 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])); |
#3
|
|||
|
|||
MS Access Query criteria Problem
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 "Niroshana" wrote in message ... 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])); |
Thread Tools | |
Display Modes | |
|
|