Between [ ] And [ ] problem

[ajak1316]

Very new to Access and I have what I guess is a really, really simple problem but I just can't seem to see what I am doing wrong. I have broken my Database down to the most simplistic form to see if it helps but it doesn't. My Table now contains two fields: Date (Date/Time format) and Days (number format). I have put these into a query and calculated a third field: Latest Retest Date: [Date]+[Day]. I entered test data of 01/01/08, 01/02/08 and 01/03/08 with days set to 7 in each case. This works fine. However I then put a Paramater Query in Criteria line under Latest Retest Field of Between [First Date] And [Last Date]. If I run the query with First date 09/02/08 and last date 09/03/08 I get no results despite the third record showing a Latest Retest Date of 08/03/2008. However, if I run with a first date in advance of all three dates, 01/01/2008 for example and a last date beyond all three dates of 31/12/2008 for example, it shows all three records as expected. I guess it has to be a formatting error but I have spent hours looking at FAQs and MS Tutorials. It may simply be a case of not seeing the wood for the trees but it is SO frustrating I would appreciate any advice.

Thanks a lot

ajak1316

 

[raskew]

Hi -

Access generally interprets dates in American (mm/dd/yy) short-date format.
Thus 09/02/08 = 2-Sep-08, 09/03/08 = 3-Sep-08 and 08/03/08 = 3-Aug-08.

Given a date such as 31/12/08, which obviously won't translate in American
short-date format, Access will usually interpret it as intended, i.e. 31-Dec-08.

Hth - Bob

 

[ajak1316]

Hi Bob

Thanks for taking the time to reply. I tried re-entering dates in American format, i.e. first date as 02/09/08 (9th Feb 08) and last date as 03/09/08 (9th March 08) but it still doesn't bring up the expected record containing 8th March 08.

 

[raskew]

How are your dates entered in the database? If as shown in your first post, there may still be a problem

Bob

 

[ajak1316]

Hi Bob

Dates entered in European format 01/03/08 etc but I have formated this field in the table as Medium Date so they appear 01-Mar-08 etc. I will change these to American format to see if it work.

ajak

 

[ajak1316]

Hi Bob

Added two new dates in American format into table, 02/01/08 for 1st Feb and 03/01/08 for 1st March. My calculated field shows these at 9th Jan (+7) and 10th Jan (+7) and Paramater query still not showing them when I use either 09/02/08 for first and 09/03/08 for last nor is I use american format 02/09/08 and 03/09/08.

ajak

 

[raskew]

Ajak-

See this MSKB article http://support.microsoft.com/kb/q130514/ describing how Access stores dates.

As a cross-check, copy/paste this to a new query, changing field and table names to agree with your table.

SELECT [YourField], CDbl([YourField]) AS Expr1
FROM YourTable;

Run the query.
9-Feb-08 should equal 39487
8-Mar-08 should equal 39515
9-Mar-08 should equal 39516

Bob

 

[ajak1316]

Hi Bob

Did that and you are right
09/02/08 equals 39487
08/03/08 equals 39515
09/03/08 equals 39516

ajak

 

[neileg]

Access follows your regional settings in Windows. So inputting US dates on a UK computer just confuses matters. When you run a query you are using SQL and Ql is an american invention so it assumes dates are american format, unless it is clear they are not. The most straightforward solution is to use a medium date format dd-mmm-yyyy. This is unambiguous and avoids the problem. But you have to apply this to everything, so instead of using parameter prompts, use a form to capture user input so you can apply the format there, as well.

 

[ajak1316]

Thanks Neil. I have set properties on table and on my calculated field to medium date and they all appear to be in European format but still I can't get my Between [ ] And [ ] to work.

ajak

 

[Brianwarnock]

I would use DateAdd("d",[days],[date]) and drive the query from a form with the input formatted as dates.

But also I would not use fields named Date and Days as they are ACCess reserved words nor would I use fields with blanks in the names as they can cause more syntax problems.

Brian

I see that Neil beat me to use of a form.

 

[ajak1316]

Hi Brian

Thanks for that. I appreciate everyones efforts. Tried renaming all fields eliminating spaces to avoid potential syntax errors but still it doesn't work. Haven't considered form design yet but I can't figure out just what I am doing wrong. Form design may be favourite but the Between [ ] And [ ] is a standard function the the query should be able to handle. Shouldn't it?

I added another field to query with Expr2ateAdd("d",[days],[date]) and like all the other fields it shows correctly calculated date but I cut and past my Between[ ] And [ ] to the criteria line and again it doesn't work.

ajak

ajak

 

[Brianwarnock]

Prompted dates often give people problems that's why I always use a form and I guess Neil does too, try it.

Brian

 

[raskew]

Ajak-

Would you please post your query SQL.

Bob

 

[Brianwarnock]

Just did a simple test using your fieldnames and data

using #09/02/08# or Forms!frmDates!firstdate

both work with DateAdd, which I prefer, and [Date]+ [Days]

a prompt does not work with either.

Brian

 

[raskew]

Try this, exchanging table and field names.

SELECT
    OrderID
  , OrderDate
FROM
   Orders
WHERE
   (((OrderDate) Between DateAdd("d",7,[startdate]) 
AND
   DateAdd("d",7,[enddate])));

This worked for me where attempting to filter on a calculated field didn't.

Bob

 

[surendra87]

man, you are looking for the wrong thing. the dates that you entered into the table are:
Jan 1, 08
Jan 2, 08
Jan 3, 08

you will not get anything if you enter start date after Jan 3. if you want the date in the table to be Mar 1, 08 then this is what it should look like: 3/1/8

In addition, use office 2007 which has a calendar next to the date field so you always enter the right date. when i first started databases, i encountered this problem alot. if you dont have office 2007 then search the internet for a code to display a calendar.

If this dont work, shoot me dead!!!

 

[Brianwarnock]

man, you are looking for the wrong thing. the dates that you entered into the table are:
Jan 1, 08
Jan 2, 08
Jan 3, 08

If this dont work, shoot me dead!!!

Did you read the thread?
Or are you just an ignorant American who doesn't understand proper dates ie European ones.

Brian

 

[ajak1316]

Hi Bob

Appreciate your efforts. As I said I am very new to Access so not too sure how to "post my SQL". Someone has previously mentioned I should use forms which I appreciate may give me the result I need but I haven't got to the position where I have looked at forms yet. Either way I still don't understand why a standard function of Between [ ] And [ ] doesn't work on a calculated date field. I assumed it was my lack of knowledge but is it a problem with Access not doing what it should?

ajak

 

[neileg]

Hi Bob

Appreciate your efforts. As I said I am very new to Access so not too sure how to "post my SQL".

Open your query in design view. From the menu, select SQL view and copy and paste the SQL text from there.

Someone has previously mentioned I should use forms which I appreciate may give me the result I need but I haven't got to the position where I have looked at forms yet. Either way I still don't understand why a standard function of Between [ ] And [ ] doesn't work on a calculated date field. I assumed it was my lack of knowledge but is it a problem with Access not doing what it should?

No it's not Access, it's SQL. SQL wasn't written to cope with anything other that US date formats. It's a feature or a design flaw or a bug, but whatever you call it, you need to work around it. Using a form solves the problem, and is a much more elegant way of capturing user input anyway.