Quote:Original post by chbrules
Nevermind, I just figured it out myself:

set dater = Server.CreateObject("ADODB.RecordSet")	dater.Open "SELECT * FROM TradeshowTimes WHERE Tradeshow_ID = '" & rs("Tradeshow_ID") & "' ORDER BY Tradeshow_ID ASC", sConn, 1, 2, 0		Response.Write Replace( dater("Setup_Date"), "/" & year(dater("Setup_Date")), "" ) & " - "		dater.MoveLast		Response.Write Replace( dater("Setup_Date"), "/" & year(dater("Setup_Date")), "" )

I hope that's not too sloppy. I get quirk at blank fields though, I'll have to do error checking too on it.

Unbelievably ugly, and totally inefficient. If there is more then one tradeshow on the report and you are thus repeating this code, you should be sent to SQL jail. You have caused a ton of records to be pulled from the database that you aren't even going to use.

As I've tried to explain to you all along, the correct method, taught on the first day, is this:

dater.Open "SELECT Min(Setup_Date) AS StartDate, Max(Setup_Date) AS EndDate FROM TradeshowTimes WHERE Tradeshow_ID = '" & rs("Tradeshow_ID") & "' GROUP BY Tradeshow_ID ", sConn, 1, 2, 0		Response.Write Replace( dater("StartDate"), "/" & year(dater("Setup_Date")), "" ) & "<font size='2'> - </font>"		Response.Write Replace( dater("EndDate"), "/" & year(dater("Setup_Date")), "" )

I think you really need to take a step back if you intend to continue working on SQL. You believe you know enough about SQL, yet your posting shows you don't even understand the absolute basics. To be generous I would wager your SQL experience before this has been on MySQL (since you mentioned PHP), which would have given you a very bad impression of how databases work (sort of like learning to program in a language with no loops or functions and only a goto - when given a C compiler you would proceed to write absolutely terrible C code because you don't understand how it works).

While you can certainly buy a book, you could also get a free crash course in the absolute basics at a site like W3Schools. While it's not even going to cover the capabilities of Access, it should give you a better understanding of SQL. You should also look into the crash course in ADO.

While you can do it in SQL, for your situation (and frankly most, since it is getting into very advanced conditional SQL) you can do it in ASP code. ASP is not really a language, instead it's a platform for connecting a language to IIS. By default VBScript is the language you are using (with ASP providing a few objects like "Response" and "Request") so if you are stuck you can always look up the VBScript reference (if you are interested, you can also use &#106avascript out the box, or install 3rd party language DLLs to allow you to write ASP pages in languages like Python or Perl).<br><br>With VBScript you just need to use IsNull(), a function that returns True or False.<br><br>Also, in case you didn't know, when you get a recordset, the "EOF" property (dater.eof) will return true if there are no records (you also use EOF to determine when you've reached the end while browsing with MoveNext)

As I mentioned, if no records are returned, you'll want to use dater.eof to check that.