ADO - SQL Not working as intended

[dynamictiger]

Below is a sub that is not working. Problem is i do not know where the fault is.

The SQL statement I am using is based on a query that does work, so I do not think the error is in there.

The problem is that if I pass the datCurrentServiceDate as the 12th of August, and intServicePerson as 2, I get 0 records returned.

If I then open the form and run the query I am returning the two jobs previously booked for service person 2 on 12/8/02.

As you can see I have started to break down the SQL statement by removing and commenting out the serviceperson lines, but this returns 0 records too.

I am not too familiar with ADO, so I could have made an error in setting this up.

Any help, advice, guesses or stabs in the dark welcome.



Private Sub DecideTime()

'We SQL statement to have a look at the individual diary and derive a time
'based on estimates in previous jobs as to when teh service person should be on site
'if the time is promised we add an extra allowance on both sides

Dim rstTime As ADODB.Recordset
Dim datCurrentServiceDate As Date
Dim intServicePerson As Integer


Set rstTime = New ADODB.Recordset

'Allocate the calculated service date to the SQL statement
datCurrentServiceDate = Format(Me.ServiceDate, "Medium Date&quot

'Allocate the passed in serviceperson as the nominated service diary
intServicePerson = Me.ServicePerson

Debug.Print datCurrentServiceDate & " " & intServicePerson

rstTime.Open "SELECT tblServiceTime.ServiceDate, tblServiceTime.ServiceRequired, " & _
"tblServiceTime.TimePromised, tblServiceTime.AllowedTime, " & _
"tblServiceTime.ServicePerson, " & _
"DateAdd('h',[AllowedTime],[TimePromised]) AS NextTime " & _
"FROM tblServiceTime " & _
"WHERE (((tblServiceTime.ServiceDate) = " & _
"#" & datCurrentServiceDate & " #)) " & _
"WITH OWNERACCESS OPTION;", _
CurrentProject.Connection, adOpenStatic

Debug.Print rstTime.RecordCount

' "And ((tblServiceTime.ServicePerson) = " & _
" " & intServicePerson & &quot) "
Do

Debug.Print rstTime!NextTime

rstTime.MoveNext

Loop Until rstTime.EOF





End Sub

 

[cmmrfrds]

As an aid in debugging, I suggest putting your SQL Statement into a variable and then using the variable in the open. It is easier to read and debug.

Dim SqlString as string

SqlString = " your sql etc........"
Debug.print SqlString

rstTime.Open SqlString,CurrentProject.Connection, adOpenStatic

I always use Format(Me.ServiceDate, "Short Date&quot to avoid any problems with the time part of the date.

WHERE (((Format(tblServiceTime.ServiceDate,"Short Date&quot = " & "#" & datCurrentServiceDate & "#"

 

[dynamictiger]

I have tried about 5 or 6 combinations of the line

WHERE (((Format(tblServiceTime.ServiceDate,"Short Date&quot = " & "#" & datCurrentServiceDate & "#"

in the current configuration it looks like

"WHERE (((Format(tblServiceTime.ServiceDate,'Short Date') ) = " & "#" & datCurrentServiceDate & "#" & &quot) "

In all configurations that don't turn my VBE a delightful red colour, it is failing to return a record. If I take this line out it returns the current count of records and times promised no problem.

I found the table full of nulls, which was to be expected as I had not built in any code to stop the record saving. I took these out to see if they caused a problem so that is eliminated.

I can't decide if the Format command is causing a ptoblem or the search against currentServiceDate.

The query works fine, so why would the SQL not work? The date passing in is correct, the date is formatted to Medium Date this should not cause a problem.

I do not understand this at all.

 

[Paddyo]

It might have something to do with the date format -

in your first post you have formatted datCurrentServiceDate as a medium date but your are using the short date format for the field in your SQL statement.

 

[gol4]

I think the problem is you are passing single Quotes in your function
If I do this
?format(date,"short date&quot
8/9/2002
?format(date,'short date') this give error expected expression

the sql in your first post needs double quotes in the dateadd and then needs an & and " to join it to the rest of the string you are trying to construct

I think what is happening while constructing the code if you put " in the function it expects the end of the string so rather then construct the string you replace it with single quotes when the proper way is to treat it for what is is, a function

strsql = "select fld, " & dateadd("d",1,date) & " from tbl1"
not
strsql = "select fld, dateadd('d',1,date) from tbl1"

"SELECT tblServiceTime.ServiceDate, tblServiceTime.ServiceRequired, " & _
"tblServiceTime.TimePromised, tblServiceTime.AllowedTime, " & _
"tblServiceTime.ServicePerson, " & _
DateAdd("h",[AllowedTime],[TimePromised])& " AS NextTime " & _
"FROM tblServiceTime " & _
"WHERE (((tblServiceTime.ServiceDate) = " & _
"#" & datCurrentServiceDate & " #)) " & _
"WITH OWNERACCESS OPTION;", _

 

[cmmrfrds]

Paste in the sql statement from the variable that is displayed in the debug that way we can see how it gets resolved.

Dim SqlString as string

SqlString = " your sql etc........"
Debug.print SqlString

 

[dynamictiger]

The outcome of the strSQL as shown is:

SELECT tblServiceTime.ServiceDate, tblServiceTime.ServiceRequired, tblServiceTime.TimePromised, tblServiceTime.AllowedTime, tblServiceTime.ServicePerson, AS NextTime FROM tblServiceTime WHERE (((tblServiceTime.ServiceDate) = #12/08/2002 #)) WITH OWNERACCESS OPTION;

The DateAdd("h",[AllowedTime],[TimePromised])is returning no value in the SQL statement.

 

[cmmrfrds]

I don't see where this is part of the sql statement.
DateAdd("h",[AllowedTime],[TimePromised])
or did you leave it out on the example.

Is your where clause working now?

I don't think the snytax you have for the dateadd is going to work. Are you trying to calculate hours between the 2 dates?

 

[dynamictiger]

The dateadd part of the SQl statement did not print in the debug window, hence tblServiceTime.ServicePerson, AS NextTime FROM tblServiceTime , should read something like
tblServiceTime.ServicePerson, DateAdd("h",[AllowedTime],[TimePromised])AS NextTime FROM tblServiceTime .

The SQL Statement is still returning a count of 0 records, the count should be around 3, according to the query.

 

[cmmrfrds]

The quotes are not being put in the string correctly.

"tblServiceTime.ServicePerson, " & _
" DateAdd(""h"",[AllowedTime],[TimePromised])" & _
" AS NextTime " & _
"FROM tblServiceTime " &

OR
Dim qt as string
qt = Chr$(34) '- a double quote

"tblServiceTime.ServicePerson, " & _
" DateAdd(" qt & "h" & qt & _
",[AllowedTime],[TimePromised]) AS NextTime " & _
"FROM tblServiceTime " &

'- the where clause
"(((Format(tblServiceTime.ServiceDate," & qt & Short Date & _
qt & &quot = #08/12/2002 #)) "