Comparing Two Fields With Identical Input Masks

Aug 31, 2007

I have a form control with the input mask on it for a phone number. I am using an append query to append the information entered in the form to a table. I need to pull two fields from a linked table from another database. What I need to do is be able to compare the entered phone number in the form with the phone number from the table. The problem I am running into is that it won't match the phone numbers. The same mask is used in the table I am pulling from. Why can I not just put =[Table].[Phone_No] in the Criteria of the field where the number from the form is placed so that I can get the right record from the linked table?

View Replies

Input Masks

Sep 12, 2005

I want to force user to input state in capital letters, ie; CA, CO, WA, etc., with two text characters only. Working in design view, I used this character: > in the input mask field property area,
which I understand (perhaps mistakenly) will convert characters to uppercase. Now, I cannot enter any info in the state field.
Any help would be most appreciated.

View 1 Replies View Related

Input Masks

Jul 11, 2006

Hi,

I have a text box on a form to enter doc id's. The doc id's can either be a single letter followed by six digits or two letters followed by six digits.

I can create masks for either of them but not so it allows either of the options to be entered, is it possible to create a mask to cover both options?

Thanks

View 4 Replies View Related

A Little Help Needed With Input Masks..

Apr 13, 2006

I was wondering if anyone could point me in the right direction..

I'm working on a database for work :eek: there are a million things giving me a headache (I've attended two 1 day courses but haven't a clue :rolleyes: )but one little thing that is annoying me is a problem I have with input masks..

In my Contacts table, I have First Name, Surname, Company, etc..
I've made the first letter of the first name and surname have a capital letter ie;

FIRST NAME
Field Size: 20
Format: *Is clear*
Input Mask: >L<CCCCCCCCCCCCCCCCCCC

SURNAME
Field Size: 20
Format: *Is clear*
Input Mask: >L<CCCCCCCCCCCCCCCCCCC

COMPANY
Field Size: 30
Format: *Is clear*
Input Mask: >L<CCCCCCCCCCCCCCCCCCCCCCCCCCCCC

I have a problem in that, usually company names contain more than one word plus PLC or LIMITED at the end.. I want to be able to make each word start with a capital but as the lengths vary, I don't know where to put these in the input masks :confused: :o

Any help? I'm sure it's easy but I don't know :o

View 1 Replies View Related

Input Masks (Last Names!!)

Feb 2, 2005

Hey all,

I had a question about input masks. The boss wanted all the fields to automatically capitalize names and last name. Did that, was happy for awhile. What we didnt realize is that there are names like DiGianno or McDonald that still need caps in certain spots.

The original input mask went like this: >?<CCCCCCCCCCCCCCCCCCCCCCCCC;;
This mask would caps the first letter and lowercase the rest.
What i need it to to is CAPS the first but leave whatever may follow up to the user..

In other words.... The first letter is always caps, the rest that follow is up to the user, upper or lower.

