run-time error '-2147217900 (80040e14)

[Tripodkid66]

I hope someone can help me with this. When I run the code I get the following error;

"run-time error '-2147217900 (80040e14)' no value given for one or more parameters"

What am I missing here? Thanks in advance for any help!

strQueryDate = QueryDate.Value
mySQL = "SELECT tblFSE.FSERegion, Sum(tblWeek.WeekTimeSTD) AS SumOfWeekTimeSTD, Sum(tblWeek.WeekTimeOT) AS SumOfWeekTimeOT, " & _
"Sum(tblWeek.WeekTimeExtOT) AS SumOfWeekTimeExtOT, Sum(tblWeek.WeekTimeTVL) AS SumOfWeekTimeTVL, " & _
"Sum(tblWeek.WeekTimeADM) AS SumOfWeekTimeADM, Sum(tblWeek.WeekExpenseTotal) AS SumOfWeekExpenseTotal, " & _
"Sum(tblWeek.WeekInventory) AS SumOfWeekInventory, Sum(tblWeek.WeekAsteaOpenCases) AS SumOfWeekAsteaOpenCases " & _
"FROM tblFSE INNER JOIN tblWeek ON tblFSE.[FSEID ] = tblWeek.FSEID " & _
"WHERE (((tblWeek.WeekEndDate) Like " & "11/8/2014" & "))" & _
"GROUP BY tblFSE.FSERegion " & _
"HAVING (((tblFSE.FSERegion)= " & "East" & "))"

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

Dim rsEast As New ADODB.Recordset
rsEast.ActiveConnection = conn1
rsEast.Open mySQL, conn1, adOpenDynamic, adLockOptimistic

 

[CJ_London]

You need to use text qualifiers

WHERE (((tblWeek.WeekEndDate) Like '" & "11/8/2014" & "'))"
HAVING (((tblFSE.FSERegion)= '" & "East" & "'))"

However I can see other potential issues...

I presume you are aware that date fields are stored as decimal numbers, the part to the left of the decimal point identifying the date (11/8/2014 is 41862 for example) and the part to the right is the time expressed in seconds as a percentage of the number of seconds in 24 hours (e.g. 3am=0.125) so I suspect your query won't return anything.

Also Like is usually only used when you are wanting a range of possible records e.g. e.g. Like "11/08/%" will return all records that start with 11/08. My point is you would be better to use = in this situation.

if you want to do a text comparison on dates you need to use format, but I would standardise on using two digits for the day and month otherwise the code gets extremely complicated

e.g.

... format(tblWeek.WeekEndDate,"mm/dd/yyyy")='" & "11/08/2014" & "'....

or do a proper date comparison - either
....tblWeek.WeekEndDate=#" & "11/08/2014" & "#....
or
....tblWeek.WeekEndDate=cdate('" & "11/08/2014" & "')....

 

[Tripodkid66]

Thank you soo much for your insightful reply! That fixed the problem but introduced a new one. tblWeek.WeekEndDate and tblFSE.FSERegion are variables as seen bellow. If I use a fixed date like 11/8/2014 then everything works fine. But I need to be able to use wild cards like 11/*/2014, when I do this the query returns nothing and the code throws error 3021.

Dim strQueryDate As String
Dim mySQL As String
Dim strRegionName As String
Dim myResults

strRegionName = "East"
strQueryDate = QueryDate.Value

mySQL = "SELECT tblFSE.FSERegion, Sum(tblWeek.WeekTimeSTD) AS SumOfWeekTimeSTD, Sum(tblWeek.WeekTimeOT) AS SumOfWeekTimeOT, " & _
"Sum(tblWeek.WeekTimeExtOT) AS SumOfWeekTimeExtOT, Sum(tblWeek.WeekTimeTVL) AS SumOfWeekTimeTVL, " & _
"Sum(tblWeek.WeekTimeADM) AS SumOfWeekTimeADM, Sum(tblWeek.WeekExpenseTotal) AS SumOfWeekExpenseTotal, " & _
"Sum(tblWeek.WeekInventory) AS SumOfWeekInventory, Sum(tblWeek.WeekAsteaOpenCases) AS SumOfWeekAsteaOpenCases " & _
"FROM tblFSE INNER JOIN tblWeek ON tblFSE.[FSEID ] = tblWeek.FSEID " & _
"WHERE (((tblWeek.WeekEndDate) Like '" & strQueryDate & "'))" & _
"GROUP BY tblFSE.FSERegion " & _
"HAVING (((tblFSE.FSERegion)= '" & strRegionName & "'))"

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

Dim rsEast As New ADODB.Recordset
rsEast.ActiveConnection = conn1
rsEast.Open mySQL, conn1, adOpenDynamic, adLockOptimistic

myResults = rsEast.Fields(1).Value

 

[CJ_London]

Well, first of all you are using ADO which uses the % character as a wildcard rather than *.

Also note that with ADO you cannot have the % at the beginning e.g. ....like '%/4/2014' will generate an error

You would be better off using a DAO recordset unless there is a specific reason why you need to use ADO.

Secondly, refer to my previous post when you want a string type comparison - you need to format the weekenddate as a string.