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.
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.