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 Replies

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

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 6 Replies View Related

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

General :: Remove Or Hide File Tab On Custom Ribbon

Apr 23, 2013

In Access 2010 I made a custom ribbon.The File Tab still gives users access to features that should not be available to them such as: Compact & Repair, Encrypt etc.I spent a few hours looking for a solution but found none that works.It does not seem to be possible to hide the File Tab, but is there a way to hide the options it reveals?

View 3 Replies View Related

Queries :: Hide Zero Fields From Query

Aug 14, 2013

I have a query that selects different values from different tables.The values of these fields change all the time and I would like the result of my query to exclude - hide the fields that are zero. However, I only want to hide the specific zero fields not the hole record.Non zero fields should still be visible.

View 10 Replies View Related

Reports :: Hide Subreport If No Data And Remove Blank Space

Sep 11, 2013

I have a report which contains 3 subreports. Now I require to do the following:

1. Hide the subreport if there is no data.
2. Remove the Blank space created when the data in subreport is null.

I tried to Set Can Grow and Can Shrink to Yes but still the blank space was not removed.

When I reduced the height of the subreports in the design view the blank space was reduced but it was still there.

What can be done so that the complete report auto adjusts itself if any sub-report is null? Something like the whole page auto fits itself in the available space?

The option of reducing the size of sub report in design view to minimum is there but it makes the report very un-handy for future reference.

View 3 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

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

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

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

Remove Data From Fields

Feb 14, 2008

Hi, I am new to access. My father likes to gamble and has lists of horses he likes to check to see if they are running. He is currently using Microsoft Word and has to check each one by one. I thought if I could get the lists in to Access then it would be a lot easier. I have a field called 'Name' where I am going to put in the horses. The problem is many of the horses in his list have the country next to their name in brackets. I want to remove the brackets and their contents automatically. So if I enter 'horse (GBR)' then I want only 'horse' to be entered. How would I go about doing this. I have brackets in other fields so want this limited to the 'Name' field only. Is this possible?

Thanks :)

View 12 Replies View Related

Hide Blank Fields

Nov 4, 2006

Hi Friends,

Sounds Impossible But i believe there must be a genius out there to solve this

I was wondering if there is a way to stop displaying fields on a form which contain spaces or blank values. I m using a query that takes data from a table named School

I have a table which have 5 fields. lets say: Field1, Field2, Field3, Field4, Field5

My fields from 1 to 3 have data but field4 and field5 do not have a value in it. What i wish to achieve is to show only those fields which have a value in it. Blank or field with spaces must not be displayed. Is It Possible.

Regards,

Darno

View 1 Replies View Related

Hide/unhide Txt Fields

Oct 16, 2004

This is complicated!
I need to be able to have user run 5 different queries,
I think, and have the results from those queries show
up one under the other on one form footer.
Ex. fld 1 fld 2 fld 3 Date Range
fld 1 fld 2 fld 3 Date Range
fld 1 fld 2 fld 3 Date Range

Each of the rows would be based on a different query.
Also,
part of the query has a date range, and I would like
to have the Date Range from query show up next
to each row.

Thanks

View 1 Replies View Related

Remove Duplicates From Four Different Fields/columns

Jul 31, 2007

Hi everyone,

I am working on a database to manage newsletter subscriptions. Each subscriber record has the option of having up to four email addresses registered to his/her name.

Is there a way to check for duplicate email addresses in the entire database? It would have to compare all values in all four fields of all records.

Any ideas on how to implement such a thing? I'm clueless...

Thanks!

View 2 Replies View Related

Remove Dups Based On 2 Or More Fields

Oct 2, 2007

Greetings to all,I have duplicate records in my table (i.e. there are two or more records that are identical, and I only want to keep one of them). Is there another way to delete the duplicate records other than one at a time? Assigning primary keys and appending only unique records to a new table is not an option because in some instances, I want to maintain duplicate values within a field (i.e. the desire to delete is based on more than one field)Any help would be greatly appreciatedCheers, Lori (An Access amateur)

View 9 Replies View Related

General :: Remove Data Fields

Aug 6, 2014

I have a large application that was built 8 years ago and the user now wants to remove some of the data fields. Is there an efficient way to figure out what objects would be affected if we delete these columns?

View 3 Replies View Related

Remove Some Certain Words / Tags In Fields

Aug 9, 2013

We use Access to pull data from data warehouse and this year they upgraded the new system on the back end and moved old data to the new one. Problem is that in some tables having big fields such as Asset Long Description, Work Long Description and when moved to the new database system, somehow there are certain some XML tags such as <br> </br, etc.. added into the description. So when we pull the data onto the table in Access, is there a way to remove those tags out of the descriptions fields? a macro or module?

View 1 Replies View Related

Forms :: How To Hide Fields On A Form

Sep 10, 2013

I am trying to hide certain fields on a form based on the value in another field (if yes or no). I used the change event to enable/disable the fields in question. however, this doesn't work. This is the code below...

Private Sub Combo314_Change()
If Me.Combo314 = "Yes" Or Me.Combo314 = "No" Then
Me.Reason_Label.Visible = False
Me.Combo316.Enabled = False
Me.Label946.Visible = False
Me.Label77.Visible = False
Me.care_not_qualified_date.Enabled = False

