Expression not recognizied as a Date 1

[Dophia]

Hi Everyone:
I have an expression in a query which displays a date, but when I use the query to run a report, the date is not recognized as such. I want to run the report for a specific month or year, but the query will not sort by date. I tried CDate to convert the text to a date, but then when I use a range for the report, access says it is too complicated. Here are the two expressions:

Estimated_Release: Format(IIf(([ReleaseDate])="",[Arrival_Date],[ReleaseDate]),"mmm-dd-yy",0,0)

ReleaseDate: Format(([Euthanized_Date] & " " & [Adoption_Date] & " " & [Date_Released] & " " & [Departure_Date]),"mmm-dd-yy",0,0)

Any suggestions?
Thanks, Sophia

 

[Remou]

The date delimiter is hash (#), not quote (").

 

[Dophia]

Where would I replace the " with a #?
Sophia

 

[Remou]

Sorry, I misread your post.

I do not believe that this:

[tt]ReleaseDate: Format(([Euthanized_Date] & " " & [Adoption_Date] & " " & [Date_Released] & " " & [Departure_Date]),"mmm-dd-yy",0,0)[/tt]

Can ever work. Is it your intention to get a 'closed' date from one of the possible reasons that the record has been closed?
Why not format the date in the report, rather than in the query? That way it will sort properly.

 

[Dophia]

I use the other expression (Estimated_Release)in my report and format it as a date, but i cannot get it to only give me records for certain months, as it returns all of the records.


What I am trying to do is to get a "release date" if none exists. So, I use the ReleaseDate expression to see if there is a date in a multiple of fields, then if there is a blank, I use the other expression (Estimated_Release) to use another date as the release date.
The report runs with the Estimated_Release as a grouping level.But it will not print only certain months.
Can you see a possible solution?
Sophia

 

[Remou]

Please post the SQL.

 

[Dophia]

Here is the SQL:

SELECT tblAnimals.Pound_Sheet_No AS tblAnimals_Pound_Sheet_No, tblAnimals.Arrival_Date, tblAnimals.Category_ID, (Format(IIf([ReleaseDate]="",[Arrival_Date],[ReleaseDate]),"mmm-dd-yy",0,0)) AS Estimated_Release, Format(([Euthanized_Date] & " " & [Adoption_Date] & " " & [Date_Released] & " " & [Departure_Date]),"mmm-dd-yy",0,0) AS ReleaseDate, tblAnimals.Euthanized_Date, tblAdopter.Adoption_Date, tblAnimal_ReleaseOther.Date_Released, tblPound_Release_Owner.Departure_Date
FROM ((((tblAnimal_Out_Category INNER JOIN tblAnimals ON tblAnimal_Out_Category.Animal_Out_Category = tblAnimals.Animal_Out_Category) LEFT JOIN tblAdopter ON tblAnimals.Pound_Sheet_No = tblAdopter.[PS_#]) LEFT JOIN tblAnimal_ReleaseOther ON tblAnimals.Pound_Sheet_No = tblAnimal_ReleaseOther.Pound_Sheet_No) LEFT JOIN tblDrug_Log ON tblAnimals.Pound_Sheet_No = tblDrug_Log.Pound_Sheet) LEFT JOIN tblPound_Release_Owner ON tblAnimals.Pound_Sheet_No = tblPound_Release_Owner.Pound_Sheet_No
GROUP BY tblAnimals.Pound_Sheet_No, tblAnimals.Arrival_Date, tblAnimals.Category_ID, tblAnimals.Euthanized_Date, tblAdopter.Adoption_Date, tblAnimal_ReleaseOther.Date_Released, tblPound_Release_Owner.Departure_Date;

 

[Remou]

Paste the SQL below into a query and see if it returns the data you expect, if it does, we can set up a field to accept a month number. Is that what you wish?

[tt]SELECT tblAnimals.Pound_Sheet_No AS tblAnimals_Pound_Sheet_No, tblAnimals.Arrival_Date, tblAnimals.Category_ID, IIf(IsNull([Euthanized_Date]),IIf(IsNull([Adoption_Date]),IIf(IsNull([Date_Released]),[Departure_Date],[Date_Released]),[Adoption_Date]),[Euthanized_Date]) AS ReleaseDate, IIf(IsNull([ReleaseDate]),[Arrival_Date],[ReleaseDate]) AS Estimated_Release, tblAnimals.Euthanized_Date, tblAdopter.Adoption_Date, tblAnimal_ReleaseOther.Date_Released, tblPound_Release_Owner.Departure_Date
FROM ((((tblAnimal_Out_Category INNER JOIN tblAnimals ON tblAnimal_Out_Category.Animal_Out_Category = tblAnimals.Animal_Out_Category) LEFT JOIN tblAdopter ON tblAnimals.Pound_Sheet_No = tblAdopter.[PS_#]) LEFT JOIN tblAnimal_ReleaseOther ON tblAnimals.Pound_Sheet_No = tblAnimal_ReleaseOther.Pound_Sheet_No) LEFT JOIN tblDrug_Log ON tblAnimals.Pound_Sheet_No = tblDrug_Log.Pound_Sheet) LEFT JOIN tblPound_Release_Owner ON tblAnimals.Pound_Sheet_No = tblPound_Release_Owner.Pound_Sheet_No
GROUP BY tblAnimals.Pound_Sheet_No, tblAnimals.Arrival_Date, tblAnimals.Category_ID, tblAnimals.Euthanized_Date, tblAdopter.Adoption_Date, tblAnimal_ReleaseOther.Date_Released, tblPound_Release_Owner.Departure_Date;[/tt]

 

[Dophia]

When I paste the above into the query, it does give me the correct dates, but when I put a date range in the Criteria row in the column "Estimated_Release", such as >#12-31-2005#, I get the correct records, but first I get a prompt box as follows:
Enter Paramter Value
Release Date
OK, Cancel
If I click on Ok.

Sophia

 

[dhookom]

You should be able to replace all the IIf(IsNull()) syntax with something like:
Nz([Euthanized_Date], Nz([Adoption_Date], Nz(Date_Released], [Departure_Date]))) AS ReleaseDate,
Nz([ReleaseDate],[Arrival_Date]) AS Estimated_Release,

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Remou]

dhookom
Very useful, thanks.

Sophia, have a look at this:
[tt]SELECT tblAnimals.Pound_Sheet_No AS tblAnimals_Pound_Sheet_No, tblAnimals.Arrival_Date, tblAnimals.Category_ID, nz([Euthanized_Date],nz([Adoption_Date],nz([Date_Released],[Departure_Date]))) AS ReleaseDate, IIf(IsNull([ReleaseDate]),[Arrival_Date],[ReleaseDate]) AS Estimated_Release, tblAnimals.Euthanized_Date, tblAdopter.Adoption_Date, tblAnimal_ReleaseOther.Date_Released, tblPound_Release_Owner.Departure_Date
FROM ((((tblAnimal_Out_Category INNER JOIN tblAnimals ON tblAnimal_Out_Category.Animal_Out_Category = tblAnimals.Animal_Out_Category) LEFT JOIN tblAdopter ON tblAnimals.Pound_Sheet_No = tblAdopter.[PS_#]) LEFT JOIN tblAnimal_ReleaseOther ON tblAnimals.Pound_Sheet_No = tblAnimal_ReleaseOther.Pound_Sheet_No) LEFT JOIN tblDrug_Log ON tblAnimals.Pound_Sheet_No = tblDrug_Log.Pound_Sheet) LEFT JOIN tblPound_Release_Owner ON tblAnimals.Pound_Sheet_No = tblPound_Release_Owner.Pound_Sheet_No
GROUP BY tblAnimals.Pound_Sheet_No, tblAnimals.Arrival_Date, tblAnimals.Category_ID, tblAnimals.Euthanized_Date, tblAdopter.Adoption_Date, tblAnimal_ReleaseOther.Date_Released, tblPound_Release_Owner.Departure_Date
HAVING (((nz([Euthanized_Date],nz([Adoption_Date],nz([Date_Released],[Departure_Date]))))>12-31-2005));[/tt]

 

[Dophia]

Remou: Thank YOU! This works perfectly. I only added the date delimiters to the last part of the SQL:
[Departure_Date]))))>#12-31-2005#))

Thanks for your guidance and showing me how to apply this for future queries!!
Sophia