Is there any way to do this? Couldnt figure out a solution! :(

Any help would be appreciated

Mateo

View 3 Replies View Related

Question On Input Masks

Oct 21, 2005

Does an input mask have to be static? The format for the numbers entered in a control in my form is digits separated by periods for WBS number entry (9.9.9.9.9). Each '9' will represent a digit. I want to allow the user to enter different depths (ie 1.1, 1.2.3, 1.2.2.1, etc.) up to 5 levels.

Is it possible to make the input mask automatically adjust as more numbers are entered?

View 1 Replies View Related

Queries :: Comparing Data Differences Between Two Identical Access Tables?

Dec 10, 2013

I have two identical Access tables, for example tblDataYesterday and tblDataToday. The data from tblDataToday has updated data changes in it that are different from tblDataYesterday. I have a maximum of 100 fields to review. I want to compare the two tables and display in a query the differences between the data from both tables.

View 1 Replies View Related

Question About Duplicates And Input Masks

Dec 23, 2005

Greetings

I was wondering how I go about this?

I have a table that holds vessls and barrels in a winery. When I enter data via a form I have a lookup to showme the location of a vessel or barrel. The vessels are static and have a two letter location ie WT or RT etc. The barrels, once filled are put in different locations in a warehouse. Each location has a code ie WW011121 etc.

I have a range of codes in the form as follows;

Private Sub Location_Enter()
If IsNothing(Me.Location) Then 'means it is probably a barrel
'and needs a warehouse location
Me.Location.InputMask = ">LL00000"
End If
End Sub

Private Sub Location_Exit(Cancel As Integer)
If IsNothing(Me.Location) Then 'tried to leave without a location
MsgBox "Location Required", vbCritical, gstrAppTitle
Cancel = True
End If
End Sub

What I wish to write is some code to find a ducplicate location in the warehouse not in the static locations.

What I have written thus far is;

Private Sub Location_BeforeUpdate(Cancel As Integer)
If Me.Location.InputMask = ">LL00000" Then

then the air code would be to find duplicates from there.

Would it be best to compare string length (clues needed) as the static locatons on have two letter where as the barrel locations will have two letters and five numbers. Or is there a trick in dcount to look at bits of the contents of a field? ie ignore fields with on two letters and do a dcount in fields with say two letters and five numbers?

I do not have any experience writing code involvine string lengths.

Thanks in advance

~rbinder

View 2 Replies View Related

Lookup Field And Input Masks

Jun 21, 2007

I have my database done, for the most part. But I want to add a lookup to a field in my Component table so that I can associate a particular number (paragraph number in a pub) to a failure mode description. In the Failure Mode table, I have associated a paragraph with a failure mode:

Example: 6-66.2Visual
6-66.8Continuity
6-66.8Insulation

Yes, some paragraphs have more than one failure mode. The primary key for this table is an autonumber which allows me to do this with minimal headache.

When I created a lookup within the appropriate field in the Component table, it erases my format/input mask and makes it look like this:

6662 Visual
6668 Continuity

I wasn�t able to put an input mask in the General tab of the FailureMode field in the Component table � I guess because it has the paragraph AND the failure mode in the lookup and 9-99.99;;_ just won�t cover the text for the failure mode description. The input mask is present in the Failure Mode table but isn't "crossing over" to the lookup!

I REALLY need those �masks� in the paragraph callout to help me distinguish and match the failure mode! Can someone please explain how to do this?

Thanks!

View 8 Replies View Related

Input Masks And Append Querries

Apr 18, 2007

I have a field in a table with the input mask LLL0000;0;_

when I use an append querry to put data in the table it still puts in data which does not follow the input mask. Is there some tick box I have not checked or somthing because this is getting really annoying.

All help really appreciated.

View 2 Replies View Related

Input Masks And Validation Rules

Sep 8, 2004

I'm using a simple input mask via the wizard for a date of birth field in a table, but the table is accepting entirely invalid dates such as 32/12/2005. When I tried inputing something like this in the wizard it says invalid input, so why is it valid in the table?
Should I set validation rules?
I tried this:
>31/12/2004
and set the validation text to "Invalid data"
i.e. limiting input to less than 31/12/2004... but it still didn't work.

Can anyone help?
thanks

View 1 Replies View Related

Forms :: Input Masks For Access 2007?

Nov 23, 2013

I have a field in my table called SATS. This is then on my form as a control bound to the SATS field in my table. The application is for a surgery where the person doing vitals ( blood test and HPpressure testing) need to enter the redings. The norm is 96%RA80 tHE 96 never goes beyond 100. The RA will always be there and the 80 can be higher up to three digits.

What i manage to do so far is this 99\%LL999;;- the problem is that the R has to typed everytime. How can i set it to __%RA__

I have is that if we want to scan the past consults and link it to a patient. What are the requirements?

View 1 Replies View Related

Best Way To Combine To Tables With Identical Fields

Sep 18, 2006

I have an Access DB built that links to an excel spreadsheet. The spreadsheet changes so I cannot import and have to link.

Now the source of my spreadsheets is producing two spreadsheets with identical fields but different data. I need to combine the data from these two spreadsheets. Can I link them both and combine them in a query? How?

Appreciate any advise.

View 2 Replies View Related

Multiple Excel Tables With Identical Fields

Feb 20, 2006

Hi All,
Being a newb, have a hopefully straightforward question. I'm writing a vehicle management database which covers eleven seperate areas. The data is currently contained in a spreadsheet with eleven seperate data sheets, one for each area. My thinking is I use linked tables as the spreadsheet needs to be occasionally updated.
My difficulty...
If I want to cycle through all records, I assumed I could query against all tables but don't seem to be able to.
The tables are not currently linked in any way and contain fields such as registration, emissions, list price, make and model and so on.
Any suggestions would be greatly appreciated.

Many Thanks

Q :)

View 1 Replies View Related

General :: Storing 2 Identical Fields On Same Table In Database?

