Using the DatePart function in an Access 2003 HAVING clause

  • Thread starter Thread starter Tom B.
  • Start date Start date
T

Tom B.

I am trying to use the following statement to retrieve time card records for
a particular employee for a particular month. I keep getting an error message
that indicates Expecting Expression at the m in the DatePart function.

rstTimeCardHoursMTD.Open "SELECT TimeCardHours.CaseNum, " & _
"Sum(Hours) AS TotCaseHrsMTD, DatePart("m", DateWorked) AS [Month] "
& _
"FROM T_TimeCardHours INNER JOIN T_TimeCards " & _
"ON T_TimeCardHours.TimeCardID = T_TimeCards.TimeCardID " & _
" GROUP BY CaseNum" & _
"HAVING TimeCards.EmployeeID = lngAccessID AND DatePart("m", DateWorked) =
lngMonth"

How can I break this up so it works?
 
I think your problem is here:
"HAVING TimeCards.EmployeeID = lngAccessID AND DatePart("m", DateWorked) =
lngMonth"

It appears you are using two variables in your HAVING clause. SQL does not
know about VBA variables. You are actually sending a request from Access to
Jet and the string sent will not include the value of the variable, but the
name of the variable. The names of variables or other objects containing
value have to be outside the quotes. Also, depending on the data type of
the field being referenced, the syntax will be different. Text values must
be enclosed in quotes, Dates in #, but no delimiters for number fields. I
think this will fix your problems:

"HAVING TimeCards.EmployeeID = " & lngAccessID & " AND DatePart(""m"",
DateWorked) = " & lngMonth
 
I modified it to the following:

rstTimeCardHoursMTD.Open "SELECT T_TimeCardHours.CaseNum, " & _
"Sum(Hours) AS TotCaseHrsMTD " & _
"FROM T_TimeCardHours INNER JOIN T_TimeCards " & _
"ON T_TimeCardHours.TimeCardID = T_TimeCards.TimeCardID " & _
" HAVING T_TimeCards.EmployeeID = " & lngAccessID & _
" AND DatePart(""m"", DateWorked) = " & lngMonth & _
" GROUP BY CaseNum"

but I get a run-time error '-2147217900(80040e14)':
Syntax error in HAVING clause

Klatuu said:
I think your problem is here:
"HAVING TimeCards.EmployeeID = lngAccessID AND DatePart("m", DateWorked) =
lngMonth"

It appears you are using two variables in your HAVING clause. SQL does not
know about VBA variables. You are actually sending a request from Access to
Jet and the string sent will not include the value of the variable, but the
name of the variable. The names of variables or other objects containing
value have to be outside the quotes. Also, depending on the data type of
the field being referenced, the syntax will be different. Text values must
be enclosed in quotes, Dates in #, but no delimiters for number fields. I
think this will fix your problems:

"HAVING TimeCards.EmployeeID = " & lngAccessID & " AND DatePart(""m"",
DateWorked) = " & lngMonth


Tom B. said:
I am trying to use the following statement to retrieve time card records
for
a particular employee for a particular month. I keep getting an error
message
that indicates Expecting Expression at the m in the DatePart function.

rstTimeCardHoursMTD.Open "SELECT TimeCardHours.CaseNum, " & _
"Sum(Hours) AS TotCaseHrsMTD, DatePart("m", DateWorked) AS [Month]
"
& _
"FROM T_TimeCardHours INNER JOIN T_TimeCards " & _
"ON T_TimeCardHours.TimeCardID = T_TimeCards.TimeCardID " & _
" GROUP BY CaseNum" & _
"HAVING TimeCards.EmployeeID = lngAccessID AND DatePart("m", DateWorked) =
lngMonth"

How can I break this up so it works?
Click to expand...
Click to expand...
 
I am trying to use the following statement to retrieve time card records for
a particular employee for a particular month. I keep getting an error message
that indicates Expecting Expression at the m in the DatePart function.

rstTimeCardHoursMTD.Open "SELECT TimeCardHours.CaseNum, " & _
"Sum(Hours) AS TotCaseHrsMTD, DatePart("m", DateWorked) AS [Month] "
& _
"FROM T_TimeCardHours INNER JOIN T_TimeCards " & _
"ON T_TimeCardHours.TimeCardID = T_TimeCards.TimeCardID " & _
" GROUP BY CaseNum" & _
"HAVING TimeCards.EmployeeID = lngAccessID AND DatePart("m", DateWorked) =
lngMonth"

How can I break this up so it works?
Click to expand...

I'm sure Klatuu is right, but another thing I saw is that the doublequotes
around "m" are being interpreted as closing and opening the doublequotes
around the entire string. You're getting one string constant

