Access 2007 - Date range query bizarre behaviour

[subtilty]

Hi All,

I am having a weird issue with a query.

The date field in my table is set to general long time so: dd/mm/yy hh:mm:ss

Now I perform a query: Between "01/05/12 00:00:00" And "03/05/12 00:00:00" and it works fine and brings back data for the 2nd. However if I change the query to: Between "25/04/12 00:00:00" And "03/05/12 00:00:00" it brings back completely random data all after the 3rd so it looks like as soon as I add a date that is not the same month it acts strange.

I am not allowed to send over the table due to data protection but do any of you have any idea what is going on? Thanks very much.

 

[Galaxiom]

Are your dates stored as text insted of date datatype?

 

[subtilty]

Hi thanks for replying, yes they are stored as text.

 

[RainLover]

Galaxiom

How did you work that one out. Well done.

 

[Galaxiom]

Once you change the field to DateTime the query will work properly. As text they are being sorted alphanumerically.

 

[subtilty]

Hi thanks, I now get 'Data type mismatch in criteria expression'

 

[Galaxiom]

Delete the criteria from the field and reenter it. It should format the criteria like this: #1/3/2012#

If that doesn't fix it delete the field from the query and reenter it.

 

[subtilty]

If I enter it like that it automatically puts "'s around it. Is that causing an issue?

I have also tried deleting the field from the query and re-entering it but still the same issue.

 

[Galaxiom]

Galaxiom

How did you work that one out. Well done.

Only thing that made sense.

In fact the question should have made it clear but I didn't notice how literal the poster was being until I looked back.

Between "01/05/12 00:00:00" And "03/05/12 00:00:00"

 

[Galaxiom]

If I enter it like that it automatically puts "'s around it. Is that causing an issue?

That is what Access does when entering criteria into a text field so the query still thinks it is a text field.

If the table is stored in another database try relinking the table.

Then try making a new query.

 

[subtilty]

Great thanks very much.