Pass a parameter to a query using VBA

[webcats]

Hi,

I have a problem with trying to pass a parameter to a query through vba. I know, you've all heard this before. But here's the problem:

I have a query that loads an array with 30 dates back from the date the user first input.

I then compare that array with one from a table which I load the last 30 entries.

If the dates don't match, I have to load the files in until they do.

The problem I'm having is getting the parameter to pass to the query... Here's what I have right now:

<CODE:>
Function FindMissingDates(Area As String, varPickDate As Variant)

Dim LoadedDays() As Variant
Dim NeededDays(30) As Variant
Dim intUpper As Integer
Dim intI As Integer
Dim rs1 As ADODB.Recordset
Dim dteAddDate As Date
Dim getDate As Date
Dim cmdLoaded146 As ADODB.Command
Dim lngAffectedCur146 As Long
Dim dt146Date As ADODB.Parameter

Set dt146Date = New ADODB.Parameter

'dt146Date.Value = varPickDate


Set rs1 = New ADODB.Recordset

'rs1.Open "qry146LoadedDays_" & Area, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly

Set cmdLoaded146 = New ADODB.Command

cmdLoaded146.ActiveConnection = CurrentProject.Connection
'cmdLoaded146.CommandType = adCmdFile
Set dt146Date = cmdLoaded146.CreateParameter(dt146, adDBDate, adParamInput)
cmdLoaded146.Parameters.Append dt146Date
dt146Date.Value = varPickDate
cmdLoaded146.CommandText = "SELECT tbl146Percent_SB.File_Date " & _
"FROM tbl146Percent_SB " & _
"WHERE (((tbl146Percent_SB.file_Date) > [dt146] - 30));"



Set rs1 = cmdLoaded146.Execute

LoadedDays = rs1.GetRows(Fields:=Array("File_Date"))

Set cmdLoaded146 = Nothing



'What dates are already loaded


rs1.Close

dteAddDate = varPickDate '(DateAdd("d", -1, Date))

'Load the array with dates back 30 days
For i = 0 To 29
NeededDays(i) = dteAddDate
dteAddDate = dteAddDate - 1
Next i

'Debug.Print "Needed Days"

'For X = 0 To 29 Step 1
' Debug.Print NeededDays(X)
'Next X


'Debug.Print "Loaded Days"

intUpper = UBound(LoadedDays, 2) + 1

'For intI = 0 To UBound(LoadedDays, 2)
' Debug.Print LoadedDays(0, intI)
'Next intI

'Compare the two arrays and load the missing files, if any

For icnt = 0 To 29 Step 1

If NeededDays(icnt) <> LoadedDays(0, icnt) Then

getDate = NeededDays(icnt)

DoCmd.SetWarnings False

ImportMissing146 Area, getDate

'After loading a missing date, reload the array with the new values

rs1.Open "qry146LoadedDays_" & Area, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly

Erase LoadedDays

LoadedDays = rs1.GetRows(Fields:=Array("File_Date"))
rs1.Close

End If

Next icnt


Set rs1 = Nothing
DoCmd.SetWarnings True

End Function

<END CODE>

I'm getting an error at the Set rs1 = cmdLoaded146.Execute line.

Error: "Data type mismatch in criteria expression."

Can anyone help?
Thanks.

 

[PHV]

You may have to replace this:
Set dt146Date = cmdLoaded146.CreateParameter(dt146, adDBDate, adParamInput)
By this:
Set dt146Date = cmdLoaded146.CreateParameter("dt146", adDBDate, adParamInput)

Don't you use Option Explicit instruction ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[webcats]

Hi PHV,

I received the same error...



I used to use the "Option Explicit", but somewhere along the line the option was removed and I keep forgetting to manually add it.

 

[webcats]

Wasn't there a checkbox somewhere you could check to have the "Option Explicit" automatically added? I can't find it anywhere...

 

[PHV]

While in VBE, menu Tools -> Options ... -> tab Editor

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[webcats]

Ahh, I see it: "Require Variable Declaration".

Thanks!