[code]....

View 4 Replies View Related

Continuous Models And Hide Some Fields

Oct 14, 2014

I have a continuous form. Is it possible to hide a particular field in one record only. Where I put in front of each record checkbox, which is hidden for example field (age).

View 1 Replies View Related

Queries :: Remove Duplicates - If 2 Fields Are Equal

Nov 19, 2013

I have a large table with many fields and many rows. There is no primary key. I'll call one field ParentPN, and another field ChildPN. There are many other fields as well. I want to identify all rows where BOTH the ParentPN and ChildPN occur more than once. I know how to create a query to identify duplicates of ONE field in the table, but not two. I can solve this with VBA: I will read the two fields of interest in the first row, then compare both values with every other row. If it finds another row with BOTH ParentPN and ChildPN identical with the first, that's a "hit". Then, repeat with all the other rows. I could find ways to make this run faster, but I was wondering if there are any build in functions to accomplish this. I looked at the Find Duplicates query builder, and all I see is I can select ONE field to search for dupes, not two.

View 14 Replies View Related

Combine Tables - Remove Duplicates And Add Fields

Sep 28, 2015

I have 4 or 5 tables. Most of the fields are exactly the name but they all have at least 1 to possibly 5 or six fields that are not in the other table. Additionally there are some duplicates within the individual tables as well as across tables.

i.e.

I have a

Student Table - with all the info on the student as well as a column called student that identifies them as such however it does not have the columns parent, donor, appeal, designation.....
Parent Table - with all the info on the student as well as a column called parent that identifies them as such however it does not have the columns student, donor, appeal, designation.....
Donor Table - with all the info on the student as well as a column called donor that identifies them as such however it does not have the columns student, parent, appeal, designation.....
Appeal Table - with all the info on the student as well as a column called appeal that identifies them as such however it does not have the columns student, parent, donor, designation.....

-A person can be within one of these tables more than once but with all the same information.
-A person can also fall into all of these parameters so they could be on every table with the same information in addition to the missing columns,=.

Question 1 : what is the best way to dedupe and delete the individual tables (they all have account numbers)
Question 2: I was thinking create a new table with all the columns available, however how do i dedupe across tables while populating the additional columns from each?

View 9 Replies View Related

Hide Fields On A Form Dependant On Contents Of Txt Box?

May 16, 2005

Hi,

How can I get access to hide certain fields or controls on a form if a certain field has no data?

Allow me to explain further:

I have a form that displays data = frmPlantMain
and a associated image = Image1
The image is stored outside of the db as a jpeg and the field in the record source table (Image1) contains a link to the image. For example: C:databasePicture1.jpg

There is also a hidden text box that contains the message "No Image available" = lbl_NoPix
This would be displayed in place of the image if no image has been inserted.

I also have a seperate "insert new image" command button = cmdInsertPic

If the current record has a image then I want to have the controls Image1,lbl_NoPix & cmdInsertPic hidden. This seems fairly easy but it gets more complicated now.

If there is no image I would like these controls visable so that the user can add a new image. I would then need the record to be requiried or refreshed to reflect the fact that a new image has been added.

This is where I fall down - I do not know how to tell access to look at the field in the table (Image1) and if there is no linked or associated image then set the visable propertys of controls a,b & c on the form to true.

I hope I have explained my problem correctly and I thank you for your time in advance.

Regards,

Kenny

View 5 Replies View Related

General :: Show / Hide Fields With Answers?

Sep 19, 2012

I have a combo box with the following

YES
NO
EXPIRED
this is named "Permit_Holder"

when you click yes i want two extra fields to appear called

"Permit_Date_Issued"
"Permit_Date_Expired"

but i cannot seem to get them to hide or show?

I have used the below codes but no success

Private Sub Permit_Holder_AfterUpdate()
If Me.Permit_Holder = 1 Then
Me.Permit_Date_Issued.Visible = False
End If
End Sub

Quote:

Private Sub Form_Current()
Me.Permit_Date_Issued.Visible = False
End Sub

View 6 Replies View Related

Modules & VBA :: Hide Fields When Field Is Cleared While Typing?

Jul 13, 2015

I am working on a database. I created a list box (ListBox) that filters record for a form. This list box is controlled by another field (SearchBox) where I type search string.

The ListBox visibility is hidden when the form loads. It only becomes visible when the user starts to type in the SearchBox. The records gets filtered and when the user clicks on the selection, the form goes to the record and the ListBox becomes hidden again. All this works fine. My problem is for some technical reasons I want the ListBox to show only when there is character in the the SearchBox. When I try to type in the SearchBox the ListBox shows but when I try to clear the SearchBox with backspace the ListBox is still visible

I tried:

Code:
If me.SearchBox = Null Then
me.ListBox.Visible = False
Else
me.ListBox.Visible = True

I also tried

Code:
If IsNull (SearchBox) Then

Still when I type backspace the ListBox remains visible.

EDIT: I forgot to mention that I put this code on the current event of the SearchBox Field.

View 9 Replies View Related