CHANGE DATE

[rjkealey]

I HAVE ACCESS DB WITH FIELD OF DATE ADDED (fldDate)
I AM TRYING TO COMPARE DATE ENTERED BY USER(FLDMONTH & FLDYEAR) WITH DATE IN (FLDDATE)ACCESS DB AND RESPONSE.WRITE ALL RECORDS MATCHING (FLDMONTH AND FLDYEAR).

I AM HAVING TROUBLE GETTING IT TO COMPARE DATE BY MONTH AND YEAR THEN WRITE THOSE

ANY HELP WOULD BE GRAETLY APPRECIATED

RJKEALEY AND

 

[DotNetGnat]

try using Month(fldDate) and Year(fldDate) to compare...

something like this...

...WHERE FLDMONTH=Month(request.form("fldDate")) AND FLDYEAR=Year(Request.form("fldYear")

-DNG

 

[rjkealey]

I HAVE BEEN TRYING THAT BUT CAN NOT GET IT TO WORK HERE IS MY SQL STATEMENT

sql="SELECT * from tblCharlotte WHERE fldDeed like '%" & fldYear & " AND " & fldMonth & "%' order by fldDeed"

RJK

 

[DotNetGnat]

try this:

sql="SELECT * from tblCharlotte WHERE Year(fldDeed)='" & fldYear & " AND Month(fldDeed)='" & fldMonth & "' order by fldDeed"

-DNG

 

[rjkealey]

KEEP GETTING ERROR

Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query expression 'Year(fldDeed)=' 2004 AND Month(fldDeed)='12' order by fldDeed'.

/currentMonthPropListTable.asp, line 60


THANKS AGAIN FOR YOUR HELP
RJKEALEY

 

[DotNetGnat]

oops...you are missing single quote...

try this:

sql="SELECT * from tblCharlotte WHERE Year(fldDeed)='" & fldYear & "' AND Month(fldDeed)='" & fldMonth & "' order by fldDeed"

-DNG

 

[rjkealey]

WELL I DO NOT GET ERROR ANY MORE BUT IT IS NOT COMPARING DATE IN DB
I PUT RESPONSE.WRITE FLDYEAR AND FLDDATE AND IT SHOWS CORRECTLY BUT IT WILL NOT COMPARE TO DB AND SHOW RECORDS

THANKS DNG
RJKEALEY

 

[DotNetGnat]

do a response.write on your sql string...something like this...

response.write(sql)

post the output here...

-DNG

 

[rjkealey]

SELECT * from tblCharlotte WHERE Year(fldDeed)=' ' AND Month(fldDeed)='September' order by fldDeed

 

[DotNetGnat]

First observe that your fldYear is empty...

Second...how does your fldDeed in database look like and also how does your fldMonth and fldYear input variables look like...

give some samples...

-DNG

 

[rjkealey]

STILL WORKING THIS MESS OUT

I SAW FLDYEAR WAS EMPTY FIXED THAT THOUGH
FLDDEED IN DB IS " 11/25/04 "

I HAVE A PAGE WITH 2 BUTTONS 2004 AND 2005
I WANT USER TO PRESS 2004 AND IT WILL GO TO LIST OF ALL 12 MONTHS USER SELECTS MONTH AND SEARCHES DB BY FLDdEED AND DB WRITE ONLY RECORDS WITH MONTH AND YEAR

<table align="left" width="600" cellspacing="2" cellpadding="2" border="1" frame="box" rules="all">
<th width="600" colspan="4" align="center">Deals By Month
</th>
<tr>
<td><b>RECORD COUNT</b></td>
<td><b>DATE IN</b></td>
<td><b>OWNER</b></td>
<td><b>SALESPERSON</b></td>

</tr>
<%

dim fldTack
dim fldParcelId
dim fldOwnerName
dim fldLegal
dim fldPropAdd
dim fldTitle
dim fldContract
dim fldSalesPerson
dim fldDeed
dim recCount
dim fldYear
dim fldMonth

fldYear=request.form("fldYear")
fldMonth=request.form("fldMonth")



set RS=Server.CreateObject("adodb.Recordset")


'sql="SELECT * from tblCharlotte WHERE fldDeed like '%" & fldYear & " AND " & fldMonth & "%' order by fldDeed"
sql="SELECT * from tblCharlotte WHERE Year(fldDeed)='" & fldYear & "' AND Month(fldDeed)='" & fldMonth & "' order by fldDeed"



RS.open sql, MyConn
recCount=0


If RS.eof then
response.write "<center>There are no records in the database"
response.write "<br>Please check back later</center>"


Else
Do while not RS.eof
fldTack = RS("fldTack")
fldDeed = RS("fldDeed")
fldOwnerName = RS("fldOwnerName")
fldSalesPerson=RS("fldSalesPerson")
fldContract=RS("fldContract")
if fldContract<>"SOLD" AND fldContract<>"PENDING" and fldContract<> "DEAD" and fldContract<> "SURPLUS" and fldContract<> "CONTRACT" and fldContract<> "To Recording Date " THEN
recCount=recCount+1

%>
<tr>

<td><font color="black"><%= recCount %></font></td>
<td><font color="black"><%= fldDeed %></font></td>
<td><font color="black"><%= fldOwnerName %></font></td>
<td><font color="black"><%= fldSalesPerson %></font></td>

</tr>


<%

END IF
RS.MoveNext
loop
End If


%>
</table>

 

[DotNetGnat]

OK its good that you fixed the fldYear field...i hope its now either 2004 or 2005

Now the next problem is your field fldMonth...it is showing as september or october...i mean it is showing the month names instead of month numbers which we compare later in our sql string...so change that input so that
when you do

fldMonth=request.form("fldMonth")

you have a month number in your field fldMonth

then use this sql string...

sql="SELECT * from tblCharlotte WHERE Year(fldDeed)='" & fldYear & "' AND Month(fldDeed)='" & fldMonth & "' order by fldDeed"

so if we do a response.write on the above string it should look something like this...

sql="SELECT * from tblCharlotte WHERE Year(fldDeed)='2004' AND Month(fldDeed)='8' order by fldDeed"

then it will work fine without any problems...

post back if you still get any errors...

-DNG

 

[rjkealey]

I HAVE STILL NOT BEEN ABLE TO GET THE CORRECT INFO FROM DB ARE THERE ANY TUTORIALS I CAN RUN THROUGH?

WHEN I DO THE ABOVE IT DOES NOT COMPARE DATES CORRECTLY

ANY HELP OR IDEAS WILL BE GREATLY APPRECIATED

RJKEALEY

 

[DotNetGnat]

did you try the hardcoded sql string...

sql="SELECT * from tblCharlotte WHERE Year(fldDeed)='2004' AND Month(fldDeed)='8' order by fldDeed"


??

Also post some sample data from your database...

Also i just observed that in my previous post i had a mistake...

instead of this..

fldMonth=request.form("fldMonth")

i meant

fldMonth=Month(request.form("fldMonth"))

-DNG