recordset SQL syntax error

[CBrighton]

Hello,

I am having issues with a VBA recordset:

Set r = db.OpenRecordset("SELECT tblHolidays.DateBooked, sum(tblHolidays.TotalHours) AS SumOfTotalHours " & _
"FROM (tblHolidays INNER JOIN tblStaff ON tblStaff.Name = tblHolidays.Name) " & _
"HAVING tblHolidays.DateBooked = #" & Format(DateSerial(Right(lstDates.ItemData(i), 4), Mid(lstDates.ItemData(i), 4, 2), Left(lstDates.ItemData(i), 2)), "mm\/dd\/yyyy") & "# " & _
"AND tblStaff.HolType = '" & Forms!frmSelectAgent!cboTeam & "' " & _
"GROUP BY tblHolidays.DateBooked")

The long piece of code relating to the date is required as my listbox, which is fed by a calendar control, stores the dates as strings rather than dates.

The problem I am having is with the HAVING clause. Using the code above I get "Run-time error '3133': Syntax error in HAVING clause."

I know the date part of the HAVING clause works in a different recordset (the project is a holiday database, I have already used the above date code to check a recordset for duplicate holiday requests, records which already exist with the requested date & name combo) and the HolType code is very simple, I can't see that being wrong.

I cannot see anything wrong with the SQL which I have used...

Any help would be greatly appreciated.

 

[namliam]

Judging from your construction your listbox dates look like DD-MM-YYYY, correct?

Meanwhile I suggest you do something like :

dim mySQL as string
mySQL = ""
mySQL = mySQL & " SELECT tblHolidays.DateBooked, sum(tblHolidays.TotalHours) AS SumOfTotalHours " 
mySQL = mySQL & " FROM (tblHolidays "
mySQL = mySQL & " INNER JOIN tblStaff ON tblStaff.Name = tblHolidays.Name) "
mySQL = mySQL & " HAVING tblHolidays.DateBooked = #" & Format(DateSerial(Right(lstDates.ItemData(i), 4), Mid(lstDates.ItemData(i), 4, 2), Left(lstDates.ItemData(i), 2)), "mm\/dd\/yyyy") & "# "
mySQL = mySQL & " AND tblStaff.HolType = '" & Forms!frmSelectAgent!cboTeam & "'  "
mySQL = mySQL & " GROUP BY tblHolidays.DateBooked"
debug.print mySQL

Then check the actual SQL being used probably that will help a lot...

Also instead of the format/daterial thing you can use dateserial only...which would be something along the lines of:

DateSerial(" & Right(lstDates.ItemData(i), 4) & "," & Mid(lstDates.ItemData(i), 4, 2) & "," & Left(lstDates.ItemData(i), 2)) & ") "

Just makes things look easier on the eyes IMHO.

 

[gemma-the-husky]

you may get a problem using the form reference in the recordset. I am not sure.

I generally design queries like this in the visual pane

then you can just do

r= db.openrecordset("mystoredquery")

that way, access sorts out the sql for you!

 

[CBrighton]

-Snip-

I tried the SQL you posted but I still get the same runtime error.

:Edit:

Obviously I didn't pay too much attention, rereading your post it is more about the format than the content as you told me to check the SQL.

I know I should use a string to store things like this, the problem is I'm totally self-taught. Recently I have been trying to stick to naming conventions and coding conventions but depending on how much of a hurry I am in I don't always think about it.

As for the date thing, you may be right. I tried loads of combinations both in the OnClick event of the calendar and the code which first uses the dates in the listbox. When I found the first combination which worked I stopped immediately... it took long enough and annoyed me enough to not want to change it again.

:edit2:

Also, being in the UK I'm not sure if DateSerial alone would format it to the mm/dd/yyyy format used by SQL or use my regional settings which are dd/mm/yyyy (and you are correct in thinking that is the format used in the listbox).

-Snip-

My experiance has been the opposite. When I use queries which refer to controls on a form they do not work when building it in the query builder and using that query as the recordset.

When the query has no references I have had no [problems but if it refers to a text box, etc I have always had to use the SQL directly within VBA.

The majority of my work is not so much databases as using Access as an interface for other applications, I regularly use recordsets rather than bound forms and frequently use references to controls in them.

 

[CBrighton]

I forgot, the Debug.Print gives the following:

SELECT tblHolidays.DateBooked, sum(tblHolidays.TotalHours) AS SumOfTotalHours FROM (tblHolidays INNER JOIN tblStaff ON tblStaff.Name = tblHolidays.Name) HAVING tblHolidays.DateBooked = #04/28/2010# AND tblStaff.HolType = 'FCR FT' GROUP BY tblHolidays.DateBooked

All of which looks correct. The date is in MM/DD/YYYY format as required by SQL and is enclosed in #s, the team name is enclosed in 's.

 

[CBrighton]

After all that it was simply the fact that it used HAVING instead of WHERE.

I constructed the query in Access's query builder originally then copied the code into the VBA module and updated it with the control references to allow me to loop though the listcount of the listbox. The query builder selected HAVING for the original query so I kept that but out of desperation I just changed it... and it worked perfectly.

Hurrah!


Thanks for the replies but this is now sorted.

 

[namliam]

IAlso, being in the UK I'm not sure if DateSerial alone would format it to the mm/dd/yyyy format used by SQL or use my regional settings which are dd/mm/yyyy (and you are correct in thinking that is the format used in the listbox).

When using it like:

... DateBooked = #" & DateSerial(Right(lstDates.ItemData(i), 4), Mid(lstDates.ItemData(i), 4, 2), Left(lstDates.ItemData(i), 2)) & "# ...

No

When using it as I posted

...DateBooked = DateSerial(" & Right(lstDates.ItemData(i), 4) & "," & Mid(lstDates.ItemData(i), 4, 2) & "," & Left(lstDates.ItemData(i), 2)) & ") "

Yes, NOTE the dateserial is NOT part of the substituation rather its part of the SQL.

My experiance has been the opposite. When I use queries which refer to controls on a form they do not work when building it in the query builder and using that query as the recordset.

When the query has no references I have had no [problems but if it refers to a text box, etc I have always had to use the SQL directly within VBA.

The majority of my work is not so much databases as using Access as an interface for other applications, I regularly use recordsets rather than bound forms and frequently use references to controls in them.[/QUOTE]