Is there a way to hide a column in a query that does not have any data in it?
I have tables that are populated with YES/NO boxes and I only want to bring back the information for fields that are YES. Currently, the queries run and bring back all fields.
Each query will only have 1 result as they are limited by the primary key.
Thanks!
Hi Gurus,
Here's my situation. I have three columns in Access:
Attribute Name, DateValue, CharValue.
I wish to create a make table query which does the following:
If Attribute Name = 'approved_date' then take the value from DateValue Column and populate it in a New table 'new'.
but if the
Attribute Name = 'description' the take the value from CharValue column and populate it in the same table 'new' in a different column. Please help, how to achieve this.
Thanks,
Kon
I have a table which has a column and some of them are filled and some are blank. I want to fill the blank ones with the previous values, how do I do this. Thanks for any help..
View 4 Replies View RelatedOK, here we go...... Is there a way to convert multiple rows to a single record. For example:
Rec# Existing Data
001 | 1000
002 | 2001
003 | 3252
004 | 6573
005 | 8983
006 | 9897
Is there a way to put this all in one record, like this:
Rec# . 1st# . 2nd# . 3rd# . 4th# . 5th# . 6th#
001 | 1000 | 2001 | 3252 | 6573 | 8983 | 9897
And can you do this from the query design view?
Just curious.
I am making this harder than it has to be i'm sure. I had a previous post of auto-texting a field, well why can't I just take two fields I have, such as the ID field which is an autonumber field and combine that with a type field that I have which is D.
If I could merge this in a query, wouldn't it give me the results as D01, D02, etc?
I couldn't find anything helpful online, one side said do the following.
SELECT receivables2.type+receivables2.ID
FROM receivables2;
Only I get errors.
I also tried this.
SELECT merge(receivables2.type; receivables2.ID);
FROM receivables2;
But it says merge is an invalid expression.
Could someone please help?
I have a form displayed as data sheet, how can I block the column so that the final user will not modify the width of the columns?
M
hi all
i am adding colums like
Col_1 + Col_2 + col_3 + ...so on
now when a field is empty/null there is error in result
how to overcome this please help
i am adding colums ( not SUM(col_1) )
thanks in advance
NMK
I have a table containing about 120 records of 40 fields containing integer values. The values are 0 (for 'no experience'), 1 - 5 (for evaluation of experience) and 9 (for question not answered). I would like to generate a row of averages for the 40 columns.
Access includes the '0's when using the Avg function. (So 1,0,3,0,1,4 yields 1.5 (1+0+3+0+1+4 / 6) rather than the accurate 2.25 (1+0+3+1+4 / 4)). I can tackle this in two ways: I either convert all zero's to NULLs, as Access will not count NULL in an Avg function call, or I can do each column in a seperate query using a WHERE clause. I also have the problem of screening out the 9's. I'm reluctant to create 40 queries and then another to amalgamate the results as this seems a very silly way to solve this problem. I cannot convert both the zeroes AND the 9's to NULL as to do so would lose valuable data.
Can anyone suggest how I can obtain a full row of averages for the 40 fields, ignoring 0's and 9's?
I created a fixed header cross tab query that totals up how many photos of each size there is in an order. I wrote some visual basic code to total up the price (kind of complex with the different packages) but any orders without at least one of every size photo completely locks up Access. The following is the code:
Private Sub Text66_DblClick(Cancel As Integer)
Dim Units
Dim UnitsCache
Dim PackagesCache
Dim F4x5Cache
Dim F5x7Cache
Dim E8x10Cache
Dim E11x14Cache
Dim S16x21Cache
Dim T21x24Cache
Dim WalletsCache
Dim T4x5
Dim T5x7
Dim T8x10
Dim T11x14
Dim T16x21
Dim T21x24
Dim TWallets
If [4x5] = Null Then
T4x5 = 1
Else
T4x5 = [4x5]
End If
If [5x7] = Null Then
T5x7 = 1
Else
T5x7 = [5x7]
End If
If [8x10] = Null Then
T8x10 = 1
Else
T8x10 = [8x10]
End If
If [11x14] = Null Then
T11x14 = 1
Else
T11x14 = [11x14]
End If
If [16x21] = Null Then
T16x21 = 1
Else
T16x21 = [16x21]
End If
If [21x24] = Null Then
T21x24 = 1
Else
T12x24 = [21x24]
End If
If [Wallets] = Null Then
TWallets = 1
Else
TWallets = [Wallets]
End If
F4x5Cache = T4x5
F5x7Cache = T5x7
E8x10Cache = T8x10
E11x14Cache = T11x14
S16x21Cache = T16x21
T21x24Cache = T21x24
WalletsCache = TWallets 8
UnitsCache = T8x10 + T5x7 2 + T4x5 4
Text58 = 0
Text56 = " "
Text54 = " "
Text52 = " "
RerunPackages:
PackagesCache = "x"
If [T21x24Cache] > 0 And [WalletsCache] >= 2 And [UnitsCache] >= 4 Then
PackagesCache = "F"
[WalletsCache] = [WalletsCache] - 2
[UnitsCache] = [UnitsCache] - 4
[T21x24Cache] = [T21x24Cache] - 1
Text58 = Text58 + 439
GoTo FoundOne
End If
If [S16x21Cache] > 0 And [WalletsCache] >= 2 And [UnitsCache] >= 4 Then
PackagesCache = "E"
[WalletsCache] = [WalletsCache] - 2
[UnitsCache] = [UnitsCache] - 4
[S16x21Cache] = [S16x21Cache] - 1
Text58 = Text58 + 305
GoTo FoundOne
End If
If [E11x14Cache] > 0 And [WalletsCache] >= 1 And [UnitsCache] >= 3 Then
PackagesCache = "D"
[WalletsCache] = [WalletsCache] - 1
[UnitsCache] = [UnitsCache] - 3
[E11x14Cache] = [E11x14Cache] - 1
Text58 = Text58 + 236
GoTo FoundOne
End If
If [WalletsCache] >= 2 And [UnitsCache] >= 4 Then
PackagesCache = "C"
[WalletsCache] = [WalletsCache] - 2
[UnitsCache] = [UnitsCache] - 4
Text58 = Text58 + 169
GoTo FoundOne
End If
If [WalletsCache] >= 1 And [UnitsCache] >= 3 Then
PackagesCache = "B"
[WalletsCache] = [WalletsCache] - 1
[UnitsCache] = [UnitsCache] - 3
Text58 = Text58 + 127
GoTo FoundOne
End If
If [WalletsCache] >= 1 And [UnitsCache] >= 2 Then
PackagesCache = "A"
[WalletsCache] = [WalletsCache] - 1
[UnitsCache] = [UnitsCache] - 2
Text58 = Text58 + 89
GoTo FoundOne
End If
FoundOne:
If PackagesCache <> "x" Then
If Text56 = " " Then
Text56 = PackagesCache
GoTo RerunPackages
ElseIf Text54 = " " Then
Text54 = PackagesCache
GoTo RerunPackages
ElseIf Text52 = " " Then
Text52 = PackagesCache
GoTo RerunPackages
End If
End If
UnitsCache = (T8x10 + T5x7 2 + T4x5 4) - UnitsCache
Do
If F4x5Cache < 4 Or UnitsCache = 0 Then
GoTo Skip4x5
End If
F4x5Cache = F4x5Cache - 4
UnitsCache = UnitsCache - 1
Loop
Skip4x5:
Do
If F5x7Cache < 2 Or UnitsCache = 0 Then
GoTo Skip5x7
End If
F5x7Cache = F5x7Cache - 2
UnitsCache = UnitsCache - 1
Loop
Skip5x7:
Do
If E8x10Cache < 1 Or UnitsCache = 0 Then
GoTo Skip8x10
End If
E8x10Cache = E8x10Cache - 1
UnitsCache = UnitsCache - 1
Loop
Skip8x10:
Text42 = 0
Redo:
If WalletsCache >= 5 Then
Text42 = [Text42] + 40
WalletsCache = [WalletsCache] - 5
GoTo Redo
End If
If WalletsCache = 4 Then
Text42 = [Text42] + 37
ElseIf WalletsCache = 3 Then
Text42 = [Text42] + 33
ElseIf WalletsCache = 2 Then
Text42 = [Text42] + 28
ElseIf WalletsCache = 1 Then
Text42 = [Text42] + 18
End If
Text28 = [F4x5Cache] * 12
Text30 = [F5x7Cache] * 25
Text32 = [E8x10Cache] * 40
Text34 = [E11x14Cache] * 110
Text36 = [S16x21Cache] * 155
Text38 = [T21x24Cache] * 249
Text40 = [25x31] * 362
Text44 = [16x20 Collage] * 210
Text46 = [Hard Cover w/Poetry] * 212
Text48 = [Hard Cover Book] * 98
Text50 = [Soft Cover Book] * 68
Text66 = Text28 + Text30 + Text32 + Text34 + Text36 + Text38 + Text40 + Text42 + Text44 + Text46 + Text48 + Text50 + Text58 - Text60
End Sub
I have a report that contains 5 graphs, one for each day of the week. I run this report daily. When I run the report on a Monday, only the Monday graph displays data and the other graphs are blank as there is no data for that day just yet. Is it possible to hide these graphs if there is no data? I did find one suggestion to create a text box with the following code
Code:
Private Sub Report()
If [SumOfWed] = 0 Then
Me.Graph24.Visible = False
Else
Me.Graph24.Visible = True
End If
This does't work for me.Graph24 is the name of Wednesdays graph and the row source for the graph is TRANSFORM Sum([Wed]) AS [SumOfWed] SELECT [Machine] FROM [qryShiftDays] GROUP BY [Machine] PIVOT [Shift];
I have a report in access having multiple columns. because of multiple columns it is not possible to display it in A4 size.
I want to hide the column when there is no data, Is there any possibility to hide column on the basis of criteria ?
I am creating a database for an ambulance organization to track call history. One of the fields that needs to be populated is patient's last name. I need to show the last name when printing the form for billing purpose. However, I only want to shoe the first three letters of the last name in the table. For example, if the patients last name is Smith. When printing the form I would see the name Smith, but in the table I would only see Smi**.
View 1 Replies View RelatedI am posting a report to the web, in a pdf format. the issue is I have data that is hidden under conditional formating in which if it meet the criteria the font is white and background white. This is great since it shows up blank on pdf. However if a slick person takes the pdf highlights the page and transfers it to a word document, he can highlight that area change the font to black and see the data. Is there a way to hide the data prior to creating a pdf.
View 6 Replies View RelatedI am exporting data from Access to excel, once all the data is exported into multiple sheets. I have one Master Sheet which has formula / reference to other sheets. I want to hide those rows which do not have data but contain formulaes referencing back to another sheet.
How to hide those records has formula but do not have data.
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.
I have an Undo button : [btnUndo] and would like it hidden until someone starts to enter data, where it will become visible...
what code would I use? and where would
Code:
me.btnUndo.visible = true
be triggered?
I want to hide external data and databasetools tabs only from my access program. How i do it ?
View 3 Replies View Relatedi want to hide external data and databasetools tabs from my access program.
how i do it ?
Hello
I have had a look through the forum and I am not sure if this can be done. I have a table. i.e. with columns Decsription 1, Cost 1, Description 2, Cost 2 Description 3, Cost 3 description 4, cost 4 etc.
I am looking to try and gave a combo box show this information as follows. I.e
Colum 1 on Combo; Column 2 on Combo
Description 1 Cost 1
Description 2 Cost 2
Description 3 Cost 3
Description 4 Cost 4
I want to be able to auto fill some fields on a form based on the combo box selection.
Has anyone done this before and can it be done.
Thanks
How do I hide the report footer based on the report's data ?
I'm trying to hide if number of users = 1
The report's data is a query built inside the report's RecordSource, not a self standing query.
Hi.
I was just wondering if you can hide a form in the windows status bar?
How can I remove all the bars specially the menu bar:
File, Edit, View, Insert,...
Goal - Show how many policies (dbo_Policy22.Policy.Number) were cancelled during a certain time period (Invoice Date).
Problem - a policy can be cancelled more than once and I don't want to count it after the initial cancel status (885).
Question - Below is my query. I'm thinking I type "Distinct" somewhere, but I have no idea where.
Please help :) Thank you.
SELECT dbo_Producer22.Number, dbo_Producer22.Name, dbo_Invoice22.Invoice_Date, dbo_Policy22.POLICY_STATUS, dbo_Policy22.Policy_Number
FROM dbo_Producer22 INNER JOIN ((dbo_Insured22 INNER JOIN dbo_Invoice22 ON dbo_Insured22.Insured_Key = dbo_Invoice22.Insured_Key) INNER JOIN dbo_Policy22 ON dbo_Insured22.Insured_Key = dbo_Policy22.Insured_Key) ON dbo_Producer22.Producer_Key = dbo_Policy22.Producer_Key
GROUP BY dbo_Producer22.Number, dbo_Producer22.Name, dbo_Invoice22.Invoice_Date, dbo_Policy22.POLICY_STATUS, dbo_Policy22.Policy_Number
HAVING (((dbo_Invoice22.Invoice_Date) Between [Forms]![PfrmYearToDate]![txtStartDate] And [Forms]![PfrmYearToDate]![txtEndDate]) AND ((dbo_Policy22.POLICY_STATUS)=885));
I was wondering if there is a way to hide 1 record from a query
say I have 3 user accounts and on a form I only want to display 2 of them form the query,so the other record is still present but cant be deleted by accident or viewed
I have a form with various fields, two of these fields are "Date Purchased" and "Lease End Date". When there is no value entered originally, I have made a default value of 12/30/9999.
The problem is that when you open the form and these two fields are next to each other, the user says it confuses them seeing a date even though it is a dummy date.
Is there a way for me to create a expression that will be something like:
IIf(DateField = 12/30/9999, Me.Datefield.Visible = False, Me.Datefield.Visible = True)
or VBA
Private Sub Form_AfterUpdate()
If Me.DateField.Value = "12/30/9999" Then
Me.DateField.Visible = False
Else
Me.DateField.Visible = True
End If
End Sub
If have tried both ways and neither works.
Is it possible to just hide a record? My form is based on a query so I know I could remove the record that way but I also have a box that calculates a average time, so the data that is produced by that record still needs to be taken into account. I want to hide some records pureley so the user dosent have to keep scrolling thriugh loads of records. Is it possible to do this?
View 9 Replies View Related