"HAVING TimeCards.EmployeeID = lngAccessID AND DatePart("

followed by a meaningless "m", which Access doesn't recognize as a variable!

Try replacing "m" with 'm' (using singlequotes to ikeep from interfering with
the quoted string).

Then fix the variables as Klatuu suggests.
 
Several problems with your string.

First- you need to use two quote marks if you need to embed quotes in the
string - so "m" needs to be ""m"" in the date part expression.

Second - you are missing a space before the HAVING.

Third - I suspect lngMonth is a variable. If so you need to concatenate its
value into the string. You cannot just refer to the variable.

Fourth - Recordsets don't have an Open action (as far as I know)

I would do the following

Dim StrSQL as String

StrSQL = "SELECT TimeCardHours.CaseNum, " & _
"Sum(Hours) AS TotCaseHrsMTD, DatePart(""m"", DateWorked) AS [Month] " & _
"FROM T_TimeCardHours INNER JOIN T_TimeCards " & _
"ON T_TimeCardHours.TimeCardID = T_TimeCards.TimeCardID " & _
" GROUP BY CaseNum" & _
" HAVING TimeCards.EmployeeID = " & lngAccessID & _
" AND DatePart(""m"", DateWorked) = " & lngMonth

Now that you've gotten this far, you can work out what you need to do next.
Are you trying to open a form with the records that would be returned by the
string? Do you want to populate a list box with the information? OR ????

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
John,
I modified the code as follows:

rstTimeCardHoursMTD.Open "SELECT T_TimeCardHours.CaseNum, " & _
"Sum(Hours) AS TotCaseHrsMTD " & _
"FROM T_TimeCardHours INNER JOIN T_TimeCards " & _
"ON T_TimeCardHours.TimeCardID = T_TimeCards.TimeCardID " & _
" HAVING T_TimeCards.EmployeeID = " & lngAccessID & _
" AND DatePart('m', DateWorked) = " & lngMonth & _
" GROUP BY CaseNum"

but I'm still getting a syntax error.

John W. Vinson said:
I am trying to use the following statement to retrieve time card records for
a particular employee for a particular month. I keep getting an error message
that indicates Expecting Expression at the m in the DatePart function.

rstTimeCardHoursMTD.Open "SELECT TimeCardHours.CaseNum, " & _
"Sum(Hours) AS TotCaseHrsMTD, DatePart("m", DateWorked) AS [Month] "
& _
"FROM T_TimeCardHours INNER JOIN T_TimeCards " & _
"ON T_TimeCardHours.TimeCardID = T_TimeCards.TimeCardID " & _
" GROUP BY CaseNum" & _
"HAVING TimeCards.EmployeeID = lngAccessID AND DatePart("m", DateWorked) =
lngMonth"

How can I break this up so it works?
Click to expand...

I'm sure Klatuu is right, but another thing I saw is that the doublequotes
around "m" are being interpreted as closing and opening the doublequotes
around the entire string. You're getting one string constant

"HAVING TimeCards.EmployeeID = lngAccessID AND DatePart("

followed by a meaningless "m", which Access doesn't recognize as a variable!

Try replacing "m" with 'm' (using singlequotes to ikeep from interfering with
the quoted string).

Then fix the variables as Klatuu suggests.
Click to expand...
 
TRY

rstTimeCardHoursMTD.Open "SELECT T_TimeCardHours.CaseNum, " & _
"Sum(Hours) AS TotCaseHrsMTD " & _
" FROM T_TimeCardHours INNER JOIN T_TimeCards " & _
" ON T_TimeCardHours.TimeCardID = T_TimeCards.TimeCardID " & _
" WHERE T_TimeCards.EmployeeID = " & lngAccessID & _
" AND DatePart("m", DateWorked) = " & lngMonth & _
" GROUP BY CaseNum"

I did miss that the HAVING clause might have a problem and it would be more
efficient to put the filter criteria into a where clause anyway.

I'm not sure you can use the apostrophes in place of the two quote marks.
DatePart is a VBA function and I know that an apostrophe won't work in VBA
module, but from SQL the apostrophe might get translated correctly.

By the way a good way to troubleshoot this would be to assign the SQL string
to a string variable. When you get an error you can DEbug.print the SQL
string to the immediate window and copy that to a blank query. The error
messages are usually better and often you will get the cursor placed at or
near the actual code causing the problem.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
John,
I modified the code as follows:

