Query That Sometimes Works?

[nakedbamboo]

I have a query that I am trying to get to work. I had it working perfectly until I added a piece and I would like to figure out why it is not working. Below is a snippet of the query that uses a form as its source.

(((Alumni.CurrentCity)=[Forms]![AlumniSearch]![CurCity])) OR
(((Alumni.CurrentState)=[Forms]![AlumniSearch]![CurState])) OR
(((Alumni.PermCity)=[Forms]![AlumniSearch]![PermCity])) OR
(((Alumni.PermState)=[Forms]![AlumniSearch]![PermState])) OR
(((Alumni.[Undergrad Grad Date])>=[Forms]![AlumniSearch]![UDD] And (Alumni.[Undergrad Grad Date])<=[Forms]![AlumniSearch]![UDD2])))

Everything worked good until I added the last criteria. I am trying to get all records between two dates: UDD and UDD2 on the search form. The dates are a custom format (mm/yyyy) created with a mask (00\/0000;. The query appears to function as long as the two dates don't span different years, i.e. I can search 01/1999 to 12/1999. But if I search for 12/1998 to 12/1999, it will not return any results. I am wondering if it has to do with the custom format. Any guesses as to what the problem may be? If it is the mask causing the trouble, how would I do this with only a month and year entered? Thanks for any help.

 

[rac2]

I don't know what a custom format or a mask is within the context of Access, but I have seen this kind of problem before.

Is it possble that the Data Type of the .[Undergrad Grad Date] column is Text? This would account for your result with 12/1998 and 12/1999 since the sort order of text is

09/1998
09/1999
10/1998
10/1999
11/1998
11/1999
12/1998
12/1999


If not and the Data Type is Date/Time then it may be that Access is converting the Date/Time value to a string instead of converting the string to a Date/Time value. You might try using an explicit conversion function, DateValue(), like so-

SELECT blah
FROM blah
WHERE blah
   OR(
       ( Alumni.[Undergrad Grad Date] >=

DateValue(

[Forms]![AlumniSearch]![UDD]

)

 ) And ( Alumni.[Undergrad Grad Date])  <=

DateValue(

[Forms]![AlumniSearch]![UDD2]

)

 )
      )

I feel this latter possibility is less likely; I would guess that Access would try to convert a string to a date; and going the other way would almost never be the desired result. Furthermore, Access may not be able to convert strings like mm/yyyy to dates at all. This could get quite messy.

Let me know whether this helps.

 

[nakedbamboo]

I figured it out. It was the mask. It was storing the date as a number, i.e. 01/1999 -> 019999. So different months and years really screwed up the chronological order. I looked at the Short Date mask and it had an extra zero on it (00/00/0000;0 so I made my mask just like it with out the first zero or the front slash and it works. The dates are stored as 01/1999. I don't have any idea what that last zero does. Does anybody know? Thanks for the idea rac2.