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

Why doesn't this expression work in an Access Query?

Postcode 1: Left(Trim([Clients].[Postcode]),InStr(1,[Clients].[Postcode]," ")-1)

I am getting an error that the expression is too complex.

If I put the InStr in a column of it's own and the Left with a constant for the length, they both work.

What I need to do is show the first part of the postcode.  In the UK we have postcodes (zipcodes) that have a space in the middle eg: NW1 4FS or B1 2GT or WC1Y 3CE.
Is there another way to parse the string?

Could it be data related?  There are some postcode fields that are empty or don't have a space
in my VBA code I want to be able to delete all the records from a table and then set the ID back to 1 for the next time records are added to the table. What is the best way to do that?
Access reporting issue.

The report  rpt_DueDates   allows me to pull PMs by Craft, Unit and Due Date. It shows the tasking that is due based on the input dates.

The report  rpt_AllTasks    allows me to pull ALL the tasking by TAG_NUMBER

I would like to be able to combine these two reports.

The Main report being rpt_DueDates  which will show only the tasks due based on the start and end dates entered.  Then at the bottom of the page I would like to show all that tasks that might be called at any time.

Example:  run the rpt_DueDates for Start Date  3/1/19  End Date 4/1/19  Craft  M   and Unit   0   >   The report runs for 104 TAG_NUMBERs showing tasking  that is to be done between the dates specified.

I would then like to show all the tasking associated with each TAG_NUMBER at the bottom of the report.  

Example for TAG_NUMBER  >   0-FA-C-5003B
Main Report  =  1 task for the Start Date  3/1/19  End Date 4/1/19  
Sub Report   =   12 tasks associated with TAG_NUMBER  0-FA-C-5003B

I need to know if this is possible and if it is then I need specific instructions on how to do or an example please.
I want a page break in my report for each new instance of a field [first name].  how do I do this?
I have a  report in the detail section with a rectangle property as visible = false.   And if a certain value appears for one of the fields I want the box to be visible to highlight in green.  The rectangle is set to position - set to back.  However, the rectangle box refuses to show - how do I make this happen?

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

  If Me.JobTicketOff = "NBT" Then
    If bxGreen.Visible <> True Then bxGreen.Visible = True
    If bxGreen.Visible <> False Then bxGreen.Visible = False
  End If
End Sub

Open in new window

I also tried this but got an error: Run-time error '32521'
You can't change the value of this property in the OnPaint event with the highlight yellow on bxGreen.Visible = True

Private Sub Detail_Paint()
  If Me.JobTicketOff = "NBT" Then
    If bxGreen.Visible <> True Then bxGreen.Visible = True
    If bxGreen.Visible <> False Then bxGreen.Visible = False
  End If
End Sub

Open in new window

Is there a way to export a microsft access report to .pdf?
I would like to take a user input (a date) via an OK / Cancel box... so I'm successfully using an input box.  

Now I would like to make sure the user input date is:
- Not later than todays date
- If the user presses "cancel" the input box will cancel
- If the user presses OK and it's not a valid date and later than today it will prompt for an entry again.

I'm using the following code but I can't get it to check dates correctly.  If I Dim sOutRxDate as a date... it helps but then I can't tell if the user pressed escape.

Any ideas?

Dim sOutRxDate As String

    Do Until IsDate(sOutRxDate) And sOutRxDate <= Now()
        sOutRxDate = InputBox("What date was the outside Rx was issued?" & vbCrLf & vbCrLf & vbCrLf, "Outside Rx")
        If StrPtr(sOutRxDate) = 0 Then Exit Sub 'User pressed cancel

Open in new window

Thank you.
Good morning, Experts.
I have the problem described below and I am hoping at least one of you can help me with it.
I have an input file Q.xlsx that I format with QualityRecords.xlsm and then import into an Access DB QualityReviews.accdb which contains the table Reviews.
The formatting program and the import process are automatically executed when the DB is opened.
The input file contains some text fields that are concatenated into a column titled Comments, which is column W on the QualityReviews tab in the QualityRecords.xlsm workbook.  The cells in this column have a General format.  The contents in this column can be more than 255 characters in length.
The formatting program properly creates the contents in Column W.
In the DB, the records from QualityRecords.xlsm are imported into the Reviews table.  The Comment field in the Reviews table has a data type of Long Text.

After the records are imported into the DB, the content of the Comment field in the Reviews table is truncated at 255 characters, as if it is a Short Text data type.  
Can any of you explain this, and please tell me how I can get the full content of the Comment column from the QualityRecords.xlsm program in the Comment field in the Reviews table of the DB?
The relevant files are attached.
Thank you in advance for your time, and for any guidance you can give me in this matter.
I want to hide two controls in a sub form based on the condition selected from the parent form , I have tried all what I can , nothing is working out my code is on sub form before update event:

If (Me.Parent!txtProductCH <> [Purchase Order]) Then
Me.CboProduct.Visible = False
ElseIf (Me.Parent!txtProductCH = [Purchase Order]) Then
Me.CboProduct.Visible = True
ElseIf (Me.Parent!txtProductCH <> [Purchase Order]) Then
Me.CboPrices.Visible = False
ElseIf (Me.Parent!txtProductCH = [Purchase Order]) Then
Me.CboPrices.Visible = True
End If

