Funktion i sql-kode

Korrekt syntaks for DateDiff er således:

DateDiff("d";[Dato1];Date())

DateDiff - funker fint og der er heller ikke det, som jeg spørger om hjælp til :-D

Det er derimod syntaksen for, hvordan man indsætter værdien "Oprettelse" i select-sætningen.

strQuery="SELECT DISTINCT ID, VISNINGER, OPRETTELSE FROM PLUSPROFIL_BANNER ORDER BY VISNINGER / " & DateDiff("d", OPRETTELSE, Date())

Lige nu står "OPRETTELSE", som om den er en variabel (hvilket den jo ikke er), men jeg ved ikke hvordan jeg skal indsætte de nødvendige aprostroffer og/eller plinger omkring "OPRETTELSE", så sql-maskinen opfatter "OPRETTELSE", som en kollone-værdi - ligesom ID og VISNINGER er.

Prøv:
DateDiff("d";[Oprettelse];Date())

strQuery="SELECT DISTINCT * FROM PLUSPROFIL_BANNER ORDER BY VISNINGER / " & DateDiff("d"; [OPRETTELSE]; Date())

Microsoft VBScript compilation (0x800A03EE)
Expected ')'

VBScript !
THis is from ASP!

Where is OPRETTELSE, is it a field in the dB or a field on an ASP Page?

If it is in the dB then I suggest that you create a query in the database its easier to get the correct syntax. Then your SQL will look like this

strQuery="SELECT * FROM YourQuery"

I can also make the query for you if you sedn the dB to

NOSPAMeksperten@santhell.dkNOSPAM

remove NOSPAM!

sedn = send

Hi terry... I was waiting for you :-D

It's a db-field (from the same table as "SELECT DISTINCT ID, VISNINGER, OPRETTELSE FROM PLUSPROFIL_BANNER").

There is only one table and that's PLUSPROFIL_BANNER

23/01-2004 18:08:37

Like:

strQuery="SELECT DISTINCT ID, VISNINGER, OPRETTELSE FROM PLUSPROFIL_BANNER ORDER BY VISNINGER / " & DateDiff("d", get_value_of_OPRETTELSE_from_the_same_table_somehow, Date())

I'm not so active during working hours (have work to do :o) )

I can't send the db because it is mysql-database (but the sql-syntax should be the same and therefore I posted it i the access-forum)

You have a problem I think then!

I very much doubt that MySQL understands DateDiff so you cant send it IN the SQL. And because you dont have the value of the field (OPRETTELSE) then you cant calculate the difference. You will need to make your select and then in code calculate the difference

Okay, but I hoped that I did not have to put it in 2 requests... as I wrote in the beginning.

But DateDiff does work in mysql as in access:

This is working:
strQuery="SELECT DISTINCT ID, VISNINGER, OPRETTELSE FROM PLUSPROFIL_BANNER ORDER BY VISNINGER / " & DateDiff("d", "01-01-2001", Date())

Isnt it possible to make a query in MySQL?

Yes and the sql-syntax is nearly the same.

You say

This is working:
strQuery="SELECT DISTINCT ID, VISNINGER, OPRETTELSE FROM PLUSPROFIL_BANNER ORDER BY VISNINGER / " & DateDiff("d", "01-01-2001", Date())

But this IS NOT in MySQL!
If you look at the contents of strQuery I am sure you will see that this part

DateDiff("d", "01-01-2001", Date())

has been converted to a number

Yes

This:
SELECT DISTINCT ID, VISNINGER, OPRETTELSE FROM PLUSPROFIL_BANNER ORDER BY VISNINGER / " & DateDiff("d", "01-01-2004", Date())

Returns:
SELECT DISTINCT ID, VISNINGER, OPRETTELSE FROM PLUSPROFIL_BANNER ORDER BY VISNINGER / 23

And it is working.

But it should be something like:

strQuery="SELECT DISTINCT ID, VISNINGER, OPRETTELSE FROM PLUSPROFIL_BANNER ORDER BY VISNINGER / " & DateDiff("d", get_value_of_OPRETTELSE_from_the_same_table_somehow, Date())

So as I said earlier Datediff is NOT getting sent to the database it is getting transaled to a number BEFORE it gets sent! This means that you DO NOT have the value of OPRETTELSE to use in Datadiff.
So as far as I see it you have two options!
1: Find out IF there is a function in MySQL (NOT vbScript) which can do the same as DateDiff.
2: Select the fields you want and then in code (using Datediff) calculate the difference.

But I have a feeling you may getting a better answer under ASP, as I cant help you with that!

Anyway, I'm off for something to eat now!

I mysql (men hvorfor dog stille spørgsmålet i access når det er til mysql.....?) kan du

strQuery="SELECT DISTINCT ID, VISNINGER, OPRETTELSE FROM PLUSPROFIL_BANNER ORDER BY VISNINGER / ( to_days(now()) - to_days(DITDATOFELT))

Ups - du kan komme til at dividere med 0. Læg fx bare een til nævneren ;)

Hej Erikjacobsen

Mange tak for løsningen, det hjalp. Tryk svar.

Og jeg beklager, at jeg har lagt spørgsmålet i access-kategorien, men der var igennem lang tid ingen hjælp at hente i mysql-kateogien, og så troede jeg, at jeg kunne smide den her, fordi der kun var tale om sql-kode, som lige så godt kunne være til en access-db.

whats importand is that you got an answer in the end :o)

Tak, jeg samler ikke på point. Men der er så meget forskel på sql-dialekterne, at
man er nødt til at fortælle hvilken man bruger ;)

Ok, men så tager jeg selv de points, som du skulle have haft :-D