SQL Help

[RayMilhon]

I have a Query that I run in VBA for a recordset that I'm having a problem with. Here's the query

SELECT dbo_tbl_adv_source.advsource, dbo_tbl_Adv_type.Adv_Type, dbo_tbl_adv_schedule.adv_rundate 
FROM dbo_tbl_adv_source 
INNER JOIN dbo_tbl_adv_schedule ON dbo_tbl_adv_source.advsourceid = dbo_tbl_adv_schedule.adv_sourceid 
INNER JOIN dbo_tbl_Adv_type ON dbo_tbl_adv_schedule.adv_TypeID = dbo_tbl_Adv_type.Adv_TypeID 
WHERE dbo_tbl_adv_schedule.adv_schedid = 1;

Everytime it runs I get a syntax error. I put it into Query Analyzer and get the same error. Take the query and put it into SQL Server change the dbo_ to dbo. and it works perfectly. I've looked at the query I don't see any syntax error. Data Structure is

dbo_tbl_adv_source
advsourceid autokey
advsource text

dbo_tbl_adv_type


adv_typeID autokey
adv_type text

dbo_adv_schedule
schedid autokey
adv_sourceID numeric foreign key to dbo_tbl_adv_source
adv_typeid numeric foreign key to dbo_tbl_adv_type
adv_rundate date

[ranman256]

Don't run it as vb, try it as a query. It can help you with the error.

[RayMilhon]

As I stated in my original post I put the SQL into the query analyzer and got the same error.

[aytee111]

Recreate the query in Access from scratch. Make sure it works. Then copy the SQL to VBA.

[RayMilhon]

Thanks aytee111 that worked don't know why I didn't think of that. Probably because that was the easiest way to fix it.

[orange]

Ray,

What is different from the version that caused the error?

[RayMilhon]

Here's the sql that works

SELECT dbo_tbl_Adv_Source.AdvSource, dbo_tbl_Adv_type.Adv_Type, dbo_tbl_Adv_Schedule.Adv_RunDate 
FROM (dbo_tbl_Adv_Schedule INNER JOIN dbo_tbl_Adv_type ON dbo_tbl_Adv_Schedule.adv_TypeID = dbo_tbl_Adv_type.Adv_TypeID) 
INNER JOIN dbo_tbl_Adv_Source ON dbo_tbl_Adv_Schedule.Adv_SourceID = dbo_tbl_Adv_Source.AdvSourceID 
WHERE (((dbo_tbl_Adv_Schedule.Adv_SchedID) =  1)) 
GROUP BY dbo_tbl_Adv_Source.AdvSource, dbo_tbl_Adv_type.Adv_Type, dbo_tbl_Adv_Schedule.Adv_RunDate;

Other than adding the group by and reordering the From clause I don't really see much of a difference.

[orange]

I think the key is the bracket

....(dbo_tbl_Adv_Schedule INNER JOIN dbo_tbl_Adv_type ON 
dbo_tbl_Adv_Schedule.adv_TypeID = dbo_tbl_Adv_type.Adv_TypeID) ....

Glad you have it resolved.

[RayMilhon]

Orange I think you're right. Thanks