I have a date range selection drop down list. I want the user to be able to pick options such as Today, Yesterday, This Month, Last Month, and have two date fields below the drop down populate with the appropriate dates. I have two columns in the table used to display the date range name, DateStart, DateEnd. In each I have some code such as =Date() for Today. I can get some simple VBA code to pull =Date() out and put it into the text box, but it comes in as plain text. I want the result of =Date() instead. How can I force VBA to evaluate my code snippet and return the resulting value to the text box? Or am I doing things the hard way and should just hard code this into the form's VBA?
Hi. I'm not sure what the problem is here - perhaps you can provide more details of the code you use so we can see what is going on?
Text boxes themselves don't 'know' anything about types - they are just a means to collect and pass on characters typed by the user or displayed from underlying tables/VBA etc. Type conversion takes place on the fly, as bound fields have specific types, and unbound fields have implied types when used in numeric calculations, for example.
When you display a date value in a text box it is really just text, not a date in the way that Access actually stores dates (as date/time numeric values where the Long Integer part is the date and the decimal component the time). Hence why I'm not sure what is causing you difficulties - are you trying to compare the user-selected values with other fields and finding that it doesn't work because they are really text? If you are you will need to convert the value to a date explicitly, using the CDate function to do so.
With CDate([text box reference]) you can be sure that you are using a date, not a text value, in any comparison or range test.
By the way, the fact that text representations of dates are not dates is why you have to place hashes around specific references to dates in the criterion line of an Access query - #15/02/2008# - as this tells the query engine to treat the reference as a date, not text.
If you need more assistance I'd be glad to help if you could expand a bit on the code you are trying to use.
Regards
Stewart