SQL WHERE clause not working

[toon10]

Hi

I have an SQL statement I’m using within my VB app, which works fine. I want to modify it to so that some date criteria are met. I can’t seem to get the line to work.

This is the working version without criteria.
RS.Open "SELECT ou_dba.request.request_id, ou_dba.non_part_usage.person_id, ou_dba.request.zippost, ou_dba.place.global_name, ou_dba.non_part_usage.description, ou_dba.non_part_usage.bill_price, ou_dba.req_assign.promised_begin_dt, ou_dba.person.name, ou_dba.person.first_name, ou_dba.request.status, ou_dba.request.action_status FROM ou_dba.request INNER JOIN ou_dba.non_part_usage ON ou_dba.request.request_id = ou_dba.non_part_usage.request_id INNER JOIN ou_dba.req_assign ON ou_dba.request.request_id = ou_dba.req_assign.request_id INNER JOIN ou_dba.place ON ou_dba.request.place_id = ou_dba.place.place_id INNER JOIN ou_dba.person ON ou_dba.req_assign.person_id = ou_dba.person.person_id ORDER BY ou_dba.request.request_id", db, adOpenStatic, adLockOptimistic

This works fine but when I add criteria on the end it all falls apart. DateFrom and DateTo are valid dates as selected via some date pickers.

Here’s the same statement with a WHERE clause. I’ve tried a few variations but without success!

RS.Open "SELECT ou_dba.request.request_id, ou_dba.non_part_usage.person_id, ou_dba.request.zippost, ou_dba.place.global_name, ou_dba.non_part_usage.description, ou_dba.non_part_usage.bill_price, ou_dba.req_assign.promised_begin_dt, ou_dba.person.name, ou_dba.person.first_name, ou_dba.request.status, ou_dba.request.action_status FROM ou_dba.request INNER JOIN ou_dba.non_part_usage ON ou_dba.request.request_id = ou_dba.non_part_usage.request_id INNER JOIN ou_dba.req_assign ON ou_dba.request.request_id = ou_dba.req_assign.request_id INNER JOIN ou_dba.place ON ou_dba.request.place_id = ou_dba.place.place_id INNER JOIN ou_dba.person ON ou_dba.req_assign.person_id = ou_dba.person.person_id WHERE (ou_dba.req_assign.promised_begin_dt > " & DateFrom & ") AND (ou_dba.req_assign.promised_begin_dt < " & DateTo & ")"

Any ideas?

Thanks
Andrew

 

[madlarry]

Hi,

The where clause:

WHERE (ou_dba.req_assign.promised_begin_dt > " & DateFrom & ") AND (ou_dba.req_assign.promised_begin_dt < " & DateTo & ")"

This is the same date ou_dba.req_assign.promised_begin_dt in both the 'greater than' and 'less than'. It will never be true.

 

[madlarry]

Sorry, my mistake, it will be true.

Have you tried enclosing the dates in apostrophes ' ?

 

[toon10]

Yes, I want to bring back records where the date is between DateFrom and DateTo (DateFrom and DateTo are different dates).

It's not that the statement returns no records, it's that my app doesn't like the statement.

 

[toon10]

I have tried to enclose the dates in apostrophies. I think I'll try it again, maybe I got the syntax slighlty wrong!

 

[madlarry]

What is the error message that is produced?

 

[toon10]

its error 3709

"The connection cannot be used to perform this operation. It is either closed or invalid in this context

 

[SteveGlo]

Toon10

Sometime ago I had similar problems and I vaguely remember that bracketting seemed to be important. I remember creating the queries in Access and then looking at the resultant SQL to get the syntax.

Here is an example of one of my queries showing the sort of bracketting that seemed to be needed:

SELECT DISTINCT tblMaster.BoroughNo AS Borough,tblMaster.SiteNo AS Site,tblMaster.ClientScheme AS 'Client Scheme',tblProgramme.fldProposedDuctCompletionDate AS 'Proposed Duct Completion' FROM (((((((tblMaster LEFT JOIN tblWorkGroupLeader ON tblMaster.pkey = tblWorkGroupLeader.fkey) LEFT JOIN tblPROM ON tblMaster.pkey = tblPROM.fkey) LEFT JOIN tblProgramme ON tblMaster.pkey = tblProgramme.fkey) LEFT JOIN tblControllerModifications ON tblMaster.pkey = tblControllerModifications.fkey) LEFT JOIN tblPoleInstallation ON tblMaster.pkey = tblPoleInstallation.FKEY) LEFT JOIN tblPROMDetail ON tblPROM.pkey = tblPROMDetail.fldtblPROMFKEY) LEFT JOIN tblSites ON (tblMaster.BoroughNo = tblSites.BoroughNo) AND (tblMaster.SiteNo = tblSites.SiteNo)) LEFT JOIN tblGeneralInformation ON tblMaster.pkey = tblGeneralInformation.fkey WHERE ( ((tblProgramme.fldProposedDuctCompletionDate) = #06-Jul-2004# OR (tblProgramme.fldProposedDuctCompletionDate) = #30-Jul-2004#))

 

[Swi]

Why don't you use the BETWEEN statement?

RsPullCriteria.Open "SELECT * FROM Master WHERE [PMID Number] = " & _
txtPMIDNumber.Text & " AND [Entry Date] BETWEEN #" & _
mskBegDate.Text & "# AND #" & mskEndDate.Text & "# " & _
"ORDER BY [PMID Number], [Entry Date]", CONN, adOpenForwardOnly, adLockReadOnly

Swi