Dateadd criteria problem

[anthonyk]

Hi there, im hoping someone might be able to painlessly tell me what im incorrectly doing here/expecting.

Im using Access 2007

I am creating a query to show ongoing tasks in a database.
in the fields I have
Start date (dd/mm/yyyy)
Duration (months)

Basically i want the query to calculate is if start date + Duration is greater than today...

so ive used
Dateadd("m",[duration],[start date])

this is great up to this point - it will show on running the finish date in the format dd/mm/yyyy

but when I add the criteria
>date()

I get the error message "data type mismatch in criteria expression"

I think the criteria is formed correctly - as it works on the start date field independently.
Infact if i put any criteria in the field that the dateadd is present i get the same message.

Basically - are the two conditions - dateadd of two fields and criteria functions compatible?
If not is there any other way i could combat the problem?

regards

[Scott Price]

Try enclosing your start date in # marks within the DateAdd statement.

As a test I wrote these two statements in the Immediate window:

?DateAdd("m", 1, 15/3/2008)>Date()
?DateAdd("m",1,#15/3/2008#)>Date()

The first evaluates false, the second evaluates true. What is happening with these two statements is that vba attempts to change the variant returned by the DateAdd into a recognizable date, and for the first it comes up with something in the year 1900. This obviously is not greater than today :-) Enclosing in hash marks (#) makes it correctly evaluate.

I do find it strange that you are getting a type mismatch error, though...

Regards,
Scott

[Scott Price]

After doing some more testing (using Access 2003) I'm starting to wonder what really is happening here.

Doing what you are trying to do works perfectly well for me without adding # marks or anything else. I recreated as best I could in my test database, but could not reproduce the error.

Check to make sure that the startdate is being stored as a Date/Time value, not a Text value. Please also post the sql for your query for us. After pasting the sql into the reply window, please enclose it in the code tags by selecting the sql text and clicking the # icon on the top of this reply window.

Thanks!

Regards,
Scott

[anthonyk]

Hi there,

Thaks for your help so far.. Im gonna be honest my SQL knowledge - you could write on a postage stamp...

Here is the Sql code from my query where it calculates the end date accurately

SELECT [Tbl Projects].[Projects Code], [Tbl Projects].[Project Type], [Tbl Projects].[Region], [Tbl Projects].[Start Date], [Tbl Projects].[Duration (mths)], [Tbl Projects].[Cost], DateAdd("m",[Duration (mths)],[Start Date]) AS [end]
FROM [Tbl Projects];

here is the same query with me adding the criteria that causes the error...

SELECT [Tbl Projects].[Projects Code], [Tbl Projects].[Project Type], [Tbl Projects].Region, [Tbl Projects].[Start Date], [Tbl Projects].[Duration (mths)], [Tbl Projects].Cost, DateAdd("m",[Duration (mths)],[Start Date]) AS [end]
FROM [Tbl Projects]
WHERE (((DateAdd("m",[Duration (mths)],[Start Date]))>Date()));

the start date field is date/time type and the duration (mths) field is a number.

Thanks again

[Scott Price]

Open your query in design view and in the Criteria space below the Field where you have

end: DateAdd("m", [Duration (mths)], [Start Date])

add the

>Date()

.

The sql will look nearly the same, if not exactly the same, but it throws an error when attempting to use the whole DateAdd() >Date() as a WHERE criteria.

Give this a try and let me know if it works.

Regards,
Scott

[anthonyk]

hiya,

yeah thats exactly what i was doing - so just tried and still gives me an error.

[Scott Price]

Very strange, since it works perfectly for me!

There are a few differences with the way A2003 and A2007 work, but I wasn't aware that this was one of the difficulties. Let me research a bit and I'll get back to you.

Regards,
Scott

[Scott Price]

Just for grins and chuckles, can you open the VBA editor window (Alt+F11 does the trick in A2003), then in the Immediate window (Ctl+G) type in

?Date()

and hit enter.

Let me know what format the date is in: dd/mm/yyyy or mm/dd/yyyy.

Regards,
Scott

[missinglinq]

I thought of that too, Scott! It would really help if people at least listed their country of residence in their profiles, especially in Date related questions!

Linq ;0)>

[Scott Price]

Thanks Linq!

I noticed that in his first post he mentioned the dd/mm/yyyy format... However reading online about A2007, it says that Date() returns in mm-dd-yyyy...

Testing with A2003 I found that the Date() function returns in the format specified in the control panel settings.

Now I'm just wondering if A2007 changed this function to only return in one format. Hopefully not, but I can't think of any other situation that would give rise to the error he reports.

Regards,
Scott

[Killer42]

Surely the Date() function returns a Date value, not a formatted string. How it is subsequently formatted should be independent of where the value came from.

[Scott Price]

Well, according to Allen Browne the 'wonder from down under': International Dates in Access there is a potential problem using this type of date in a calculated field in a query. He recommends as a solution using the CVDate() as a wrapper for the whole date calculation in order to take care of potential nulls (cvdate is better than cdate for this) and to erase any ambiguity that Access could use to throw an error.

That would translate into:

end: CVDate(DateAdd("m", [Duration (mths)], [Start Date]))

Regards,
Scott

[MMcCarthy]

Out of Curiousity can you substitute Now() for Date(). There have been some rumours of the Date() function not being fully supported in 2007.

[Killer42]

Out of Curiousity can you substitute Now() for Date(). There have been some rumours of the Date() function not being fully supported in 2007.

This certainly sounds like a more fruitful line of investigation, since as far as we know, the result of the DateAdd() was already being interpreted as a date value before, and only comparing it to Date() produced the mismatch.

[NeoPa]

To illustrate whether or not the Date function is fully supported in A2007 SQL it may well be worth doing a test by substituting Now() for Date().

If this turns out to be the case however, that would be an extraordinarily retrograde step for Access. Very few people can manage to get the logic right using Now() when testing for dates as it is. Simply put, when testing raw dates (no time elements) the Date() function should ALWAYS be used, as using Now() will result in false logic.

As far as formats are concerned, Killer is right on the mark. The values returned from both the Date() and DateAdd() functions should be Date/Times and not formatted strings. This means that the format that they are displayed in is entirely irrelevant.

The format of date LITERALS in SQL however, is neither irrelevant nor dependent on your regional settings (See Literal DateTimes and Their Delimiters (#)). This is layed down in the SQL standards as m/d/y. Access is very forgiving in this respect, allowing all sorts of different date formats in SQL as long as they are not ambiguous. When ambiguous (EG 1/5/2008) it will always assume the SQL standard of m/d/y.