Date Function

desireemm · March 23rd, 2007, 06:34 PM

Hi all I have a database that has SQL Server as the engine and Access 2003 on the front end. I am converting a MDB to an ADP but Jet SQL and Sql Server are like apples and oranges they dont exactly speak the same language. I need for the users to create a function that allwos users to enter BETWEEN [Enter the Start Date] AND [Enter the End Date] from 1/21/2005 to 1/31/2005 In access theres a parameter box that pops up and allows the users to enter in an End date and a Beginning date then it generates a report for them or a query which ever. How do you do this in SQL, I have a database that I created that has the same thing as a function its like this: =@Enter_FINALSUITEDONE then a dialog box pops up and allows them to enter in a final suit date and then they can see all the finals for that date. Can anyone help me please

Code:

SELECT [Main Table].[IR Number], [Main Table].Date, [Main Table].Inspector, [Main Table].Area, [Main Table].Violation, [Main Table].[Violation Type], [Main Table].Loss, [Main Table].[Loss Type], [Main Table].Employee, [Main Table].Action, [Main Table].[Action Type], [Main Table].Notes
FROM [Main Table]
WHERE ((([Main Table].Date) Between [Enter the Start Date] And [Enter the End Date]))
ORDER BY [Main Table].[IR Number]

bishnupokhrel · March 26th, 2007, 04:28 AM

hi desireemm,

you can create a access form which asks for two values, and replace the values and fire the query, its seems simple to me

strQuery = " ... WHERE ((([Main Table].Date) Between [Enter_the_Start_Date] And [Enter_the_End_Date])) ... "
strQuery = replace(strQuery,"Enter_the_Start_Date", first_value)
strQuery = replace(strQuery,"Enter_the_End_Date", second_value)

execute the query and you are done.

let me know if i am wrong.

Regards
Bishnu Pokharel
[email protected]
http://www.bishnupokharel.com.np/