Calculating number of weeks between two dates 1

[Enea]

I need to write a query that calculates the number of weeks left between today() and April 1, 2007. I wrote the following, but although it does not give me an error, it does not display anything:

SELECT DateDiff("ww", now(),"April 1, 2007") AS Weeks
FROM tblTest;


I don't really need tblTest but I don't know any other way to do this. Does access have a fake table that one can use in cases like this?

I would appreciate if someone can guide me.

Thank you

 

[Remou]

Where are you trying to do this? Where do you want the answer to appear? DateDiff is a function, so you can use it in a number of different ways.

 

[mjross]

You need to enclose your dates in # so Access knows its a date, "" is text thats why its returning no result.

Amend your SQL to

SELECT DateDiff("ww",Now(),#1/4/1997#) AS Weeks;

and it will work.

You don't need a dummy table, you can save the query as an SQL query, you could also declare a function and use
DateDiff("ww",Now(),#1/4/1997#)

in the function.

 

[r937]

You don't need a dummy table

since which version? i tried your query in Access 97 and got an error



r937.com | rudy.ca

 

[mjross]

I'm using 2000/2003 and it works fine there.

Can you copy the SQL into a new query (dont use the query wizard, just click Create Query in Design View, Cancel the box asking you to select tables etc, switch to SQL view and paste in) and try running the new query?

 

[Enea]

Wonderful. Thank you MjRoss. It works.

 

[dhookom]

mjross deserves a star since the reply was "wonderful" and "it works".

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]