A query about queries!

[Wullig]

Hi folks,
I'm no database whizz, but usually get by with most things, but I have an annoying problem I'm curious to know the answer to.

Recently been upgraded to Access 2007, and I have a database that pulls in excel files for comparing.
I use a first query to strip text from the middle of a text string in the orig table, using Mid() & InStr() - This works perfect, I get what I need.

Then I use another query which compare a master table against the values generated by Query 1 - This can match records OK, but the records which have no match in Query1 have '#Error' displayed & I can't filter them (in 2003 I just used 'Is Null' to display non matching records)

BUT, If I use query1 to make a new table, then use the new table compare against the master table it works perfectly.

So somehow, the fact I'm using a query which uses a calculated field from another query, it returns some error from the calc in Query1 if the record doesn't exist. I'm certain access2003 did not do this.

Obviously I don't want to manually make tables to get round this, can anyone tell me what is the problem & how to get round it?
Thanks very much
Wullig

[LillMcGill]

I guess I don't understand the second query. Why are records that don't have matching values in both fields displayed at all?

OK, I re-read and understand what you are after, I think... your goal is to produce records in master Table that aren't in the Query1 results.
I am unable to replicate the error on version 2010. Sorry

[NTC]

1. If the text manipulation is not needed in order to make the matches - - then make the matches first, and then do the text manipulation second.

2. If it does work in 03, put that query into sqlview and then copy/paste the sql over into 07. This implies that 07 is building sqlstatements differently off the query design grid than did 03. Maybe. First time I've heard of it.

I agree that temp tables are generally an inelegant solution. But they aren't the end of the world and everything happens in an instant - so I wouldn't rule it out if that is the only solution.

Am interested to know what you find.