Nov 5, 2013

I want to be able to store 2 identical fields on the same table in the database. The copy will start off being the same as that input in the original field. 90% of the time the 2 fields will always be identical but the copy may be changed at a later stage. I looked into using the copy as a calculated field but found out these can not be updated. Is there another control I can use or do I have to code the population of the 2nd field specifically (in AFTER UPDATE EVENT?) myself. The added complication is that the original and copy must be selected from a combo box as all values are held in a separate table.

Also trying to make the second field = to the first field in the design stage of the table using default value

SO if I put default value = [table].[fieldname] I get an error stating could not find field 'table].[fieldname', and yes the opening and closing square brackets are missing from the error.

View 5 Replies View Related

Forms :: Use Conditional Formatting To Indicate Mismatch Between 2 Of Identical Fields

Nov 4, 2014

I have a form with 2 sub forms which have identical fields from different records. I want to use conditional formatting to indicate a mismatch between 2 of the identical fields. I have been told I should use [NewField] <> [OldField] but I don't know how to complete the syntax. If I call the first sub_form_1 and the second sub_form_2 and the field name is path_length on both, how do I compile the statement?

View 8 Replies View Related

Tables :: Merging Table Data Fields If Names Are Identical

Sep 24, 2012

Have 2 tables:

...TableA: 1 indexed field "Name" and many data fields in ~ 1000 records
...TableB: 1 indexed field "Name" and 2 data fields (DataB1 and DataB2) in about
~ 50 records

Tables currently have NO relationship set.

Want to add TableB data fields to TableA if Name are identical.

DataB1 and DataB2 fields are Not currently in TableA but could be added very easily.

Update (or whatever) will be done weekly where all previous data for DataB1 and DataB2 will be cleared from TableA before job is run.

View 1 Replies View Related

Comparing Two Fields.

Dec 30, 2007

Hello,

I am a Beginner in MS Access and SQL and I need a query to compare two fields.

I am having two tables; T1 and T2, and I want to compare their
fields TF1 and TF2, and display the non matching fields in new
field.

Example:

Table T1
TF1

Location Louisville
Number 555345
Name Smith

Table T2
TF1

Location Louisville
Number 553450
Name Smythe

Thanks in Advance.:)

View 1 Replies View Related

Comparing Fields In Access

Apr 28, 2006

I have a database with a pre-surgery field and a post-surgery field. The use must enter the current procedure in the pre-field and then the actual surgery in the post field. I need to check to see if the fields match verbatim. The field is a memo field.

The users know how to copy and paste from the pre to the post fields. 90% of the time it is the same but not always.

Can anyone provide some help?

Thanks,
Deborah

View 2 Replies View Related

Comparing Two Fields In A Query?

Jun 18, 2007

I am trying to compare two different fields in the same query and return the most recent date in some another field. Would I be able to do this and if so how?

Any help would be greatly appreciated.

View 14 Replies View Related

Comparing 2 Fields--Looking For Differences

Jun 1, 2005

Hello,
I have 2 fields that are supposed to have the same information for each record. I got the information from different sources, which is the reason for having 2 fields. I wanted to make sure that the information was accurate. Turns out, that some of it isn't accurate--all the fields don't match.

Here's a little example of what I have:

|___TITLE___|___ISSN 1___|___ISSN 2___|
|__TITLE 1__|__12345678__|__12345678__|
|__Title 2__|__22224444__|__23224322__|
|__Title 3__|__98765432__|__98765432__|

As you can see, the ISSN numbers for Title 1 and 3 match. There's no problem there. I want a query that would list all those like Title 2, where the ISSN numbers don't match. Then I'd have a list of all of the problem Titles, and I could look up the real ISSN numbers.

Thanks

-Siena

View 2 Replies View Related

IIF Statement Comparing Two Fields?

Sep 28, 2012

The two tables are joined together by a primary and foreign key in the query, . I have not added the actual tables below , as data is senitive - i'm using an example. This join is made using the graphic relationship between the tables.

Table_1 with below column
Mode
6
6
6
6
6
4
4

Primary KEY
0001
0002
0003

[code]....

Initially the pseudo code i'm trying to get to work is;

if table_1.mode = 6 and table_2.type = 06 , then "MATCH" , else "NO_MATCH"

Expr1: IIf(IsNumeric([TABLE_2]![TYPE])="06" And IsNumeric([TABLE_1]![MODE])=6,"MATCH","NO_MATCH") ,

