Date Problems in a Query and Report - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Date Problems in a Query and Report

Helen Renaud

2 years ago

Hello: I have a database with 5 tables and each table has a specifically named date field. When I try to put them all within one query, using the following field, the dates are not recognized as a date when I try to put it in a report.Also, there will only be one date per table for a specific animal, so the following will show only one record based on what happened with the animal.

SelectedDate: Nz([Adoption_Date],Nz([Departure_Date],Nz([Euth_Date],Nz([Date_Released],[Date_Animal_Out]))))

	Sami Shamma @Reply 2 years ago
break the problem into smaller code and see where you are having issues. is it possible that any of the dates have other than a valid date or Null?

	Kevin Robertson @Reply 2 years ago
I'd like to see a screenshot of the query.

	Helen Renaud OP @Reply 2 years ago
Hello Sami and Kevin: thanks for the response. Sami: I have done what you suggested and they are all dates. Kevin: I think you want to know that the resulting query is left aligned, meaning it is text. What can I do about that?

	Kevin Robertson @Reply 2 years ago
Use the `CDate()` function. `SelectedDate: CDate(Nz([Adoption_Date],Nz([Departure_Date],Nz([Euth_Date],Nz([Date_Released],[Date_Animal_Out])))))`

	Helen Renaud OP @Reply 2 years ago
Kevin: I tried that too, but when I run the query, the error is "invalid use of null". I am not sure how to fix that.

	Kevin Robertson @Reply 2 years ago
I have just noticed you don't have the NZ function around the last date.

Helen Renaud OP @Reply

2 years ago

Hi Kevin: I was using Chatgpt and it said to leave off the last Nz. But, I put it back in now. I have been working on this for two days and it is so frustrating.

Some progress...When I run the query, for the two dates, Jan 16 and 17/2024, it shows Jan 16/2006 to Jan 17/2024, but only those two dates, each year. But, when I am call this query from a form, that will open a report, it works. I don't understand what that is happening.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View