I am having problems with an SQL qu

[PeteCan]

I am having problems with an SQL query from an .asp page to an access database, can someone please help!!!.

The fields in the database are as follows:
ID
SupportTeamName
StartDate
EndDate
FirstPersonInitials

Basically I want to select the row where the Date falls between StartDate and EndDate, the SQL I have is

StrSQL="SELECT FirstPersonInitials, SecondPersonInitials FROM CalloutDetails " _
& "WHERE SupportTeamName Like '%" & Replace(SupportRef, "'", "''&quot & "%' " _
& &quot;and WHERE StartDate >= #&quot; & CurrentDte2 & &quot;# AND EndDate <=#&quot; & CurrentDte2 & &quot;&quot;

SupportRef is a variable
CurrentDte2 is todays date converted to mm/dd/ccyy format.

 

[r937]

take out the 2nd WHERE, it's just AND, not AND WHERE

rudy

 

[PeteCan]

I've done that and am now getting

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression 'SupportTeamName Like '%ANDI%' and StartDate >= #04/2003/15# AND EndDate <=#04/2003/15'.

 

[dodge20]

The apostrophe( ' ) in user input causes this error. Looking at your query, I think it is the single quote with the double quotes around your %

Like '%&quot;
&quot;%'

Dodge20

 

[r937]

you need a closing octothorpe (#) to terminate the EndDate date string

 

[PeteCan]

Adding the # to the end removed the SQL error, however the problem I am getting now is that nothing is output from the query.
The SQL code is now

StrSQL=&quot;SELECT * FROM CalloutDetails &quot; _
& &quot;WHERE SupportTeamName Like '%&quot; & Replace(SupportRef, &quot;'&quot;, &quot;''&quot & &quot;%' &quot; _
& &quot;and StartDate >= #&quot; & CurrentDte2 & &quot;# and EndDate <=#&quot; & CurrentDte2 & &quot;

which gives SQL output of

SELECT * FROM CalloutDetails WHERE SupportTeamName Like '%ANDI%' and StartDate >= #04/15/2003# and EndDate <=#04/15/2003#

I have isolated the SQL and tested if both the startDate and EndDate work independent of each other, which they do and the output from both tests is correct, so it is something to do with how they are combined. IDEAS please??

 

[r937]

how many rows have startdate and enddate both equal to #04/15/2003# ?

because that's what you're saying:

where s >= x and e <= x

actually the greater and less parts are not required, if you use the same date for both start and end, only start=end=x satisfies the conditions

what you want is x between start and end

where s <= x and x <= e

note: datetimes include time, so if your dates have a time component, it's better to say

where s <= x and x < e + 1

e.g. for all of april,

where StartDate <= #04/01/2003# and EndDate < #05/01/2003#



rudy

 

[PeteCan]

No two start or End Dates will be the same.

The start or end date maybe the actual date or the date may fall between them.

So where we have a date of 15/04/2003, this falls within the row where StartDate=14/04/2003 and EndDate=18/04/2003,
Hence why I have coded it that way, so don't understand why it doesn't work.!!!

and the dates don't have a time component.

 

[Rick39]

Have you tried replacing the % with *?

StrSQL=&quot;SELECT * FROM CalloutDetails &quot; _
& &quot;WHERE SupportTeamName Like '*&quot; & Replace(SupportRef, &quot;'&quot;, &quot;''&quot & &quot;*' &quot; _
& &quot;and StartDate >= #&quot; & CurrentDte2 & &quot;# and EndDate <=#&quot; & CurrentDte2 & &quot;

 

[r937]

pete, yes, i realize that's what you were trying to do but you didn't code it that way

asterisks instead of percent signs for wildcard is also worth trying (i only have access 97 and it definitely requires asterisks)

there might be an ADO/DAO difference, i'm sure i heard somewhere that percents are okay, but i cannot find that bookmark at the moment

see also

http://msdn.microsoft.com/library/d...-us/off2000/html/acconWildcardCharactersS.asp

anyhow, the date range thing was the most important

and wouldn't you know it, i did it in a hurry and got the sign wrong!!

you want

startdate <= currentdate and currentdate <= enddate

rudy