[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

Microsoft Access

219K

Solutions

285

Content

51K

Contributors

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.

Challenging Access query

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 …

2 Comments

10 Tips to Protect Your Business from Ransomware

LVL 1

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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?

Thanks,
Bob C.

Microsoft Access

4 Comments

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?

Microsoft Access

2 Comments

Question by

SpaceCoastLife

4h ago

ODBC Crashes MS Access

MS Access crashes when attempting to attach SQL Server tables using ODBC

20 Comments

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

5 Comments

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
  .MoveFirst
  Do While Not .EOF
    If kem_DateTo.Value >= Date Or kem_DateTo = " " Then
      Me.cmdEdit.visible = True
      Me.kem_DateTo.BackColor = vbRed
    End If
    .MoveNext
  Loop
 
End With

End Sub

Open in new window

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

Thanks
Massimo

8 Comments

access 2016 show same fields but different rows/data on same page report

Microsoft Access

2 Comments

I have a table in Access with 3 columns: first is transactionID, the second is textline, 3rd is msg. check example below pls

transactionID      textline      msg
564231      3      hello
564231      4      hi
564231      5      hello
564231      9      hi
564202      1      hello
564202      2      hi
564202      8      hello
564202      22      hi
513830      7      hello
513830      10      hi
513830      13      hello

I would like the result to be as follows
Return the transactionID, textline, msg for those with consecutive textlines per transactionID

transactionID      textline      msg
564231      3      hello
564231      4      hi
564231      5      hello
564202      1      hello
564202      2      hi

Microsoft Access

2 Comments

I have a current Access DB saved an *.accdb. I am trying to convert this DB to the *.mdb format. However the current Access DB has embedded macros within it. How can I disable these macros so that I can convert this Access DB into the older MDB format??

Sincerely,

Patrick Q

PATRICK QUINN
SENIOR DATABASE DEVELOPER
NA ALIʻI, LLC
An 8(a)ccessible Native Hawaiian Owned Company

11 Comments

Question by

Asatoma Sadgamaya

1d agoSolvedPrivate

Access Cdate gives #Error

Hi
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])

ie.

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

Please have a look.

Thank you
A

Microsoft Access

14 Comments

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 …

7 Comments

LVL 6

Question by

bfuchs

1d ago

Continuous form performing very slow in Access 2016

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.
Untitled.png

5 Comments

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?
Thanks

Microsoft Access

8 Comments

Question by

SteveL13

2d agoSolved

Strange form field bevavior

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.

Microsoft Access

15 Comments

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
DoCmd.Maximize

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"

Forms!WebBrowser!WebBrowser0.ExecWB OLECMDID_OPTICAL_ZOOM, ExecOpt.OLECMDEXECOPT_DONTPROMPTUSER, 150, DBNull.Value

7 Comments

Access form getting data from database. Using forms within Access to display data. I printing two Access forms per page(printer settings 2 per sheet). The bottom of my report is butted up right on the top of my 2nd report. How do you get a space (1") between them?

6 Comments

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?

3 Comments

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?

Microsoft Access

2 Comments

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?

Thanks,
john
EE_HWB.docx

10 Comments

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-HIX-DQR@company.com;", "", , "New DQR", "Please see the latest DQR issued by HIA QA", , False

9 Comments

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

<?php

$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() {

        self::$db->close();
        
    }
}

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

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

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

    print_r($var);

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

Open in new window

The caller is

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

    $db = Access_DB::getDB();

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

    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.

5 Comments

LVL 51

Question by

Dale Fye

3d ago

TSQL - Refer to table.column in the main query from within a sub query

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
LEFT OUTER JOIN (
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.

Dale

5 Comments

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

Thoughts?

Microsoft Access

7 Comments

Question by

kay soo

3d agoSolved

Append Query with Criteria

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:

INSERT INTO LinkedTbl
SELECT *
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.

12 Comments

Is there a way to print “Grouping Headers” in a report using controls? If I know the ctrl.name 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

Microsoft Access

3 Comments

First
Prev
1
2
3
4
5
6
7
Next
Last

Microsoft Access

219K

Solutions

285

Content

51K

Contributors

Microsoft Access

Related Topics

Microsoft Access

Related Topics