However this calculated field returns incorrect results, i.e. the query returns MATCH for rows that do not match , e.g. mode=6 and type=GL , Previously , when it was returning #Error when trying to match mode=6 and type=GL when it should ideally return "NO_MATCH", which is why I added Isnumeric.

Once I have sorted this out, I would like to achieve this as the calculated field

if table_1.mode = 6 and table_2.type = 06 , then "MATCH" , else "NO_MATCH" or if table_1.mode = 4 and table_2.type = 04 , then "MATCH" , else "NO_MATCH

how I can do this?

View 6 Replies View Related

Comparing Fields Of Two Tables And Output To A Third

Jul 20, 2005

It has been a while since I last used Access, recently I've used mySQL and PHP.

First a little description of what I'm trying to accomplish:
I have three tables...

tblImport - has the fields: TestID(PK), i001, i002, i003, i004 ... i025
Note: i00# field contains the multiple choice answer (i.e. 1,2,3,4,5) as imported from a CSV file.

tblStudentAsr - has the fields TestID(PK), StudentID(PK), 001, 002, 003 ... 025.
Note: 00# containes the multiple choice answer (i.e. 1,2,3,4,5) as enterd by the student.

tblResult - TestID(PK), StudentID(PK), a001,a002, a003 ... a025.
Note: the fields a00# have the datatype set to "yes/no"

What I'm trying to do is compare the answers in tblImport to the answers in tblStudentAsr then output the result to tblResult .

Here is some dirty pseudocode:
if i001 = 001 then
INSERT 1 INTO tblResult
else
INSERT 0 INTO tblResult

The above example gets a little repetitive since I would have to do that for each question.
Is it possible to put the answer fields of each table into a recordset then compare them?
(I have heard that using rs's can be a little slow. Although there is only a max of 25 questions the number of students can be quite large)

Alternativly, can I accomplish this using only SQL statements?

What would be the best way to attack this?

Any suggestions (or alternate suggestions) would be greatly appreciated.

Thanks,

salmonman

View 6 Replies View Related

Comparing Two Fields In One Criteria Field

Aug 9, 2005

Hi,
I just started doing something in access and need your help.
I have created a database and need to create a query which will sort all important fields within a certain time period. I have a date field and in the criteria field I wrote an expression:">Forms![Insert_date_form]![starting_date_field] and < Forms![insert_date_form]![ending_date_field]"

Its purpose is to show all fields which date is between this to dates. User will have to enter this dates in a separate form(Insert_date_form) which will store it in a small database containing only these two fields, e.g. generate report of all employees that are started working within a period between starting_date and a ending date. When I try to create and open a report based on this query I've been prompted with a small dialog which says: Enter parameter value.... for starting_date_field and same for ending_date_field. I don't need that. These dates are allready entered by user in a insert_date form and stored in its databese.

Is there a beter way to do this?

Thanks!
P.S. Sorry for my poor english. It's not my native language.

View 8 Replies View Related

Queries :: Comparing 2 Fields Query?

Mar 11, 2014

Trying to Compare 2 fields Status with answers Yes or No, from 2 Tables PipeLine and Pipe, and get the fields that don't match, what am i doing wrong?

SELECT PipeLine.[Project Name], PipeLine.[Capacity DC], PipeLine.[Project Num], PipeLine.[Status]
FROM PipeLine LEFT JOIN Pipe ON PipeLine.[Project Name] = Pipe.[Project Name]
WHERE (((Pipe.[Status]) ="Yes");

View 1 Replies View Related

General :: If Statement Comparing To Fields

Sep 27, 2012

query is refering to 2 tables

Table_1 with below column
Mode
6
6
6
6
6
4
4

Table_2 with below column

Type
06
GL
PL
04
16

Both tables are joined, and the pseudo code i'm trying to get to work is;

if table_1.mode = 6 and table_2.type = 06 , then "MATCH" , else "NO_MATCH"

I have tried below, but doesn't work.

Expr1: IIf(IsNumeric([TABLE_2]![TYPE])="06" And IsNumeric([TABLE_1]![MODE])=6,"MATCH","NO_MATCH") , returns incorrect results.

Ideally would like this

if table_1.mode = 6 and table_2.type = 06 , then "MATCH" , else "NO_MATCH"

or

if table_1.mode = 4 and table_2.type = 04 , then "MATCH" , else "NO_MATCH

View 6 Replies View Related