Topics Search

SSN query criteria

SSN query criteria
Views: 18
In my table all SSN data is showing in the format 000/00/0000CC;;_ (13 characters- example 000-00-0000AA).

I would like to create a query which returns only part of the SSN field, basically only the first 11 digits.

I have created a text box on a search form along with a cmdbutton that opens the form. The query has this criteria:

Sponsored Links:

Similar posts...


Error 3085 Function Mid not defined

Error 3085 Function Mid not defined Icon
I am just trying to upgrade my database from 2003 to Access 2007 (I have VISTA) and have notice that when I open a form I get an error message 3085, function mid not defined.
I assume to problem is caused by a list box on my form which has as a records source a query with a field:
LastTwoDigits: MID ([SSN], 12,2).
This criteria returns the last two digits on a SSN. This is quite strange because in Access 2003 I don't get any error message. If I remove the listbox then, the error message disappears. I have looked at the references and what is missing is the Microsoft Office Control 1.0 Type Library. I have removed it but still get the problem.
This is the SQL in my query:

SELECT mytable.BGN, mytable.[ssn], mytable.BLN, Mid([mytable],12,2) AS BIC, Left([ssn],11) AS SocSecNo, mytable.Amount FROM Mytable WHERE (((Left([ssn],11)) Like forms!welcome!ssnsearch)) ORDER BY mytable.BGN, Mid([ssn

Multiple Criteria in DSum expression

Multiple Criteria in DSum expression Icon
I am currently programming a report that needs to spit out all of the money paid to a specific client. The Dsum function I used was:

=DSum("CashforVisit","Financials","Financials.SSN= " & [SSN])

Which worked excellent. However, I need a way to add another criteria to the DSum function, specifically that the payments happenned in 2011. This report grabs information from table Financials, which documents every payment, the SSN of the recipient, and the date of the payment.

Values to sum: CashforVisit
Table name: Financials
Criteria1:"Financials.SSN= " & [SSN]
Criteria2: DateofVisit >= 1/1/2011
Criteria3: DateofVisit

Query Duplicates- Access 2003

Query Duplicates- Access 2003 Icon
I have about 52k of records with duplicates.

It is a list of names with SSN. Where there are two names with the same SSN and name, one my have a middle initial and one without one, or any combination with the Initial. I would prefer the one that does have the Initial, but then also get one of the duplicates that does not have this criteria to consider for the next record comparison.

SSN Format

SSN Format Icon
I'm importing a ms excel spreadsheet into an access table. The spreadsheet contains a column with SSN's. I'm having a problem with access including recognizing SSN's that start with a "0". It will only show part of the SSN (the #'s that are not zero). The spreadsheet correctly identifies the whole SSN. I have setup the table field to an imput mask for SSN but still not working.

linking Querries

linking Querries Icon
I am having problems like usual. Here is what I am trying to do

I have an imported table that has only SSN on employees. On another table I have employees SSN and first name and last name and all other kinds of info.

How would I run a querry that would use the SSN's that I got on the one table and display the correct name for the ssn

Query to find id's with duplicate data across multiple tables

Query to find id's with duplicate data across multiple tables Icon
I have 6 tables with the same design but the data is different because they are pulling data from 6 different places (it is an ODBC connection to other databases).

I want to create a report that will show me the id for each row.

The table design is: employeeID, SSN, Name .

So, I want a report that will show me every SSN and for each SSN, I want to see the employeeID (if it exists) for each table.

The columns for the report would be: SSN, employeeID1, employeeID2, employeeID3, employeeID4, employeeID5, employeeID6

Trying to Link tables

Trying to Link tables Icon
I am still new to access and need a little help to do something that is probally really simple.

I have a table that contains all the information that I need for my event. The data that is important for this question is the Last Name, First Name, Rank and SSN.

What I want to do is to be able to put the SSN into an access table via a barcode reader. This is not the problem. the problem is that after I input the SSN, I would like the Last Name and First Name and Rank to populate on the table that the SSN is scaned into.

Any help would be better than what I have know.

Field auto add numbers to value

Field auto add numbers to value Icon
I have a field for SSN. If the SSN has a 0 or 00 as the first numbers it auto deletes those numbers. Is there a way in access that when the record is visible the 0's are relpaced so that the SSN will read the 9 digit.

Also I have another fiels EIN with 7 digits an I need for there to be 2 9's in the beginning. I was goingto use vba but when I go to yp it I only get .value <


Value is tho only option to get. IDK y it does this or how to get it to shop if there is a ay.

Query Help: MS Access and FileMaker Pro

Query Help: MS Access and FileMaker Pro Icon
I am writing my first database app (windows form in c#) and I am using an access database. I have 2 tables with the following fields:
payroll: (Store, lastName, PayFreq, SSN (PK), HireDate, EffectiveDate, TermDate, DeductionCode, Deduction, NAD CHAR) AND hrconnection: (Store, lastName, CoTime, Plan, Dependants, HireDate, EffectiveDate, TermDate, SSN (PK), Deduction).
I want to find the records based on SSN that are in the payroll table and are not in the hrconnection tables and then find the record based on SSN that are in the hrconnection but are not in the paypal table.

Examples of query criteria

Examples of query criteria Icon
When you want to limit the results of a query based on the values in a field, you use query criteria. A query criterion is an expression that Access compares to query field values to determine whether to include the record that contains each value.
In this topic:
* Introduction to query criteria
* Criteria for Text, Memo, and Hyperlink fields
* Criteria for Number, Currency, and AutoNumber fields
* Criteria for Date/Time fields
* Criteria for other fields