The SQL is correct.

Post #107,554 by tseliot 6/27/03 12:49:43 PM Reply	The SQL is correct. ..syntactically, anyway. Perhaps one of your Serial Number fields has leading/trailing spaces, nulls, tabs, or similar? Heck, maybe one's Unicode and the other isn't. :) Many fears are born of stupidity and ignorance - Which you should be feeding with rumour and generalisation. BOfH, 2002 "Episode" 10
Post #107,558 by tablizer 6/27/03 1:32:51 PM Reply	run toy data Perhaps one of your Serial Number fields has leading/trailing spaces, nulls, tabs, or similar? Indeed. Maybe run it with some "toy" data that you type in yourself. If that works, then it may indeed be above. If it does not work, then give Access the evil eye. Perhaps try it with a regular join just to see what happens. ________________ oop.ismad.com
Post #107,559 by boxley 6/27/03 1:33:13 PM Reply	how would I TRIM the above statement? my methods cause access to crash thanx, bill will work for cash and other incentives [link\|http://home.tampabay.rr.com/boxley/resume/Resume.html\|skill set] questions, help? [link\|mailto:pappas@catholic.org\|email pappas at catholic.org] As the Poets have mournfully sung. Death takes the innocent young, The rolling in money, the screamingly funny, And those who are very well hung. W.H. Auden
Post #107,564 by orion 6/27/03 1:51:48 PM Reply	Try this: UPDATE hsinventory SET hsinventory.[Serial Number] = Trim("" & [Serial Number]); UPDATE tbl_Matrix_master SET tbl_Matrix_master.serial_number = Trim("" & [serial_number]); It works with Access 2000. Trims out the spaces. It also avoids the null errors and other stuff by padding a blank to the text before trimming it. I created two tables named the same as yours, entered a few serial numbers into each table, had a few that matched, and the ones that matched came up in that query before and after the trim. I made sure that some columns had spaces after them. I think Access ignores the extra spaces. Any Relationships you have set on the two tables? I can't think of anything else that could hose it up right now. "I wonder how much of this BS Corporations will continue to shallow before they start looking into alternatives to Microsoft software?" -[link\|http://z.iwethey.org/forums/render/content/show?contentid=106839\|Orion]
Post #107,565 by boxley 6/27/03 2:18:14 PM Reply	your update ran OK, thanks Norm but same problem so I will assume the relationship is in error. There is a newbie(to us) called Diane who will take problem over, She unfortunately is a HP nix admin who knows as much as I do about crapcess so hopefully she can figure it out. thanx, bill will work for cash and other incentives [link\|http://home.tampabay.rr.com/boxley/resume/Resume.html\|skill set] questions, help? [link\|mailto:pappas@catholic.org\|email pappas at catholic.org] As the Poets have mournfully sung. Death takes the innocent young, The rolling in money, the screamingly funny, And those who are very well hung. W.H. Auden
Post #107,590 by orion 6/27/03 5:18:27 PM Reply	Access relationships Under the tools menu is the Relationships command, which shows the relationships of the tables. See what fields are linked between those two tables. This is not hard to do, and I know you can do it. So far the only two fields I know about are serial number fields. There could be others. Like for example, what is the Primary key to the table? There may be a relationship set up between the primary keys of the two tables that is shorting out your query. One thing you could do, is make a new database, import those two tables to it, and then try running your query on that. It won't copy the relationships. Something else I thought of is that the database might be damaged in some way, if this is a brand new database it is unlikely, but if it has been around for a long time you may want to repair and compact the database, then see if the problem goes away. But before you do that, be sure to back it up first. Sometimes the repair and compact might remove data that is damaged, and last thing you need is losing data. "I wonder how much of this BS Corporations will continue to shallow before they start looking into alternatives to Microsoft software?" -[link\|http://z.iwethey.org/forums/render/content/show?contentid=106839\|Orion]

Welcome to IWETHEY!