Building a QUERY in Code

[musicomp33]

I'll apologize ahead of time because I know I won't get the terminology correct in this question...

This is using Access 2000

I have a query that I need to build in an already existing database, the SQL for this query has 2 seperate JOINS. When I import this query from one database to another I don't have any problems. However, when I try to do the Query building through code (or even adding a new query in the database) I get an error message stating that the "JOIN expression is not supported."

If anyone has any ideas on how I can do this I'd really appreciate it.

[PilgrimPete]

Could you post some code?

It might be that when you're building the query from code you are introducing a subtle syntax error which manifests itself as a JOIN error.
I did say might!!

Anyway, seeing some code might help start us off...

[musicomp33]

hey here's what I have, I did check for extra spaces that might be in the SQL, but I didn't find any. I'm not sure if that's what you mean or not.

'Query Fixes
db.QueryDefs.Delete "1500Information"
DoEvents

strSQL = "SELECT [1500InformationMain].*, [1500InformationSub].[Trip Date], [1500InformationSub].[Service Code], [1500InformationSub].Rate, [1500InformationSub].[Pickup Modifier], [1500InformationSub].[Take to Modifier], [1500InformationSub].[Diagnosis Code], [1500InformationSub].[Place of Service Code], [1500InformationSub].DayUnits, [1500InformationSub].EMG, [1500InformationSub].ReservedLocalUse24K, [1500InformationSub].COB, [Company Medicaid Info].[Provider Number], [Company Medicaid Info].[Fed Tax ID], [1500 Facility].[Facility Name], [1500 Facility].[Facility Address], [1500InformationSub].[Prior Authorization No], [1500InformationSub].RateCharge, [Company Information].[Authorized By], [1500Provider].[Provider Name], [1500Provider].[Provider Address], [1500InformationSub].[Charge ID] " & _
"FROM (((1500InformationMain INNER JOIN 1500InformationSub ON [1500InformationMain].[Invoice ID] = [1500InformationSub].[Invoice ID]) LEFT JOIN Trip ON [1500InformationSub].[Trip ID] = Trip.[Trip ID]) LEFT JOIN Facility ON Trip.[Pickup Facility ID] = Facility.[Facility ID]) LEFT JOIN [1500 Facility] ON Trip.[Trip ID] = [1500 Facility].[Trip ID], ([Company Information] LEFT JOIN [Company Medicaid Info] ON [Company Information].[Provider ID] = [Company Medicaid Info].[Provider ID]) LEFT JOIN 1500Provider ON [Company Information].[Provider ID] = [1500Provider].[Provider ID] WHERE ((([1500InformationSub].DayUnits) Is Not Null))"
Set qdf = db.CreateQueryDef("1500Information", strSQL)
.Value = .Value + 1
DoEvents

db.QueryDefs.Delete "1500InformationSub"
DoEvents

[PilgrimPete]

OK. So are you saying that if you copy this SQL and paste it into the Access Querybuilder it works, but using DAO and creating your querydef doesn't?

First guess: I would take a look at the INNER JOIN, I'm not sure it will like to mix INNERs with LEFTs.

[musicomp33]

actually if I paste it in the query builder it doesn't like it. Only if I go and import the query from another location where it was originally built does it work correctly.

[PilgrimPete]

OK, now we're getting somewhere.
Can you post the SQL from the query that does work - maybe there's a typo somewhere?

[musicomp33]

I did try that and I get the same error. I'm not real good with SQLs (my boss did this one, it's my job to fix it, nice huh?), but I have tried that. and I've compared the one I imported that's working with the SQL that isn't when I paste it in and everything looks the same.

Maybe you're right about the INNER and LEFT JOINS, see I don't know it well enough to know if that's true or not.

[PilgrimPete]

Weird. Are you pasting the SQL from an Access 2K database to another Access 2K database?

It might be worth changing the INNER to a LEFT - just to see if it works... it might change the resultset unacceptably, but at least we'll see where the problem is, so we can take it from there.

Any chance that you can post the SQL that works? I might be missing something really obvious.

[Ecniv]

It might be access playing up.
I tried to save Sql statements before and it truncated the statement at 256 characters... not useful.

If you run it to create the query then open the query in the Access 2k mdb and see what has been placed there, you can see whether it was stored correctly.


Vince

[musicomp33]

Alright here is the good SQL from the query that works.



SELECT [1500InformationMain].*, [1500InformationSub].[Trip Date], [1500InformationSub].[Service Code], [1500InformationSub].Rate, [1500InformationSub].[Pickup Modifier], [1500InformationSub].[Take to Modifier], [1500InformationSub].[Diagnosis Code], [1500InformationSub].[Place of Service Code], [1500InformationSub].DayUnits, [1500InformationSub].EMG, [1500InformationSub].ReservedLocalUse24K, [1500InformationSub].COB, [Company Medicaid Info].[Provider Number], [Company Medicaid Info].[Fed Tax ID], [1500 Facility].[Facility Name], [1500 Facility].[Facility Address], [1500InformationSub].[Prior Authorization No], [1500InformationSub].RateCharge, [Company Information].[Authorized By], [1500Provider].[Provider Name], [1500Provider].[Provider Address], [1500InformationSub].[Charge ID]
FROM (((1500InformationMain INNER JOIN 1500InformationSub ON [1500InformationMain].[Invoice ID] = [1500InformationSub].[Invoice ID]) LEFT JOIN Trip ON [1500InformationSub].[Trip ID] = Trip.[Trip ID]) LEFT JOIN Facility ON Trip.[Pickup Facility ID] = Facility.[Facility ID]) LEFT JOIN [1500 Facility] ON Trip.[Trip ID] = [1500 Facility].[Trip ID], ([Company Information] LEFT JOIN [Company Medicaid Info] ON [Company Information].[Provider ID] = [Company Medicaid Info].[Provider ID]) LEFT JOIN 1500Provider ON [Company Information].[Provider ID] = [1500Provider].[Provider ID]
WHERE ((([1500InformationSub].DayUnits) Is Not Null))

[PilgrimPete]

Hmmmm.
OK, I'm stumped.
They're identical.

Maybe Vince's truncation idea is the reason..? But that wouldn't explain why when you paste this into a new query it doesn't work...

My only other suggestion (from way out there in left field) is to check that you do actually have all the tables in the target database. Sorry, I'm a bit stuck...

[musicomp33]

hey no problem. Thanks for all your help, you gave me a lot of your time.

My boss did something else to get around it. ??? Something with linked tables. lol I'm not sure about these databases sometimes. I'll stick with coding. lol


thanks again.