Fejl i sql

strSQL = "Select distinct datopublish from search where (date > " & Date() + 1 & "# AND date < #" & Date() + 364 & "#) order by datopublish"

//>Rune

Her er "syntaxen" for den:

SELECT * FROM tabelnavn WHERE date > #dato1# AND date < #dato1#

//>Rune

You need to make sure that the date is in th ecorrect format

MM-DD-YY or YYYY-MM-DD

Ithink you can use the format function in ASP, and also dateAdd.

Format(DateAdd("d", 1 Date()), "yyyy-mm-dd")

strSQL = "Select distinct datopublish from search where (date > " & Format(DateAdd("d", 1, Date()), "yyyy-mm-dd") & "# AND date < #" & Format(DateAdd("d", 364, Date()), "yyyy-mm-dd")& "#) order by datopublish"

>> medions
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression '(date > 05-01-2003# AND date < #03-01-2004#)'.

Jeg glemte en # sorry! prøv igen!

strSQL = "Select distinct datopublish from search where (date > #" & Date() + 1 & "# AND date < #" & Date() + 364 & "#) order by datopublish"

//>Rune

>> medions
Microsoft JET Database Engine (0x80040E10)
No value given for one or more required parameters.

strSQL = "Select distinct datopublish from search where (date > #" & Format(DateAdd("d", 1, Date()), "yyyy-mm-dd") & "# AND date < #" & Format(DateAdd("d", 364, Date()), "yyyy-mm-dd")& "#) order by datopublish"

Så prøv lige den her!

//>Rune

Microsoft VBScript runtime (0x800A000D)
Type mismatch: 'Format'

if format doesnt work in VBSCript then what about datepart?

>> terry
Du har frie hænder til at komme med kodeforlag ligesom medions :-)

Er den første ikke ok hvis du bare vender de to datoer om?

Select distinct datopublish from search where (datopublish between #03-01-2003# and #05-01-2004#) order by datopublish

ups... så ikke der var forskel på år også....

>> medions

Jeg har desperat brug for at svar :)

Hvorfor siger den følgende til dit sidste kodeforslag?

Microsoft VBScript runtime (0x800A000D)
Type mismatch: 'Format'

Hvis det kan være nogen hjælp så ligger datoerne i en access-database og de er formateret efter følgende princip: f.eks.: 03-01-2003

Hmm så prøv med denne!

strSQL = "SELECT DISTINCT datopublish FROM search WHERE (date > #" & DatePart(DateAdd("d", 1, Date()), "dd-mm-yyyy") & "# AND date < #" & Datepart((DateAdd("d", 364, Date()), "dd-mm-yyyy")& "#) order by datopublish"

//>Rune

Microsoft VBScript compilation (0x800A03EE)
Expected ')'
/dbsite/artikel/artikel1.asp, line 242, column 173

Hvis feltet:

datopublish
01-02-1999
06-06-2002
03-03-2002
12-12-2002
09-08-2003
01-02-2003

Efter den nedenstående kode bliver putputet:
09-08-2003
01-02-2003
selvfølgelig formatet skal rettes

<%
Set myConn=Server.CreateObject("ADODB.Connection")
myConn.Open ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE="+server.Mappath("/db/selvet.mdb"))
strSQL = "Select * from search  where (datopublish between #" & (Date() + 1) &  "# and #"& (Date() + 364) & " #) order by datopublish"
set rs = myConn.execute(strSQL)
do while not rs.EOF
if isDate(rs("datopublish")) then
myDates = myDates & rs("datopublish") & ", "
End If
rs.MoveNext
Loop
if not myDates = "" then
myDates = left(myDates,len(myDates)-2)
end if
response.write("***" & myDates & "***")
myDateArr = Split(myDates,",")
%>

Det har jeg testet og virker fint

Prøv at læs denne artikel og se om du får noget ud af det?!

http://activedeveloper.dk/brevkassen/default.asp?category=14&question=201

//>Rune

datopublish i din tabel, har den en datatype som "Dato og klokkeset"?

strSQL = "SELECT DISTINCT datopublish FROM search WHERE date > #" & DatePart(DateAdd("d", 1, Date()), "dd-mm-yyyy") & "# AND date < #" & Datepart(DateAdd("d", 364, Date()), "dd-mm-yyyy") & "# order by datopublish"

>> terry
Microsoft VBScript runtime (0x800A000D)
Type mismatch: '[string: "dd-mm-yyyy"]'

>>hossein
>datopublish i din tabel, har den en datatype som "Dato og klokkeset"?
Ja, og de er som sagt formateret på følgende måde: 05-01-2003

what am I doing!!!!! (still sleeping here :o))
back in 5

