CODEWITHSUNDEEP
vb.netms-access
Domitius
Domitius

Reputation: 159

Ms-Access: Join 3 Tables

Does anyone have a example of how to join 3 tables, I have the following statement but I'm getting a missing a (syntax error "operator error")

da = New OleDbDataAdapter("SELECT [S].[Scheduled Vege], [V].[Description], 
        [DS].[Task], [DS].[Task Date], [DS].[Completed] FROM [Scheduled] AS S 
        INNER JOIN [Date Schedules] AS DS ON [S].[SchedID] = [DS].[SchedID] 
        INNER JOIN [Vegetables Descriptions] AS V ON [V].[Task] = [DS].[Task] 
        WHERE [DS].[TaskNumber] >= " & aFromDate & " AND [DS].[TaskNumber] <= " & aToDate & " 
        AND [DS].[Completed] = '" & aCompleted & "' ", conn)

thanks

Upvotes: 2

Views: 3153

Answers (3)

Domitius
Domitius

Reputation: 159

Found the problem, because it's an access database the fisrt from statement and INNER JOIN needs to be in brackets as shown below.

da = New OleDbDataAdapter("SELECT [S].[Scheduled Vege], [V].[Description], 
[DS].[Task], [DS].[Task Date], [DS].[Completed] FROM ([Scheduled] AS S 
INNER JOIN [Date Schedules] AS DS ON [S].[SchedID] = [DS].[SchedID]) 
INNER JOIN [Vegetables Descriptions] AS V ON [V].[Task] = [DS].[Task] 
WHERE [DS].[TaskNumber] >= " & aFromDate & " AND [DS].[TaskNumber] <= " & aToDate & " 
AND [DS].[Completed] = '" & aCompleted & "' ", conn)

Upvotes: 5

Paul Sonier
Paul Sonier

Reputation: 39480

Your join looks okay, but I think your problem might be where you're comparing 'TaskNumber' to 'aFromDate'; numbers and dates are different types in SQL, and should cause a problem trying to compare them like this.

Upvotes: 0

Goyuix
Goyuix

Reputation: 24330

Does your query execute if you remove your WHERE clause? The only thing that immediately jumped out at me was your dates are not necessarily going to be in a format that the OleDB provider is going to like.

My guess is that you need to encapsulate your dates like '1-Jan-2009' or something similar.

Upvotes: 0

Related Questions