Using 'between' in dates from Works database

[Guest]

Hello, you were kind enough to answer my question regarding date criteria in
queries using data from a works database where there are several dates in the
format dd.mm.yyyy in one field. Could someone please help me to develop the
query so that years between say 2002 and 2007 can be selected in one query?
Your help would be most appreciated.

 

[Allen Browne]

So you imported the data from Works into an Access table.

Open that table in design view.
This "date" field: is it a Date/Time type? Or a Text type field?
That will make a difference to what you need to do here.

If it is a Date/Time field, you will be able to filter it just by creating a
query, and typing the dates into the Criteria row under the field.

If it is a Text field, you will need to convert it to a Date/Time field,
either in the table or in the query, before you can work with the data
reliably. Post back if you need more detail on this.

 

[Guest]

Thank you for responding so quickly.

The field is a text field. I am able to query on one year by using the
following criteria. This shows me every row where that year is in the field.
Like "*" & [Enter four digit year:] & "*"
Is there any way of adjusting this criteria to show all rows where years
between say 2002 and 2007 are in there?

Sadie

 

[Allen Browne]

You could try typing this expression into the Field row:
IIf([MyField] Is Null, Null, DateSerial(Right([MyField],4),
Mid([MyField]4,2), Left([MyField],2))

Replace MyField with the name of your field.
You can treat it as a date field, and use Criteria like this:

= #1/1/2002# And < #1/1/2008#

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you for responding so quickly.

The field is a text field. I am able to query on one year by using the
following criteria. This shows me every row where that year is in the
field.
Like "*" & [Enter four digit year:] & "*"
Is there any way of adjusting this criteria to show all rows where years
between say 2002 and 2007 are in there?

Sadie

So you imported the data from Works into an Access table.

Open that table in design view.
This "date" field: is it a Date/Time type? Or a Text type field?
That will make a difference to what you need to do here.

If it is a Date/Time field, you will be able to filter it just by
creating a
query, and typing the dates into the Criteria row under the field.

If it is a Text field, you will need to convert it to a Date/Time field,
either in the table or in the query, before you can work with the data
reliably. Post back if you need more detail on this.

 

[Guest]

Sorry I'm assuming you mean the field row in the query design view? if so it
comes up with an error message.

You could try typing this expression into the Field row:
IIf([MyField] Is Null, Null, DateSerial(Right([MyField],4),
Mid([MyField]4,2), Left([MyField],2))

Replace MyField with the name of your field.
You can treat it as a date field, and use Criteria like this:

= #1/1/2002# And < #1/1/2008#

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you for responding so quickly.

The field is a text field. I am able to query on one year by using the
following criteria. This shows me every row where that year is in the
field.
Like "*" & [Enter four digit year:] & "*"
Is there any way of adjusting this criteria to show all rows where years
between say 2002 and 2007 are in there?

Sadie

So you imported the data from Works into an Access table.

Open that table in design view.
This "date" field: is it a Date/Time type? Or a Text type field?
That will make a difference to what you need to do here.

If it is a Date/Time field, you will be able to filter it just by
creating a
query, and typing the dates into the Criteria row under the field.

If it is a Text field, you will need to convert it to a Date/Time field,
either in the table or in the query, before you can work with the data
reliably. Post back if you need more detail on this.

Hello, you were kind enough to answer my question regarding date
criteria
in
queries using data from a works database where there are several dates
in
the
format dd.mm.yyyy in one field. Could someone please help me to
develop
the
query so that years between say 2002 and 2007 can be selected in one
query?
Your help would be most appreciated.

 

[Guest]

Allen,

I think you missed that the OP indicated that there were multiple dates in a
single field.

--
Email address is not valid.
Please reply to newsgroup only.

You could try typing this expression into the Field row:
IIf([MyField] Is Null, Null, DateSerial(Right([MyField],4),
Mid([MyField]4,2), Left([MyField],2))

Replace MyField with the name of your field.
You can treat it as a date field, and use Criteria like this:

= #1/1/2002# And < #1/1/2008#

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you for responding so quickly.

The field is a text field. I am able to query on one year by using the
following criteria. This shows me every row where that year is in the
field.
Like "*" & [Enter four digit year:] & "*"
Is there any way of adjusting this criteria to show all rows where years
between say 2002 and 2007 are in there?

Sadie

So you imported the data from Works into an Access table.

Open that table in design view.
This "date" field: is it a Date/Time type? Or a Text type field?
That will make a difference to what you need to do here.

If it is a Date/Time field, you will be able to filter it just by
creating a
query, and typing the dates into the Criteria row under the field.

If it is a Text field, you will need to convert it to a Date/Time field,
either in the table or in the query, before you can work with the data
reliably. Post back if you need more detail on this.

Hello, you were kind enough to answer my question regarding date
criteria
in
queries using data from a works database where there are several dates
in
the
format dd.mm.yyyy in one field. Could someone please help me to
develop
the
query so that years between say 2002 and 2007 can be selected in one
query?
Your help would be most appreciated.

 

[Allen Browne]

Yes: it goes in the Field row in query design.

You can probably adjust the expression to get it to work.
Perhaps like this:

IIf([MyField] Is
Null,Null,DateSerial(Right([MyField],4),Mid([MyField],4,2),Left([MyField],2)))

If that doesn't work, there may be invalid dates, or inconsistent data (e.g.
leading zeros missing.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Sorry I'm assuming you mean the field row in the query design view? if so
it
comes up with an error message.

You could try typing this expression into the Field row:
IIf([MyField] Is Null, Null, DateSerial(Right([MyField],4),
Mid([MyField]4,2), Left([MyField],2))

Replace MyField with the name of your field.
You can treat it as a date field, and use Criteria like this:

= #1/1/2002# And < #1/1/2008#

Thank you for responding so quickly.

The field is a text field. I am able to query on one year by using the
following criteria. This shows me every row where that year is in the
field.
Like "*" & [Enter four digit year:] & "*"
Is there any way of adjusting this criteria to show all rows where
years
between say 2002 and 2007 are in there?

Sadie

:

So you imported the data from Works into an Access table.

Open that table in design view.
This "date" field: is it a Date/Time type? Or a Text type field?
That will make a difference to what you need to do here.

If it is a Date/Time field, you will be able to filter it just by
creating a
query, and typing the dates into the Criteria row under the field.

If it is a Text field, you will need to convert it to a Date/Time
field,
either in the table or in the query, before you can work with the data
reliably. Post back if you need more detail on this.

Hello, you were kind enough to answer my question regarding date
criteria
in
queries using data from a works database where there are several
dates
in
the
format dd.mm.yyyy in one field. Could someone please help me to
develop
the
query so that years between say 2002 and 2007 can be selected in one
query?
Your help would be most appreciated.

 

[Guest]

Sadie,

Can you give an example of what the text looks like in one of the fields
that contains multiple dates? Are the dates separated by commas, or some
other delimeter?

The easiest and fastest way may be to write a function to parse this field
and check the values. However, you could do this by creating a table
(tbl_Years) with a field YearValue (don't use Year as a field name because it
is a reserved word) and has one record for each year in the range of years
you are interested in (2000, 2001, .....2020). Then, create a query that
looks something like (assumes you have an ID field in your table):

SELECT DISTINCT yourTable.*
FROM yourTable, tbl_Years
WHERE yourTable.DateField Like "*" & tbl_Years.YearValue & "*"
AND tbl_Years.YearValue BETWEEN [Enter start year] AND [Enter end year]

Dale

 

[John Spencer]

You could enter "200[2-7]" in response to the prompt. That should find
every record where the field contains 2002, 2003, 2004, 2005, 2006, or 2007.

The real problem here is that you are storing multiple values in one field,
so it is tough to search properly.

If you wanted All the 2000 dates, you could Enter "200#" as the response.

All years ending in 9 "###9"
All years in the 21st century ending in 9 - "20#9"

If you wanted to get the odd years from 2000 to 2010 - "200[13579]"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thank you for responding so quickly.

The field is a text field. I am able to query on one year by using the
following criteria. This shows me every row where that year is in the
field.
Like "*" & [Enter four digit year:] & "*"
Is there any way of adjusting this criteria to show all rows where years
between say 2002 and 2007 are in there?

Sadie

So you imported the data from Works into an Access table.

Open that table in design view.
This "date" field: is it a Date/Time type? Or a Text type field?
That will make a difference to what you need to do here.

If it is a Date/Time field, you will be able to filter it just by
creating a
query, and typing the dates into the Criteria row under the field.

If it is a Text field, you will need to convert it to a Date/Time field,
either in the table or in the query, before you can work with the data
reliably. Post back if you need more detail on this.

 

[Guest]

Thank you all for your responses. I think that this one from John is the
simplest and seems to work well. Again many thanks

Sadie

You could enter "200[2-7]" in response to the prompt. That should find
every record where the field contains 2002, 2003, 2004, 2005, 2006, or 2007.

The real problem here is that you are storing multiple values in one field,
so it is tough to search properly.

If you wanted All the 2000 dates, you could Enter "200#" as the response.

All years ending in 9 "###9"
All years in the 21st century ending in 9 - "20#9"

If you wanted to get the odd years from 2000 to 2010 - "200[13579]"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thank you for responding so quickly.

The field is a text field. I am able to query on one year by using the
following criteria. This shows me every row where that year is in the
field.
Like "*" & [Enter four digit year:] & "*"
Is there any way of adjusting this criteria to show all rows where years
between say 2002 and 2007 are in there?

Sadie

So you imported the data from Works into an Access table.

Open that table in design view.
This "date" field: is it a Date/Time type? Or a Text type field?
That will make a difference to what you need to do here.

If it is a Date/Time field, you will be able to filter it just by
creating a
query, and typing the dates into the Criteria row under the field.

If it is a Text field, you will need to convert it to a Date/Time field,
either in the table or in the query, before you can work with the data
reliably. Post back if you need more detail on this.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello, you were kind enough to answer my question regarding date
criteria
in
queries using data from a works database where there are several dates
in
the
format dd.mm.yyyy in one field. Could someone please help me to
develop
the
query so that years between say 2002 and 2007 can be selected in one
query?
Your help would be most appreciated.