Query by month problems largely resolved

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Well, I've gotten the query to not find the June 2004 data.

Since most of my June 2004 data display a 2-digit year (despite the input
mask requiring a 4-digit year - which is another issue I'm having with this
db), I tried the criteria "Between "06/01/2005" And "06/30/2005" And Not
Like "06/**/04""
in the query. It got rid of all the June 2004 data except the specific date
of 06/23/2004 - which for some reason shows a 4-digit year like it's supposed
to. So I added "And Not Like "06/23/2004"" to the above string.

It worked - all I'm getting now is the June 2005 data.

There's got to be an easier way, so I'd still welcome suggestions.

Ruth
 
A little bit tricky since I'm missing the context of your original query:
IS the field you're searching for a date field? If so, I would have
expected your "between" criteria to have worked. If not, you should think
about changing it into one.

Another alternative would be to use the month and Year functions like so:
month(FieldName) = 6 and year(FieldName) = 2005

But again, these will only work for date fields...
 
Well, I've gotten the query to not find the June 2004 data.

Since most of my June 2004 data display a 2-digit year (despite the input
mask requiring a 4-digit year - which is another issue I'm having with this
db), I tried the criteria "Between "06/01/2005" And "06/30/2005" And Not
Like "06/**/04""
in the query. It got rid of all the June 2004 data except the specific date
of 06/23/2004 - which for some reason shows a 4-digit year like it's supposed
to. So I added "And Not Like "06/23/2004"" to the above string.

It worked - all I'm getting now is the June 2005 data.

There's got to be an easier way, so I'd still welcome suggestions.

Ruth
Click to expand...

The easier way is to use an Access Date/Time type field to store your
data. It appears that you are storing the data in a Text field. Access
does not recognize the TEXT STRING "06/23/2004" as a date - it's a
bunch of ASCII text characters, greater than the text string
"05Babylon" and less than the text string "077777777".

If you create a new, empty Date/Time field in your table you can run
an Update query updating it to

=CDate([YourCurrentTextField])

You would then be able to use a criterion of
= DateSerial(Year(Date()), [Enter month number:], 1) AND < DateSerial(Year(Date()), [Enter month number:] + 1, 1)
Click to expand...

to prompt for a month (6 being June) and get this year's data for that
month.

John W. Vinson[MVP]
 
Back
Top