syntax query

[sven2]

Hello,

what is wrong in the following syntax:

WHERE (DATEDIFF(m, MIN(Wachtlijstdeelnemers.Inschrijvingsdatum), CurDate) > 3)

I want to select the dates that are older than 3 months --> starting from the current date.

Thanks in advance,
Sven.

 

[Brianwarnock]

The m should be in " " , Datediff("m",

Brian

 

[sven2]

Hello,

this query gives not an error but doesn't show the records I want to see ...

SELECT Wachtlijstdeelnemers.Cursusnummer, Cursus_stam.Cursusnaam, (Wachtlijstdeelnemers.Cursusnummer) AS AantalDeelnemers
FROM Wachtlijstdeelnemers INNER JOIN
Cursus_stam ON Wachtlijstdeelnemers.Cursusnummer = Cursus_stam.Cursusnummer
WHERE (DATEDIFF ("m",(Wachtlijstdeelnemers.Inschrijvingsdatum), getDate()) > 3)
GROUP BY Wachtlijstdeelnemers.Cursusnummer, Cursus_stam.Cursusnaam
HAVING (COUNT(Wachtlijstdeelnemers.Cursusnummer) > 5)

There are more than 5 records that have an inschrijvingsdatum older than 3 months for the same cursusnummer, but they are not selected when I run this query ???

So, does anybody know what is wrong?

Thanks in advance,
Sven.

 

[vbaInet]

Why don't you use DateAdd() instead:

>= dateadd("m",3, date())

You may also need to format your date in the U.S. format as well.

 

[sven2]

Hello,

I try to explain what I want to do ...

I have a table "toolbox" and in this tabel there are 3 fields like

* personID
* ToolboxID
* Date

Now I want to make a select query that returns the toolboxID that:

* have more than 4 personID
* and 1 of these 5 or more personID should have a date that is older than 3 months

How can I do this in a query?

Thanks in advance,
Sven.

 

[vbaInet]

You have that query already just use the dateadd() I gave you instead of using Datediff.

 

[vbaInet]

Here's a link:

http://www.techonthenet.com/access/functions/date/dateadd.php

 

[Brianwarnock]

I don't know what the getdate is in this
WHERE (DATEDIFF("m",(Wachtlijstdeelnemers.Inschrijvingsdatum),getDate())> 3) I would be using Date()

vbaInet my logic may be wring but shouldn't your dateadd be

Wachtlijstdeelnemers.Inschrijvingsdatum < datedadd("m",-3,Date())
I think he wants the date in his field to be less than 3 months less than the current date.

Brian

 

[vbaInet]

A bit confusing Brian. In his post #3 he said "older than 3 months".

I think he's using the getDate() function to get the date that relates to a PersonID. If that's the case he should be using a subquery don't you think?

 

[Brianwarnock]

Hmm not sure of the best way to proceed if that is the case, but he had curdate() in the first post so it is a bit confusing.
Anyway back to the World Cup.

Brian

 

[vbaInet]

Maybe sven2 could explain what his getDate() and curDate() functions actually do?

Anyway back to the World Cup.

Brian

Not looking good for us at the mo!! lol. It's early days though.

 

[sven2]

Hello,

with the code "getdate" or "currentdate" I try to get the actual date.
I am working with SQL server as backend, so I don't think date will work ... but maybe I am wrong??

And it has to be so that one or more of the dates (inschrijvingsdatum) should be less than 3 months than the current date.

Thanks in advance,
Sven.