Using BETWEEN on the date field

[Mbowe]

Hi! Can anyone help me with this? I would like to select the data where criteria is between 01/01/2006 and 12/31/2006, for some reasons it selects everything, seems criteria not to be working.

This is the criteria:

Between "1/1/2006" AND "12/31/2006"

I also tried:

Between #1/1/2006# AND #12/31/2006# which did not select any record at all.

But if I try ="12/31/2006" then works fine. Where am I doing wrong?



Thanks.

 

[SkipVought]

Do you have REAL DATES? STRINGS are not dates. What is the format of this field?

A String 12/31/2006 will not sort properly, since the sequential priority is year, month, day.

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[Mbowe]

The format (data types) of the field is just TEXT. Any suggestion?

Technology does not drive change -- it enables change.

 

[SkipVought]

Why would ANYONE put a date into a text field?

Where DateValue([MyTextDate]) Between #1/1/2006# AND #12/31/2006#

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[Mbowe]

Tell me man, this is a link odbc table from the professional company using date field as text. Anyway, I tried your code did not work, says data type mismatch.

Technology does not drive change -- it enables change.

 

[PHV]

SQL code:
SELECT ...
FROM ...
WHERE CDate([your Text field]) Between #2006-01-01# And #2006-12-31#

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

What is a typical value from this field?

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[Mbowe]

Typical Value is 12/31/2006. I am not sure if that's what you asked. There are date vaules in mm/dd/yyyy format as text data type.

Technology does not drive change -- it enables change.

 

[SkipVought]

Try using the DateSerial function

Where DateSerial(Right([MyDate],4),Left([MyDate],2),Mid([MyDate],4,2)) Between....

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[Mbowe]

SkipVought, I just want to confirm one thing as I am new to Access SQL; Do I replace MyDate with my actual field name? My field name is pro1_indmgtab.desc1 (table_name.field_name)

Technology does not drive change -- it enables change.

 

[PHV]

SELECT ...
FROM ...
WHERE CDate([pro1_indmgtab].[desc1]) Between #2006-01-01# And #2006-12-31#

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Where DateSerial(Right([pro1_indmgtab.desc1],4),Left([pro1_indmgtab.desc1],2),Mid([pro1_indmgtab.desc1],4,2)) Between....

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[Mbowe]

You know, I must be doing something wrong when I use your codes. I am using sql view to add my code then when I go back to the design view my code looks like this:

In the Field: Cdate([pro1_indmgtab].[desc1])
In the Table: just blank
In the Criteria: Between #1/1/2006# AND #12/31/2006#

Technology does not drive change -- it enables change.

 

[PHV]

And the problem is ?

 

[Mbowe]

PHV, I get an error msg saying "Data type mismatch in criteria expression"

Technology does not drive change -- it enables change.

 

[lespaul]

What's the backend database you're connected to?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Mbowe]

Leslie, we are Progress Version 9.1D connected to SQL Server, I am not sure about the version.

Technology does not drive change -- it enables change.