SQL Returning Not All of Recordset

[vix666]

I have this sql statement below which has recently stopped working and is only returning 14 rows instead of 42 rows. Im completely baffled by it. So any ideas would be greatly appeciated?

Thanks in advance

Vicky

strSQL = "SELECT COUNT([Complaints Table].Counter) AS CountOfCounter, [Complaints Table].[Type of Complaint], Format([Date],""mm"&quot AS Month " & _
" FROM [Complaints Table]" & _
" WHERE [Complaints Table].Date Between #" & [Forms]![Print Request between dates]![sdate] & "#" & _
" And #" & [Forms]![Print Request between dates]![edate] & "#" & _
" AND [Complaints Table].[Attributable to Company]=""epco""" & _
" GROUP BY [Complaints Table].[Type of Complaint], Format([Date],""mm"&quot" & _
" ORDER BY [Complaints Table].[Type of Complaint], Format([Date],""mm"""

 

[DanChard]

It looks to me as though it's likely to be something to do with the start date and/ or end date entered onto the form. Or perhaps the complain date in the table?

The things that cause me most trouble usually boil down to a date being in American format somewhere when I'm expecting it to be British.

Please post again if the problem persists,
Dan

 

[MakeItSo]

Hi there,
Guess Dan is right: looks like a date problem to me, too:
Format([Date],""mm"&quot AS Month
You are using "Date" and "Month" as a field expression here, but it's a function in Access:
Rename [Date] to e.g. [DateField] and use the month function instead of Format:
myMonth=Month([DateField])

This should remove your problems.

[santa2]
Andy

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[vix666]

Thanks for the help. I have changed the dates and it was already storing it as a british date so this did not get rid of my problems. So i have tried writing a simple query eg select [fieldname],[fieldname] from [tablename]; and it only has 1 row for its recordset when it should have over 900. Im so confused why it is doing this, it seems really odd. Any ideas whats happening?

Thanks

Vicky

 

[MakeItSo]

Errr just one row instead of 900 with a simple select query?
You have sth like
SELECT [Counter], [Type of Complaint] FROM [Complaints Table];
and you don't get all records?
Beats me!
Such a select query does nothing but simply displays those two columns of your table, no record selection is made...
You must have overlooked something really simple (like some remaining criteria)

Andy

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[vix666]

I agree i must've overlooked something really simple but can't think what. what did you mean by remaining criteria?

Thanks

Vicky

 

[MakeItSo]

Have you set up this simple select query from scratch, i.e. new, or have you just removed the other tables/fields from your original query?
In the latter case, there could still be some select criteria (in SQL a WHERE clause) remaining from the original query.
Or: You have removed the select fields from the query, but not the additional tables and thus not the JOIN --> this would also lead to such a limitation.

If it's a brand new query: ???
I'm kinda puzzled now!

 

[MakeItSo]

Hi Vicky!
By the way:
back to your first post:
is only returning 14 rows instead of 42 rows.

Are you sure it really should return 42 rows? You have grouped your records.
So If you have, let's say three records per Type of Complaint, you might only get one grouped record from this with the count of 3.
--> So all 42 records would be combined into the 14 you get. Could that be what you're baffled about?

Andy

 

[vix666]

nope i know that they are group, they are grouped to give me a total of complaints per complaint type per month over the date period specified. It should return 42 rows which is grouped from 156 rows.

Im completely baffled by this one

Thanks Anyway

Vicky

 

[vix666]

I have kind of set it up from scratch, it still uses the same strSQL as the string but i entered it all from scratch.

Thanks for the help. Any other suggestions?

Vicky

 

[MakeItSo]

I just had an idea:
perhaps the problem is something totally different:
As I said, the simple select query should return all records.
As it doesn't: Perhaps your db is corrupted. Try a compact/repair or even set-up a new db and import all elements into this one.

Just a (not completely wild) guess...
Andy

 

[vix666]

Thanks alot for all your help. I finally figured out what it was. it was concerning the dates and the format which they were held in. in the table they were held as long date and the form which i referenced to get the dates to query by was holding the date as a short date and it caused a conflict. So now its all sorted.

Thanks for all your help though

Vicky

 

[MakeItSo]

I knew it must have been something simple!

[blue]If everything runs smoothly, you must have overlooked something! Murphy's law[/blue]

Cheers,
Andy