Microsoft Access





Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Share tech news, updates, or what's on your mind.

Sign up to Post

Access - need to convert all text to upper string.

I need to convert all fields in a table to upper string.  I would prefer to do this with a query.
CEOs need to know what they should worry about
CEOs need to know what they should worry about

Nearly every week during the past few years has featured a headline about the latest data breach, malware attack, ransomware demand, or unrecoverable corporate data loss. Those stories are frequently followed by news that the CEOs at those companies were forced to resign.

Access:  Need to remove blanks, similar to an excel trim formula.

How can I remove all the blanks from a column of data using a query?
I have a small database, which I have included.
Four tables.  
Tblcompany  Fields are Companyid, companyname
tbepayrolldate  fields are payrolldate, current week y/n
tbl employee   field are employeeid, Firstname, Lastname, Companyid
tblweeklypayroll fields are payrolldate,employeeid,grosspay

So I create a form with a subform that gives me the payrolldate and the subform gives me the detail of the weeklypayroll.

Now I want to add a filter so I am only getting the data from one company.  IE I choose company 2, I only get the data from tblweeklypayroll for employees from company 2.

I also want to limit the dropdown list to employees from that company, when I do dataentry.

I know there has to be a easy way but I am stumped.

THank you in advance.
Access - This record set is not updateable ????????

My db which we made a few changes to (mostly form size and I'm not sure what else) has stopped working and I am getting the error that "This Recordset is not updateable.

What causes this and where should I start looking?????  Help Please!!!
Access Database: I have two tables. Table1 and Table2. Table1 has a field called EntityCode (this is the primary key) and a field named Form.
Table2 has a field named "Entity Code" (this is the related field) and a field named "2017_5471Filed". How can I run a query that will link both tables based on EntityCode and copy the contents from field Form to field "2017_5471Filed"? There are about 25,000 records to copy.
I'm trying to find the average time in hh:mm:ss between two dates. I would like the see the average difference by year and month.
Access -  I am using an Access template for reporting issues.

The DB opens with the form Issue List.

I would like to change this to open with the form  Opening Form.

I can't seem to find where and how the Issue List is being called to open automatically.

I have tried this code on the sub-form it does not fire where am I going wrong here?

Private Sub Form_LostFocus()
MsgBox "Please save your data now", vbOKOnly, "Internal Audit Manager"
End Sub

This is really disappointing All I need is to inform the users that they have completed the data entry and they need to save the data that is all


Getting an invalid string with this as the control source for a field on a form...

= DSum("[Qty1TotalCost]", "tblBiddingVendorPrices", "[EstimateID] = " & Forms![frmVendorBids]![txtEstimateID] & " AND [Qty1TotalCost]","tblBiddingVendorPrices","[Qty1Select]= 'X'" & " )

Open in new window

Notes:  Qty1Select is a text field.    EstimateID is a number field.

Can someone help?
Let's say I have two tables:  TableA and TableB - FieldA has some values similar in FieldB and I want to do a compare.
The problem with TableB and it's FieldB - the values are longer than what's found in TableA, FieldA.  

To explain further I provided a sample database:

Table A - FieldA

TableB - FieldB has:

I want a query listing with a join to show the following results:

(Table A, TableB combined)

FieldB, FieldA
University191803ABC, University191803
UL158, UL1580AH14L

These match because the first set of characters match- from the start of the value.  

(University191803 for the first one.  University192303 for the second row.)
The first two are 19 characters that match whereas the last example grabs the first 5 cause of the smaller value: UL158....

How would I go about creating such a query in Microsoft Access?
Ensure Business Longevity with As-A-Service
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

I have the following in VB that creates an Excel file and places it in a folder:

DoCmd.OutputTo acOutputTable, "Data2", acFormatXLS, "C:\Feeds\Data2Export" & "\Data2-" & SuppID & ".xls", False

I need to convert that so it creates a tab delimited csv file instead. How to do that?

Can anyone point me to a resource on the proper way to document an Access database? I have a 14 year old departmental database with 1,000+ tables and queries, of which I don't yet know how many are active or artifacts and I need to high-level document it for the department but also in more detail for myself. I know FMS has software...although I'm not sure I can install it on my system. How would you go about doing it?

Hi Experts

I am trying to encrypt data from a field with filed type longtext in Microsoft Access throug VBA.  I have downloaded an AES encryption script from Rabbit.  Here is the code - I have no idea of how to implement it.  The text input filed is called Text1 and I am trying to output it to Text3.  Your help will be much appreciated

Option Compare Database
' Dependency on "functions Base64" and "functions SHA1"
Function StoreEncryptAES(sMessage As String, sPassword As String, Optional iRounds As Integer = 20000) As String
    StoreEncryptAES = Encode64(EncryptAES(SHA1HASH(sMessage) & ":" & sMessage, sPassword, iRounds))
End Function

' Dependency on "functions Base64" and "functions SHA1"
Function RetrieveDecryptAES(sMessage As String, sPassword As String, Optional iRounds As Integer = 20000, Optional asString As Boolean = False) As String
    Dim dMessage As String
    Dim hash As String
    Dim separator As Integer
    dMessage = DecryptAES(Decode64(sMessage), sPassword, iRounds)
    If InStr(dMessage, ":") < 1 Then
        If asString = True Then
            RetrieveDecryptAES = "#invalidpassword"
            Err.Raise 640, "functions AES", "Invalid password/passphrase"
        End If
    End If
    hash = Left(dMessage, InStr(dMessage, ":") - 1)
    RetrieveDecryptAES = Right(dMessage, Len(dMessage) - (Len(hash) + 1))
    If hash <> SHA1HASH(RetrieveDecryptAES) Then
        If asString = True Then
            RetrieveDecryptAES = 

Open in new window

Hi Experts!

I want to settle on the code below to generate a progress meter on each report, now the code works okay but you have to keep on clicking next to move it on. Is there a way to put it on auto so that when the report in loading it is also moving automatically and when the report finishes it should disappear automatically?

Private Sub Report_Load()
Const lngRange = 10
Dim varReturn As Variant
Dim lngStep As Long
varReturn = SysCmd(acSysCmdInitMeter, "Showing progress", lngRange)
For lngStep = 0 To lngRange Step 2
varReturn = SysCmd(acSysCmdUpdateMeter, lngStep)
MsgBox "Next Step " & lngStep
varReturn = SysCmd(acSysCmdSetStatus, "Processing last step")
MsgBox "Change status on left"
' clear status bar text
varReturn = SysCmd(acSysCmdSetStatus, " ")
End Sub

I will appreciate your help on this!

See picture here

Hello, All.

In my access database column, I have the following string of numbers and characters.

The column (CASTID) has the following

Open in new window

I have a code to use in ASP, but what I need now, is a query within access to get the number.

So, in this case, we are needing only 2011
And looking at the string, we have two 2011's
A single 2011
And at the end, 20119

We only want, returned what is searched for.
So in this case, the only record(s) we need returned is 2011.

Is I do a search with like
FROM Media
WHERE CastID Like "*2011*"

Open in new window

It returns all the numbers in the row but also finds records that do not have the number associated with it.

Access 2003, I built a table with 216 fields. Now I am trying to add the first record, and I can add data into the first 50 or so fields, but after that I get "Record is too large" error. The data I am putting into that first row is tiny, well below the size limit of each field.

I read here that the error may have to do with table field name limits or field data type sizes, maybe Access 2003 simply cant handle a table with this many fields. True?
I want to run my report based on  2  Parameters  -  sometimes
and sometimes
I want to rum my report based on   1  parameter.

This is what I current use for my  2  parameters.

SELECT tbl_TechIDS.TechID, tbl_PMs.New_Crafts, tbl_TechIDS.Description, tbl_TechIDS.Unit, tbl_Tasks.Tasking, tbl_PMs.P_Interval
FROM tbl_TechIDS INNER JOIN ((tbl_PMs INNER JOIN tbl_Tasks ON tbl_PMs.PM_ID = tbl_Tasks.PM_ID) INNER JOIN tbl_TechPMLink ON tbl_PMs.PM_ID = tbl_TechPMLink.PM_ID_FK) ON tbl_TechIDS.EQ_ID = tbl_TechPMLink.EQ_ID_FK
WHERE (((tbl_PMs.New_Crafts)=[Enter Craft ID]) AND ((tbl_TechIDS.Unit)=[Enter Unit ID:]));

I would like to be able to use a wild card to get all  tbl_PMs.New_Crafts   and then use the parameter for  tbl_TechIDS.Unit

Please note, I am looking for MS Access VBA script to rename an excel file as below.

C:\folder 1\ abcd.xlsx to
C:\folder 1\ abcd_16April2019.xlsx (changing the file name with today date)

Thank you
Access;  Maintaining field width
I have several subforms and I can't seem to get them to maintain the width set in design view.
Default view is Datasheet
Form width is set to 10"
Field width is set to 10"
When I open the main form the subform width is 10"  but the Field width is not.
Exploring ASP.NET Core: Fundamentals
LVL 13
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Hi Experts,

When designing a table that will contain many Yes/No questions, some of them only have only options of Yes or No, while others have also N/A.

Since the triple state fields would have to be defined as either text or number, would it make more sense to define all of them as number fields, or better to define as Yes/No (Bit) type for those who only have two options?

I am working on converting some of my queries in MS Access to stored procedures in SQL Server 2014 but I can't seem to figure out how to convert this part of my query. Can anyone help me figure this one out?

MS Access Query: IIf(IsNull([MaterialRoundness]),"   ",Format([MaterialRoundness],".0000##")) AS MaterialRoundness1

How would I write that in a stored procedure. That is just one part of my stored procedure that I cannot figure out. So if the [MaterialRoundness] IsNull then I want to leave 2 blank spaces, otherwise load the value that is in the [MaterialRoundness] field.
I'm using the below code to send Outlook emails from MS Access. I would like to encrypt the email using vba ... maybe enable the encryption button on the ribbon.

    Dim sMail                 As String
    Dim objOutlook            As Object
    Dim rs                    As DAO.Recordset
    Dim sSQL                  As String
    Dim strName               As String
    Dim sSubj                 As String
    Dim sBody                 As String
    Dim sTo                   As String
    Dim strCC                 As String
    Dim sCC                   As String

    'Get Email Addres
    sSQL = vbNullString
    sSQL = sSQL & "SELECT   tblTempShipNotice.Email," & vbCrLf
    sSQL = sSQL & "         tblTempShipNotice.Lname," & vbCrLf
    sSQL = sSQL & "         Concatenate(""SELECT Emails FROM tblFO WHERE FO='"" & Nz([FO],""~999"") & ""';"","";"") AS CCEmails," & vbCrLf
    sSQL = sSQL & "         tblTempShipNotice.Adjudicator" & vbCrLf
    sSQL = sSQL & "FROM tblTempShipNotice;"

    Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)
    With rs
        If .RecordCount <> 0 Then
            Set objOutlook = CreateObject("Outlook.Application")
            Do While Not .EOF
                'Initialize the variable for each loop
                strName = ""
                sMail = ""
                sSubj = ""
                'Populate the variables with our record data
                strName = Nz(rs![Adjudicator], "")

Open in new window

Access - need combo box to re-query . The form  frm_MAIN_TechIDs  has a combo box  Combo46.  (continuation of prior questions)
It shows a list of all the TechIDs that have a PM associated.
This form is where I associate PMs with the TechIDs.
When I add a NEW TechID  it does not show up in this list - I assume because it does not have a PM associated with it yet.  
Is there a way to get this combo list to show TechIDs that do not have a PM associated yet?
The Combo already has an Embedded Macro associated with 'After Update'
I can't get the combo to show the NEW TechIDs that do not have a PM associated.
Hello Experts,
I would like to add a check box to a Microsoft Access Form that filters the records displayed by the Form.  

For the purposes of this question, let's assume that the table/form represent shoes.  I have 1000 records in a table (t-Shoes), and I'm displaying those records w/ a form (f-Shoes).  One of the fields in the table is Color, and 100 of the shoe records contain a Color = Red value.

How can I add a check box to f-Shoes so that when the user checks the box, the displayed records only show records where Color = Red?

Hi Experts There!

I want to have a situation where once the cursor is in the sub-form it should not leave the sub-form unless a user moves it manually to the parent form, the reasons are that I want the users to have more time reviewing the data sheet before leaving it. The problem with access it designed to save data once the last control is reached and left, that is the reasons why the cursor should not leave the data sheet until a user is very much satisfied only then can he/she intervene. I tried the cycle property it does not work , it only works for the parent form.



Microsoft Access





Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.