Dateadd criteria problem

[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 "compatibil ity" 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