Table aliases x 3 but no results when there should be

[GRose]

Hello all.

Just noticed I haven't posted in here since 2006, I was amazed I could still log in!

I have a query which uses the same table 3 times, the aliases all appear ok and if I run it via SQL Management Studio, I get the expected results.
The code is as below:

SELECT 
dbo_SROrganisation.Name, 
dbo_SR18WeekWait.DateCompleted, 
dbo_SR18WeekWait.TreatmentFunction, 
A.ConfiguredListOption AS OptionA, 
dbo_SR18WeekWait.Status,
B.ConfiguredListOption AS OptionB, 
dbo_SRReferralIn.ServiceOffered, 
C.ConfiguredListOption AS OptionC

FROM 
((((dbo_SRReferralIn RIGHT JOIN dbo_SR18WeekWait ON dbo_SRReferralIn.RowIdentifier = dbo_SR18WeekWait.IDReferralIn) 
LEFT JOIN dbo_SRConfiguredListOption AS A ON dbo_SR18WeekWait.TreatmentFunction = A.RowIdentifier) 
LEFT JOIN dbo_SRConfiguredListOption AS B ON dbo_SR18WeekWait.Status = B.RowIdentifier) 
LEFT JOIN dbo_SRConfiguredListOption AS C ON dbo_SRReferralIn.ServiceOffered = C.RowIdentifier) 
INNER JOIN dbo_SROrganisation ON dbo_SR18WeekWait.IDOrganisationVisibleTo = dbo_SROrganisation.ID

GROUP BY
dbo_SROrganisation.Name, 
dbo_SR18WeekWait.DateCompleted, 
dbo_SR18WeekWait.TreatmentFunction, 
A.ConfiguredListOption, 
dbo_SR18WeekWait.Status, 
B.ConfiguredListOption, 
dbo_SRReferralIn.ServiceOffered, 
C.ConfiguredListOption

HAVING 
(((dbo_SROrganisation.Name)="GROrg1") 
AND
((dbo_SR18WeekWait.DateCompleted) Between #12/1/2016# And #12/31/2016#));

What is happening is that I get no data for OptionB or OptionC but as I stated, if I run it via SQL MS, it works fine.

I tried using [ and ] around the aliases just in case but it made no difference so what is going on?

 

[Minty]

Is it a date thing ? You have US formatted dates in the SQL above (correctly for SQL Server) but if you are in the UK the dates in the Access query window would be interpreted as UK - if you are in Huntingdon UK that is...

 

[GRose]

Hello Minty.

No, its not the date. In the query design window it shows in UK format but in the SQL view (within Access 2010) it shows that way round.

The query does get results, the exact same number of lines as the SQL MS version but there is just no data in the Access query for OptionB and OptionC.

And yes, its Huntingdon UK.

 

[Minty]

Data types in joins ? SQL will happily join on a text field in one table to a number field in another. Access won't. Although normally it would also complain.

 

[GRose]

Maybe? In SQL it is linking a varchar to a bigint although Access reports them both as text when looking at design view of the table.

I should have mentioned, Access is using linked tables.

 

[Minty]

BigInt is almost certainly your problem. I'm sure I read recently about an issue with how Access interpret Bigint. Have a google - I'm sure that's the issue.

Or store the query as a view on SQL and link to that. (Cop out)

 

[GRose]

Right, problem solved.

View created to replicate the table except it casts the bigint as a varchar.
View now replaces table.
Query works fine.

Time to go home and have a rest, cheers Minty.

 

[Minty]

I have found the original thread https://www.access-programmers.co.uk/forums/showthread.php?p=1515922#post1515922 and in there is a link to the solution (In the future)