SQL Query via VBA 1

[mkov]

I am trying to query a table through code and am getting an error message. Here is what I have:

Private Sub Label7_Click()

Dim CnCurrent As ADODB.Connection
Set CnCurrent = CurrentProject.Connection

Dim rsManualTds As ADODB.Recordset
Set rsManualTds = New ADODB.Recordset

Dim dtBegDate As Date
Dim dtEndDate As Date
Dim strSQL As String

dtBegDate = InputBox("Enter the Beginning Date.&quot
dtEndDate = InputBox("Enter the Ending Date.&quot

strSQL = "SELECT * FROM [Manual Trades Table]" & "WHERE (([Manual Trades Table].Date) Between [dtBegDate] And [dtEndDate])"


rsManualTds.Open strSQL, CnCurrent

End Sub


When I get to "rsManualTds.Open strSQL, CnCurrent" I get the following error message:

run-time error'-2147217904 (80040e10)':
No value given for one or more of the required parameters.

I know the problem is that it is not picking up the value that is input for dtBegDate and dtEndDate. My question is what do I need to change so that it will pickup the value that are input for each of those. If I use "=date()" instead of the between statement, it works fine.

I am fairly new to SQL queries in VBA, but I am good with VBA in general, so this is very frustrating.

Thanks for your help.

 

[nicsin]

You missed a space before the WHERE. Replace your code with this and it should be fine:


strSQL = "SELECT * FROM [Manual Trades Table] " & "WHERE (([Manual Trades Table].Date) Between [dtBegDate] And [dtEndDate])"

 

[mkov]

Tried that and it still gave me the same error message.

 

[nicsin]

There is another problem indeed. Try this:

strSQL = "SELECT * FROM [Manual Trades Table] " & "WHERE (([Manual Trades Table].Date) Between #" & dtBegDate & "# And #" & [dtEndDate] & "#)"

 

[mkov]

Worked, thank you.

 

[nicsin]

Glad to help!