Problems with date formats

[redwatty]

Hi people,

I am attempting to display my company's internal meetings on the hompage of our intranet. I only want to display the meetings for this week.

I have created a number of table in Access. meeting, week_comm, week_end

The table are designed as follows

meeting Date Type

meeting ID autonumber
meeting_title text
weekday Number
meeting_time Number
week_comm Date/time
week_end Date/time
meeting_room text

The table receives the data from an input form, fields weekday and meeting_time are IDs from seperate tables.

week_comm and week_end have only two fields the ID and actual lists of dates of mondays from now until the new year.

On the home page I managed to get the data displayed ordered by day of the week and by time. However the bit that is continually stumping me is how to only display the records for the meetings which have the correct week commencing. I think I am running in circles re date formats and data type mismatches.

Does anybody have a quick or indeed slow fix for this or a place I might look for inspiration.

I am on the verge of madness.

Red

 

[CTekMedia]

What are you writing in? .asp, ColdFusion, PHP or? Peace

http://www.bluetone.biz

BT

 

[schase]

<% dim searchtime, rsMessage, dteStart 'any others needed
searchtime= CInt(request.Querystring(&quot;vTime&quot) 'convert string to integer for sql query
if IsNull(searchtime) OR searchtime=&quot;&quot; then
searchtime = -7
else
searchtime=searchtime*-1 ' negative days
end if

dteStart = DateAdd(&quot;d&quot;,searchtime,Now())
%>
<% dim searchtime2, rs, dteStart2 'any others needed
searchtime= CInt(request.Querystring(&quot;vTime2&quot) 'convert string to integer for sql query
if IsNull(searchtime) OR searchtime=&quot;&quot; then
searchtime = -7
else
searchtime=searchtime*-1 ' negative days
end if

dteStart2 = DateAdd(&quot;d&quot;,searchtime2,Now())
%>
<%
set rs = Server.CreateObject(&quot;ADODB.Recordset&quot
rs.ActiveConnection = MM_YOUR_STRING
rs.Source = &quot;SELECT * FROM YourTable WHERE fldDate between #&quot; & dteStart & &quot;# and #&quot; & Now() & &quot;# ORDER by fldDate ASC, fldReadDate DESC&quot;
rs.CursorType = 3
rs.CursorLocation = 2
rs2.LockType = 3
rs.Open()
rs_numRows = 0
%>

And down on the table to display - this will allow them to select different date periods

<form name=&quot;moretime2&quot; action=&quot;yourpage.asp&quot; method=&quot;GET&quot;>
For last
<select name=&quot;vtime&quot; Class=&quot;inputbox&quot;>
<option value=&quot;&quot;>Choose</option>
<option value=1>1 Day</option>
<option value=3>3 Days</option>
<option value=7 selected>7 Days</option>
<option value=15>15 Days</option>
<option value=30>30 Days</option>
</select>
<input type =submit value=submit name=&quot;submit&quot; class=&quot;inputsubmit&quot;>
</form> &quot;Damn the torpedoes, full speed ahead!&quot;

-Adm. James Farragut

Stuart

 

[Jables]

Try expirementing with the following.

Now forget about all those dateadd functions and get rid of all that hand coding madness. In the Ultradev recordset dialog box, use the following as your SQL statement:

SELECT * FROM YourTable
WHERE fldDate Between ((Date()-vartime) And Date())
ORDER BY fldDate ASC, fldReadDate DESC

Don't forget to add that vartime variable in the little variable box under the SQL statement...

Name: vartime
Default Value: 365 (that way if there's an error all of the data for the year is displayed)
Run-Time Value: CInt(Request.QueryString(&quot;vTime&quot) *NOTE: I haven't tried this, but you may not even need to convert the QueryString value to an integer for it to work. So you might try getting rid of the CInt function if you get an error.

I have used this method (i.e. Date()+X) successfully. I have always had trouble when using the Now() function with asp in Ultradev. Dunno why.

Don't hesitate to ask if you have further questions.

-Clay

 

[mlawson]

Hi,

Actually I have to comment that avoiding hand-coding is a little narrow sighted. Many designers migrate to UD from a design background and if they encounter problems that UD throws up, without being able to code the problem out, some (in my experience) throw their hands up and address the issue head-on within the scope of UD. Others like schase and myself appreciate that ASP came along before UD, and if you can't get the UD code to do it for you, roll your sleeves up and get stuck in.
Sorry to rant there, however I am a firm believer (like THE man Rick Curtis, Extreme Ultradev) that queries of this nature should be created within the database program Access or SQL Server and let the db handle the execution of the code.
To that end I would offer my opinion that in your database the coding that schase would work just fine (also I am biased having employed that code myself and got it working fine!). While some of the code that jables offers is ok , if it fails to have the next year's info thrown up is a worse db event that coding for a set period.

My preference would be a dateserial code that would present the current week of the year and your standard datediff or 'between..and' could be coded without having to concern yourself with overboard fallbacks.

M &quot;There are 3 kinds of people; those that can count and those that can't&quot;

 

[Jables]

Hey there,

Yeah, I totally respect the attitude that ASP came before UD. And I've spent my fair share of hours trying to change up UD code to get it to do the thing that I actually want. I'll be the first to admit that I have a lot of bad habits when it comes to programming in general. I am definitely not very qualified to dole out advice on the matter of writing elegant code.

Then again, this is an Ultradev forum and if you're just trying to get a simple recordset based on a querystring variable, this is, I think, the least frustrating way to do it *with ultradev*. And I agree, that default value of 365 was a dumb choice. I realized it right after I posted. Just use 7 then you get everything from the last week only. And that's only if there's some sort of error.

It just seems to be that your average UD user isn't looking to reinvent the wheel, but if you want to use all of your own ASP code, then go ahead. I would think that somebody wanting to debug some ASP might post in the ASP forum.

And definitely, use Access or SQL Server to get your queries straightened out before you port them to UD. This is one of the best pieces of advice I've picked up from the Tek-Tips forums. I also screwed up on that query in my last post. It should be

SELECT * FROM YourTable
WHERE fldDate Between (Date()-vartime) And Date()
ORDER BY fldDate ASC, fldReadDate DESC

..too many parentheses in that first post. Oops.

Your friendly brute force programmer,
Jables

 

[schase]

I think we all have bad habits - heck I'm now trying to go through a 137 page project to close out my session variables when they're no longer needed.

However, When things start to go out of the scope of UD4's up front capabilities, sure there is sometimes a work around.... but have fun trying to debug it.

It usually is easier to find some code, and insert away. Even with the knowledge i've gained over the past 6 months on ASP, my latest project is only about 40% handcoded - probably because about 40% of it is &quot;out of the box&quot; type stuff that would have taken me much longer to figure out - much less debug if something went wrong.

I also respectfully have to disagree with the forum choice. In my experience, if a user is used or uses UD4, than they will naturally migrate to the forum for UD4 - hoping someone can point them in the right direction. In addition, alot of folks over in the ASP forum will post methods to achieve your goal - but letting you use your mind and figure it out. Rightfully so too, they want you to learn not just cut and paste. Here, some such as I will give full code - after all, I figure if you are in this forum, you probably do not have the time to learn ASP from the &quot;hello world&quot; up to scheduling events or file handling.

As an additional note - one of Ultra Developer's very strong suits is the code view. It has a great purpose - to give you the designer far better control over your application.

&quot;Damn the torpedoes, full speed ahead!&quot;

-Adm. James Farragut

Stuart

 

[Jables]

Stuart,

After going through these posts one more time, I must say that I have misunderstood something. Without really thinking, I perceived that both the first AND third posts in this thread were by redwatty. Therefore, I thought he was presenting the ASP code as code he could not get to work when this code was actually your helpful suggestion to him. Since I thought that the code was redwatty's bugged code, I sort of brushed it off and offered my own suggestion. Now I see how my tone appeared a little disrespectful of your suggestion. Sorry about that. Definitely wasn't my intention. Anyway, just wanted to pull my foot out of my mouth there. Jeez...I've got to be more observant.

 

[schase]

AHHH

see now my foot goes great with a little salsa &quot;Damn the torpedoes, full speed ahead!&quot;

-Adm. James Farragut

Stuart

 

[redwatty]

Hi everyone,

Sorry to have opened up a can of worms or even worse a can of whup ass! I am purely an UD man at the mo. I have to admit that I get a little overawed when presented by large chunks of ASP code. I have had some programming knowledge (VB) but in a 'use it or lose it' scenario, have lost it. I have therefore resisted the tempatation to try to amend the excellent coding that has been offered. I have actually managed to cobble together a rather long winded SQL statement that makes use of manually entered database records of start and end dates of every week until the end of the year. I then fluked a statement that uses the now() function to display the records entered for the week.

Don't ask me how but it works and until the time when IT Managers are given large training budgets and time, I'll leave the hard coded ASP to you lot, the experts. I do however really appreciate all the effort you have put in.

Cheers

Red

&quot;Help me. I'm somewhere, where I don't know where I am&quot;

Homer J Simpson