More date processing issues

[WAVP375]

Table defines date of birth [DOB] as 'Short Date', I have also tried defining it as m/d/yyyy. Query selecting birthdays in next 30 days. In the query I have a calculated field as follows Birthday: DateSerial(Year(Date()),Month([DOB]),Day([DOB])) and in the criteria I have Between Date() And DateAdd("d",30,Date()). When requesting datasheet view a message 'Data type mismatch in criteria expression' appears.



I'm baffled! Any thoughts?

[davegri]

Don't see anything wrong. Described scene duplicated in attached and works OK.

Birthday30Days.zip

[CJ_London]

dates are stored as numbers - what you see as a date is just a format of that number so ' I have also tried defining it as m/d/yyyy' is irrelevant. What is more likely is that your DOB field is not specified as a date type.

[WAVP375]

The table I am working with is imported from Excel. There are 5 date fields in the spreadsheet. Three of them give me no problems, two have this issue. In the spreadsheet all dates are formatted as m/d/yyyy. It is imported to create a new table. This table after import defines the fields as Date/Time with a format of m/d/yyyy. In an append query I manipulate some of the fields, change names and eliminated some of the fields. This table I created with all dates defined as Date/Time and format m/d/yyyy.

Does this offer any clues?
Thanks

[davegri]

Maybe you could use the IsDate function to help track the problem down.
IsDate returns True if the expression is a date or is recognizable as a valid date; otherwise, it returns False

[Minty]

Don't apply a format to date time fields in tables. Access stores them correctly internally. Formatting dates should only be done for display purposes on forms.
Applying a format will only confuse the issue. If you want to ensure a date use CDate()

[CJ_London]

Perhaps there is an issue with the data - check all dates are imported correctly - excel has a habit of hiding data type issues

[WAVP375]

Thanks for the suggestions. The IsDate returns a -1 for all date fields which I assume is True.
Any other ideas.

[davegri]

Any other ideas.

Did you look at post #2? It works.
You could import your table into that database and improvise the same solution for troubleshooting?

[WAVP375]

Problem solved. I changed direcrions a little. I chose to look at next month instead of next 30 days.
Thanks for your ideas they have made me aware of new possibilities for solutions to future problems.