Modules & VBA :: Remove Data From One Field If Separate Field IsNotNull

Apr 1, 2014

Is there a way to remove data from one or multiple fields if the value of a separate field IsNotNull?

View Replies


ADVERTISEMENT

How To: Separate Data In One Field Into 2 Fields

Mar 18, 2005

I am new to Access and was wondering if there was a way to parse the data from a field into two fields.

I have a field named tName which contains both the first name and last name of a person.

How can I get the corrosponding last name into a new adjacent field called tLastName (this field doesn't exist yet)?

Thanks so much in advance,
Paul

P.S. What if the the person doesn't have a last name? or if they included a middle initial?
I know, it's messed up. This DB has a bad setup.

View 2 Replies View Related

Queries :: Separate 6 Digit Data In Same Field

Jun 10, 2015

In same field Im saving every time 6 digits data and i need to separate them. (It was not my fault. They created DB like this... )

For example my data is

123412(main-field) and i want to see them 1234(field1) 12(field2)

View 2 Replies View Related

Queries :: Extracting Data From List In Separate Field

Sep 19, 2014

695515*97
531924*11
495853*131
377035*181

Is there a way to extract the 97, 11, 131, 181 numbers from the above list in a separate field?

View 1 Replies View Related

Remove Last 3 Characters From Field Data

May 22, 2007

I trying to figure out how to remove the last 3 Characters from my data

Such as

MA-D-97
MA-09
UJS-KK-OL-20

Like the data to look like

MA-D
MA
UJS-KK-OL

I Have This so far

Right([TYPEFY],3)

that selects the last three now is there a remove function I can use??

Remove(Right([TYPEFY],3) ???

Need help thanks

View 4 Replies View Related

Modules & VBA :: Return A Value From A Separate Query / Have To Show Up In A Field On Main Form

Jun 28, 2013

I want to return a value from a separate query and have that show up in a field on my main form. The field name from the query is "Six Month Date" and the query name is "DT6Monthqry". I want to return the value displayed in the"Six Month Date" from the query, matching the CustomerID in the query result to the CustomerID that is being displayed on the main form. The CustomerID is a text field.This is the code I placed in the Control Source property for the field I want to be displayed on the main form:

=DLookUp("[Six Month Date]","DT6Monthqry","CustomerID=" & [CustomerID])

The result of this displays a flashing "Error" in the field, even when I go to a Customer that I know has a result in the "DT6Monthqry".

View 8 Replies View Related

Tables :: Extract Data From Memo Field And Put Into Separate Fields Of New Table

Jun 15, 2014

I have a notes field in the customer table that is a memo field. An example of one customer's notes field data :

<div>20.3.14 Ordered 2 cartons</div>
<div>4.3.14 Ordered 2 cartons</div>
<div>18.2.14 ordered 1 carton</div>
<div>30.1.14 ordered 3 cartons SCENTED wipes</div>

[Code] ....

I want to extract the date to append to a date field in a "Calls" table and the comment into a text field in the "Calls" table. Is there a way I can do this via query or code?

View 2 Replies View Related

Modules & VBA :: Remove Spaces From Middle Of A Field

Jun 9, 2015

I'm trying to improve the dialer that i place on microsoft access forms. basically there's a command button that dials the number that is in a text box, but we have a new job that's just come in that requires numbers to be copied and pasted into the text box. these numbers have a space between the dialing code and the telephone number and i need to make the dialer check for a space and remove it before dialing.

Here's the code for the dialing buttons:-

Private Sub cmd_Dial_Click()
On Error GoTo Err_cmd_dial_Click
CT.MakeCall "8" & txt_telephone.Value & "#", "", False, "", "", False

[code]...

View 5 Replies View Related

Forms :: Can A Field Auto-fill From Previous Entry In Separate Field

Mar 21, 2013

I've just returned to work after kids and started managing a large Access database related to health, back-tracking over many years.

Currently in filling a form we physically enter:
Apples 2.2
red apple 2.4
red apple cut 2.45
Oranges 5.6
Cucumbers 8.5

Is it possible to get field 2 to automatically fill with a number code due to the text typed in field 1?

FWIW, I'm confident at more basic Access e.g making follow on default value = Dlast("field""table") type stuff but the more complex stuff I haven't touched since Uni over a decade ago and you will need to be gentle while I blow away the cobwebs

View 3 Replies View Related

Queries :: Syntax In Query To Remove Duplicate Data For Field

Apr 26, 2013

I'm having difficulty with the syntax in this query to remove duplicate data for the field "StocktransID".

Code:
SELECT DISTINCT tblStockTrans.StockTransID, tblItem.Brand, tblItem.Category, tblItem.SubCategory,
tblItem.Model, tblItem.Description, IIf(TransTypeID=3,Quantity*-1,Quantity) AS Qty,
tblTransaction.TranstypeID, tblItem.ItemID, tblTransaction.TransactionID, tblItem.ItemType,
tblItem.Origin, tblOption.ParentID

[code]...

View 6 Replies View Related

Auto-filling A Field Based On Entry In A Separate Field

Jul 17, 2015

I am creating a database tracking physicians and their contracts. I currently have two tables: PhysicianT and ContractsT, with corresponding forms to enter information in them. I have an issue with the Contracts form; I want to be able to select a physician from a dropdown list (looked up from PhysicianT) and have Access autofill their Physician ID #.

PhysiciansT looks like this:

physicianID (AutoNum) name (Calculated)
1 Barker, Bob
2 Burgundy, Ron
3 Stark, Tony

Upon selecting Barker, Bob from my dropdown list, I want "1" to appear in the Physician ID # field in my Contracts form.

View 3 Replies View Related

Modules & VBA :: Filter Subform Data - Only Show Records Where Field A Is Higher Value Than Field B

Oct 24, 2013

I want to filter my subform data, to only show records where field A is a higher value than field B.

Code:
Me.MySubform.Form.Filter = "A > B"
Me.MySubform.Form.FilterOn = True

This way it doesn't find field B.

Code:
Me.MySubform.Form.Filter = "A > " & MySubform.Form!B
Me.MySubform.Form.FilterOn = True

This way it seems to filter all record to the field B value of the first record.

View 5 Replies View Related

Modules & VBA :: Mandatory Data Entry In Field Before Moving To Another Field

Aug 22, 2014

I have a form that users will use to add new records (customers).

There is a field named VAT_Registration_no

First of all i want some code to check for duplicates in that field only,before entering the next field.If the record exists i want to show a msgbox and set focus to the vat_registration_no field. Also I want the same thing to happen if the vat_registration_no field is empty.here is what i have tried:

Private Sub VAT_registration_no_AfterUpdate()
Dim btest As Boolean
If VAT_registration_no = "" Or IsNull(Me.VAT_registration_no) Then
MsgBox "Please enter a Vat Registration No.", vbOKOnly, "error"
Me.VAT_registration_no.SetFocus
Else
btest = True
End If
End Sub

and to all other fields:

Private Sub textfield_Enter()
If Not btest Then
Me.textfield.SetFocus
End If
End Sub

If I just press enter to go straight to the second field I dont get a msg. If I write something and delete it and press enter i get the msg but when I press ok the cursor goes to the next field. I want it to go to the vat_registration_no field again. And I also want this to happen even if dont write something and then delete it.

View 3 Replies View Related

Forms :: How To Remove Field From Field List

Nov 11, 2014

I had two fields in my table which I previously used in the form. I deleted the fields from the form itself but the fields are still listed under Field List and not even under any table...

Delete buttons doesn't work on it, and neither does backspace?

How do I remove it? Right click only gives me two options:

"Add Field to View" or "Edit Record Source" which returns an error saying this command cannot be used.

View 2 Replies View Related

Reports :: Separate Pages By Unique Field?

Jul 29, 2013

I have a DB with all my patients and (among other things) all their referral source.

I would like to print a status report of all my patients, grouped by the referral source, and fax them to the appropriate offices. Easily done with the report wizard.

What I need to know is, how do I create one report, but force a new page for each referral source?

View 4 Replies View Related

Splitting Text Values From A String Into Separate Field

Jan 25, 2005

I have a table field which long ago was merged from several other fields. When the data was merged into the field it was delimited by "1." then "2." up to "5."
Example: MergedField = "1.Animal 2.Large 3.African 4.Grey 5.Long Nose"

I now want to split it appart in a query where "1.Animal" goes into expression1, "2.Large" goes into expression2, etc.

I need to base the text on where the one number begins and grab everything until the next number in the mergefield is detected.

Can someone show me the syntax for this.

Thanks!

View 1 Replies View Related

Queries :: Create Separate Columns From Same Field And Table

Apr 9, 2013

I have 1 table that I duplicated to make 3 tables total. I did this b/c I am trying to create separate columns from the same field and table. The field is Workorder. Each workorder list the workorder number followed by a dash and then code. I am wanting to put all of the workorders with the same code in it's own column.

I have 5 codes that i am searching for. The first column list the workorder and a code (123456789-AD). The second column (123456789-BC). I'm good to this point but my problem occurs next.

The third column i am trying to put 3 types of workorder and it's code in the same column. As follows, (123456789-CD, 123456789-TC, and 123456789-PTC. However, when I do this it takes the results from the 3rd column and applies it to the 3rd column but also the 1st and 2nd column. I tried a UNION query and unless I am doing it incorrectly it does not work.

View 3 Replies View Related

Queries :: Parameter In Query By Two Separate Form Field

Oct 31, 2013

I want to set parameter in my select query with or function as in detail.i have two form with same field

1-with field A
2-With Field A

now i want to select data for a table .with a parameter select with Field A in form 1 or Field A in Form 2.How can i do it

View 5 Replies View Related

Forms :: Display Field From Separate Table In A Form To Use As Reference

Mar 25, 2014

In my form (source tblJobs) i input a contract number from tblContracts. but i would also like it to show the contract address when the contract number is entered. Both fields belong to tblContracts but i only wish to enter the contract number, the address will just be used as a reference as it isnt something i would have to enter again.

So for example

112 - Main Road

If i enter 122 in my ContractNo field i would like it to show the contract address for that specific contract.

Can i do this? If So how?

View 5 Replies View Related

Forms :: Search Form With Each Field In Separate Text Boxes

Dec 8, 2014

i want to search a phone number in contact information table with column names - number, name, address, dob, gender, f-name, m-name etc and show them in a form with each field in separate text boxes in access 2010.

View 4 Replies View Related

Calculating Field Specific Averages From Records In Separate Tables

Apr 28, 2014

Let's say I have a table sort of like this one: [URL] .... (Table 1)

What I want to do is make another table that references the first table: [URL] ... (Table 2)

I want the cells in the Average field in Table 2 to calculate an average of all the values for records in Table 1 with Color fields that correspond to the Color field in Table 2 (this makes a little more sense if you look at the pictures). I could do this in Excel, but then problems would arise whenever I would add a new entry to the database, or re-alphabetized the data, since Excel math is depends entirely on the positions of cells, and I want these averages to be continually calculated correctly and to change whenever I add related records to the database.

View 1 Replies View Related

Remove All Clicks From The Field

Aug 15, 2006

Field type is Yes/No in table. I try to remove all clicks from the field Needs help.

Dim qry As String, rst As Object
qry = "UPDATE Research_New Set PrintQueue = 0 "
Set rst = CurrentDb.OpenRecordset(qry)

It gives error:
Invalid operation

Table: Research_New
PrintQueue Data Type: Yes/No

View 1 Replies View Related

General :: 2 Fields From Separate Tables Needed To Create Calculated Field?

Aug 19, 2013

The interface being used is a main form with various tabs and a subform on each of these tabs.

There is one field ('max power density') in my database that is calculated using 'Max Rated Power' and 'Cylinder Capacity' however these are in different tables and subforms. The 'max power density' and 'max rated power' are in table and subform 1 but 'cylinder capacity' is in table and subform 2. Is it possible to keep them in separate tables/subforms and still calculate the field?

View 7 Replies View Related

Update Field On Separate Table If Result From Form Equals Certain Criteria

Mar 17, 2012

I have a form, which i use to book in products received. What i want to do is if the quantity received is equal to what was ordered then i'd like to update a yes/no field (Order Complete) on a separate table to true.

View 8 Replies View Related

Need To Remove Spaces From Field In Table

Sep 28, 2006

I have a table that for some reason when I imported the data, placed some spaces before about 75% of the numbers in only one column. I need these numbers to be exactly the same as in another table because I use this number to compare to records and import other data depending on the corresponding numbers.

I tried doing a find and replace, but for some reason it doesn't find the spaces to replace. If I type in the space and the number, then in the replace with type only the number, it works. But I cannot do that since there are literally 10's of thousands of different numbers.

Someone mentioned a "LTRIM" command. Does this work within Access and if so, how does it work?

Thanks,
David

View 3 Replies View Related

Remove Numbers In Middle Of Field ?

Jan 5, 2007

Hello,

Is it possible to remove a couple of numbers in the middle of a field by using an update query ?

I have for example: YF000491 and would like to make it YF0491.

Any help would be greatly appreciated.

Thanks

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved