loric
|
| Posted: 04/08/2002, 10:25 AM |
|
ASP...
I have this code in my form/open event:
CutOffDate = dateAdd("d", -14, date())
'set the value of the sWhere variable
swhere = "where Date_Added >= #" & CutOffDate & "#"
fldimage_url = "<img src=" & fldimage_url & " width=50 height=50 border=0></a>"
if fldValidated=1 then
fldDate_Added = Date()
end if
This works to only show records for only two weeks after they've been added then it won't display the records...this is good.
However, there is also a field called Validated that gets set to 1 or 0 by me in my admin area. I have Validated=1 in the Form/SQL to display by date added only if Validated=1. Unfortunately, it doesn't work if I have the CutOffDate code above. If I take that code out, the Validated sql works...bizarre! It's one or the other. Anyone have any thoughts on this while I still have hair? I've tried moving that code everywhere to no avail...
|
|
|
 |
Andrew B
|
| Posted: 04/08/2002, 6:51 PM |
|
Take a look at what CC does for it's input variables, this mimics that
if not (HasParam) then 'if we have no prarm, we don't need to 'and' it, but we do need to add the 'where'
sWhere = " WHERE "
else 'we already have some sort of a where clause, so we need to 'and' this one to the end of it.
sWhere = sWhere & " AND "
end if
<your code>
..
sWhere = sWhere & <your where statement>
..
</your code>
Basically, you have to ensure that the SQL statement is generated properly.
Take a look at the code CC generates to get more of a handle on it. It can be a bit tricky, since you have to remember to take into account the cases where the CC code has gotten a parameter already, and when it hasnt, as well as how your's functions.
|
|
|
|
loric
|
| Posted: 04/10/2002, 8:38 AM |
|
cc has generated this code... seems to me that this is the culprit somehow. I'm just not that versed in asp yet to properly track how..
if HasParam then
sWhere = " WHERE (Validated=1) AND (" & sWhere & ")"
else
sWhere = " WHERE Validated=1"
end if
|
|
|
|
loric
|
| Posted: 04/10/2002, 4:37 PM |
|
I really don't understand... maybe someone else has any ideas?
|
|
|
|
Nicole
|
| Posted: 04/11/2002, 1:44 AM |
|
Loric,
the exact solution depends on the place you have put Validate=1 clause. I need to know the values of sSQL and sWhere variables when the form is loading. Put print code for sSQL and sWhere variables to Open event of the form and let me know what was printed.
|
|
|
|
loric
|
| Posted: 04/11/2002, 9:57 AM |
|
Thanks Nicole!
select L.CatID as L_CatID, L.Date_Added as L_Date_Added, L.Description as L_Description, L.Link as L_Link, L.LinkID as L_LinkID, L.Title as L_Title, L.UID as L_UID, L.hit_number as L_hit_number, L.image_url as L_image_url, C.CatID as C_CatID, C.Name as C_Name from (Links L left join Category C on C.CatID=L.CatID) where Date_Added >= #3/28/2002#
|
|
|
|
Nicole
|
| Posted: 04/12/2002, 1:40 AM |
|
Loric,
as I see there's sSQL var only.
So sWhere is empty. Try this code:
CutOffDate = dateAdd("d", -14, date())
sWhere = " and Date_Added >= #" & CutOffDate & "#"
|
|
|
|
loric
|
| Posted: 04/12/2002, 8:25 AM |
|
I tried that...and get this..
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.
|
|
|
|
loric
|
| Posted: 04/15/2002, 3:32 PM |
|
I'm only able to use one at a time ....
CutOffDate = dateAdd("d", -14, date())
swhere = "where Date_Added >= #" & CutOffDate & "#"
or this in my form/sql .. Validated=1
but never both..
|
|
|
|
Nicole
|
| Posted: 04/16/2002, 4:31 AM |
|
Loric,
ok, lets try to build totally custom where clause. Do not put condition to WHERE field on SQL tab, just custom code in Open event:
CutOffDate = dateAdd("d", -14, date())
swhere = "where Validated=1 and Date_Added >= #" & CutOffDate & "#"
|
|
|
|
loric
|
| Posted: 04/16/2002, 7:44 AM |
|
Thanx Nicole, you rok!! Works :) I was so close the whole time...didn't think of that ;)
|
|
|
|
|