Open in new window

Where do go wrong here?


I seem to have an issue with a query designer criteria.  Here is what I'm using:

Between [Forms]![frmSelectRecord]![txtStartDateOneYearAgo] And [Forms]![frmSelectRecord]![txtEndDateOneYearAgo]

But the query is only returning a count of 5 records while I can see 31 records in the table.  Is there something wrong with my criteria?  Should I be writing it differently?
I'm using the following code to import an Excel file and I would like to add the Excel file name to the import table ,,,, tblReceiptIndex

Private Sub cmdImport_Click()
Dim oXL As Object
Dim wkb As Object
Dim wks As Object
Set oXL = CreateObject("excel.application")

On Error GoTo Err_Handler

Set wkb = oXL.Workbooks.Open(Me.txtFileName)

Set oXL = Nothing

DoCmd.SetWarnings False
If IsNull(Me.txtFileName) Or Len(Me.txtFileName & "") = 0 Then
    MsgBox "please select excel file"
    Exit Sub
End If

'Delete records in the temp table
DoCmd.OpenQuery "qryDelReceiptsIndex"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblReceiptIndex", Me.txtFileName, True

Open in new window

Private Sub cmdSelect_Click()

    Dim strStartDir As String
    Dim strFilter As String
    Dim lngFlags As Long
    ' Lets start the file browse from our current directory
    strStartDir = CurrentDb.Name
    strStartDir = Left(strStartDir, Len(strStartDir) - Len(Dir(strStartDir)))

    strFilter = ahtAddFilterItem(strFilter, _
                        "Excel Files (*.xls*)", "*.xls*")
    Me.txtFileName = ahtCommonFileOpenSave(InitialDir:=strStartDir, _
                     Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _
                     DialogTitle:="Select File")

End Sub

Open in new window

