SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2004
    Location
    london
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need Help with Date Search

    I thought I had an SQL count searching for a "Today" coded correctly, but I keep getting a 0 count back.

    Using Access Database, date format at dd/mm/yyyy

    I have this:

    Code:
    <%
    Function MyDate()
    Dim dteCurrent, dteMonth, dteDay, dteYear
    dteCurrent = Date()
    dteDay = Day(dteCurrent)
    dteMonth = Month(dteCurrent)
    dteYear = Year(dteCurrent)
    MyDate =  dteDay& "/" & dteMonth & "/" & dteYear
    End Function
    
    response.write MyDate
    
    %>
    I then have this as the SQL:

    Code:
    	'Response.write(SQL1)
    	set rs1 = MyConn.execute("Select COUNT(*) as tpt from ppt_table where date_of_ppt="&MyDate&"")
    	if rs1.EOF = false then
    		TodayApp = rs1("tpt")
    	else
    		TodayApp = 0
    	end if
    	set rs1 = Nothing
    	
    	response.write TodayApp
    But I am getting is "0" where I am actually calling TodayApp is displayed on the page - when it actually should be a number over "0".

    Any ideas?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    msaccess requires hash marks (octothorps) to delimit date strings

    ... WHERE datecolumn = #17/01/2012#
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Dec 2004
    Location
    london
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks I have tried:

    where date_of_ppt=#"&MyDate&"#")
    where date_of_ppt="#&MyDate&#"")
    where date_of_ppt="&#MyDate#&"")

    and all do not work, sorry - always learning

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    taking another look at your function, i really don't think you need it at all

    try this for your query --
    Code:
    SELECT COUNT(*) AS tpt FROM ppt_table WHERE date_of_ppt = Date()
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Dec 2004
    Location
    london
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks but no joy on that one either.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    try running the query i posted right in the SQL query window
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Jun 2007
    Posts
    689
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try this:

    Code ASP:
    dim db_date_delimiter: db_date_delimiter="#"
    ... where date_of_ppt="& db_date_delimiter & MyDate & db_date_delimiter )

  8. #8
    SitePoint Zealot
    Join Date
    Dec 2004
    Location
    london
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the help but still no joy. Going to post to a job site as I am at a loss myself.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by dutton View Post
    Thanks for the help but still no joy.
    did you run my query directly in thw SQL window?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •