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?
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.
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?
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
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! :(
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?
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.
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.
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:
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?
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.
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.
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?
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?
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.
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.
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?
...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.
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.
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?
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.
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.
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
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.
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.
