I need to run a query using a sql 2008 R2, with an access 2007 front end. The query needs to allow me to set parameters for a start date and an end date. Any suggestions?
David92595
Full Version: Sql Start And End Date Query
I just had to reply because we have similar names
Use a pass through query. Now a pass through query will not accept parameters, so you must create the query using VBA and passing the values to variables in the procedure
Here is an example that I use. This creates a Pass through query named RejCleared. DtYester is the previous business day. fAddWorkdays is a function that I use to calculate the previous business day accounting for holidays and weekends.
Edit: Removed PWD values. Doug
Use a pass through query. Now a pass through query will not accept parameters, so you must create the query using VBA and passing the values to variables in the procedure
Here is an example that I use. This creates a Pass through query named RejCleared. DtYester is the previous business day. fAddWorkdays is a function that I use to calculate the previous business day accounting for holidays and weekends.
CODE
Function RejectsCleared()
Dim Db As DAO.Database
Dim qdExtData As QueryDef
Dim strSQL As String
Dim DtYester As Date
DoCmd.SetWarnings False
Set Db = CurrentDb
DtYester = fAddWorkdays(Date, -1)
If IsTableQuery("", "RejCleared") Then
DoCmd.DeleteObject acQuery, "RejCleared"
End If
strSQL = " SELECT * FROM YourTableName WHERE YourDatefield = TO_Date('" & DtYester & "','mm/dd/yyyy')"
Set qdExtData = Db.CreateQueryDef("RejCleared")
' qdExtData.Connect = "ODBC;DSN=BASERS1;UID=G182769;PWD=XXXXXXX;DBQ=BASERS1;DBA=W;APA=T;EXC=F;
FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MT
S
=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;"
'qdExtData.Connect = "ODBC;DSN=BASERS1;UID=g182769;PWD=XXXXXXX;DBQ=BASERS1;DBA=W;APA=T;EXC=F;FEN
=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T
;
MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;"
qdExtData.Connect = "ODBC;DSN=BASEPS1;UID=a881367;PWD=XXXXXXX;DBQ=BASEPS1;DBA=W;APA=T;EXC=F;FEN
=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;
MTS=T
;
MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;"
qdExtData.SQL = strSQL
Db.Close
Set Db = Nothing
Dim Db As DAO.Database
Dim qdExtData As QueryDef
Dim strSQL As String
Dim DtYester As Date
DoCmd.SetWarnings False
Set Db = CurrentDb
DtYester = fAddWorkdays(Date, -1)
If IsTableQuery("", "RejCleared") Then
DoCmd.DeleteObject acQuery, "RejCleared"
End If
strSQL = " SELECT * FROM YourTableName WHERE YourDatefield = TO_Date('" & DtYester & "','mm/dd/yyyy')"
Set qdExtData = Db.CreateQueryDef("RejCleared")
' qdExtData.Connect = "ODBC;DSN=BASERS1;UID=G182769;PWD=XXXXXXX;DBQ=BASERS1;DBA=W;APA=T;EXC=F;
FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MT
S
=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;"
'qdExtData.Connect = "ODBC;DSN=BASERS1;UID=g182769;PWD=XXXXXXX;DBQ=BASERS1;DBA=W;APA=T;EXC=F;FEN
=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T
;
MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;"
qdExtData.Connect = "ODBC;DSN=BASEPS1;UID=a881367;PWD=XXXXXXX;DBQ=BASEPS1;DBA=W;APA=T;EXC=F;FEN
=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;
MTS=T
;
MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;"
qdExtData.SQL = strSQL
Db.Close
Set Db = Nothing
Edit: Removed PWD values. Doug
Lets be honest, it's a pretty awesome name... 
I currently have a pass-through set up, but i keep getting an error:
ODBC--call failed[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to date. (#8114)
Here is my VBA that pass the @startDate and @EndDate to the pass through query:
Dim DB As Database
Dim A As QueryDef
Dim B As String
Dim S As String
Dim E As String
S = Forms!StartAndEndDate![@StartDate]
E = Forms!StartAndEndDate![@EndDate]
B = Forms![StartAndEndDate]![RunQuery]
Set DB = CurrentDb()
Set A = DB.QueryDefs(B)
A.SQL = "exec dbo.usp_" & B & " '" & S & "'" & "'" & E & "'"
Any suggestions on what I need to change?
I currently have a pass-through set up, but i keep getting an error:
ODBC--call failed[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to date. (#8114)
Here is my VBA that pass the @startDate and @EndDate to the pass through query:
Dim DB As Database
Dim A As QueryDef
Dim B As String
Dim S As String
Dim E As String
S = Forms!StartAndEndDate![@StartDate]
E = Forms!StartAndEndDate![@EndDate]
B = Forms![StartAndEndDate]![RunQuery]
Set DB = CurrentDb()
Set A = DB.QueryDefs(B)
A.SQL = "exec dbo.usp_" & B & " '" & S & "'" & "'" & E & "'"
Any suggestions on what I need to change?
I am not trying to pass a value to a parameter in a stored procedure. I am not referring to a stored proc at all. I havent done that and I dont know if it will work in your case. You would probably have to struggle with the syntax. Also my query was written for an Oracle Database. There are minor differences with SQL Server as you probably know.
I am creating the entire sql statement in the query def of the pass thru query that I am creating. Actually the SQL string goes on for about 200 lines and I just shortened it to show you how I use a date variable.
I am creating the entire sql statement in the query def of the pass thru query that I am creating. Actually the SQL string goes on for about 200 lines and I just shortened it to show you how I use a date variable.
I just noticed that I published my password on the internet.....reeeeeeeeeeealy smart
your welcome to email an admin and have them delete this string. I've posted similar questions on other string, by boss asked me to ask this question...
I've had better luck on my other strings anyways (no offense)
I've had better luck on my other strings anyways (no offense)
>> I need to run a query using a sql 2008 R2, with an access 2007 front end. The query needs to allow me to set parameters for a start date and an end date. Any suggestions? <<
Sure ... in Access 2007 create Linked Table obejcts that point to you SQL Server tables, then create a Query object that uses the Linked Table objects as its datasource, then in columns that represent the date you wish to filter upon use something like
Between [Enter StartDate] And DateValue([Enter EndDate]) + TimeValue("23:59:59")
When you open your Query object, you will be prompted for the start and end.
Sure ... in Access 2007 create Linked Table obejcts that point to you SQL Server tables, then create a Query object that uses the Linked Table objects as its datasource, then in columns that represent the date you wish to filter upon use something like
Between [Enter StartDate] And DateValue([Enter EndDate]) + TimeValue("23:59:59")
When you open your Query object, you will be prompted for the start and end.
I want to thank everyone for their help on this. I got it done, and not a moment to soon either.
Thank you all,
David92595
Thank you all,
David92595
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.