Access query - calculated value or VBA? (1 Viewer)

Hi All,

I have a query which gets a list of records from a table. Each record will, among other things, contain the details of a person and in most cases this will include a Social Security type number (SSN) for the format [0-9]{6-12} (thats all numeric length 6-12).

I need to have a calculated field which tells me, based on SSN how many previous records are in the database for that SSN. The issue is that sometime people are putting in NIL, Unknown, UNK etc.

So before preforming the calculation I need to check that whats in the SSN field is not null, and matches pattern. I don't think this can be all done in SQL?

Ultimately the query is sent to a report.

How can I achieve this in Access?

Thanks

Al

How can I achieve this in Access?

Click to expand...

What is 'this'? You've asked about 2 very different things--validating data and creating a query. which would you like hlep with and what have you tried?

If it's data validation, usually that is done on a form upon input. You catch and validate data prior to it being submitted. Once in there you assume its correct.

plog said:

What is 'this'? You've asked about 2 very different things--validating data and creating a query. which would you like hlep with and what have you tried?

If it's data validation, usually that is done on a form upon input. You catch and validate data prior to it being submitted. Once in there you assume its correct.

Click to expand...

Hi,

Good point - let me clarify - I am not attempting to tackle validation yet - there is already a lot of data in the database which I need to deal with.

So the question is how to create this calculated field for each record in a result set. The logic iteself looks like:

1. Find all records matching some criteria which gives a result set (this bit works)
2. For each record in this result set look at the Table.SocialSecurityNumber
3. If SocialSecurityNumber matches regex then
4. Query database for count of existing records where SocialSecutiyNumber = Current SocialSecurityNumber in result set.

I hope this is clear?

I havent tried it yet, as at a high level I am not sure how to approach this problem? I dont think it can be done with a query alone?

Cheers

Al

Your numbered objectives really don't make sense individually, to wit:

3. If SocialSecurityNumber matches regex then

There's nothing to do there. It's an incomplete sentence--all subject and no predicate.

Taken together it seems like you simply want a totals query to count all the SSN values. So that SQL would be this:


That will tell you how many times a SocialSecurityNumber value appears in your initial query. If that's not what you hope to achieve, please demonstrate your issue with sample data.

you should be able to do this in a query


SELECT *
FROM myTable
WHERE ftnregex(SSN)=true

this assumes ftnregex is a public function which inspects the SSN value passed to it and returns true if it matches the pattern

see this link for a potentially suitable function

https://stackoverflow.com/questions/8146485/returning-a-regex-match-in-vba-excel

its for excel, but should work in Access - you'll need to change the pattern to what you require and change the return value to a Boolean result

you can create a function to test
if ssno is valid or not.
copy and paste in standard module in vbe
the following code.

in your query you call it: