How to get 3 datefields within a range?

[crashc123]

I have 3 date fields in my db and I want to be able to brng up all records that have any one of those fields between certain dates. example, if any of the dates in those 3 fields fall in this week bring the record up

Select * FROM mytable WHERE datefieldA >= today AND datefieldA <= weekfromtoday OR datefieldB >= today and <=weekfromtoday OR datefieldC >=today and datefieldC <=weekfromtoday

when I try what I have above I get :
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression
'datefieldA >= #12/28/2001# And datefieldA <= #1/4/2002# Or datefieldB >= #12/28/2001# And datefieldB <= #1/4/2002# Or datefieldC >= #12/28/2001# And datefieldC <= #1/4/2002#'.
/week.asp, line 71

 

[programsecrets]

Here's a guess:

Was the whole SQL string concatenated properly? Perhaps you did something like this:

SqlText = &quot;SELECT * FROM MSG&quot;
SqlText = &quot;WHERE datefieldA >= today&quot;

when it should be something like this???

SqlText = &quot;SELECT * FROM mytable&quot;
SqlText = SqlText & &quot; WHERE datefieldA >= today&quot;

 

[crashc123]

Actually the problem turned out to be that one of my date field's name started with a number. I changed it from 8110Due to EightyoneTenDue and that took care of the problem. I didn't want to do that in the beginning because I had to change all of my other asp pages and the database field, but......

here's what the working statement looks like:

Dim t ' Today's Date
Dim vbShortDate ' So the Date can be mm/dd/yy
vbShortDate = 2
Dim w ' Week from today's date

t = FormatDateTime(Now(),vbShortDate) 'Today's date
w = DateAdd(&quot;d&quot;,&quot;7&quot;,Now()) 'Week from today's date
w = FormatDateTime(w,vbShortDate)

' MapPath of virtual database file path to a physical path.
' If you want you could hard code a physical path here.
' Use Line below only if in the same folder, no sub folders
'strDBPath = Server.MapPath(&quot;db1.mdb&quot
%>
<p align=&quot;left&quot;><b>Projects , 8110 Forms, or DTR Due this week ( from <%= t %> to <%= w %>):</b></p>
<% ' Create an ADO Connection to connect to the sample database.
' We're using OLE DB but you could just as easily use ODBC or a DSN.
Set cnnSearch = Server.CreateObject(&quot;ADODB.Connection&quot

' This line is for the Access sample database:
' us below if using MapPath above
'cnnSearch.Open &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; & strDBPath & &quot;;&quot;

cnnSearch.Open &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\

http://wwwroot\fpdb\db1.mdb&quot;

' Now we do our statement to query db.
'find records where due date is between today and a week from today
strSQL = &quot;Select * FROM Projects &quot; _
& &quot;WHERE DateDue >= #&quot;& t &&quot;# And DateDue <= #&quot;& w &&quot;# &quot; _
& &quot;Or eightyonetenDue >= #&quot;& t &&quot;# And eightyonetenDue <= #&quot;& w &&quot;# &quot; _
& &quot;Or DTRDue >= #&quot;& t &&quot;# And DTRDue <= #&quot;& w &&quot;# &quot; _
& &quot;ORDER by DateDue&quot;



Thanks for taking the time, You are very appreciated!
Happy New Year!!!!

Sonya
s-)

 

[programsecrets]

I'm happy you figured it out. Congratulations... and as they say, you go girl!

 

[crashc123]

Thanks again programsecrets
Happy New Year!

Sonya
*:->*