I have designed a query with the following fields.

Date;
NumberofDaystoCallBack;
CallBackDate: [Date]+[NumberofDaystoCallBack]

I have created a formated field called DateToCallBack, which is the calculated field.
For example when I enter say today's date 03/11/07 in the Date field
and enter 10 as the number of days to call back in, the calculation field
works just fine and gives me the correct call back date which in this example
would be 13/11/07.
The problem I am having is,
I am trying to set a criteria on the calculated field for a
Between [Start Date] And [End Date], but when I run the query it gives me
all the day dates in the range I specified properly, but it gives me that in every month, when I only wanted one specific month.
For example,
I select between 1 Nov 07 and 8 Nov 07
It gives me all the 1st to the 8th of every month, when really I only wanted Nov.

Any help or suggestions? :confused:

Could you post the SQL from your query please..

I have designed a query with the following fields.

Date;
NumberofDaystoCallBack;
CallBackDate: [Date]+[NumberofDaystoCallBack]

I have created a formated field called DateToCallBack, which is the calculated field.
For example when I enter say today's date 03/11/07 in the Date field
and enter 10 as the number of days to call back in, the calculation field
works just fine and gives me the correct call back date which in this example
would be 13/11/07.
The problem I am having is,
I am trying to set a criteria on the calculated field for a
Between [Start Date] And [End Date], but when I run the query it gives me
all the day dates in the range I specified properly, but it gives me that in every month, when I only wanted one specific month.
For example,
I select between 1 Nov 07 and 8 Nov 07
It gives me all the 1st to the 8th of every month, when really I only wanted Nov.

Any help or suggestions? :confused:

Here is the SQL on this
(please note that the field names are in french).

SELECT SuiviFacialTBL.Date, SuiviFacialTBL.NombredeJourdeRapelleFacialAppoin, [Date]+[NombredeJourdeRapelleFacialAppoin] AS [Date de Rapelle Appointment]
FROM ClientTBL INNER JOIN SuiviFacialTBL ON ClientTBL.ClientID = SuiviFacialTBL.ClientID
WHERE ((([Date]+[NombredeJourdeRapelleFacialAppoin]) Between [Start Date] And [End Date]));

Could you post the SQL from your query please..


[/QUOTE]Here it is, Thanks.
(please note that the field names are in french).

SELECT SuiviFacialTBL.Date, SuiviFacialTBL.NombredeJourdeRapelleFacialAppoin, [Date]+[NombredeJourdeRapelleFacialAppoin] AS [Date de Rapelle Appointment]
FROM ClientTBL INNER JOIN SuiviFacialTBL ON ClientTBL.ClientID = SuiviFacialTBL.ClientID
WHERE ((([Date]+[NombredeJourdeRapelleFacialAppoin]) Between [Start Date] And [End Date]));[/QUOTE]

The 1st thing I noticed was that you are using Date as an object name, Date is an ACCESS reserved word and using it can cause problems. I seem to remember another poster having odd problems which were only resolved when he replaced Date by something else eg FldDate. It might be worth a try.

Brian

Although I think you should change the name of the object Date I was not convinced that that was the problem I did a test and discovered that the problem occored when prompting for dates but not when they were hard coded, when prompting the months and years are ignored???
I've no idea why there should be a difference.

Brian

OK if I use a form , my usual approach, to provide the parameters it all works ok, and a little bell is ringing in my brain saying that this has occurred before, but the slow old grey matter can't think when.

Brian

You have to format the dates used in an SQL query in U.S. format (not fun I know but hey - that's the way it has to be).

Even if that's true Bob, I still don't understand the result.
In my test the start and enddates were 01/01/2006 06/01/2006 and it selected all records with the date starting between 01/ and 05/ irrespective of the other values eg it returned 04/01/2000

All European format

Brian

I can't explain the why's of all of it but I do know that there have been several posts on the site, including by MVP's, that say that SQL queries need dates formatted in U.S. format to work consistently. Since I am on the "other side of the pond" and use that format it's never been an issue to me so I really can't comment other than from what I've heard.

But Brian - I'll liken it to Allen Browne's response to a poster on the MS forums recently. He said something to the effect of "I can't explain why it worked for you all that time but doing something the wrong way will eventually catch up to you."

I can't explain the why's of all of it but I do know that there have been several posts on the site, including by MVP's, that say that SQL queries need dates formatted in U.S. format to work consistently. Since I am on the "other side of the pond" and use that format it's never been an issue to me so I really can't comment other than from what I've heard.

But Brian - I'll liken it to Allen Browne's response to a poster on the MS forums recently. He said something to the effect of "I can't explain why it worked for you all that time but doing something the wrong way will eventually catch up to you."

Hi Brian & Bob,
Thank you both for such quick replies.
Bob, you mention using US date setting format. How do I go about
setting my queries in US Date format?
Should it be set in my regional settings as well?
It sounds simple enough!

Many Thanks to Both,
Luc

Hi Brian & Bob,
Thank you both for such quick replies.
Bob, you mention using US date setting format. How do I go about
setting my queries in US Date format?
Should it be set in my regional settings as well?
It sounds simple enough!

Many Thanks to Both,
Luc

GUESS WHAT BOB.
I went ahead and changed my regional settings to US Format and it worked like a charm.
THANKS A BUNCH! YOU MADE MY DAY!
Can't thank you enough.
I just want you to know that I only registered this morning to this Access Forum and I must say, I was very impressed with you and Brians quick replies.

I appreciate it very much.
Wish you the best.
Regards,
Luc

Hi Bob
Good to know Allen is still around I used his audit trail code a few years ago, it was well commented making it easy for a newboy to change for my own needs. I agree with his comment, I also don't run into this problem except when replying to the forum as I have always used aform to enter parameters and drive queries/reports, there one can format the input and check validation if required.

Glad it was sorted for you LUMEN, Bob is very knowledgeable so you were in good hands.

Brian

Glad it was sorted for you LUMEN, Bob is very knowledgeable so you were in good hands.

And Brian is no slouch either.

Also, Welcome to Access World Forums Luman. We're glad you stopped in.