select query

[talktobatchu]

Hello
I'm using a select query in VBA to retrieve a set of
rows by enterning the begining year and ending year by
using a 'Between' keyword. When I run the query it gives
me the set of rows for all the corresponding years which I
entered. Instead what should be done to get only the two
specified rows matching the begining year and ending year.

here is the code as it looks:
((HepatitisA_90to03Data.Year) Between [Enter the Starting
Year (>1989)] And [Enter Ending Year])

here I need only the rows related to the starting year and
ending year which I enter. which way in access can this be
done.
Thank you.

talktobatchu

 

[fredg]

Hello
I'm using a select query in VBA to retrieve a set of
rows by enterning the begining year and ending year by
using a 'Between' keyword. When I run the query it gives
me the set of rows for all the corresponding years which I
entered. Instead what should be done to get only the two
specified rows matching the begining year and ending year.

here is the code as it looks:
((HepatitisA_90to03Data.Year) Between [Enter the Starting
Year (>1989)] And [Enter Ending Year])

here I need only the rows related to the starting year and
ending year which I enter. which way in access can this be
done.
Thank you.

talktobatchu

The query is returning what you have asked for...
All records between Date1 and date2

What you want, if I understand you correctly, is only records for the
Year of Start Date and only records for the Year of End Date

1) You have a field named [Year].
Year is a reserved Access/VBA word and should not be used as a field
name.
See the Microsoft KnowledgeBase article for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'


2) After you change the field name from 'Year' to something else, you
can use....
If [YearField] is a Number datatype field and contains a number value
for the year, i.e. 1999, not an actual date value i.e. 5/25/1999:

Where HepatitisA_90to03Data.YearField= [Enter the Starting
Year (>1989)] Or HepatitisA_90to03Data.YearField = [Enter Ending
Year]

However...

If the [YearField] is actually a Date Datatype field, (i.e.
5/25/1999) then you should use:

Where Year(HepatitisA_90to03Data.[YearField]) = [Enter the Starting
Year (>1989)] or Year(HepatitisA_90to03Data.[YearField]) = [Enter
Ending Year]

In both cases, if you enter 1990 at the first prompt and 2002 at the
second prompt and only records for those 2 years will be returned

3) I assume the 90to03 in the table name refers to the years of
records in the table.
If you really have a table named HepatitisA_90to03Data you are not
using Access properly.

What are you going to do next year when the data extends into 2004?
And then into 2005? Create a new table each time and re-name it?

A table named HepatitisA with all the records having a Date Datatype
field will do just as well. And you simply add records into it, even
if it's an archive table. Then use a query, as above, to extract what
ever year's records you wish to see.

 

[talktobatchu]

Hello

Thank you Fred for the solution and it worked fine.

My table actually contains columns of: districtno,
yearfield, countyname, Population, NoOfcases and
Percentage for a particular disease. As you have said
there seems to be a lot of redundancy and improper design
in the table. The table contains repeated entries for
year, countyname and the districtno to store the data.

So which way would be the best to store the above kind of
data into a access table. Thank you.

talktobatchu