Date as a TempVar

Question

Hello,

I am working on a form with a datepicker text box. At the "On Change" event, I have set a temporary variable "tmpWeekBegin" that will be used as a criteria for a query. To test I've created another text box with data source =[TempVars]![tmpWeekBegin]. The test text box is returning a date of 12/30/1899. Both are formated for Short Date.

I've been googling this for a week and not been able to solve the problem. I'd like to make this file easily ported to Sharepoint, but that won't be for about a year and the need is immediate, hence I'd like to solve this with macros instead of VBA as I understand Sharepoint doesn't support VBA (and I've very little understanding of it anyway).

Thank you for any input.

Chris

Answer 1

I did a bit of experimenting and while delimiting the value with hashes didn't seem to work, the following expression in a macro did:

CDate([Forms]![MyForm]![txtMyDate].[Text])

where MyForm is the form name, and txtMyDate the text box name.

Answer 2

The simpler way to do this would be to have this query "look back" on your form and pick up the date value. In query design, for the WeekBegin field, in the Criteria row write something like:

Forms!myForm!myDateField

FYI: 12/30/1899 is day 0 in the way VBA and Access are counting day numbers. It tells me the tempvar has not really been set correctly by your code. I don't use tempvars myself so I don't know all the ins and outs about them.

Format has nothing to do with it. Format is about how data is displayed, not what its value is. e.g.

12/31/2013 and Dec-31-2013 are two formats of the same value.

Answer 3

Tom and Ken, thank you for your input.

Ken, I can't seem to get a formatting expression to be picked up by the TempVar statement in the macro, so it looks like I will be following Tom's advice, which seems like it is more straight forward than what I was trying to do.

Thanks again,

Chris

Answer 4

It may be that, when assigning a value to the temporary variable, the date has been misinterpreted as an arithmetical expression. If we take today's date, for example, the expression would evaluate to a number (1.4903129657228E-03) which, in Access's implementation of the date/time data type, represents a date/time value 2 minutes and 9 seconds after midnight on 30 December 1899.  We can see this in the debug window:

? Format(12/04/2013,"mm/dd/yyyy hh:nn:ss")

12/30/1899 00:02:09

If that value is formatted as a date it will not show the hours:minutes:seconds:

? Format(12/04/2013,"mm/dd/yyyy")

12/30/1899

If the expression is wrapped in the hash date delimiter characters when assigning the value, however, it will assign the correct date/time value:

? Format(#12/04/2013#,"mm/dd/yyyy")

12/04/2013

So you might be able to get the right result by wrapping the value in the hash characters when assigning it to the temporary variable, but, like Tom, I never use temporary variables, so am unsure whether this is possible or not.