Select query not working... only showing 14 records out of 48!

[HelenWy]

SOLVED: Select query not working... only showing 14 records out of 48!

Hi all - I'm stuck. I have (currently) 48 records in my database, and I am running a query to bring back all records since 1st April - it just happens that everything in there only goes back to 1st April.

For some reason, though, my query will only show me 14 records - I have tried making a brand new one, I have tried only using 1 or 2 fields as a test, I have taken out my date range etc etc but I still only get 14 records!

Has this happened to anyone else? I really can't see why it is doing this and I need to get it fixed ASAP.

Helen

 

[MStef]

You make something wrong, obviously.

 

[DCrake]

What does the SQL statement look like? are you sure its not translating the dates dd/mm or mm/dd

 

[HelenWy]

Well, obviously I "make" something wrong, MStef - thanks for that! I was asking if anyone had any idea *what*...

DCrake - thanks for a more sensible suggestion! I will check the SQL statement.

 

[HelenWy]

DCRake - thank you! That is exactly what it is doing - do you know how I can fix it? Strange that it hasn't happened before...

 

[DCrake]

Ok let us into the secret what is your SQL saying?? A little knowledge goes a long way.

 

[HelenWy]

So sorry - here it is!

SELECT COMPLAINTS.[COMPLAINT NUMBER], COMPLAINTS.YEAR, COMPLAINTS.QUARTER, COMPLAINTS.[DATE COMPLAINT RECEIVED BY SSD], Divisiondata2.DIVISION, [Service Area2].[Service Area], Area2.Area, Cluster2.Cluster, [Type of Service 2].[Type of Service]
FROM [Type of Service 2] INNER JOIN (Cluster2 INNER JOIN (Area2 INNER JOIN ([Service Area2] INNER JOIN (Divisiondata2 INNER JOIN COMPLAINTS ON Divisiondata2.ID = COMPLAINTS.Division2) ON [Service Area2].ID = COMPLAINTS.[Service Area2]) ON Area2.ID = COMPLAINTS.Area2) ON Cluster2.ID = COMPLAINTS.Cluster2) ON [Type of Service 2].ID = COMPLAINTS.[Type of Service2]
WHERE (((COMPLAINTS.[DATE COMPLAINT RECEIVED BY SSD]) Between #4/1/2009# And #6/30/2009#));

You can see the dates at the bottom that have been transposed. I'm curious to know why this hasn't ever been a problem before though! I'm probably doing something utterly weird and random...

 

[DCrake]

What dates did you enter into the query on the condition line?

Whay you could try is using medium date format.

Between #1-Apr-09# and #30-Jun-09#

David

 

[HelenWy]

Hi David

I enter 01/04/2009 (i.e. 1st April) and 30/06/2009, and this format hasn't thrown up this issue in the past (when there have been other records on the database).

I will try the medium date format and see if it works though! Thanks for your help.

 

[HelenWy]

I have tried the medium date format in the query and it still gives the same results. Curious. I tried changing it in the SQL statement and it continues to revert back to the short date format, and transposing.

 

[DCrake]

Have you set up any formats in your source table?

Format dd/mm/yyyy
Input Mask 99/99/0000;0;_

David

 

[HelenWy]

No, hadn't done that - didn't realise I had to until now! Have tried it with both the format inputted (already had short date selected, but changed it to dd/mm/yyyy) and also with the input mask. Sadly, nothing seems to have changed and I'm still only getting 14 results. The SQL statement keeps switching the dates back to US format.

And this makes me think that for 2 years m

 

[HelenWy]

No, hadn't done that - didn't realise I had to until now! Have tried it with both the format inputted (already had short date selected, but changed it to dd/mm/yyyy) and also with the input mask. Sadly, nothing seems to have changed and I'm still only getting 14 results. The SQL statement keeps switching the dates back to US format.

And this makes me think that for 2 years my results have been wrong. OMG.

 

[DCrake]

You could try saving the format as medium date in your table. There is also a sample table full of dates in the sample database section that I posted a couple of weeks ago that you could incorperate into your mdb and use this to get the date ranges out of your queries. It handles both dmy and mdy dates.


Link

David

 

[Brianwarnock]

You said in your first post

I have taken out my date range etc etc but I still only get 14 records

!

so why do we think that it is to do with the dates, hard coded dates in SQL are in US format.

Brian

 

[HelenWy]

Brian - I was actually just thinking the same thing. The funny thing is that I ran the query yesterday and it worked perfectly, bringing back all the records.

Sounds like the date issue is a red herring - as I said, it's not been a problem until today. So, ignoring that bit - what else could be causing my records to only show a small portion??

 

[HelenWy]

OK, have just checked another table in my database and a query of the same design brings back the right number of records - it appears to be a problem with this particular table/these records.

 

[Brianwarnock]

The only place that filtering is taking place is on the joins, are you sure all records should be selected given the data in each join field?

Brian

 

[DCrake]

Out of curiosity more than anything I created a query that returned dates and I set a where condition on the dates. What I wanted was all dates between the 1st April 2009 and 30 June 2009

In the conditon line I entered

Between 39904 and 399904

It returned the correct records. It did this for dates that were dmy and mdy. So in conclusion if you want to filter a query based on dates get the where condition to pass the Julien date as opposed to the formatted date.

The easiest way to do this is to either do it directly in the query using a function or by calculating the number on your form and get your query to refer to the number and not the date.

Public Function GetJulien(Optional AnyDate As Date = 0) As Long

     GetJulien = DateDiff("d",0,AnyDate)

End Function

So in your query instead of using

Between #01/04/2009# And #30/06/2009#

Use

Between GetJulien(#01/04/2009#) And GetJulien(#30/06/2009#)

David

 

[HelenWy]

Somehow, I've managed to solve it. I don't know how, but I have! Thanks guys for trying to help - must have been gremlins in the system