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

How to find missing number in a sequence by applying query in Ms Access. Any help will be highly appreciable.
Hello Experts:

I need some help with some data management (using Excel and Access).

Background on Excel File (incl. two tabs "Raw Data" and "Summary"):
- The "Raw Data" tab contains four columns [Incident Date], [Incident Time], [Age], [Gender]
- Also, for each of these four columns, I added an adjacent "Delta" column using an IF statement to compare two adjacent cells (per row).
- For example, for the [Date] colummn, in cell B2, I compare cell A2 with A3.  If date = 07/01 = 07/02, I output either "Same" or "Different".  In this case, cell B2 returns "Different".
- Alternatively, in cell B3, I compare cell A3 with A4.  If date = 07/02 = 07/02, I output either "Same" or "Different".  In this case, cell B2 returns "Same".
- The remaining formulae (in columns D, F, and H) follow the same principle as applied in column B.
- Now column I... it's using a nested IF formula.   I want to determine where columns A, C, E, G are all the "Same" (or "Different").
- In this case, out of 323 rows, 90 rows are exactly the "Same" and 233 rows equal "Different" (see tab "Summary").

Background on Access file:

- I imported the Excel data from "Raw Data" (without the "Delta" columns).   Table "00_tblRawData_323_Records" includes 323 records.
- I created a Select/Make Table query ("00_qry_233_Records") which uses the "Group" feature in the query.  Upon executing the MakeTable query, it now creates table "01_tblRawData_233_Records".  
- The records in …
Hi Experts.

In Access 2010 when I delete a record in a form and then browse to the deleted record it displays #deleted.

Is there a method to stop this from displaying?

I believe that it results from Access doing a logical rather than a physical deletion until something happens like the form is closed (which would also close the table).

If this is the case is there a way to 'force' the closing?

Bob C.
I have a very long horizontal form.  When I click a command button on the form I want the focus of the form to jump down to a designated spot on the form.  Can this be done?
MS Access crashes when attempting to attach SQL Server tables using ODBC
I am getting old but somehow the following code does not yield the desired result:

xName = "Steven Murray Thomas"
MsgBox InStr(1,xName," ")

gives zero and not the expected 7. Any help why?
Thank you so much Michael
I have a continuous form on an Access form. One of the controls on it is a DateTo textbox and another one is an Edit button. The button is hidden by default.
How do I set it visible if the DateTo is greater than today's date or empty?

I tried to loop through the recordset, but it doesn't work. To see which records are affected, I have changed the background colour of one of the controls.

I used the following code to loop through the form:

Sub ShowHideButton()

With Me.Recordset
  Do While Not .EOF
    If kem_DateTo.Value >= Date Or kem_DateTo = " " Then
      Me.cmdEdit.visible = True
      Me.kem_DateTo.BackColor = vbRed
    End If
End With

End Sub

Open in new window

Is it possible to archive this with VBA or would I have to use another technique?

When I use following formula to convert text date to date format, I get #Error wherever text date is blank. I need null instead of #Error

Base Date: CDate([QryRprtDtl].[BsDate])


text date     Base Date
09/10/18         09/10/18

Please have a look.

Thank you
Hi, I am not quite sure how to come about the query for the intended results, am not sure if that is possible, hopefully Gurus here can help out.
Both Table share the same structures, LinkedTbl and LocalTbl

LinkedTbl                                                             LocalTbl
StampedId           Client                               StampedID           Client
      31                     Client1                                       31                     Client1
      32                     Client2                                       32                     Clinet2
      33                     Client3                                                                Client3
      34                     Client3                                                                Client3
      35                     Client3                                                                Client3

I tried to create a query to update the blank StampedId field from LinkedTbl to LocalTbl so that I able to get the following end results, but it was updated with the last number (35) from LinkedTbl to the null StampID field in LocalTbl, perhaps due to duplicate Clients in Client Column (must allowed duplicate Client entry in column), there are no other field in both Tbls allowed for index.

Supposed results after update query:

LinkedTbl                                                             LocalTbl
StampedId           Client                                  
Hi Experts,

I have a continuous form in my application containing a few text boxes that are set for conditional formatting.

While the application was in MDB format (A2003) it worked fine.

However since converted to ACCDB (A2016) the form is loading/formatting records extremely slow.

Every time user filters or sorts it keeps repainting the entire screen several time, moving thru all records up and down...

What is the solution?

Attached screenshot of what the form looks like.
Hi There,
I like to find how many weeks exist in each month for a year. I have query for each month. I have query in access (summing the qty each month for each Item) for each month January through December from a table that has Qty for each item for  each day.

The query has the year, month, Total Quantity. For example the Jan query would have the following fields
Yr        Mth   Qty    Item
 2018    1      10000   ABC