Just a question, are you trying to select those from todays dateand one year on?

yes

strSQL = "SELECT DISTINCT datopublish FROM search WHERE datopublish <= #"  & Year(date) & "-" & month(date) & "-" & Day(date) &  "# AND datopublish < #" & Year(dateadd("yyyy",1,date)) & "-" & month(dateadd("yyyy",1,date)) &  "-" & Day(dateadd("yyyy",1,date)) & "# order by datopublish"

but I have no idea if these functions can be used in VBSCRIPT

this is what we are looking for
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/vsfctformatdatetime.asp

and ALL functions
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/vtorifunctions.asp

what you have to notice though, is that the formatDateTime function uses the computers REGIONAL settings, and this means if the server is using Danish Regional Settings you WILL get the wrong format.

I corrected year and day by replacing them - and the sql-output was:

SELECT DISTINCT datopublish FROM search WHERE datopublish <= #5-1-2003# AND datopublish < #5-1-2004# order by datopublish

Date-output (on the screen):
14-01-1999, 01-01-2001, 01-02-2001, 05-02-2001, 15-02-2001, 20-02-2001, 01-03-2001, 02-03-2001, 07-03-2001, 09-03-2001, 11-03-2001, 16-03-2001, 20-03-2001, 23-03-2001, 27-03-2001, 30-03-2001, 01-04-2001, 04-04-2001, 06-04-2001, 07-04-2001, 09-04-2001, 13-04-2001, 18-04-2001, 20-04-2001, 22-04-2001, 23-04-2001, 25-04-2001, 01-05-2001, 04-05-2001, 2002, 19-12-2002, 20-12-2002, 21-12-2002, 22-12-2002, 23-12-2002, 24-12-2002, 25-12-2002, 26-12-2002, 27-12-2002, 28-12-2002, 29-12-2002, 30-12-2002, 31-12-2002, 01-01-2003, 02-01-2003, 03-01-2003, 04-01-2003, 05-01-2003, 06-01-2003, 07-01-2003, 08-01-2003, 09-01-2003, 10-01-2003, 11-01-2003, 12-01-2003, 13-01-2003, 14-01-2003, 15-01-2003, 16-01-2003, 17-01-2003, 18-01-2003, 19-01-2003, 24-01-2003

Problem:
It takes all dates - and not only dates from todays date and one year on.

I'm not sure what you mean! "I corrected year and day by replacing them" do you mean that you hardcoded the dates?

If so try

SELECT DISTINCT datopublish FROM search WHERE datopublish <= #2003-01-05# AND datopublish < #2004-01-05# order by datopublish


Here is another possible solution

SELECT DISTINCT datopublish
FROM search
WHERE (((CLng([datopublish]))>=CLng(Date()) And (CLng([datopublish])) < CLng(DateAdd("yyyy",1,Date()))))

>I'm not sure what you mean! "I corrected year and day by replacing them"
I just put day before month and month before year.

I will now try your new solution.

The way dates are displayed on the screen is taken from the PC's regional settings which in this case is the SERVER. This has NOTHING to do with how the date is actually stored in the database. Dates are stored as large numbers and that is why I can use Clng to convert it to a long integer.

>SELECT DISTINCT datopublish FROM search WHERE datopublish <= #2003-01-05# AND datopublish < #2004-01-05# order by datopublish
Expected 'Case'

>Expected 'Case'
Sorry - my mistake.

But "SELECT DISTINCT datopublish FROM search WHERE datopublish <= #2003-01-05# AND datopublish < #2004-01-05# order by datopublish" does the same as "Kommentar: zeusdk 05/01-2003 11:54:57"

I will now try your last new solution.

?
what is 05/01-2003 12:28:40 telling us?

what about
05/01-2003 11:41:12
have you tried this?

SELECT DISTINCT datopublish FROM search WHERE datopublish <= #01/05-2003# AND datopublish < #01/05-2004# order by datopublish

Does the same as "Kommentar: zeusdk 05/01-2003 11:54:57"

And

