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

Thanks.

Answer 2

With proper table design no repeating will be needed at all. The table of tests would be related one-to-many to a table for the dates that test was administered.

You can use a criterion of IS NOT NULL on the datefield to prevent the error, or you could use the NZ() function to return an appropriate date if none is entered: e.g. to have NULL dates default to today's date, you could use

NZ([DateCast], Date())

Answer 3

Creating a separate record for each test will mean that the client has to repeat too much information too many times.

I have identified the problem. It is in the IIf statement. Null is not recognised as a date.

Thanks for all your help!

Answer 4

If you have separate check boxes for each of the days then your table is not well designed. I can see having a separate record for each test. But you should have ONE field for the number of test days and use the value stored in that field in your Date Add.

Try:

DateValue(TestDate) or "TestDate = #" & Date() & "#"