Between function problem with text fields

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

Guest

Hello there

Hope someone can help me, I have a text field called amended date which can hold data such as 1/2003, 2/2003 or 45/2003 etc. I need to extract data that is between weeks 1 and 5 for example, but when i put a criteria of Between "1/2003" and "5/2003" I get all the records displayed. I cannot change the way this data is input as it comes from a mainframe database.

Many thanks in advance

Regards

Sue
 
Sue,

The trick here is to take the two parts apart and apply your filters
separately, e.g. like:
Left([DateField], Len([DateField])-5) >= "1" and Left([DateField],
Len([DateField])-5) <= "5"
Right([DateFiled],4) = "2003"
where DateField is the name of that field in your table.

HTH,
Nikos

Sue McKeating said:
Hello there

Hope someone can help me, I have a text field called amended date which
Click to expand...
can hold data such as 1/2003, 2/2003 or 45/2003 etc. I need to extract data
that is between weeks 1 and 5 for example, but when i put a criteria of
Between "1/2003" and "5/2003" I get all the records displayed. I cannot
change the way this data is input as it comes from a mainframe database.
 
Also, you need to use the numeric values of the functions if you want to use
between. Between "1" and "5" will return "45" because that is between the
textual values. One method to do this would be to add two calculated columns to
your query.

Field: SearchWeek: Val([Amended Date])
Criteria: Between 1 and 5

Field: SearchYear: Val(Right([Amended Date]))
Criteria: 2003

Or try the following (which will not work across years ( for instance, "45/2003"
to "2/2004"))
Field: SearchVal: Right("0" & [Amended Date],7)
Criteria: Between "01/2003" and "05/2003"


Nikos said:
Sue,

The trick here is to take the two parts apart and apply your filters
separately, e.g. like:
Left([DateField], Len([DateField])-5) >= "1" and Left([DateField],
Len([DateField])-5) <= "5"
Right([DateFiled],4) = "2003"
where DateField is the name of that field in your table.

HTH,
Nikos

Sue McKeating said:
Hello there

Hope someone can help me, I have a text field called amended date which
Click to expand...
can hold data such as 1/2003, 2/2003 or 45/2003 etc. I need to extract data
that is between weeks 1 and 5 for example, but when i put a criteria of
Between "1/2003" and "5/2003" I get all the records displayed. I cannot
change the way this data is input as it comes from a mainframe database.
Many thanks in advance

Regards

Sue
Click to expand...
Click to expand...
 
Whoops. Forgot to make sure there was a value for the val function AND that val
will evaluate an expression with arithmetic operators.

Field: SearchWeek: Val(Left(""& [Amended Date],2)
Criteria: Between 1 and 5

Field: SearchYear: Val(Right("" & [Amended Date],4))
Criteria: 2003

To get this to work across years.
Field: SearchWeek: Right("" & [Amended Date],4) &
Format(Val(Left(""& [Amended Date],2),"00")
Criteria: Between "200301" and "200305"

Searchweek will convert your string to a yyyyww string.

John Spencer (MVP) said:
Also, you need to use the numeric values of the functions if you want to use
between. Between "1" and "5" will return "45" because that is between the
textual values. One method to do this would be to add two calculated columns to
your query.

Field: SearchWeek: Val([Amended Date])
Criteria: Between 1 and 5

Field: SearchYear: Val(Right([Amended Date]))
Criteria: 2003

Or try the following (which will not work across years ( for instance, "45/2003"
to "2/2004"))
Field: SearchVal: Right("0" & [Amended Date],7)
Criteria: Between "01/2003" and "05/2003"

Nikos said:
Sue,

The trick here is to take the two parts apart and apply your filters
separately, e.g. like:
Left([DateField], Len([DateField])-5) >= "1" and Left([DateField],
Len([DateField])-5) <= "5"
Right([DateFiled],4) = "2003"
where DateField is the name of that field in your table.

HTH,
Nikos

Sue McKeating said:
Hello there

Hope someone can help me, I have a text field called amended date which
Click to expand...
can hold data such as 1/2003, 2/2003 or 45/2003 etc. I need to extract data
that is between weeks 1 and 5 for example, but when i put a criteria of
Between "1/2003" and "5/2003" I get all the records displayed. I cannot
change the way this data is input as it comes from a mainframe database.
Many thanks in advance

Regards

Sue
Click to expand...
Click to expand...
Click to expand...
 
Back
Top