strSQL = "SELECT DISTINCT datopublish FROM search WHERE (((CLng([datopublish]))>=CLng(Date()) And (CLng([datopublish])) < CLng(DateAdd("yyyy",1,Date()))))"

Is not working because of the 2 "'s in the string.

I misunderstod "Kommentar: terry 05/01-2003 12:33:14" and "Kommentar: terry
05/01-2003 12:34:26"

Please wait - I will soon write the errors that they returned.

>what is 05/01-2003 12:28:40 telling us?
>what is 05/01-2003 12:28:40 telling us?
They are both doing the same as "Kommentar: zeusdk 05/01-2003 11:54:57".

The problem is that some solutions return all the dates and other solutions return no dates.

Sorry it should have said:

>what is 05/01-2003 12:28:40 telling us?
>05/01-2003 11:41:12?
They are both doing the same as "Kommentar: zeusdk 05/01-2003 11:54:57".

The problem is that some solutions return all the dates and other solutions return no dates.

strSQL = "SELECT DISTINCT datopublish FROM search WHERE (((CLng([datopublish]))>=CLng(Date()) And (CLng([datopublish])) < " & CLng(DateAdd("yyyy",1,Date())) & ")"

cant be far wrong, although IF the other answers are not working then there IS something else which is wrong I am sure!

can you send me the database? eksperten@santhell.dk

Microsoft JET Database Engine (0x80040E14)
Missing ), ], or Item in query expression '(((CLng([datopublish]))>=CLng(Date()) And (CLng([datopublish])) < 37991)'.

well the problem is obvious when you can see the result with the correct data!

This WILL hopefully work:

strSQL = "SELECT DISTINCT datopublish FROM search WHERE datopublish >= #"  & Year(date) & "-" & month(date) & "-" & Day(date) &  "# AND datopublish < #" & Year(dateadd("yyyy",1,date)) & "-" & month(dateadd("yyyy",1,date)) &  "-" & Day(dateadd("yyyy",1,date)) & "# order by datopublish"

the problem is we have been focused on getting the date correct that we didnt notice
<= (less than or equals)
which should be >= (Greater than or equals)

using clng() will give problems because there are records with NULL in date the field!

Yes, yes, yes, yes !!! :-) It is working now thanks to you :-)

I have another - proberly a very little problem - and that is that I have a dropdown-box where all the dates, which are NOT in the sql-string are listed. But they are not listed at all - instead all the dates are listed. If you also can fix this problem I will give you 100 points ekstra.

<%
    Set myConn=Server.CreateObject("ADODB.Connection")
    myConn.Open ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE="+server.Mappath("/db/selvet.mdb"))
   
    strSQL = "SELECT DISTINCT datopublish FROM search WHERE datopublish >= #"  & Year(date) & "-" & month(date) & "-" & Day(date) &  "# AND datopublish < #" & Year(dateadd("yyyy",1,date)) & "-" & month(dateadd("yyyy",1,date)) &  "-" & Day(dateadd("yyyy",1,date)) & "# order by datopublish"
    'strSQL = "Select distinct datopublish from search where (datopublish between #" & Date() + 1 & "# and #" & Date() + 364 & "#) order by datopublish"

    set rs = myConn.execute(strSQL)
   
    do while not rs.EOF
        if isDate(rs("datopublish")) then
            myDates = myDates & rs("datopublish") & ", "
        End If
    rs.MoveNext
    Loop
   
    if not myDates = "" then
        myDates = left(myDates,len(myDates)-2)
    end if
   
    response.write "*** " & myDates & " ***"
    myDateArr = Split(myDates,",")
%>

<select name="datoPublikation" style="font-size: 10pt; font-family: Trebuchet MS">

<%
'Jeg skal have en uge til at reagere på
dato = date () + 1
datoSlut = DateAdd("yyyy",1,date)
i = 0
do while dato <= datoSlut
  if CDate(myDateArr(i)) <> CDate(dato) then
    Response.Write "<option value=""" & dato & """>" & dato & "</option>"
  else
    i = i + 1
    if i > ubound(myDateArr) then i = ubound(myDateArr)
  end if
  dato = dateAdd("d",1,dato)
loop
%>

</select>

No, wait I fixed myself.

So its all working now?

Thanks :o)

Thank you again - terry.

>So its all working now?
Yes, yes - everything :-)