Good catch
Didn't even check that field name against the reserved ones at
http://www.aspfaq.com/show.asp?id=2080
- best to just create a new field in the DB (or rename the existing one) w/ an unreserved name - say EditDate
--
| You may have to place the Timestamp field in square brackets -
| [Timestamp] - in the grey webbot code. Timestamp is a reserved word.
| --
| Ron Symonds
| Microsoft MVP (FrontPage)
| Reply only to group - emails will be deleted unread.
|
| | > Time in date/time field is not relevant
| > - it still has a date element to which you are adding (- 7) days
| >
| > Don't change your PC date, just change the value of - 7 to whatever
| > # days your data is aged to (0 is today)
| >
| > On a new page make a std DBRW query to see if you get any results
| > showing all DB fields and their values from the DB (look for
| > errors in CaSe or data types and what both Timestamp and D1 return)
| > Then add 1 criteria at a time to see where you are generating a
| > field mismatch or error
| >
| >
| > --
| >
| > _____________________________________________
| > SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
| > "Warning - Using the F1 Key will not break anything!" (-;
| > To find the best Newsgroup for FrontPage support see:
| >
http://www.net-sites.com/sitebuilder/newsgroups.asp
| > _____________________________________________
| >
| >
| > | > | Ok the page comes up, still shows an error,
| > | In the search field the ChkDate is 7 days previous, Active is yes,
| > when
| > | submit query is clicked no records are show.
| > |
| > | I changed the date on my PC so that the ChkDate would match the
| > date of the
| > | records in the database and still no records found.
| > |
| > | Is there a way to do it by running a custom query, would it be
| > easier if i
| > | added another field with a date which is only dd/mm/yyyy and not
| > having the
| > | time.
| > |
| > | only thing is i don't know how to automatically add the date in a
| > hidden
| > | field.
| > |
| > | I've had a look at some Access query stuff but most of it is
| > pretty vaig, i
| > | suppose i'd ave to fully commit some quality time to it if i am to
| > learn
| > | anything, can you suggest any good books for a beginner to learn
| > from.
| > |
| > |
| > | Al....
| > |
| > | "Stefan B Rusynko" wrote:
| > |
| > | > Repost your new SQL string and criteria
| > | > Did you name the form fields and set their values as per my
| > original post
| > | >
| > | > PS
| > | > for more meaningful DBRW error messages open the hidden folder
| > /_fpclass/ and edit the fpdbrgn1.inc file
| > | >
| > | > At about line 19, change :
| > | > fp_DEBUG = False
| > | > to :
| > | > fp_DEBUG = True
| > | >
| > | >
| > | > --
| > | >
| > | > _____________________________________________
| > | > SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
| > | > "Warning - Using the F1 Key will not break anything!" (-;
| > | > To find the best Newsgroup for FrontPage support see:
| > | >
http://www.net-sites.com/sitebuilder/newsgroups.asp
| > | > _____________________________________________
| > | >
| > | >
| > | > | > | Were a step further, the page is at least loading up but
| > there's an error
| > | > |
| > | > | Database Results Wizard Error
| > | > | The operation failed. If this continues, please contact your
| > server
| > | > | administrator.
| > | > |
| > | > | everything matches what you specified
| > | > |
| > | > | i also tried BlnActive = Yes and BlnActive = "Yes" still the
| > same error
| > | > |
| > | > |
| > | > | thanks Al....
| > | > |
| > | > | "Stefan B Rusynko" wrote:
| > | > |
| > | > | > Not quite
| > | > | >
| > | > | > These 3 line of code go in separate lines!
| > | > | > <%
| > | > | > Dim ChkDate, blnActive
| > | > | > ChkDate = DateAdd("d", -7, Date())
| > | > | > blnActive = True
| > | > | > %>
| > | > | >
| > | > | > Note:
| > | > | > You may need to use blnActive = Yes or blnActive = "Yes"
| > because of your field type being yes/no
| > | > | > - recommend instead changing your DB in Access to make the
| > Yes/No field a True/False field
| > | > | >
| > | > | > Add the code at the top of the page in code view (before the
| > <head> tag)
| > | > | > - because if where the DBRW will add the form code
| > | > | >
| > | > | > Set your Criteria to use the new variables - do not set
| > defaults!
| > | > | >
| > | > | > For Criteria set (using an AND)
| > | > | > Timestamp LessThan ChkDate
| > | > | > D1 Equals Active
| > | > | >
| > | > | > Should give you (each of below will be on 1 line)
| > | > | >
| > | > | > fp_sQry="SELECT * FROM Results WHERE (Timestamp <
| > '::ChkDate::' AND D1 = ::Active:
"
| > | > | >
| > | > | > fp_sDefault="ChkDate=&Active="
| > | > | >
| > | > | >
| > | > | > --
| > | > | >
| > | > | > _____________________________________________
| > | > | > SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
| > | > | > "Warning - Using the F1 Key will not break anything!" (-;
| > | > | > To find the best Newsgroup for FrontPage support see:
| > | > | >
http://www.net-sites.com/sitebuilder/newsgroups.asp
| > | > | > _____________________________________________
| > | > | >
| > | > | >
| > | > | > | > | ok, here goes
| > | > | > |
| > | > | > | the time and date field is called Timestamp
| > | > | > | the checked field is called D1 (using test database) with
| > either a yes or no
| > | > | > |
| > | > | > | <% Dim ChkDate, blnActive ChkDate = DateAdd("d", -7,
| > Date()) ' subtracts 7
| > | > | > | days from today blnActive = True %>
| > | > | > |
| > | > | > | the code above was added in above the first grey txt (in
| > FP)
| > | > | > |
| > | > | > | criteria =
| > | > | > | Timestamp LessThan Timestamp
| > | > | > | D1 Equals D1
| > | > | > |
| > | > | > | Defaults =
| > | > | > | Timestamp <%=ChkDate%>
| > | > | > | D1 <%=blnActive%>
| > | > | > |
| > | > | > | code snippets as reqyuested
| > | > | > |
| > | > | > | fp_sQry="SELECT * FROM Results WHERE (Timestamp <
| > '::Timestamp::' AND D1 =
| > | > | > | ':
1::')"
| > | > | > | fp_sDefault="Timestamp=<%=ChkDate%>&D1=<%=blnActive%>"
| > | > | > |
| > | > | > |
| > | > | > | So that you don't think i'm being ignorant it's now time
| > for me to go,
| > | > | > | thanks for your help, i'll check in tomorrow.
| > | > | > |
| > | > | > |
| > | > | > | Al....
| > | > | > | "Stefan B Rusynko" wrote:
| > | > | > |
| > | > | > | > Do not change the new variables ChkDate or blnActive to
| > match your DB field names
| > | > | > | > - they have nothing to do w/ your DB field names (they
| > are variables to compare to)
| > | > | > | >
| > | > | > | > If the date field in your DB is a timestamp (date/time)
| > in your DB it will still work,
| > | > | > | > - what are the Actual field names for the date field
| > (timestamp) and "checked" field
| > | > | > | > - is your "checked" field (for my fictive "Active" field
| > name) a True/False (preferred) or Yes/No field type in Access
| > | > | > | >
| > | > | > | > You would use your field names Only in the DBRW criteria
| > (instead of my fictive fields named DatedEdited and Active) in
| > | > | > | > DatedEdited Less Than ChkDate
| > | > | > | > And
| > | > | > | > Active Equals blnActive
| > | > | > | >
| > | > | > | > Post a snippet of the Sql select you have (from
| > fp_sQry="...." and the names of your table plus the 2 DB field
| > names/types
| > | > | > | > --
| > | > | > | >
| > | > | > | > _____________________________________________
| > | > | > | > SBR @ ENJOY (-: [ Microsoft MVP -
| > FrontPage ]
| > | > | > | > "Warning - Using the F1 Key will not break anything!"
| > (-;
| > | > | > | > To find the best Newsgroup for FrontPage support see:
| > | > | > | >
http://www.net-sites.com/sitebuilder/newsgroups.asp
| > | > | > | > _____________________________________________
| > | > | > | >
| > | > | > | >
| > message | > | > | > | > | stefan thanks as it looks as though you put a bit of
| > effort into helping me
| > | > | > | > | but unfortunatley it didn't work, the page it's self
| > wont actually load, i'm
| > | > | > | > | not sure if it's the
| > | > | > | > | <%
| > | > | > | > | Dim ChkDate, blnActive
| > | > | > | > | ChkDate = DateAdd("d", -7, Date())
| > | > | > | > | ' subtracts 7 days from today
| > | > | > | > | blnActive = True
| > | > | > | > | %>
| > | > | > | > |
| > | > | > | > | I did swap the ChkDate and Active for what they are
| > called in my database,
| > | > | > | > | also the date used is a timestamp which has time and
| > date. then followed the
| > | > | > | > | rest of the instructions.
| > | > | > | > |
| > | > | > | > | but then again if the information was wrong i'd at
| > least think the page
| > | > | > | > | would load up with an error. knowing me i've probably
| > overlooked something
| > | > | > | > | stupid.
| > | > | > | > |
| > | > | > | > | thanks for your help and of course keep up the good
| > work
| > | > | > | > |
| > | > | > | > |
| > | > | > | > | Al....
| > | > | > | > |
| > | > | > | > |
| > | > | > | > | "Stefan B Rusynko" wrote:
| > | > | > | > |
| > | > | > | > | > You can do it in 1 step w/ the DBRW
| > | > | > | > | >
| > | > | > | > | > To find your "aged" date use a new calculated
| > variable (ChkDate) to age your DB date field named as say:
| > DateEdited,
| > and
| > | > | > lets
| > | > | > | > say
| > | > | > | > | > your other field to check is named Active (a
| > True/False DB field if checked = True),
| > | > | > | > | > Set their initial values, by adding at the top of
| > the page in Code view (before any the DBRW code)
| > | > | > | > | >
| > | > | > | > | > <%
| > | > | > | > | > Dim ChkDate, blnActive
| > | > | > | > | > ChkDate = DateAdd("d", -7, Date())
| > | > | > | > | > ' subtracts 7 days from today
| > | > | > | > | > blnActive = True
| > | > | > | > | > %>
| > | > | > | > | >
| > | > | > | > | > Now Edit your DBRW criteria (More Options) to add
| > the Criteria
| > | > | > | > | >
| > | > | > | > | > DatedEdited Less Than ChkDate
| > | > | > | > | > And
| > | > | > | > | > Active Equals blnActive
| > | > | > | > | >
| > | > | > | > | > Finish by selecting the Add a Search form option in
| > the DBRW
| > | > | > | > | >
| > |> | > | > | > In the search form created by the DBRW
| > | > | > | > | > in the Form Field named ChkDate
| > | > | > | > | > set the initial value to <%=ChkDate%>
| > | > | > | > | > and for the form field named Active
| > | > | > | > | > set the initial value to <%=blnActive%>
| > | > | > | > | > (you can make them hidden form fields if you want by
| > setting the fields to type="hidden" instead of type="text" and
| > | > delete
| > | > | > all
| > | > | > | > text
| > | > | > | > | > label cells)
| > | > | > | > | >
| > | > | > | > | > Run the query page in the browser to see the
| > detailed results
| > | > | > | > | >
| > | > | > | > | > If you just want to show a single result of the # of
| > records found (instead of a table of all the matching records
| > | > found)
| > | > | > in
| > | > | > | > the
| > | > | > | > | > DBRW set the table to not show a header or split
| > results per page,
| > | > | > | > | > Then in design view select ONLY the results data row
| > (between the 2 yellow DBRW tags) in the Quick Tag selector and
| > | > Delete
| > | > | > that
| > | > | > | > row
| > | > | > | > | > only
| > | > | > | > | > Switch to Code view and add the FP variable for
| > record count (after the closing table tag) as say:
| > | > | > | > | > <p>The Record count is: <%=fp_iCount %></p>
| > | > | > | > | >
| > | > | > | > | > You can also carefully delete the now empty table
| > tags (not the DBRW content inside of them) by deleting in code
| > view
| > | > the 4
| > | > | > tags
| > | > | > | > | > <table width="100%"> <tbody> and </tbody></table>
| > | > | > | > | >
| > | > | > | > | > ENJOY
| > | > | > | > | >
| > | > | > | > | > --
| > | > | > | > | >
| > | > | > | > | > _____________________________________________
| > | > | > | > | > SBR @ ENJOY (-: [ Microsoft MVP -
| > FrontPage ]
| > | > | > | > | > "Warning - Using the F1 Key will not break
| > anything!" (-;
| > | > | > | > | > To find the best Newsgroup for FrontPage support
| > see:
| > | > | > | > | >
| >
http://www.net-sites.com/sitebuilder/newsgroups.asp
| > | > | > | > | > _____________________________________________
| > | > | > | > | >
| > | > | > | > | >
| > message
| > | > | > | > | > | > | everything was created with frontpage including
| > the database, i suppose if i
| > | > | > | > | > | could get it to count the records using the time
| > stamp that would be a start,
| > | > | > | > | > | i've looked a various help files but nothing can
| > tell me how to count records
| > | > | > | > | > | or how to only have last 7 days worth of records.
| > | > | > | > | > |
| > | > | > | > | > | Thanks Stefan, i guess i'll just keep searching.
| > | > | > | > | > |
| > | > | > | > | > | "Stefan B Rusynko" wrote:
| > | > | > | > | > |
| > | > | > | > | > | > Not possible to just post a "snippet" for you to
| > do it
| > | > | > | > | > | > - depends on your DB structure, fields.
| > connections
| > | > | > | > | > | >
| > | > | > | > | > | > But you can try playing w/ the FP DBRW (database
| > results wizard) if you have the DB created
| > | > | > | > | > | > - it should let you set both those criteria as
| > one step to show only results that meet both criteria
| > | > | > | > | > | > See FP help on using DB
| > | > | > | > | > | >
| > | > | > | > | > | > --
| > | > | > | > | > | >
| > | > | > | > | > | > _____________________________________________
| > | > | > | > | > | > SBR @ ENJOY (-: [ Microsoft MVP -
| > FrontPage ]
| > | > | > | > | > | > "Warning - Using the F1 Key will not break
| > anything!" (-;
| > | > | > | > | > | > To find the best Newsgroup for FrontPage support
| > see:
| > | > | > | > | > | >
| >
http://www.net-sites.com/sitebuilder/newsgroups.asp
| > | > | > | > | > | > _____________________________________________
| > | > | > | > | > | >
| > | > | > | > | > | >
| > | > | > | > | > | > "Sproul" <
[email protected]>
| > wrote in message
| > | > | > | > | > | > | > | > | I've got a database setup, i want to be able
| > to count the number of records
| > | > | > | > | > | > | created within the last seven days and retunr
| > only the number, next i need to
| > | > | > | > | > | > | look ath those records created and return the
| > number of records with a yes in
| > | > | > | > | > | > | a specified column.
| > | > | > | > | > | > |
| > | > | > | > | > | > | Just to say i'm new to this but would like to
| > learn more if anyone know's
| > | > | > | > | > | > | any websites i can get usefull free
| > information from.
| > | > | > | > | > | > |
| > | > | > | > | > | > | Al....
| > | > | > | > | > | >
| > | > | > | > | > | >
| > | > | > | > | > | >
| > | > | > | > | >
| > | > | > | > | >
| > | > | > | > | >
| > | > | > | >
| > | > | > | >
| > | > | > | >
| > | > | >
| > | > | >
| > | > | >
| > | >
| > | >
| > | >
| >
| >
|
|