New to Access reports. How would easily create an Access report based on an example report I have in Word format?  Is there an easy way to do this or do I have to put in text boxes for each line on the report.
I have three fields on a form.  They are all date fields (short date format).  If I enter 1/1/2019 in the first field, I want the other two fields to fill with 1/1/2018 and 1/1/2017.  (the first fill date being one year prior to the entered date and the other filled date being two years prior to the entered date.

And then there are three more fields on the form.  If I enter 1/31/2019 in the first field, I want the other two fields to fill with 1/31/2018 and 1/31/2017.  (the first fill date being one year prior to the entered date and the other filled date being two years prior to the entered date.

How can I make these 4 populated fields auto fill?
I am new to Access. I need to take the contents of a table and use them to create a letter that can automatically be printed as a .pdf.  Can anybody explain at a high level how to do this?


Please see the attached.  When the form opens click on an item in the left-hand column.  If the item does not already exist in the right hand table/form, allow if to be added.  But if the item already exists, do not allow it to be added.  What am I doing wrong?
I converted a Microsoft Access backend database to SQL. Within one of the tables I have two time fields. One is StartTime and the other is EndTime.  I am trying to get the Access form to display the correct time format, HH:MM . In SQL I have set the datatype to Time(0). In the Access form, I have the format for the field setup up as shorttime. I have an input mask:  00:00;0;_.  When I first enter data in the table, the time appears to be formatted correctly. However, after I close the record and re-open it, the time is displayed as hh:mm:ss. Is there any way, short of creating text fields for hours and minutes and then using calcs to put them back together to perform math functions, to make this work?
strip special characters from a subform field  during copy and pasting into the subform.

I have a subform named  my_data_subform

I have fields

When someone copy and pastes data from an excel sheet,
I need to delete

special characters in case they are bring unwanted characters into the fields.

QTY field  nothing but  numeric( with no decimals)
my_money  = numeric and decimals...but not      1..00 or   ..09  

description = any alpha/numeric...except special keyboard characters.

Opening a vbs script from within Microsoft Access VBA - I get the error run-time error - Invalid procedure or call or argument.
with the yellow highlight on:  Shell "" & sOpenFileLocal & "", vbNormalFocus

Shell "" & sOpenFileLocal & "", vbNormalFocus
      Exit Function

Open in new window

I have a .txt file and i am trying to import it in Access but when i do some of the records are shifted over the next column.  Does anyone know how to fix this?

thank you
Can queries have corruption? Just trying to clean up my app. Do I need to check Queries? If so how?
I’m working on a form in a legacy Access 2013 application.  The form can be used to add a new record or update an existing record.

The recordsource of the form is ‘tblInstallPay_Main’

When adding a new record the form is opened
DoCmd.OpenForm "frmInstallPay_Main", , , , acFormAdd

Currently when opening to an existing record, the form is currently opened passing the ID as OPenArgs
The logic in the form load event looks like this:
If Not IsNull(Me.OpenArgs) Then
    Me.Filter = "[ID] = " & OpenArgs
    Me.FilterOn = True
    Me.Filter = ""
    Me.FilterOn = False
'End If

I am trying to speed up the opening of the form opening with a Where Clause and commenting the filtering logic.

I commented the filtering logic and used the following to open the form.

DoCmd.OpenForm "frmInstallPay_Main", , , "[ID] = " & gCurrInstallPlanID

It works but when I trace the events triggered on the form prior to showing the selected record I see the form ‘Current’ event is triggered 4 times.  The first two times the ‘NewRecord’ logic is triggered.  The next two times the ‘existing record’ logic is triggered.  This seems very inefficient but I don’t understand why those events are being triggered.

This is the logic In the Form Current event
Private Sub Form_Current()

If Me.NewRecord Then
    prevPlanStatusID = 0
    prevPlanStatusStr = ""
    thisIsAnewPlan = True
    Me.AccountID.DefaultValue = gCurrAccountID

Open in new window

Wonder anyone can provide the Access Update Query for the following solution:
Two Tables Table1and Table2 are  INNER JOIN on ClientID And PaymentMonthYear,  Table1 is used for capturing Client's monthly payment made, Table2 is a reference Table to update the monthly supposed payment amount into one of the field of Table1 to determine if full payment made. Table1's Field [SupposedPayment] is update via query from Table2's [SupposedPayment].
But sometimes Clients made multiple payment within a single month, like to hv an update query to output as following:

TABLE2 (Reference Table)
ClienID           PaymentMonthYear              SupposedPayment
1149A                       Jan 2019                                $500.00
1523B                       Jan 2019                                 $300.00
1662C                       Jan 2019                                 $400.00
1888K                       Jan 2019                                 $100.00
1212B                       Jan 2019                                 $400.00

'Output results after upate

ClientID           PaymentMonthYear        PaymentMade       SupposedPayment                                              
1149A                         Jan 2019                      $250.00                       $500.00         …
I am using MS Access 2016 / MS Word 2016.  I have a table in Access with several currency formatted fields.  When I mail merge those fields in MS Word, the currency formatting is lost.  Is there a way for me to modify the format in Access to have Word properly retain the currency formatting?  I have 700+ merge docs and I do not want to start adding currency switches to each merge field in the docs.  

All the formatting was preserved in this Access application I wrote about 20 years ago and now that I've upgraded from Office 2000 to 2016, this became a problem.  
Thank you in advance!  

Guys I'm looking to add items from a shopping list in MS Access into a Task list in Outlook (Office 365) for both. I've bodged together code to do this using the MS Knowledge base. This is as:

Private Sub Command22_Click()

End Sub

Public Sub Process_Tasks2()

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Set db = CurrentDb
    Set qdf = db.QueryDefs("Query3 - For Task Generation")
    Set rst = qdf.OpenRecordset()
    With rst
        Do Until .EOF
            'Send E-Mail for each Record
            Debug.Print ![Shopping_Item]
            Call fnc1AddOutlookTask(![Shopping_Item])
            .MoveNext         'Move to the next Record
    End With
    Set rst = Nothing
    Set qdf = Nothing
    Set db = Nothing

End Sub

Public Sub fnc1AddOutlookTask(Task_to_add)

    Dim OutlookApp As Outlook.Application
    Dim OutlookTask As Outlook.TaskItem
    Dim olTempFolder As Outlook.MAPIFolder
    Dim myApp As Outlook.Application
    Dim myNP As Outlook.NameSpace
    Dim myRecip As Outlook.Recipient
    Dim TaskFolder As Outlook.Folder
    Dim myTask As Outlook.TaskItem
    Set OutlookApp = CreateObject("Outlook.Application")
    Set OutlookTask = OutlookApp.CreateItem(olTaskItem)
    With OutlookTask
        ' .Subject = "This is the subject of my task"
        .Subject = Task_to_add
        '.Body = "This is the body of my task."

Open in new window

Need VBA Script to tell if truly Microsoft Outlook is opened or not.
This doesn't work completely if you physically open Microsoft Outlook and then physically close it out - the function will still return True.
So at its core - I need to determine if the application is visible to the user or has been closed out.  Because even having Outlook opened and then closing out doesn't really stop the application from running in the background... thus the following function will always return True.

At one point I asked for something similar (C# script to determine if EXCEL was truly opened) and the answer had to do maybe with treeing down a bit further to any child remnants of the original parent but I didn't understand fully what that meant.  I just know it worked after.

Please review:

Public Function SendEmail(ByVal sTo As String, ByVal sSubject As String, ByVal sTextbody As String, Optional ByVal sFrom As String = "", Optional ByVal sHTML As String = "")
On Error GoTo err_proc

  Dim bSuccess As Boolean
  'Dim oApp As New Outlook.Application
  Dim oApp As Object ' New Outlook.Application
  Dim oEmail As Object 'Outlook.MailItem
  'Dim oEmail As Outlook.MailItem
  Dim fileName As String, todayDate As String
  Dim sName As String
  If EnsureOutlookIsOpen = False Then
    If MsgBox("Is your Microsoft Outlook already opened?" & Chr(13) & Chr(13) & "If not, please open it now, wait till it loads and then... " & Chr(13) & "... Click 'Yes' to continue process", vbYesNo 

Open in new window


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.