I would have to find the weekly quantity for that I would have to divide this qty by the number of weeks each month has in a year and it differs each year.
so I like to see the query result with another field MaxWk ( that will tell the total week in that month for the year)
I tried the datediff funtion doesn't work .I tried MaxWk=  DateDiff("ww",1/1/[yr],1/31/[yr],2), just returns 0  
Any idea how I can do this?
I have a continuous form that has a field that is acting strange.  There is one field in the form that only displays values when the scroll bar is clicked.  When it is not clicked the values disappear.

What would be causing this?  I have no formatting going on anywhere.
In MS Access, I have a webcontrol on a form.  I want to be able to force magnification for the URL to 125 percent.  I have found several articles on Google about this, but nothing seems to work for me.  Here is what I have now - the last line represents the best suggestion I was able to find on the intrawebs:

DoCmd.OpenForm "WebBrowser", acNormal
Forms!WebBrowser.RecordSource = "Wordpress"
    Forms!WebBrowser.Filter = "[Select]=-1"
    Forms!WebBrowser.FilterOn = True

'''''  the URL comes from tbWordpress from either be in Site, NewPost or NewPage in the Wordpress table
    Forms!WebBrowser!WebBrowser0.ControlSource = "NewPost"
Is there a way to change the sort field on an MS Access report within VBA and then display it in Print Preview with the new sort applied?
In Microsoft Access I have a report that has a Select Query as a Data Source.
The Select Query is sorted on the Item Number field.  When I run the Select Query it displays in Item Number order as it should.
But, when I do a Print Preview of the report based upon this query it is not in Item Number order.  There are no Groups, Sorts or Totals defined within the report and there is nothing in the Order By property on the Data tab.
Why would the report not print in the order specified by the Select Query?
I have a report in Access 2013 that is not displaying as I would like it.
The report has a sub-report in it.

I want all record from one table to be displayed in the Parent report and only records from another table where the link fields match to be displayed in the Sub-report.

For example:

I have a desktop PC with 5 hard drives in it.  The Desktop PCs information is stored in the parent table.  The hard drive information is stored in the child table.
Right now that displays just fine on the report

But when I add VMs, for OS accountability, I won't have a hard drive to enter.  But on the report that row shows up blank.  I don't want the blank field.  I just want it to display the next record.

As you can see on the report in the attachment.  All my VMS will have NA.  I just don't want or need that row of records showing if there is nothing in it.  
And I did the Join to be all records from Table 1 and only those records from table 2 that match

Any thoughts?

In Access db when a user clicks the Mail Report button on a form an email is created and populated with the information to send to a Distribution List. I would like the user to be presented with a list of email addresses to be able to select from, how do I do that. VB newbie.  Below is my code for sending the form.

strDocName = "PReport"
    strFilter = "DQR ='" & Me!DQR & "'"
    DoCmd.OpenReport strDocName, acViewPreview, , strFilter
    DoCmd.SendObject acSendReport, "PReport", "PDF Format", ";", "", , "New DQR", "Please see the latest DQR issued by HIA QA", , False
Hi Experts,

I'm trying to connect to a Access DB in order to sanitize some data using PHP. I Have already enabled by PHP PDO ODBC driver  (uncomment the extension=php_pdo_odbc.dll line in php.ini) and restarted my IIS.

I get the following code...
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] SQLDriverConnect: 63 [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x14b8 Thread 0x11e4 DBC 0x22f8444 Jet'.' in C:\inetpub\wwwroot\DICE-AccessNOC\includes\access_db.php:18 Stack trace: #0 C:\inetpub\wwwroot\DICE-AccessNOC\includes\access_db.php(18): PDO->__construct('odbc:Driver={Mi...') #1 C:\inetpub\wwwroot\DICE-AccessNOC\find-distinct.php(6): Access_DB::getDB() #2 {main} thrown in C:\inetpub\wwwroot\DICE-AccessNOC\includes\access_db.php on line 18

Open in new window

My includes/access_db.php is

$TBL = "TBL_DICE_Factorial_Analysis";

class Access_DB{
    private static $db;
    private function __construct() { } //no new objects can be created
    public static function getDB() {    
        $access_path = "Z:\\Business\\CLIENTS\\DICE Assessment\\Received Files\\NOC_Database_Backup_2_PHPCopy.accdb";
        //One connection per browser session
        if (!isset($db)) {
            $dsn = "odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)}; Dbq=$access_path;";
            self::$db = new PDO($dsn);
        return self::$db;
    public function __destruct() {


function print_variable($var, $label = '', $raw = true){

    echo '<br>=========START=========<br>';
    echo '<b>' . $label . '</b>';

    if ($raw == true) echo '<pre>';


    if ($raw == true) echo '</pre>';
    echo '<br>=========END=========<br>';

Open in new window

The caller is
    require_once 'includes/access_db.php';
    $query = "SELECT * FROM $TBL ORDER  BY NOC_Code, NOC_Occupation; ";

    $db = Access_DB::getDB();

    $statement = $db->prepare($query);
    $rows = $statement->fetchAll(PDO::FETCH_ASSOC);

    print_variable($rows, 'Fact Analysis');

Open in new window

I have also given full permissions to HKEY_LOCAL_MACHINE\SOFTWARE\ODBC and its children to IIS_IUSRS, but the issue persists.

I'm also posting this under ASP, as I see on Google, this also occurs under ASP.

Any help will be greatly appreciated.
I'm trying to write a query that captures some data from two different tables, but the data from the second table needs to come from the most recent record in that table, which matches the record in the main table.  In Access I would call this a correlated subquery and would use syntax like the following (rewritten for TSQL)

SELECT T1.*, T2.*
FROM Table1 as T1
SELECT Top 1 * FROM Table2 WHERE Table2.ReferralID = T1.ReferralID ORDER BY ISNULL(Table2.EndDate, GetDate())) as T2 ON T1.ReferralID = T2.ReferralID

But this in this syntax, the reference to Table1.ReferralID in the subquery return an error:
Msg 4108
The multi-part identifier "R.ReferrralID" could not be bound.

And I get the same error message when I change that syntax to Table2.ReferralID = Table1.ReferralID.

So, how do I refer to a table/field that is outside the subquery from within the subquery.  I specifically need these to be correlated because I only want one record (the one with the most recent EndDate) from Table2 for each ReferralID in Table2, where there could be as many as 5 records for a given ReferralID in Table2.  And there might be no records in Table2 for a specific ReferralID that is in Table1.

I have a form that includes a social security number (actually a text field).  If the user enters a SS number that has already been used in the table in a previous record I want the attachment ass0ciated with that previous record to be copied to the new records attachment field.  I have tried this but it doesn't work:

If DCount("[SSN]", "tblClients") > 0 Then
Me.atchClientPicture = DLookup("[ClientPicture]", "tblClients", "[SSN] = '" & Forms!frmClients!txtSSN & "'")
end if

Open in new window

Hi, I hv two Access Tables: LinkedTbl and LocalTbl, both share the exact identical structures with autonum running on both tables.
LinkedTbl is constantly updating with records from other users, and obviously the autonum in LinkedTbl will out numbered the LocalTbl which resides in Notebook.
At the end of the day, Records of LocalTbl from Notebook shall append to LinkedTbl provided the records are new to LinkedTbl.

I run the following Append Query for the update:

FROM LocalTbl
WHERE (((Exists (SELECT * FROM LinkedTbl WHERE LinkedTbl.ProductNo = LocalTbl.ProductNo))=False) AND ((Exists (SELECT * FROM LinkedTbl WHERE LinkedTbl.OrderDate = LocalTbl.OrderDate))=False));

However, even without configuration, the above query will try to append autonum from LocalTbl to LinkedTbl along with the new records, which I want to prevent, is there a way to configure the above query to leave out LocalTbl's autonum when append? Where new autonum from LinkedTbl shall assign to the inserted new records from LocalTbl.

LocalTbl data will be cleared and replaced again from LinkedTbl first thing in next working day , therefore LinkedTbl is the primary data source.

Appreciate if anyone can shed some lights in this matter thx.
Is there a way to print “Grouping Headers” in a report using controls? If I know the of the heading then I can use ctrl.value to print the first heading. However, ctrl.value will always show me the first heading only and not the subsequent report headings? Is there any other way to retrieve the information from the report only? Thanks Michael
I have attached a file. If you open the main form and try selecting a writer, the combo box does not work. I used the code below.
What is wrong?


Private Sub CmbSearch_AfterUpdate()

If Len(Form_WriterTotalByWriter_subform.Filter) > 0 Then
Form_WriterTotalByWriter_subform.Filter = Form_WriterTotalByWriter_subform.Filter & " AND " & "Writer = '" & Me.CmbSearch & "'"
Form_WriterTotalByWriter_subform.Filter = "Writer = '" & Me.CmbSearch & "'"
End If
Form_WriterTotalByWriter_subform.FilterOn = True
End Sub
I have a continuous form based on a view from SQL Server 2008R2.  
If i open the view it takes 10 seconds for the records to display.  
If i open the form it takes 20 seconds for the records to display.  You can see the Running Query in the bottom right, it runs for 10 seconds, then starts again for another 10 seconds.
It looks like it runs twice (and unfortunately takes twice as long).

Is this normal?

Any help would be greatly appreciated
I have a form on which I want to do something in records that meet certain criteria.  In my test code below, I loop through the recordset for the form, but my code sets the values for every record to the values appropriate to the first record.  The code below sets Text304 on every record to True if "rst!DateEntered > #9/27/2018 9:02:36 AM#" is true for the first record and every record to false otherwise.

Right now I'm just writing to a text field to diagnose the problem, but what I want to do is to make a field on certain records editable. How do I do that?

Private Sub Form_Load()

    Dim rst As DAO.Recordset
    Dim fld As Field
    Set rst = Me.Recordset
    Do While Not rst.EOF
        If rst!DateEntered > #9/27/2018 9:02:36 AM# Then
            Text304 = "True"
            Text304 = "False"
        End If
End Sub

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.