Missing a single date in a query

[Guest]

Hi there,

I have a query that uses a calculated field to provide a date range which is
working fine. I then have criteria which picks a date range from the
calculated field.

The problem is that I have 8 records that show up fine and one which isn't
but should. It is perfectly within the date range and when I take away my
criteria it will appear in the list just fine and even appears to be
formatted good also.

Here is my Calculated Field:
ShortRDay: Month(IIf([MinofHire_Date]>[MaxofRehire_Date] Or
IsNull([MaxofRehire_Date]),[MinofHire_Date],[MaxofRehire_Date])) & "/" &
Day(IIf([MinofHire_Date]>[MaxofRehire_Date] Or
IsNull([MaxofRehire_Date]),[MinofHire_Date],[MaxofRehire_Date])) & "/" &
Year(Date())

What I am doing here is building a Review Date based on either hire date or
rehire date. I am also taking dates from more than one database and grouping
them all in a union query thus the mins and maxs. I was thinking maybe my
formatting may be causing the problem since I am just using & "/" & to
manually build a short date field. Yet it is working fine for 8 records, just
not the missing 9th.

My Criteria is simple:
Between [Forms]![frm Monthly Reports]![FromDate] And [Forms]![frm Monthly
Reports]![ThruDate]

Just 2 short date fields that they can supply a date range in.

It must be formatting I think but I just don't see the way to fix it.

Any suggestions?

Thanks

 

[kingston via AccessMonster.com]

Are you sure the calculated field returns a real date? You're constructing a
string out of pieces of dates but the result may not be a valid value (e.g.
02/29/2006)

Hi there,

I have a query that uses a calculated field to provide a date range which is
working fine. I then have criteria which picks a date range from the
calculated field.

The problem is that I have 8 records that show up fine and one which isn't
but should. It is perfectly within the date range and when I take away my
criteria it will appear in the list just fine and even appears to be
formatted good also.

Here is my Calculated Field:
ShortRDay: Month(IIf([MinofHire_Date]>[MaxofRehire_Date] Or
IsNull([MaxofRehire_Date]),[MinofHire_Date],[MaxofRehire_Date])) & "/" &
Day(IIf([MinofHire_Date]>[MaxofRehire_Date] Or
IsNull([MaxofRehire_Date]),[MinofHire_Date],[MaxofRehire_Date])) & "/" &
Year(Date())

What I am doing here is building a Review Date based on either hire date or
rehire date. I am also taking dates from more than one database and grouping
them all in a union query thus the mins and maxs. I was thinking maybe my
formatting may be causing the problem since I am just using & "/" & to
manually build a short date field. Yet it is working fine for 8 records, just
not the missing 9th.

My Criteria is simple:
Between [Forms]![frm Monthly Reports]![FromDate] And [Forms]![frm Monthly
Reports]![ThruDate]

Just 2 short date fields that they can supply a date range in.

It must be formatting I think but I just don't see the way to fix it.

Any suggestions?

Thanks