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.

[Scott Price]

The Date() function returns a Date/Time value which in Access is stored behind the scenes as an Integer. The DateAdd() function returns a Variant with subtype Date. Theoretically it will never be able to return an invalid date value, however, one never knows with new software releases!

To quote the relevant paragraph from Allen Browne:

DATA TYPE NOT RECOGNIZED: Calculated Date Fields

Access can also misinterpret calculated date fields in queries, especially where the date format is not American and the field contains some Nulls. The obvious symptoms are that the field is left-aligned and sorted as strings.

The solution is to explicitly typecast all calculated date fields, e.g.:

DueDate: CVDate([InvoiceDate] + 30)

(Note: CDate() fails on Null values, so CVDate() has more uses than the "compatibility" issue highlighted in the Access documentation.)

This sounds to me exactly what the OP is experiencing... Because the DateAdd function returns a Variant, it's possible (probable) that when it doesn't recognize the resulting value as a Date subtype, it casts it as a String subtype instead, thus resulting in the Data Type mismatch error.

Using the CVDate() wrapper therefore correctly coerces the Variant returned from DateAdd() into a Variant with Date subtype.

Regards,
Scott

[anthonyk]

right then,

Thank you ever so much for your help thus far..

I can report..

the date format from visual basic is dd/mm/yy
Im in the UK
using Access 07

On a new database - the CVdate thing works with a criteria. so that appears to be that problem solved! Cheers for that!

however on the database i'm working on it doesnt ("rolls eyes") - i can only presume ive picked up an error somewhere etc.. or knocked an option somewhere.. so im going to spend the next few days figuring where the two DB's differ. They are idnetical in the table design and their properties as far as i can tell.

[Scott Price]

After the usual backup routine do a Compact/Repair of the database. Make sure all your code changes are compiled also. Hope you find the problem!

Good luck,

Regards,
Scott

[NeoPa]

@Scott It is worth bearing in mind that MS describe the CVDate() function as legacy functionality, to support projects designed on older systems.

I mention this merely to warn that it may disappear on you in a future version, though probably not any time soon ;)

[anthonyk]

Scott/NeoPa

Thank you both for your wisdom over the last few days

I feel quite sheepish now though, the final step that fixed the error on my database in the query was the compact/repair.. (that is using the CVdate)

I did say i was a bit of a novice.. but im a little frustrated i didnt even cotton onto a menu command...

hopefully this will be of some use to someone else though

Thanks

[Scott Price]

No need to feel sheepish, Anthony! Glad we could get it ironed out for you fairly painlessly :-)

My general rule of thumb is to Compact/Repair at the end of every day when developing a database. That's in addition to Compile & Save after ANY code changes. Access databases can get corrupted from any number of causes, but are especially vulnerable during the development phase while adding, testing, debugging and changing code. These steps help to keep the corruption bug at bay.

Good luck on the rest of the app!

Regards,
Scott