Solved

Cannot join on memo fields

Posted on 2013-12-12
8
406 Views
Last Modified: 2013-12-12
Hi,

I have an access DB and i am trying to join on memo fields, and i get the error "cannot join on memo fields"

Is there a workaround for this?

Access 2010

Many thanks
0
Comment
Question by:Seamus2626
  • 4
  • 4
8 Comments
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39713699
Try using no join and use where instead:

   Where Left(tblOne.Memofield1, 255) = Left(tblTwo.Memofield2, 255)

/gustav
0
 

Author Comment

by:Seamus2626
ID: 39713710
Havent used Access in a while, where do i put this line, in the criteria box?
0
 

Author Comment

by:Seamus2626
ID: 39713716
When i do put it ib the criteria field, i get the msg

"you have entered an operand without an operator"

thanks
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39713718
It's SQL. Use the SQL view:

Select tblOne.*, tblTwo.*
From tblOne, tblTwo
Where Left(tblOne.Memofield1, 255) = Left(tblTwo.Memofield2, 255)

/gustav
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:Seamus2626
ID: 39713722
Select INPUT D2 DATA.*, Calculation D2.*
From INPUT D2 DATA, Calcualtion D2

Where Left([INPUT D2 DATA].[Customer Type], 255) = Left([Calculation D2].[Client Type], 255)


Im getting a syntax error pointing at the second line and highlighting DATA

Many thanks
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 39713724
Yes. Look at the Left syntax:

Select [INPUT D2 DATA].*, [Calculation D2].*
From [INPUT D2 DATA], [Calcualtion D2]

Where Left([INPUT D2 DATA].[Customer Type], 255) = Left([Calculation D2].[Client Type], 255)

/gustav
0
 

Author Closing Comment

by:Seamus2626
ID: 39713729
Perfect, thanks for your patience gustav.

Regards,
Seamus
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39713736
You are welcome!

/gustav
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now