Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
 
Home > TechHelp > Directory > Access > Import New Changed < AutoTab | Year End Report >
Back to Import New, Changed Records    Comments List
Query on long text field Upload Images   Link   Email  
Elaine Heltman 
Richard, using the technique you describe in the video, how do I construct the review query correctly using a long text field in the criteria? I tried LeaseT.LeaseNotes <> [TrackingT.LeaseNotes], but the results are incorrect. Let me know if you need more information to assist. Thank you.
Richard Rost 
The criteria for your LeaseT.LeaseNotes field should be:

<>[TrackingT].[LeaseNotes]

Make sure your join is correct. Make sure you can see them next to each other WITHOUT the criteria first. Then add it.
Alex Hedley 
That's gonna be an intense search. Will the only thing that matches be the Notes?
Elaine Heltman 
Thanks, Richard, I'll try your suggestion, and yes, Alex, the Notes will be the only thing that matches.
Elaine Heltman 
I tested without the criteria and both text fields appear, which they also do when I add some test text to the spreadsheet, save, close, and run the query again. But even with the corrected criteria in the query I receive the same incorrect results: the added text does not appear, but the exact same 2 incorrect results do.
Elaine Heltman 
I thought perhaps the text in the incorrect results had some hidden code that was bolloxing up the works, so I deleted the text from the 2 incorrect results, made sure the query ran properly without the criteria, then inserted the criteria: now the query returns no results, even though there are 2 new entries in the spreadsheet which appear without the criteria. Thanks.
Richard Rost 
I can't picture what you're doing. Can you post some screen shots?
Elaine Heltman 

Elaine Heltman 

Elaine Heltman 

Elaine Heltman 
Screen shots: Query with criteria; results without criteria; results with criteria.
Thank you.
Adam Schwanz 
Pretty sure it has to do with the null values. Try changing the fields to an expression and wrap them in an NZ function.
Adam Schwanz 
So like X: NZ(LeaseNotes,"Blank") from one table and Y: NZ(LeaseNotes,"Blank") for the other table, or make them different words if you want it to showup if both are blank.
Adam Schwanz 
Adam Schwanz 
Then do criteria <>Y or whatever, can also probably get away with just "" as the value but words might be more helpful to put N/A or not completed there
Elaine Heltman 
Thanks, Adam. I've created the 2 expressions and they work fine, but then how do you properly use the expression (field), "Y" for <>Y, in the criteria of the query? I've searched the site and tried many options, but can't figure it out. Thanks again for the assistance.
Adam Schwanz 
<>[Y] should work
Elaine Heltman 
An "Enter Parameter Value" dialog pops up when running the query using <>[Y], but I don't see any typos. I'll attach a screen shot.
Elaine Heltman 

Richard Rost 
You'll have to evaluate Y first and then send those results into a 2nd query where you can apply that as a criteria.
Elaine Heltman 
More testing tomorrow morning when my brain is fresh but I believe it's working. Do you find for Y first, because in effect you're defining Y, so you can then use it in the 2nd query? Thanks.
Scott Axton 
Just for a test try making the alias "Q" or some other letter - any other letter than Y.

I know, silly maybe but I once had a program that tried to evaluate the alias - as in True.  No matter what I did it failed until I used another letter.

I also had a customer who's name was Robert True once.  Every time I ran a certain query and his name was in the results the query would fail.  (Honest story I swear!)

Elaine Heltman 
I tried your idea, Scott, with the same result as using Y.

So far so good with Rick's fix (the name of his new band?) and on to the next step. Thanks, everyone.
Adam Schwanz 
Yea that sounds like one of those weird access things, where even though you created Y in the query it doesn't want you to do stuff with it until it's "processed". So when you put it in a new query it's already defined.

Glad that worked for you.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Import New, Changed Records.
 

 
 
 

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
PCResale.NET
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

LinkedIn
Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 3/28/2024 9:31:52 AM. PLT: 0s