rstTimeCardHoursMTD.Open "SELECT T_TimeCardHours.CaseNum, " & _
"Sum(Hours) AS TotCaseHrsMTD " & _
"FROM T_TimeCardHours INNER JOIN T_TimeCards " & _
"ON T_TimeCardHours.TimeCardID = T_TimeCards.TimeCardID " & _
" HAVING T_TimeCards.EmployeeID = " & lngAccessID & _
" AND DatePart('m', DateWorked) = " & lngMonth & _
" GROUP BY CaseNum"

but I'm still getting a syntax error.

John W. Vinson said:
I am trying to use the following statement to retrieve time card records for
a particular employee for a particular month. I keep getting an error message
that indicates Expecting Expression at the m in the DatePart function.

rstTimeCardHoursMTD.Open "SELECT TimeCardHours.CaseNum, " & _
"Sum(Hours) AS TotCaseHrsMTD, DatePart("m", DateWorked) AS [Month] "
& _
"FROM T_TimeCardHours INNER JOIN T_TimeCards " & _
"ON T_TimeCardHours.TimeCardID = T_TimeCards.TimeCardID " & _
" GROUP BY CaseNum" & _
"HAVING TimeCards.EmployeeID = lngAccessID AND DatePart("m", DateWorked) =
lngMonth"

How can I break this up so it works?
Click to expand...
I'm sure Klatuu is right, but another thing I saw is that the doublequotes
around "m" are being interpreted as closing and opening the doublequotes
around the entire string. You're getting one string constant

"HAVING TimeCards.EmployeeID = lngAccessID AND DatePart("

followed by a meaningless "m", which Access doesn't recognize as a variable!

Try replacing "m" with 'm' (using singlequotes to ikeep from interfering with
the quoted string).

Then fix the variables as Klatuu suggests.
Click to expand...
Click to expand...
 
Hi John,
This was the code that finally worked.

rstTimeCardHoursMTD.Open "SELECT T_TimeCardHours.CaseNum, " & _
" Sum(Hours) AS TotCaseHrsMTD " & _
" FROM T_TimeCardHours INNER JOIN T_TimeCards " & _
" ON T_TimeCardHours.TimeCardID = T_TimeCards.TimeCardID " & _
" WHERE T_TimeCards.EmployeeID = " & lngAccessID & _
" AND DatePart(""m"", DateWorked) = " & lngMonth & _
" GROUP BY CaseNum"

My sincere thanks for your help. I couldn't have done it without your
insights.

John Spencer said:
TRY

rstTimeCardHoursMTD.Open "SELECT T_TimeCardHours.CaseNum, " & _
"Sum(Hours) AS TotCaseHrsMTD " & _
" FROM T_TimeCardHours INNER JOIN T_TimeCards " & _
" ON T_TimeCardHours.TimeCardID = T_TimeCards.TimeCardID " & _
" WHERE T_TimeCards.EmployeeID = " & lngAccessID & _
" AND DatePart("m", DateWorked) = " & lngMonth & _
" GROUP BY CaseNum"

I did miss that the HAVING clause might have a problem and it would be more
efficient to put the filter criteria into a where clause anyway.

I'm not sure you can use the apostrophes in place of the two quote marks.
DatePart is a VBA function and I know that an apostrophe won't work in VBA
module, but from SQL the apostrophe might get translated correctly.

By the way a good way to troubleshoot this would be to assign the SQL string
to a string variable. When you get an error you can DEbug.print the SQL
string to the immediate window and copy that to a blank query. The error
messages are usually better and often you will get the cursor placed at or
near the actual code causing the problem.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
John,
I modified the code as follows:

rstTimeCardHoursMTD.Open "SELECT T_TimeCardHours.CaseNum, " & _
"Sum(Hours) AS TotCaseHrsMTD " & _
"FROM T_TimeCardHours INNER JOIN T_TimeCards " & _
"ON T_TimeCardHours.TimeCardID = T_TimeCards.TimeCardID " & _
" HAVING T_TimeCards.EmployeeID = " & lngAccessID & _
" AND DatePart('m', DateWorked) = " & lngMonth & _
" GROUP BY CaseNum"

but I'm still getting a syntax error.

John W. Vinson said:
I am trying to use the following statement to retrieve time card records for
a particular employee for a particular month. I keep getting an error message
that indicates Expecting Expression at the m in the DatePart function.

rstTimeCardHoursMTD.Open "SELECT TimeCardHours.CaseNum, " & _
"Sum(Hours) AS TotCaseHrsMTD, DatePart("m", DateWorked) AS [Month] "
& _
"FROM T_TimeCardHours INNER JOIN T_TimeCards " & _
"ON T_TimeCardHours.TimeCardID = T_TimeCards.TimeCardID " & _
" GROUP BY CaseNum" & _
"HAVING TimeCards.EmployeeID = lngAccessID AND DatePart("m", DateWorked) =
lngMonth"

