Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using BETWEEN on the date field

Status
Not open for further replies.

Mbowe

Programmer
Jan 6, 2006
35
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.

 


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]
[glasses] [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! [tongue][/sub]
 
The format (data types) of the field is just TEXT. Any suggestion?

Technology does not drive change -- it enables change.
 


Why would ANYONE put a date into a text field?
Code:
Where DateValue([MyTextDate]) Between #1/1/2006# AND #12/31/2006#



Skip,
[sub]
[glasses] [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! [tongue][/sub]
 
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.
 
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
 



What is a typical value from this field?

Skip,
[sub]
[glasses] [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! [tongue][/sub]
 
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.
 


Try using the DateSerial function
Code:
Where DateSerial(Right([MyDate],4),Left([MyDate],2),Mid([MyDate],4,2)) Between....


Skip,
[sub]
[glasses] [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! [tongue][/sub]
 
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.
 
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
 

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

Skip,
[sub]
[glasses] [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! [tongue][/sub]
 
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, I get an error msg saying "Data type mismatch in criteria expression"

Technology does not drive change -- it enables change.
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top