Hi

I use below formula in Access query column, when I copy this column data set and paste in excel, then look at the count value, it shows full count of cells in the column including blank cells. How can I modify this formula which keeps only non blank values as it should be and keep blank values as blank.


EvNum1: IIf(Left(Trim([EveNum]),2)="KK",[EveNum],"")

Hope you get my point.

Cheers

Is there a way to filter a pivot table and at the same time update a pivot chart with the same filters?

I have 2 Access Forms that I am trying to pass data between.  From From my CustShipAddressList, I have an EDIT button that should open that specific CustShipAddID in a popup where I can edit the address fields.  For some reason, the button will open the pop up form but it is blank and changing my CustShipAddId number to 0 in the source table.

I have tried to 2 different types of Code.  First opens the form with a where condition and the second with openargs. Both are having the same issue.  

Thanks in advance for your assistance.  I am using Access 2016 (from Office 365 Home subscription).

Form CustShipAddressList

Private Sub EditCustShipAddBtn_Click()
   Dim CustShipAddID As Long
   Me.CustShipAddID = CustShipAddID
    DoCmd.OpenForm "CustShipAddress", WhereCondition:="[CustShipAddID]=" & Me!CustShipAddID
   'DoCmd.OpenForm "CustShipAddress", OpenArgs:=CustShipAddID
   DoCmd.Close acForm, "CustShipAddressList"
End Sub

Select all Open in new window

DBEE---092518.accdb

I'm using a customised validation rule in access. Whenever an error occurs  I call the Forms FormError event and handle the error message according to the language of the user. For example if a variable is declared as numeric and the user enters characters, access will automatically call the formError event. However, I have recently changed to the new 64bit version and here the error is handled without calling the FormError event and no message at all is displayed. Is this correct or is there anything I can do to have the same behaviour as in the 32bit version? Thank you so much for any help!

Hi,

Basically, I need to recreate 3 sheets on excel workbook using access vba. The 4th sheet on excel has got table/chart which have got excel formulas and they are looking into the other 3 sheets to generate values. Each day when I delete these 3 sheets, and recreate them, the formulas in the 4th sheet get corrupted. Even if I generate other 3 sheets, the values on the 4th sheet are not being updated because of the corrupted formulas. what is a solution for this? One solution is to generate formulas on 4th excel sheet using access vba. But there are lots of formulas (more than 100 as of now, big chance to increase). Is there any other suggestion like keeping a sheet with formulas and copy paste them using access vba.?But if I copy paste I need to do "replace" sheet name at the end and its going to be complicated. Any help on this greatly appreciated.

thank you
A

hi, I hv a sorting issues, can anyone show me how to query the results from the sample Table? currently the sorting is by Autonum.

the result i am looking for is as follow after sortingSorting-Issue.mdb:

PondName
A1
A2
A5
A10
B1
B2
B3
B4
B5
B6
B7
B8
B9
B10
B12
B13
B14
DDA1
DDA7
DDA9
DDA10
DDB1
DDC12
UK1
UK2
UK3
UK4
UK5
UK6
UK7
UK10
UK12

I am working on a music database with two main tables, tblTitles and tblSongs and other lookup tables.  The main form 'frmTitles'  has a continuous subform 'subfrmSongs'.  On frmTitles I have a field "Status".  It is a lookup field (using a combo box) and the valid selections are "Active", "For Sale" and "Sold".  Now if the status is "Active" or "For Sale" I want all fields enabled except for "SoldTo", SoldDate" and SoldPrice".  On the other hand if the status is "Sold" I want only the Status, SoldTo, SoldDate and SoldPrice enabled with all of the other fields disabled.

Finally, when the form is first opened I want the status determined from the value in Status and the fields setup according to the above.  I have tried using Case statements and If - End If but I just am missing something.

Private Sub Status_AfterUpdate()
 If Me.Status = "Sold" Then
       
          ' if the user has entered Sold
           
            Me.MediaType.Enabled = False
            Me.RollType.Enabled = False
            Me.RollOrigin.Enabled = False
            Me.RecutSource.Enabled = False
            Me.TitleNumber.Enabled = False
            Me.Title.Enabled = False
            Me.GroupPerformer.Enabled = False
            Me.Genre.Enabled = False
            Me.PurchasedFrom.Enabled = False
            Me.PurchaseDate.Enabled = False
            Me.CurrentMarketValue.Enabled = False
            Me.Condition.Enabled = False
            Me.RollRating.Enabled = False
 …

Hello.  I am adding file attachments to a table using the code below (and I know the consequences but need to do what is required) that is on a submit button.  As you can see, I am adding different fields to the table.  With what I have below only adds the Attachment field and the PropertyInspectionID field.  The other 3 do not get entered into the table.  I do a step thru and the info shows.  The sql statement has the order of the fields in the table.  I rarely work with arrays and I have a feeling that is where the problem is.

Can another set of eyes look this over and see where or what I'm missing?  

Thank you.

... John

    Dim cn As ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim filepath As String: filepath = Me.txtPath
    Dim sqlAttachment As String
   
    sqlAttachment = "SELECT AttachmentTitle, AttachmentDescription, AttachmentExtension, Attachment, PropertyInspectionID " & _
                    "FROM tblPropertyInspectionAttachment " & _
                    "WHERE PropertyInspectionID = " & Me.txtPropertyInspectionID
   
    Set cn = CurrentProject.Connection
   
    'arrays are zero based
    Dim fieldList(4) As Variant 'create array to hold list of fields to insert values into
    Dim valueList(4) As Variant 'create array to hold list of values to insert values into
   
    fieldList(0) = "AttachmentTitle"
    fieldList(1) = "AttachmentDescription"
    fieldList(2) = "AttachmentExtension"
    fieldList(3) = …

The current formula in Column E, shown below, concatenates Columns A, B, C and D to create a unique Product Number:

=A2&IF(B2<>"","-"&B2,"")&IF(C2&D2<>"","-"&C2&D2,"")

Select all Open in new window

I need to update the current formula whereby if the specific text string "-AAU" is in Column B, it is not to be included in the final concatenated Product Number in Column E.

I have included a sample spreadsheet that has the current formula in Column E, and the updated, desired results I am looking for in Column F.

Thanks in advance for assistance with this...

Andrea
Product_Numbering.xlsx

Hi,
I have a report on which there is a control called CtrlCode, if the information in this control ends with an X, I need the information already in control AccessTotalsSumOfWeight to be copied in control Total_X
I tried the following formula:
= IIF([CtrlCode] Like "*X","[AccessTotalsSumOfWeight]"
I get a mismatch

Any help would be greatly appreciated
Thanks
Jeannie

Hi!  anyone have any luck with a "TELNET" like connection with MS ACCESS?    I send simple text commands to a connected unit via TELNET.    By TELNET I mean using a communications application that converses with a connected device via IP address.     Something like sending "readbuffer01" and a response like "0,00.4,2" is returned by the conneted device.   I do this over a TELNET application like Tera Term set up for IP of the connected device xxx.xxx.xxx.xxx format for IP and TCP/IP port 3602 emulating a VT100 terminal.   Any way that ACCESS can send commands and listen for a response like these terminal programs.   I am basically trying to automate data collection over the tcp/ip connection and store in a form/table.

Thanks!
Brian

General Access Database question - I have linked tables with column names that I refer to in Queries, Forms, and Reports.
Everything functions the way we need it to.

Is there a way of changing the Linked table column names and have the existing Queries,Forms, and Reports still function correctly?

Or, do I have to start with a new Linked table with the new column names and redo all the Queries, Forms, and Reports?

Thank you in advance for any suggestions offered.