Blanking Out Unwanted Fields

Jun 21, 2007

Hi. Hoping someone can help me. Or at least tell me if this can or can't be done. I'm designing a new database at work and want to know if it's possible to blank out fields if they don't need to be filled in.

Basically in simple terms I want it so that :

For field A you have 2 options.
If option 1 is selected - fields B, C, D and E are applicable and should be filled out.
If option 2 is selected - fields B, C, D and E are unapplicable and so I'd like them to be shaded/blanked out.

Is this possible? If so how can it be done?

Thanks :)

View Replies

Import With Unwanted Blank Fields

Sep 15, 2005

Hi,
Im trying to import a spreadsheet from Excel. I use the wizard and I get the sheet imported. The only problem is that I get additional blank fields in my table in Access. How can I make sure that this does not happen? I want to keep on importing into the same table, so these useless empty fields keep on accumulating.
Any help?
Thanx,
Stacey

View 2 Replies View Related

Remove/hide Unwanted Fields In A Query

Jan 11, 2005

I have a query which contains about 19 fields in it and some of the fields contain parameters. What I'm trying to do is to run the query using the parameters I've set, but at the same time remove fields that are empty (therefore only fields that are populated will be shown). I've tried using the 'is not null' parameter but it seems to interfere with the other parameters that I've set and as a result the query doesn't show any information. Also the field containing the 'is not null' parameter is still being shown. I'm not really sure what else to try!! Any suggestions would be most appreciated!!

View 14 Replies View Related

Remove Unwanted Row

Aug 18, 2004

Hi,
I need to remove the automatic (empty) new row that access produces under the last record in the table, because i
would like to remove it when i view a query.
Could any of you kind people suggest a way that i could do this??
thanks for you help.

View 2 Replies View Related

Omitting Unwanted Results

Apr 26, 2005

I have an inventory checklist being done up now.
After the item info has been typed in (price, part number.. ), below i put in the transaction info (4 sold today, 2 recieved yesterday..)

I need by report to show the total number of all stocks and how much they are all worth.
The report does that, no problem.
Only thing is that instead of the end product,
Eg: Product X, 5 pieces, $10

it also prints the transactions in the report.
Eg: Product X 4 pieces, $8
Product X -1 piece ($2)
Product X 2 pieces, $4.

What should I do to make my report -not- print all this useless junk, but just the end product?
Thanks

View 3 Replies View Related

Unwanted Enter Parameter Value Box

Oct 23, 2014

