Make a query in VBA code

[Guest]

Hi!
I try to make a code in the VBA to run a retest query. But it will give a
error. The query will include two tables([A Details] and [A]). I wrote it
like this in SQL, but it gave me an Run-Time error '3079'. The specified
field '[Co]' could refer to more than one table listed in the FROM clause of
your SQL statement. Is this meaning I can only use one table in this method?

Thank you .
fox

VBA code
dbSelect = "SELECT [A Details].*, [A].[Seller]" & _
"FROM [A] INNER JOIN [A Details] ON [A].[Date] = [A Details].[Date] AND
[A].[Co] = [A Details].[Co] " & _
"WHERE [Co] = '" & Me.Company.Value & "' " & _
"AND [Date] >= #" & Me.EndDate.Value & "# " & _
"ORDER BY [Date]"
Set dbDetails = Gdb.OpenRecordset(dbSelect)

 

[kingston via AccessMonster.com]

Try
...WHERE [A].[Co] = ... or ...WHERE [A Details].[Co] = ...
instead of just ...WHERE [A].[Co] = ...
The same applies for the field [Date] in your criteria clause.

Hi!
I try to make a code in the VBA to run a retest query. But it will give a
error. The query will include two tables([A Details] and [A]). I wrote it
like this in SQL, but it gave me an Run-Time error '3079'. The specified
field '[Co]' could refer to more than one table listed in the FROM clause of
your SQL statement. Is this meaning I can only use one table in this method?

Thank you .
fox

VBA code
dbSelect = "SELECT [A Details].*, [A].[Seller]" & _
"FROM [A] INNER JOIN [A Details] ON [A].[Date] = [A Details].[Date] AND
[A].[Co] = [A Details].[Co] " & _
"WHERE [Co] = '" & Me.Company.Value & "' " & _
"AND [Date] >= #" & Me.EndDate.Value & "# " & _
"ORDER BY [Date]"
Set dbDetails = Gdb.OpenRecordset(dbSelect)