Re: join type not supported

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

When I take out the following line, it saves and works well:

LEFT JOIN Tbl_Archive ON marker=Tbl_Archive.[marker]

however, when I add the line it, it will not save or work.



"Allen Browne" wrote:

In the FROM clause, you have a table name starting with a number. I suspect
that Access will not be able to process that unless you place square
brackets around it, i.e.:
FROM ([1_Stg_WBStatus_Flag] INNER JOIN ...

If that doesn't solve the problem, break it down until you do get it
working, and then you can build it up again to identify where the issue
lies.

For example, just start with just:
SELECT [1_Stg_WBStatus_Flag].histflag, Tbl_Archive.[marker]
FROM ([1_Stg_WBStatus_Flag] INNER JOIN Tbl_MasterPop
ON [1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_MasterPop.[Loan Acct #])
LEFT JOIN Tbl_Archive ON [marker] = Tbl_Archive.[marker]
WHERE ((([1_Stg_WBStatus_Flag].histflag)=1)
AND ((Tbl_Archive.[marker]) Is Null));

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"geebee" <geebee@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CBA3C1B4-282C-40BD-9B2F-301B4AD54CC5@xxxxxxxxxxxxxxxx
I have the following query:

INSERT INTO Tbl_Archive ( [Loan Acct #], [Date], PopEnterDt, PrevStatus,
Status, [Short Name], [Loan Status], [Contract Date], [Mat date], [Next
Pmt
Due Date], [Payoff Date], [Paid Off?], [Nbr of Reg Pmts Made], [# Reg Pmts
Remaining], [Pmt Scd Monthly Pmt Amt], [Last Pmt Date], [Last Pmt Amt],
[Amended?], [Amended This Mo?], [DEL Group], [Days Delinquent], [Tot Past
Due
Pmts $], [Principal Balance], [Val Reserve Dollar Amt], NETBAL, [Ext?],
[Extended This Mo?], [Payoff Dollar Amt], [Flat Void Mth of Orig], [Active
BK
Flag Date], [Active BK Flag], [Inventory Status], [Charge Off Principal],
[Chg Off Type], [CHG OFF Date], [Cure Letter Date], [Skip Active Date],
[Skip
Completion Date], [Skip Completion Sts], [Redeemed Date], [Entered Date],
[Repo Ordered Date], [Active Inactive Flag], [marker] ) IN
'C:\DelqPopRes\Temp\tbl_archive.mdb'
SELECT TOP 10 Tbl_MasterPop.[Loan Acct #], CDate([Datex]) AS Expr1,
Tbl_MasterPop.PopEnterDt, Tbl_MasterPop.PrevStatus, Tbl_MasterPop.Status,
Tbl_MasterPop.[Short Name], Tbl_MasterPop.[Loan Status],
Tbl_MasterPop.[Contract Date], Tbl_MasterPop.[Mat date],
Tbl_MasterPop.[Next
Pmt Due Date], Tbl_MasterPop.[Payoff Date], Tbl_MasterPop.[Paid Off?],
Tbl_MasterPop.[Nbr of Reg Pmts Made], Tbl_MasterPop.[# Reg Pmts
Remaining],
Tbl_MasterPop.[Pmt Scd Monthly Pmt Amt], Tbl_MasterPop.[Last Pmt Date],
Tbl_MasterPop.[Last Pmt Amt], Tbl_MasterPop.[Amended?],
Tbl_MasterPop.[Amended This Mo?], Tbl_MasterPop.[DEL Group],
Tbl_MasterPop.[Days Delinquent], Tbl_MasterPop.[Tot Past Due Pmts $],
Tbl_MasterPop.[Principal Balance], Tbl_MasterPop.[Val Reserve Dollar Amt],
Tbl_MasterPop.NETBAL, Tbl_MasterPop.[Ext?], Tbl_MasterPop.[Extended This
Mo?], Tbl_MasterPop.[Payoff Dollar Amt], Tbl_MasterPop.[Flat Void Mth of
Orig], Tbl_MasterPop.[Active BK Flag Date], Tbl_MasterPop.[Active BK
Flag],
Tbl_MasterPop.[Inventory Status], Tbl_MasterPop.[Charge Off Principal],
Tbl_MasterPop.[Chg Off Type], Tbl_MasterPop.[CHG OFF Date],
Tbl_MasterPop.[Cure Letter Date], Tbl_MasterPop.[Skip Active Date],
Tbl_MasterPop.[Skip Completion Date], Tbl_MasterPop.[Skip Completion Sts],
Tbl_MasterPop.[Redeemed Date], Tbl_MasterPop.[Entered Date],
Tbl_MasterPop.[Repo Ordered Date], Tbl_MasterPop.[Active Inactive Flag],
[tbl_masterpop].[loan acct #] & [expr1] & [tbl_masterpop].[status] AS
marker
FROM (1_Stg_WBStatus_Flag INNER JOIN Tbl_MasterPop ON
[1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_MasterPop.[Loan Acct #]) LEFT
JOIN
Tbl_Archive ON [marker] = Tbl_Archive.[marker]
WHERE ((([1_Stg_WBStatus_Flag].histflag)=1) AND ((Tbl_Archive.[marker]) Is
Null));

I am geting an error message that basically says "join type not supported"
when I try to save the query. How can I fix this? I think it has
something
to do with the [marker], but I am unsure of how to fix it.

Thanks in advance,
geebee



.

Relevant Pages

  • Re: join type not supported
    ... and the query works. ... We are presuming here that the marker field is the same data type in both ... Allen Browne - Microsoft MVP. ... Tips for Access users - http://allenbrowne.com/tips.html ...
    (microsoft.public.access.queries)
  • Re: join type not supported
    ... What is Marker in this context? ... Allen Browne - Microsoft MVP. ... Tips for Access users - http://allenbrowne.com/tips.html ... How can I fix this? ...
    (microsoft.public.access.queries)
  • Re: RoundDown
    ... It that is not the behavior, use Fix() instead of Int. ... Allen Browne - Microsoft MVP. ... Perth, Western Australia ... Tips for Access users - http://allenbrowne.com/tips.html ...
    (microsoft.public.access.formscoding)
  • Re: Can I still use check boxes?
    ... You'll need to build a filter string that looks like this: ... Dim strTmp As String ... Allen Browne - Microsoft MVP. ... Tips for Access users - http://allenbrowne.com/tips.html ...
    (microsoft.public.access.formscoding)
  • Re: Record Change Confirmation
    ... Allen Browne - Microsoft MVP. ... Tips for Access users - http://allenbrowne.com/tips.html ... Private Sub Form_KeyDown ... In fact I have a book with the bookmark at the error handling chapter. ...
    (microsoft.public.access.formscoding)