How can I break this up so it works?
I'm sure Klatuu is right, but another thing I saw is that the doublequotes
around "m" are being interpreted as closing and opening the doublequotes
around the entire string. You're getting one string constant

"HAVING TimeCards.EmployeeID = lngAccessID AND DatePart("

followed by a meaningless "m", which Access doesn't recognize as a variable!

Try replacing "m" with 'm' (using singlequotes to ikeep from interfering with
the quoted string).

Then fix the variables as Klatuu suggests.
Click to expand...
Click to expand...
Click to expand...
 
Hi John,
This is the code that finally worked.

rstTimeCardHoursMTD.Open "SELECT T_TimeCardHours.CaseNum, " & _
" Sum(Hours) AS TotCaseHrsMTD " & _
" FROM T_TimeCardHours INNER JOIN T_TimeCards " & _
" ON T_TimeCardHours.TimeCardID = T_TimeCards.TimeCardID " & _
" WHERE T_TimeCards.EmployeeID = " & lngAccessID & _
" AND DatePart(""m"", DateWorked) = " & lngMonth & _
" GROUP BY CaseNum"

My sincere thanks for your help

John W. Vinson said:
I am trying to use the following statement to retrieve time card records for
a particular employee for a particular month. I keep getting an error message
that indicates Expecting Expression at the m in the DatePart function.

rstTimeCardHoursMTD.Open "SELECT TimeCardHours.CaseNum, " & _
"Sum(Hours) AS TotCaseHrsMTD, DatePart("m", DateWorked) AS [Month] "
& _
"FROM T_TimeCardHours INNER JOIN T_TimeCards " & _
"ON T_TimeCardHours.TimeCardID = T_TimeCards.TimeCardID " & _
" GROUP BY CaseNum" & _
"HAVING TimeCards.EmployeeID = lngAccessID AND DatePart("m", DateWorked) =
lngMonth"

How can I break this up so it works?
Click to expand...

I'm sure Klatuu is right, but another thing I saw is that the doublequotes
around "m" are being interpreted as closing and opening the doublequotes
around the entire string. You're getting one string constant

"HAVING TimeCards.EmployeeID = lngAccessID AND DatePart("

followed by a meaningless "m", which Access doesn't recognize as a variable!

Try replacing "m" with 'm' (using singlequotes to ikeep from interfering with
the quoted string).

Then fix the variables as Klatuu suggests.
Click to expand...
 
Hi Klatuu,
This the code that finally worked.

rstTimeCardHoursMTD.Open "SELECT T_TimeCardHours.CaseNum, " & _
" Sum(Hours) AS TotCaseHrsMTD " & _
" FROM T_TimeCardHours INNER JOIN T_TimeCards " & _
" ON T_TimeCardHours.TimeCardID = T_TimeCards.TimeCardID " & _
" WHERE T_TimeCards.EmployeeID = " & lngAccessID & _
" AND DatePart(""m"", DateWorked) = " & lngMonth & _
" GROUP BY CaseNum"

Thanks for your help and insight.

Klatuu said:
I think your problem is here:
"HAVING TimeCards.EmployeeID = lngAccessID AND DatePart("m", DateWorked) =
lngMonth"

It appears you are using two variables in your HAVING clause. SQL does not
know about VBA variables. You are actually sending a request from Access to
Jet and the string sent will not include the value of the variable, but the
name of the variable. The names of variables or other objects containing
value have to be outside the quotes. Also, depending on the data type of
the field being referenced, the syntax will be different. Text values must
be enclosed in quotes, Dates in #, but no delimiters for number fields. I
think this will fix your problems:

"HAVING TimeCards.EmployeeID = " & lngAccessID & " AND DatePart(""m"",
DateWorked) = " & lngMonth


Tom B. said:
I am trying to use the following statement to retrieve time card records
for
a particular employee for a particular month. I keep getting an error
message
that indicates Expecting Expression at the m in the DatePart function.

rstTimeCardHoursMTD.Open "SELECT TimeCardHours.CaseNum, " & _
"Sum(Hours) AS TotCaseHrsMTD, DatePart("m", DateWorked) AS [Month]
"
& _
"FROM T_TimeCardHours INNER JOIN T_TimeCards " & _
"ON T_TimeCardHours.TimeCardID = T_TimeCards.TimeCardID " & _
" GROUP BY CaseNum" & _
"HAVING TimeCards.EmployeeID = lngAccessID AND DatePart("m", DateWorked) =
lngMonth"

How can I break this up so it works?
Click to expand...
Click to expand...
 
Back
Top