Access query

  JoJoh 19:45 13 Aug 03
Locked

I have a table with various fields of data. I need
to ensure that the last 3 digits of one field are
the same 3 digits in another field. E.G. if field1
has the data agm0000456 in need to ensure that field2 has the data 456. These fields have already been manually entered in excel and pasted into access.

  Chris the Ancient 19:47 13 Aug 03

As an aside, what do you want to happen if they're the same/different? Do they appear in a report or on a form or anything?

I might then be able to help a little more.

Chris

  JoJoh 19:55 13 Aug 03

Chris -I would want to run a query that would show any codes that are different. These could then be corrected in the database.

  Chris the Ancient 19:58 13 Aug 03

It would be easier with a form or a report - but bear with me a while as I try out a couple of ideas for you.

Chris

  Chris the Ancient 20:09 13 Aug 03

It is looking fairly impossible to do this at query level at my knowledge.

If we can do it in a form - perhaps a data form that you originally enter the info - you can use vbasic to do string comparisons that will set/unset a flag that you can do something with.

I'm afraid I cannot see an easy way of doing this (so far) at query level.

Chris

  Chris the Ancient 20:23 13 Aug 03

Which means I learned something as well!!!

I created a dummy database table with two fields called [First] and [Second].

Create a select query based on that table.

Make sure that [First] and [Second] are in the query.

In the criteria line under [Second], enter the expression...

Right([Second],3)=Right([First],3)

Run the query, and onlt the fields with the last three characters the same will show.

JoJoh... Does that help?

If you want to PM me for a copy - feel free. It's something I can send in any version of Access except 1 or 2.

Chris

  Chris the Ancient 20:39 13 Aug 03

Just caught your PMs.

It helps others - who may also want to help - if replies and further questions are made through this thread. It makes for easier continuity.

Bear with me while I read through your e-mails and digest.

I shall report back here to enable others to follow.

Chris

  Chris the Ancient 20:48 13 Aug 03

To summarise for others that might be following...

A report is needed if the check shows wrong.

Flag if the first number is not the same as the second one.

While I look at what I've been creating in the background, another question...

What will 'trigger' the report?

Pls respond through the thread, I get dizzy very easily at my age switching between OE and IE!

Chris

  Chris the Ancient 20:49 13 Aug 03

Also, this will end up with some coding and adapting.

Do you have skills in vbasic?

Chris

  Chris the Ancient 20:56 13 Aug 03

When you create the query I mentioned above, when you close the database and reopen it, the query temporarily 'discards' the expression given and creates a third called... Right([Second],3) ...and makes a criteria for that field of... Right([First],3) ... and drops the + sign.

In that second criteria, prefix the expression with the word NOT to invert the logic and, hey presto, it reports all the wrong 'uns.

Now...

What triggers the check?

Chris

  Chris the Ancient 20:58 13 Aug 03

When you create the query I mentioned above, when you close the database and reopen it, the query temporarily 'discards' the expression given and creates a third FIELD called... Right([Second],3) ...and makes a criteria for that field of... Right([First],3) ... and drops the NOT + BUT = sign.

This thread is now locked and can not be replied to.

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

These are the Best Christmas Ads and Studio Projects of 2016

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…