Not sure why you are having the problem, but you might try using the DateAdd
function to calculate your dates. Or forcing the control's value to be dates by
using CDate around them. I suspect that Access (VBA) is seeing something like
12/10/2002 + 1 and then performing arithmetic (12 divide by 10 divide by 2002
add 1).
So try
CDate([Forms]![Dated Report Selection]![Text3]) + 1
Or
DateAdd("d",1,[Forms]![Dated Report Selection]![Text3])
Or
DateAdd("d",1,CDate([Forms]![Dated Report Selection]![Text3]))
Quote:
> Hi,
> I have a problem with a SQL statement. Could anyone give
> me any suggestions why the problem occurs?
> Problem.
> I have a form with 2 unbound fields in it. They are for
> Start Date, the other for End Date. They are defaulted to:
> Start Date = Today - 30 days, End Date = Today. Simple so
> far. I then build the SQL statement to check a date field
> is either Greater than Start Date - 1 and Less then End
> Date + 1.
> & "WHERE ((Incident.[Date of Incident]) > #" &
> ([Forms]![Dated Report Selection]![Text1] - 1) & "#" _
> & "And (Incident.[Date of Incident]) < #" &
> ([Forms]![Dated Report Selection]![Text3] + 1) & "#);"
> This all works fine with default dates. I also have no
> problem if I change the start date. The SQL is correct and
> the resultant table is perfectly correct.
> However, if I change the End Date at all, I get an error:
> Description: Type Mismatch
> Error Code 13
> Why can I do an arithmetic function on the Start Date - 1
> as well as an arithmetic function on End Date + 1 (when
> default) but when I change the date in End Date the
> arithmetic function will not work. It appears to be
> treating End Date once it has changed as a text string
> rather than a date.
> Does anyone have any suggestions that could rectify my
> problem?
> Neil
Type Mismatch Error 13