DateAdd Data Type

Question

Hi!

I'm not sure what have I done wrong.

I have created a form that I only want to display when TestDate = Date(). TestDate is based on a DateAdd function in the underlying query. The query works fine. On my form the calender pop-up appears on TestDate - to me this is an indication that the data type is Date. However, when I want to filter the date, it says that there is a data type mismatch. I have tried the filter function on the Form. I have also tried to do this through a macro. I get the same results every time.

TestDate is a number of days after DateCast. If I run the filter on DateCast there is no problem.

If I add Date() to TestDate criteria in the underlying query, it seems to work fine. But then my form displays empty if TestDate does not equal Date() and I do not want the form to open at all if TestDate does not equal Date().

Thanks for your help.

Answer 1

Yes, there really is a field called 14Days. It is a Yes/No field that clients have to choose to indicate when they want the product to be tested. There are a number of possible options of which clients can select more than one - 3 days, 7 days, 14 days, 21 days and 28 days. I use the Union query to bring all the different dates together in one query.

I like your suggestion. I just need to think it through because there can be a number of different test dates for one product. I collect the data via email.

My problem is not with opening the form.

The problem is that it seems that TestDate is not recognised as a date.

I'm sorry to say that I do not know how to use code to test TestDate.

However, if I run a date filter by right clicking on the TestDate field in the form, it complains about a data type mismatch. And when I ran a macro that said this form must be opened when TestDate = Date() it also gives a data type mismatch.

I was hoping that I am making a straightforward mistake somewhere. Otherwise I will just need to find another way around it.

Answer 2

Well the first problem I see is structural. Do you really have a field in your table named 14Days? Or is this a calculated column? Why use a Union query when you can just use:

IIF([TestDays] = 0, Null, DateAdd("d",[TestDays],[DateCast]) AS TestDate

Next question is how are you trying to open the form? What code are you using to test TestDate?

Answer 3

Thanks for taking the time to answer.

I had the same thought but the result gives the exact date that I am looking for. Then I changed the format in the properties to Short Date. It still gave me the same result - on the filter as well.

Here is the SQL:

SELECT [LogIn].[Job#], [LogIn].[DateCast], IIf([14Days]=0,Null,DateAdd('d',14,[DateCast])) AS [TestDate]

FROM [LogIn];

Maybe I should just add that I have a number of possible test dates: 3, 7, 14 and so on. I wrote a small query for each (like the above) and then combined them with a Union query into one query that my form is based on. However, even if I create a form based on the above query, I still have the same problem. So the problem does not come in with the Union query.

Thanks again for your time!

Answer 4

Please post the SQL of the query and the DateAdd function that you're using. Are you certain that TestDate doesn't contain a time portion (as it might if the Now() function goes into its creation)? Now() does not return the date, but rather the date and time accurate to the second; #9/10/2013 21:59:13# is not equal to Date(), it's about ten hours later!