Access query versus SQL statement 1

[hallm]

In my online database I'm using a query that I created in Access.  Ex.  SELECT * FROM test (where test is the name of the query.  Is there any benefit that I'm not aware of to just writing the query in SQL to begin with and keying it SQL statement.  Right now the only problem that I'm having is the database is about 4 mb and I'm on a dialup.  So everytime I change my queries, I have to re-upload the entire database.

 

[Karl Blessing]

you can always write your SQL Strings on the website, and not have to change the database at all, and it sounds like you need to compress your database(in Access, tools, compress) <p>Karl<br><a href=mailto:kb244@kb244.8m.com>kb244@kb244.8m.com</a><br><a href=

http://kb244.8m.com>

</a><br>Experienced in : C++(both VC++ and Borland),VB1(dos) thru VB6, Delphi 3 pro, HTML, Visual InterDev 6(ASP(WebProgramming/Vbscript)<br>

http://www.brainbench.com/transcript.jsp?pid=629151

 

[hallm]

It's compressed, it's just storing about 700 news stories and misc local information for my newspaper site.<br><br>How would I write the following query in sql:<br><br>date is Between Date() And Date()-5&nbsp;&nbsp;(basically it needs to show five days of news stories.)

 

[Karl Blessing]

you mean 5 of the highest, you could do in ADO , i belive its Pagesize = 5 (how many records to return, unsure about this one) then do an Order By, I dont know of the Order by will show the latest at time, I think it does. <p>Karl<br><a href=mailto:kb244@kb244.8m.com>kb244@kb244.8m.com</a><br><a href=

http://kb244.8m.com>

</a><br>Experienced in : C++(both VC++ and Borland),VB1(dos) thru VB6, Delphi 3 pro, HTML, Visual InterDev 6(ASP(WebProgramming/Vbscript)<br>

http://www.brainbench.com/transcript.jsp?pid=629151

 

[hallm]

No five days of stories starting from the current date.&nbsp;&nbsp;The example in my above query comes from Access, I haven't been able to make it work in SQL.&nbsp;&nbsp;For example, when a person comes to this page I want them to see stories for the past five days with the new ones first.

 

[wduty]

There may be a much simpler way to do this but the problem I find with Access date/time fields is that they have to be formatted in a way which is different than the date formatting asp gives you. So I use a brute-force approach and convert the dates into the proper format manually. In this example, I query to get the dates so you can see what's going on but would want to adjust the SQL statement to your purposes (SELECT newsstory, title FROM...). There's a reply to a discussion somewhere by Douglas Poston in which he illustrates a simpler method but I haven't managed to get it to work so here's the way I would do it:<br><br><br>ASP CODE:<br>----------------------<br>Function ODBCdatestamp(datestamp)<br>&nbsp;&nbsp;&nbsp;&nbsp;d = Array (y, &quot;yyyy&quot;, m, &quot;m&quot;, d, &quot;d&quot;, h, &quot;h&quot;, mn, &quot;n&quot;, s, &quot;s&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;for i = 0 to 10 step 2 <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;d(i) = datepart(d(i+1), datestamp)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if len(d(i)) = 1 then d(i) = &quot;0&quot; & d(i) end if<br>&nbsp;&nbsp;&nbsp;&nbsp;next<br>&nbsp;&nbsp;&nbsp;&nbsp;ODBCdatestamp = &quot;{ts '&quot; & d(0) & &quot;-&quot; & d(2) & &quot;-&quot; & d(4) & &quot; &quot; & d(6) & &quot;:&quot; & d(8) & &quot;:&quot; & d(10) & &quot;'}&quot;<br>End Function<br><br>set db = server.createObject(&quot;ADODB.connection&quot<br>db.open &quot;testdatabase&quot;<br>set rec = db.execute(&quot;SELECT * FROM cart WHERE dts BETWEEN &quot;&ODBCdatestamp(now-5)&&quot; AND &quot;&ODBCdatestamp(now)&&quot; ORDER BY dts desc&quot<br>do while NOT rec.EOF<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;response.write(rec(&quot;dts&quot&&quot;&lt;br&gt;&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rec.movenext<br>loop<br><br><br>RESULTS ON MY COMPUTER:<br>-------------------------<br>7/18/00 1:10:04 PM<br>7/17/00 1:26:05 PM<br>7/14/00 12:41:54 AM<br>7/14/00 12:41:20 AM <p>--Will Duty<br><a href=mailto:wduty@radicalfringe.com>wduty@radicalfringe.com</a><br><a href= > </a><br>

 

[hallm]

Thank you for you help.&nbsp;&nbsp;I was able to use you query but for some reason it worked without the function you wrote.&nbsp;&nbsp;I do have one more question.&nbsp;&nbsp;This is my sql statement as of right now.<br><br>SELECT * FROM news WHERE date BETWEEN (now-6) AND (now) ORDER BY date desc, time desc<br><br>I have another field in my news table that is called publish.&nbsp;&nbsp;It is a yes/no option on the database.&nbsp;&nbsp;I want add to the query where it only selects between the date span and only items where publish is yes.&nbsp;&nbsp;How is that done?

 

[wduty]

something like<br><br><br>SELECT * FROM news WHERE date BETWEEN (now-6) AND (now) AND publish=true ORDER BY date desc, time desc<br><br>might work or you might need to add some parens in there because of the AND statements. That's weird about the dates because I tried that and got an SQL syntax error on my machine (maybe the driver's different!?).<br><br>good luck! <p>--Will Duty<br><a href=mailto:wduty@radicalfringe.com>wduty@radicalfringe.com</a><br><a href= > </a><br>

 

[hallm]

Thank you, it works perfectly

 

[wduty]

Actually, it looks like you have date and time as separate columns which I guess Access understands. When you try to put a single date/time object in Access like I was doing you have to convert it to the format I set up with the function (Or do some other thing I should but don't know about). <p>--Will Duty<br><a href=mailto:wduty@radicalfringe.com>wduty@radicalfringe.com</a><br><a href= > </a><br>