If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register or Login
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Suppose you make it a string rather than a date? If tDate must be a date data type, then why not initialize it with a date before setting it with date formatted strings? In other words, use " instead of #, such as "28 June 2013". I've not coded in Access, but if variables can be defined as other types besides variants, then you won't need to initialize your variable with a date.

Please remember to rate the posts and threads that you find useful.How can something be both new and improved at the same time?

This is quite common with MS Access ( all versions ) and has always sucked, probably always will. Access obtains the date formatting from your computer's regional settings. Change that, then your problem should also have been sorted out. But, remember as well that any new info that gets stored in access might get mixed up somehow.

What you can also do in Access is to format the date column exactly as you want it, just go to the particular field's properties and set the format there.

Can you not use a string data type rather than a date type? The Format function works just as well if the variable is a string. Won't the SQL queries work if the date is a string? If not, then I suppose one possible workaround might be to change the database field to a string, though that may present its own set of issues.

Please remember to rate the posts and threads that you find useful.How can something be both new and improved at the same time?

Dates are not stored as words so if you want a word to be there then you either store it as a string or use format to make it appear in string form. Don't worry about the way the IDE shows it you can still display it as you wish to the user.

When you define a variant and use the format function you are using a string.

When you use the ## signs you are using a date which is never stored using the words as you tried in your first post but again that does not matter because a date can be displayed many different ways and it does not matter how the date looks in your code so long as it is valid.