Join text to a value? Not like search?

[bkanealy]

All,

I have two tables with the same field name "trouble ticket"; however, when one table was created the field was made text. I was going to make a query that converts the field to a value and then use that query in another query to join the information. Is it possible to do that in one query?

Also, I'm trying to block out some data in the text field using Not Like "999*" Or Not Like "123*", but this isn't eliminating the correct records. I've also tried And instead of OR. What am I missing? Thanks in advance.

Brian

 

[dcx693]

Is it possible to do that in one query?

Yes. First, let's assume the data types are compatible. Let's say you have two tables, A and B, and the field you'd like to join on is called "ticket". Create a query and add tables A and B to it. Add the ticket field from table B to the query. In the criteria for the ticket field, use something like: [A].[ticket]

That will match up results from tables A and B where the ticket field from table A is equal to the ticket field from table B.

If there's some kind of data required, just change the criteria to something like:
Val([A].[ticket])
if the ticket field in table B is numeric and the ticket field in table A is text.

If you then need to limit what gets pulled from table B field ticket, then just put the criteria on the same line. If you need to limit what gets pulled from table A field ticket, then add a new column and also place that criteria on the same line as the criteria for table B field ticket.

 

[bkanealy]

Dcx693,

Thanks, I'll give that criteria in B a try.

For the "not", I'd tried putting the criteria on the same line with no luck.

Pat,

Yes, I realize the best solution is to make it correct; however, I'm only an end user not an admin. One table is linked from an AS400. The other (the problem one) is from a Lotus Notes database, and no one wants to take responsibility for it. Thank you for the information on the Not in.


Brian

 

[bkanealy]

Pat,

I tried the Not in and still get the same thing. Yeah, I put "" around 999, because this loopy (lotus notes) database was created where the trouble ticket is a text field not numeric. So, I've been trying the variations of "999*" to get rid of the incorrect ticket numbers, but have been having zero luck. Thanks for your help.

Brian