I have a query with several different columns. One Column, CodeNum is built based on the values in Expr4. When I try to set a criteria for CodeNum (Like "8*"), and I run the query, I get a popup for Expr4 ("Enter Parameter Value | Expr4). I want to get all values of Expr4 when CodeNum starts with an 8.

CodeNum is built as follows: JCC: Left([Expr4],InStr([Expr4],"/")-1)

View 3 Replies View Related

Unwanted Conversion Of Date Field

Jul 21, 2005

I have an Excel file that I want to import into an Access db table. In that Excel file is a date field formatted mm/yy. When I import that file, Access converts the date to m/d/yyyy. Is there anyway to re-format that data after it's imported into Access (like a global change?)? I even tried setting up an input mask for that date field, but that only seems to work when you are actually typing data into the field.

Thanks for your help!

rbgolfn

View 2 Replies View Related

Query Returns Unwanted Results: Please Help

Apr 9, 2007

Hi,

I'm trying to create a database project for college and have run into a problem with a query. I am trying to find all bookings that have taken place in the last month, but with added details from other tables.

I have taken the job details and date (with validation for the last month only) from my Jobs table, and this works perfectly. However, when i try to match customer IDs to their names (stored in a seperate table), Access returns the same job multiple times with every customer name possible.

Any help you can give me to return just the one result needed would be greatly appreciated.

Mompo

View 8 Replies View Related

Removing Unwanted Spaces In A Text Box

Feb 2, 2006

I have two questionaire forms in a database. There are no tables or queries involved.
The answers to each questionaire are compiled into text boxes, one on each Form. On the main form I have another text box that puts all the answers together so that this can be transferred to another program. An example of what is in each text box is as follows:

Text1: IIf([Check325]=True,[Combo77] & " " & [Combo59] & " " & [Text65] & " " & [Text69],"") & " " & IIf([Check329]=True,[Combo78] & " " & [Combo61] & " " & [Text66] & " " & [Text70],"")

Text2: IIf([Check326]=True,[Combo102] & "" & [Combo188] & " " & [Combo91] & " " & [Text94] & " " & [Text97],"") & " " & IIf([Check330]=True,[Combo103] & " " & [Combo92] & " " & [Text95] & " " & [Text98],"")

These are just small extractions. My problem is, if an answer is not necessary in the first lot of text, but an answer has been supplied in the second lot, how do I remove the spacing that has resulted from my Formula in Text1. -

e.g: Normal answers would show perhaps
Yes Twice 16 25 Red Peter Ford Football 1965 - Whereby the first 4 answers are from Text1 and the remaining answers from Text2.
If some answers are not necessary from Text1 I would get the following result:
SPACE SPACE SPACE Twice Red Peter Ford Football 1965

Any assistance would be gratefully accepted.

View 3 Replies View Related

Query Still Pulls Unwanted Information

Dec 13, 2004

This is the code that I used to build a query. But some of the data that I thought I removed is still showing up. Any guidance on why? As always all feedback is welcome.

SELECT final.[SSN P ], final.[SSN S ], final.[SP'S SEC Y97], final.[SP'S SEC Y98], final.[SP'S SEC Y99], final.[SP'S SEC Y00], final.NCCD, final.[TC-530], final.[ DOB ], final.[ DOD ], final.[ PRIMARY NAME (ENMOD) ], final.[ PRIMARY NAME CONT'D (ENMOD) ], final.[ STREET (ENMOD) ], final.[ CITY (ENMOD) ], final.ST, final.[ZIP 1], final.ZIP2, final.ZP3, final.ZP3, final.CNLY, final.C, final.YRLR, final.[ PRIOR YEAR NAME (ENMOD) ], final.PNLY, final.P, final.[XREF SSN ], final.[INOLEX XREF CD V], final.[ INOLEX XREF TIN V ], final.[INOLEX XREF CD I ], final.[ INOLEX XREF TIN I], final.[ PRIMARY NAME (IRPTR) ], final.[ PRIMARY NAME CONT'D (IRPTR) ], final.[ STREET (IRPTR) ], final.[ CITY (IRPTR) ], final.ST1, final.[ZIP 11], final.ZIP21, final.ZP31, final.TXPD, final.[#DOCS], final.[#_SUM], final.[WAGES ], final.TX_WTHLD, final.ALLC_TPS, final.INTEREST, final.MTGINTPD, final.POINTSPD, final.PRYRRFND, final.SAV_BOND, final.DIVIDEND, final.PENS_ANN, final.[IRA_CTB ], final.NONEMPCM, final.CPTLGAIN, final.RL_ES_SL, final.GRSSDIST, final.TXBL_AMT, final.FICATXWH, final.TFICAWGS, final.MEDPYMNT, final.TFICATIP, final.FICAMISC, final.DFRDCOMP, final.UNEMPCOM, final.[RENTS ], final.ROYALTES, final.MEDCREWH, final.MEDCR_WG, final.[OR_K1 ], final.GRWINING, final.STDLNAMT, final.[ORD_DIV ], final.ROIRACTB, final.SMPL_CTB, final.[FMV ], final.CD_S_SMP, final.STK_BOND, final.[ADV_EIC ], final.FISH_INC, final.OTHERINC, final.SUB_PMTS, final.GOLD_PAR, final.CROP_INS, final.TXSTTUIT, final.TX_GRANT, final.[AG_SUBS ], final.INTFORFT, final.ORISSDSC, final.BARTERNG, final.PROFLOSS, final.AMTDBTCN, final.PATRONAG, final.MSAGRDIS, final.[TX-PRD], final.[LFRZ-RFRZ], final.MODULE_BALANCE, final.[TC-150], final.TC150_DT, final.[TC150_DLN ], final.RETRCDDT, final.[TC-290], final.[TC-291], final.[TC-300], final.DC, final.[TC-301], final.DC1, final.[TC-420], final.[TC-421], final.DC2, final.[TC-424], final.PRJ, final.[TC-425], final.PRJ1, final.[TC-460], final.TC460_DT, final.[TC-462], final.TC462_DT, final.[COLL-ASG], final.CC, final.[TC-540], final.[TC-590], final.CC1, final.[TC-591], final.CC2, final.[TC-594], final.CC3, final.[TC-598], final.[TC598 DT], final.[TC-599], final.CC4, final.[TC-610], final.[TC610 DT], final.[ TC610 AMT ], final.[TC-611], final.[TC611 DT], final.[ TC611 AMT ], final.[TC-612], final.[TC612 DT], final.[ TC612 AMT ], final.[TC-670], final.[TC670 DT], final.[TC670 AMT ], final.[TC-671], final.[TC671 DT], final.[TC671 AMT ], final.[TC672 DT], final.[TC-672], final.[TC672 AMT ], final.[TC-673], final.[TC673 DT], final.[TC673 AMT ], final.[TC-678], final.[TC678 DT], final.[TC678 AMT ], final.[TC-679], final.[TC679 DT], final.[TC679 AMT ], final.[TC-922], final.[PROCESS CDS], final.[TC-960], final.[TC-961], final.[TC-962], final.[TC-976], final.[TC-977], final.TAXPRD, final.ADJRSN
FROM final
WHERE (((final.[TC-530])<>"TC-530") AND ((final.[ DOB ])>1929) AND ((final.[ DOD ]) Is Not Null) AND ((final.[TC-150])<>"TC-150") AND ((final.[TC-290])<>"TC-290") AND ((final.[TC-291])<>"TC-291") AND ((final.[TC-300])<>"TC-300") AND ((final.[TC-301])<>"TC-301") AND ((final.[TC-420])<>"TC-420" And (final.[TC-420])<>"TC-420") AND ((final.[TC-421])<>"TC-421" And (final.[TC-421])<>"TC-421") AND ((final.[TC-424])<>"TC-424" And (final.[TC-424])<>"TC-424") AND ((final.[TC-540])<>"TC-540") AND ((final.[TC-590])<>"TC-590") AND ((final.[TC-591])<>"TC-591") AND ((final.[TC-594])<>"TC-594") AND ((final.[TC-599])<>"TC-599") AND ((final.[TC-976])<>"TC-976") AND ((final.[TC-977])<>"TC-977")) OR (((final.[ DOB ])<1987));

Reg

View 2 Replies View Related

Unwanted Dups - Query Error

Apr 10, 2006

What up folks?

I am getting unwanted duplicates due to a joining error, I think.

The goal is to extract a list of employees and their accosiated departments showing a field called [CTD] from qry_employee but there are employees associated with more than one department.. I have a qry_employee liked to another qry (�qry_employee_ctd�) containing the desired field [CTD] joined by [employee_name] but there are employees associated with more than one department. NOTE: Both tables contain the department.

Please let me know if more detail is needs to assist.

Any ideas? I am up for any suggestions and appreciate your help greatly
Thanks
T

View 1 Replies View Related

How To Search An Access Table For Unwanted <cr> Characters

Apr 3, 2006

How to search an Access table for unwanted <cr> characters

Occasionally a stray carriage return <cr> Ascii 013 character finds its way into an Access table. These destroy the database when the table is processed by an outside utility for data cleansing.

To prevent this from happening, we have been told to clean the table before submitting it, i.e. remove all of the following:

carriage return, comma, double quotes, equals, greater than, smaller than

Is there any utility available which will remove all these characters when being run only once? (i.e. not find/replace which has to be started separately for each of these characters)

How do I search for a <cr>, even with find/replace?

Thanks.

Adrian

View 2 Replies View Related

Unwanted Extra Results From Select Query

Mar 9, 2006

Hi,

I am a novice with Access and would like a steer with what I am sure is a simple issue but I can't find an answer. :confused:
I currently have 2 queries based on 2 separate tables.

Ops_Log_996_Query
SELECT [996_Table].Unit, [996_Table].Location, [996_Table].hiredate
FROM 996_Table
WHERE ((([996_Table].hiredate)=Date()));

Ops_Log_SQTU_Query
SELECT SQTU_Table.Unit, SQTU_Table.Location, SQTU_Table.hiredate
FROM SQTU_Table
WHERE (((SQTU_Table.hiredate)=Date()));

When run separately the first query returns 2 results and the other 1 result - fine so far.
I am now trying to combine the results for display in a report so I have a third query which takes its info from the first two -

SELECT DISTINCTROW Ops_Log_996_Query.Unit, Ops_Log_996_Query.Location, Ops_Log_996_Query.hiredate, Ops_Log_SQTU_Query.Unit, Ops_Log_SQTU_Query.Location, Ops_Log_SQTU_Query.hiredate
FROM Ops_Log_996_Query, Ops_Log_SQTU_Query
GROUP BY Ops_Log_996_Query.Unit, Ops_Log_996_Query.Location, Ops_Log_996_Query.hiredate, Ops_Log_SQTU_Query.Unit, Ops_Log_SQTU_Query.Location, Ops_Log_SQTU_Query.hiredate;

This displays the 2 separate records in the first 3 columns ok but in the last 3 columns the info in record 2 is a repeat of record 1. :confused:

Can anyone help me out please ?
Thanks, Oscar

View 2 Replies View Related

Unwanted Additional Recordset After Closing A Form

Nov 14, 2006

Hi!
Solution is maybe simple, but I don't know it!
When I put some value in a control (textbox) on a form, and after that if I close a form (by x button) that recordset is added to a table, but I don't want to do that- I just want to exit (close) the form! How to avoid adding that recordset to a table?
Same things happens when I, by VBA, set focus to control, assign some value to it, and just want to exit the form.

Best regards,

View 4 Replies View Related

Opening Forms And Unwanted Record Advancing..help..please!

Apr 3, 2007

Hi all,

Ok, this is a seemingly VERY simple problem gone haywire!

I have a very simple database that gathers data from 50 questions. It stores these in fields as numbers (1 - 10). no problem.

However, I've had to split the questions over 3 forms and this is where the problem begins. When I reach the end of the first lot and I click the button to open the next form, it jumps to the next record. So, on Form 1 we were on record 5 and then form 2 continues as record 6 (form 3 would be record 7).

All I want is for them all to appear in the same record.

Any hints as to what im' obviously doing wrong?

Many thanks!

View 12 Replies View Related

Forms :: Unwanted Extra Record In Subform

May 20, 2013

I have a continuous subform with allow additions set to false. To make a new record I have used some update vba to create the record direct in the underlying query, then requery the form and the partly created record appears. The user then adds a quantity and some text. The subform still appears without the new record line.....However if I click the button again to create a second new record I end up getting an extra 2 lines.

One is a duplicate of my previous one and a new blank record. These do not actually appear in the underlying table and the subform looks ok. However this extra record confuses the end user and I want to avoid it. Refresh or shift f9 does not eleviate the problem. Sometimes I even get two "current record" pointers.

View 3 Replies View Related

Modules & VBA :: Function To Clear Out Unwanted Bits Before Exporting As CSV

Sep 20, 2013

I've put the following Function together to clear out unwanted bits before exporting as csv. I was just wondering if there's a way of holding the table names together with a total count of each item replaced? I wasn't sure if these were stored and could be returned?

Code:
Public Function ClearCommas()
On Error GoTo ClearErr
Dim db As DAO.Database
Dim tb As DAO.TableDef
Dim fld As DAO.Field

[Code] .....

View 4 Replies View Related

Tables :: Time-stamp Creating New Unwanted Blank Record

Jul 28, 2014

I am trying to do has to be done in a table and without the use of forms (becasue its what works for us) in Access 2007. I have a table where my staff records a number of different data fields for accounts. I currently have a 3 table relationship established. One of these table is our "Notes" table. My staff enters continuous notes for different accounts as new information come in.

What I have done is set my "date" field's default value to "now()" which accomplishes the time-stamp I need, but at the same time as soon as they type anything in another field in that row Access creates another row which is unwanted. follow up how can I go about also having a field that auto fills w/ the user's name/ID?

View 8 Replies View Related

General :: Unwanted Deletion Related To Particular Record In Conversation Table

Mar 6, 2013

I have 2 tables.
1- customers table with 2 fields : customername,customerno
2-conversationstable with 4 fields: date,customername,customerno,details

The conversations table is for keeping memo of telephone conversations with the customers.

I built a simple form deriving from the conversations table. And added to it a combobox with 2 columns from customers table to select the customername an customerno for the form.

While deleting the record in the conversations table,through the form, I saw that the customername and customerno in the customers table of that particular customer record are deleted also. I made no links between both tables.

I wonder why that happened.

View 1 Replies View Related

Beginners Question - Auto Update Fields Based On Fields In Another Table

Nov 26, 2007

I don't have any database experience whatsoever so please go easy.
I'm guessing this kind of this is extremely simple for all of you.

I'm constructing a database of network resources and devices and I'd like to automatically update the values in one field based on the values of a field in another table.

The first table is called "IP" and the fields are called "Address", "IP Type" and "Device".
The second table is called "Devices" and contains the fields "Name", "Description", "Asset Number" and "IP".

Here's an example of the tables: (ignore the "code" tag. i've only used it to align my columns properly)

DEVICES:
NameDescriptionAssetIP
XserveFile Server107203.30.144.75
ProliantXDHCP119203.30.144.15

IP:
AddressIP TypeDevice
203.30.144.75Static
203.30.144.15Static

What I want is for the Device field in the IP table to automatically update it's values based on the values found in the Devices table. In this case, the values that should appear in the Device field in the IP table are "Xserve" and "ProliantX".

I've searched through but haven't found a complete solution, just little pieces which I'm too inexperienced to put together myself.

thank you
-Tim

View 2 Replies View Related

Creating New Table From Form Data Fields/clreaing Datat Fields.

Apr 13, 2008

I created a form in Access that retrieves data from a table. Inside the form, I am able to access/populate data fields with data from the table. I also have data fields, inside the form, that requires key-in data. I have some how lost the ability send all data field information to a second table and clear existing data fields for new entry.

Questions: What settings, code or buttons can I use to send/store data field information to a new table? What settings, code or buttons can I use to automatically clear all data fields from my form once data has been sent to the new table?

View 7 Replies View Related

Auto Rearrange Fields Or Hide Fields In DataSheet Based On Criteria

May 17, 2007

I have main form and that has one sub-form , this Main form governs/determines the data in the sub form.

This Sub form ( DataSheet Mode) has approximately 130 columns and based on the Main form criteria only ~ 20 columns has to be filled.

What I want to do is based on the main forms criteria I want to show only the columns that are applicable to main form criteria.

If I use Columnar or Tabular single form for the Sub Form I am able to hide the fields that are not required BUT IT LEAVES A SPACE/GAP on form ( for the hidden fields that are not required)

Private Sub Form_Load()
If Forms!frmShowPIforActiveAndCanAddNewPI!FrmSubFrmFi lterProductInformationPerFMT!CASETIF = True Then

Me.CASETIF.Visible = True
Else
Me.CASETIF.Visible = False
End If
End Sub

And If I use DataSheet and hide ( visible = no) a particular filed it still shows up in Sub Form

Is there a way to Auto-Re Arrange all the fields in the sub form so that the hidden ( visible = no) fields no not leave gap

Or is there a way by VBA program to select fields ( Columns) from a table to be displayed on a sub form based on a criteria

Thanks
Rahul

View 1 Replies View Related

Forms :: Adding Fields In A Table And Having Output Fill Fields On A Form

Jan 29, 2014

I have a totalquery that runs fine and give me the sum for both fields I'm looking for but I can't get the outputs to fill the fields on the form. I have tried the Dcount query in the control source but that just returns an error and locks up access.

Code:
SELECT [Tble-wcDelays].Causedby, Sum([Tble-wcDelays].HoursDelay) AS SumOfHoursDelay
FROM [Tble-wcDelays]
GROUP BY [Tble-wcDelays].Causedby, [Tble-wcDelays].LinkingID
HAVING ((([Tble-wcDelays].LinkingID)=[Forms]![Frm-ePlusCent]![cleanID]));

That is the query.

View 14 Replies View Related

Forms :: Total Query - Count Of Fields Based On Data In Other Fields

Jun 28, 2015

I have a query that creates counts of fields based on the data in other fields, basically it tells me that in a table there are two entries with value ABC????? and three of DEF????? , the query works perfectly.

When I create a form to display this data and base the form on the Query I keep getting a message box asking for the ID (key field) from the base table.

If I type * in the box (to denote all values) and press enter I get the results expected.

View 4 Replies View Related

Tables :: Set Up Validation Of Fields Unit And Size Based On Another Fields Data?

Nov 18, 2013

Basically in my order details table i have the following fields

Product
Unit
Size

At the moment i have the Product field with a dropdown that gives me all the products from my ProductT. But once i choose the correct product in the unit field it gives me all the possibilities of every product not just the units associated with that product. ie

ProductT
Grasshopper Box1000 Adult
Grasshopper Box1000 Subadult
Worm 10pz Big

When I select the grasshopper product and move on to the unit field i also get "10pz" option but this is not a product available.

How do i set up validation of the fields Unit and size based on another fields data?

View 4 Replies View Related

Modules & VBA :: Pass Data From Fields Into Table And Reset Fields

Aug 27, 2013

I have a database that will register the emails coming in and what time, also the time, date out and person.

I have a form with the fields to be filled in and a submit button. There are some fields that are automatically filled in and others need manually fill in.

below that part is a sheet (subform in the form of sheet) that should be filled in with the above data. Once it is there, it should empty the fields so they are ready to be filled in again. If I fill new data and press the submit button, it should go to the next available row.

View 2 Replies View Related