Problems with SQL statement!

[BarryMVS]

Hi all,

I'm having problems with this sql statement.

Could you please look at it and advise on any problems.

sdate & edate = dd/mm/yyyy format which is the same format as the data in the access database.

SQL statement is:

strSql1 = "SELECT * FROM (([Service Records] LEFT JOIN JobTypeTable ON [Service Records].[Call Type] = JobTypeTable.samsID) LEFT JOIN Customers ON [Service Records].[Customer ID] = Customers.[Customer ID]) LEFT JOIN [Status Codes] ON [Service Records].[Status] = [Status Codes].[Status] WHERE [Service Records].[Call Type] = 1 AND Outstanding = True AND ([Service Records].[Date Call Placed] < " & sdate & " AND [Service Records].[Date Call Placed] > " & edate & ") OR [Service Records].[Call Type] = 7 AND Outstanding = True AND ([Service Records].[Date Call Placed] < " & sdate & " AND [Service Records].[Date Call Placed] > " & edate & ") OR [Service Records].[Call Type] = 2 AND Outstanding = True AND ([Service Records].[Date Call Placed] < " & sdate & " AND [Service Records].[Date Call Placed] > " & edate & ") OR [Service Records].[Call Type] = 6 AND Outstanding = True AND ([Service Records].[Date Call Placed] < " & sdate & " AND [Service Records].[Date Call Placed] > " & edate & ") ORDER BY [Date Call Placed];"
set rs1 = conn.Execute(strSql1)

Any help is most apprechiated. I'm expecting it to be something silly that I just keep missing.

I know that data exists to be found and all I get is a blanks results table. No error message.

Cheers,

Barry

ICT Network Administrator
IT Services Manager

 

[jksmi]

HI,

dates need to have #'s around them (i.e.) #01/01/04#, but also, SQL doesnt like dd/mm/yyy format - try mm/dd/yy instead...

HTH, Jamie
FAQ219-2884

 

[BarryMVS]

jksmi,

Thanks for the advise.

I tried the #'s arround the dates, but this made no difference. I haven't changed the format as I thought it would need to be the same as the data in the database?

I don't think changing the format would help as I have data listed for the 1st Aug 2004 and 8th Jan 2004 yet neither get returned.

Any other ideas or is it that the statement is too long?

Cheers,

Barry

ICT Network Administrator
IT Services Manager

 

[jksmi]

Hi,

you can shorten this statement by using IN - i.e.

strSql1 = "SELECT * 
   FROM (
      ([Service Records] LEFT JOIN JobTypeTable
         ON [Service Records].[Call Type] = JobTypeTable.samsID)
         LEFT JOIN Customers
            ON [Service Records].[Customer ID] = Customers.[Customer ID])
       LEFT JOIN [Status Codes] ON [Service Records].[Status] = [Status Codes].[Status]
   WHERE [Service Records].[Call Type] = 1
      AND Outstanding = True
      AND ([Service Records].[Date Call Placed] < #" & sdate & "#
      AND [Service Records].[Date Call Placed] > #" & edate & "#)
      OR [Service Records].[Call Type] in (7, 2, 6)
ORDER BY [Date Call Placed];"
set rs1 = conn.Execute(strSql1)

do you definitely have the greater than and less than the right way around? you could also use BETWEEN for the dates, i.e. [SERVICE RECORDS].[DATE CALL PLACED] BETWEEN (#01/01/04# AND #01/31/04#) will return everything between 1st of Jan to 31st of Jan exclusive...

HTH, Jamie
FAQ219-2884

 

[PHV]

Have you tried something like this ?
... AND [Service Records].[Date Call Placed] Between #" & Format(sdate,'m/dd/yyyy') & "# And #" & Format(edate, 'm/dd/yyyy') & "# ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[KenReay]

Hi

In addition to the # to bound the date you must use a date in mm/dd/yy format or yyyy/mm/dd format, even if you are in UK, or otehr locale which uses dd/mm/yy. It is worth pointing out here that the dates are stored in the same format in the databse regardless of the locale, it is merely the human readable format which changes. But as I say in SQL strings you need to use USA style mm/dd/yy or the universal yyyy/mm/dd, remember the #..#

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[BarryMVS]

Thanks all, I got sucsess.

The finished SQL statement is as follows.
(I have also included the date format code.)

' sdate format change
m_lYear = DatePart("yyyy", sdate)
m_lMonth = DatePart("m", sdate)
m_lDay = DatePart("d", sdate)
sdate = m_lMonth & "/" & m_lDay & "/" & m_lYear
' edate format change
m_lYear = DatePart("yyyy", edate)
m_lMonth = DatePart("m", edate)
m_lDay = DatePart("d", edate)
edate = m_lMonth & "/" & m_lDay & "/" & m_lYear

' Total number of service calls
strSql1 = "SELECT * FROM (([Service Records] LEFT JOIN JobTypeTable ON [Service Records].[Call Type] = JobTypeTable.samsID) LEFT JOIN Customers ON [Service Records].[Customer ID] = Customers.[Customer ID]) LEFT JOIN [Status Codes] ON [Service Records].[Status] = [Status Codes].[Status] WHERE [Service Records].[Call Type] = 1 AND Outstanding = True AND [Service Records].[Date Call Placed] BETWEEN #" & sdate & "# AND #" & edate & "# OR ([Service Records].[Call Type] = 7 AND Outstanding = True AND [Service Records].[Date Call Placed] BETWEEN #" & sdate & "# AND #" & edate & "# OR ([Service Records].[Call Type] = 2 AND Outstanding = True AND [Service Records].[Date Call Placed] BETWEEN #" & sdate & "# AND #" & edate & "# OR ([Service Records].[Call Type] = 6 AND Outstanding = True AND [Service Records].[Date Call Placed] BETWEEN #" & sdate & "# AND #" & edate & "#))) ORDER BY [Date Call Placed];"
set rs1 = conn.Execute(strSql1)

Thanks for all your help.


Barry

ICT Network Administrator
IT Services Manager