Date Field query question

[Ray1127]

I have a table with membership data including effective date
If I run a query with the where clause as follows:

Select * from tablea Where eff_date between [startdate] and [enddate]

data includes
8 records with eff_date = 09/01/2008
10 records with eff_date = 10/1/2008
5 records with eff_date = 11/1/2008

If startdate = 10/1/2008 and enddate = 11/30/2008 I get a total of 5 records
If startdate = 11/1/2008 and enddate = 11/30/2008 I get 0 records
If startdate = 09/01/2008 and enddate = 10/31/2008 I get 10 records

The only thing I've noticed is that the eff_date data is imported from a text file and is originally imported as a text field. I use the CVDate(format(eff_date,"mm/yy/dddd")) function to convert it into a date field. I probably missed a step there but I have no clue as to what it could be.

 

[SkipVought]

Hi,

"I use the CVDate(format(eff_date,"mm/yy/dddd")) function to convert it into a date field. "

If eff_date is TEXT, the Format function does absolutely nothing to help you.

DateValue(eff_date) to convert a string into a date field.

assuming that eff_date is structured mm/dd/yyyy



Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ray1127]

Your right the format function I use has no effect I took it out and still got the same result. Unfortunately that still doesn't fix my problem. If the startdate = 9/1/2008 and there are records in the Table with the date 9/1/2008 why don't they show up on the query. I've tested both (between [startdate] and [enddate]) and (eff_date >= [startdate] and eff_date <= [enddate]) with the same result.

 

[lespaul]

again, is EFF_DATE a DATE field or a text field that looks like a date? you will have to go to the table properties to find out.....

Leslie

Have you met Hardy Heron?

 

[Ray1127]

It is a date field that was converted from text

 

[SkipVought]

So you're saying that eff_date, startdate and enddate FIELDS have ALL been converted to DATE data type or are startdate and enddate not fields in your table?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ray1127]

eff_date is a date field. startdate and enddate are date parameters for a query.

 

[randy700]

If you aren't all ready doing it, put your query in vba code. Try running it again, pausing to view it in the immediate window. What does it show there?


Randy

 

[SkipVought]

your "date parameters" are STRINGS, while eff_date is a DATE.

TILT!!!

Select * from tablea Where eff_date between #[startdate]# and #[enddate]#

might work to convert the STRINGS to DATES.


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ray1127]

I modified the query to test and used Select * from tablea where eff_date between CVDate([startdate]) and CVDate([enddate]) and still got the same result using 10/1/2008 for [startdate] and 12/31/2008 for [enddate] only got data for 11/1/2008 and 12/1/2008 Including 10/1/2008 should be 62 records but I only get 23.

 

[SkipVought]

Does CDate do anything different than CVDate?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ray1127]

No it doesn't

 

[SkipVought]

This is not rocket science. You must STILL have a problem with your dates.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ray1127]

I agree this is not rocket science but it still isn't working. As I stated earlier all dates are the first of the month. If I use a begin date of 09/01/2008 I get october 2008 and succeding months but none of Septembers. If I use a begin date of 8/31/2008 I get the 09/01/2008 records and they show a date of 09/01/2008. so why don't they show up if my begin date is 09/01/2008????? and it doesn't seem to matter whether I use between [startdate] and [enddate] OR
startdate<= eff_date and enddate >= eff_date. same result.

 

[randy700]

Have you tried using the # delimiters that Skip suggested several posts back?


Randy

 

[SkipVought]

Please post the SQL that you are currently using.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

What about this ?
Select * from tablea where CLng([eff_date] between CLng([startdate]) and CLng([enddate])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Ray1127]

I finally got it working by creating a form and using a dtpicker control to set the [start] and [enddate] when I run the query from the form it works fine. I have no clue as to why that works and nothing else does but at least it works.