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

x

Microsoft Access

220K

Solutions

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.

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

Sign up to Post

Access 2003
I set Breakpoints, but when I run the Subroutine, the program does stop at the Breakpoints
Very simple question:  How does one enable breakpoints
0
Big Business Goals? Which KPIs Will Help You
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Hi

In Access 2010 Professional a user using my Access solution gets the following error while trying to open a report with a query behind it.
Someone else in his office also running Access 2010 does not get any error and I don't get any error.

The expression in one of the queries that I believe is causing this is as follows

InvNum: Nz(DLookUp("[Invoice No]","Invoices","[ID] = " & Nz([Invoice ID],0)),"0")

Open in new window


Can I change my expression to something else that doesn't use Nz?

1
0
in excel how to represent below date and time stamp as it is like in database cell

2018-11-13 06:13:15.891


when i copy past it shows as 13 or something

how to import sql server results value including heading to excel without loosing date formatting etc
any tips or tutorial videoes around this?

Please advise
0
I have a client who has an application which involves a combination of VBScript and Access to download data from an FTP site and then process that data into multiple Access databases. I'm not happy with the way this was implemented but I'm stuck with it.

I received an email this afternoon which indicates that about 15-20 of the files that should have been downloaded and processed on Monday did not make it into the Access databases.

Indications are that something occurred on there servers which affected their authentication service.  Any chance that this would have affected ADO communications between the VBScript and the Access BE databases?  I'm not a huge fan of VBScript or ADO and don't use either often enough to know what will or will not affect them.

Dale
0
Hi, I'm trying to get an update query to work!  I have 2 tables with the destination table that has a date field (datetime)   (on an MSSQL server) and  the sourse is an Excel file I linked to in Access.

In my Query:

Field: Start_date
Table:  Monthly_rep
Update To: [Excel_rep].[start_date2]

the update To:  I tried to put in CAST , CONVERT  etc keep getting error!  

CAST([Excel_rep].[start_date2] as DATE)
CAST([Excel_rep].[start_date2] as DATETIME)
Cdate([Excel_rep].[start_date2])

ETC.

Thanks

Pete
0
Access vba

Using ian INSERT statement in vba.

What I need: sometimes the  " InProgress_or_Closed_at" value does not get inserted?
i'm i using the  "Now()"  correctly in the sql statement ?

Or is there a better way to write it to make sure a time stamp is posted correctly ?


ttt = Forms!dataentry.txtReOpened
                    strsql_sql = "INSERT INTO [dbo_t_nsc_trackcode_trans_time_entry](NSC_ID_Ref, [opened_at], InProgress_or_Closed_at)" & vbCrLf
                    strsql_sql = strsql_sql & "VALUES (" & Me.NSC_Id & ",'" & ttt & "' , now());"
                     CurrentDb.Execute strsql_sql, dbSeeChanges

Open in new window



Thanks
fordraiders
0
Hello,

My company's MS Access VBA software uses Azure Blob Storage and allows users to upload files from their computer to our blob storage. I have a user that requested for the file dialog box filter to default to what they last selected. Currently the filter always defaults to Image files and usually this user uploads PDFs. Is there anyway to get the filter to default to the last filter the user selected? Please see attached file to view the current code for the file dialog.

Thanks in advance!
File_Dialog_Code.PNG
0
I have two queries that return:

Gains Averaged
Losses Averaged  
And I want the final result to look like this:

Final Result
 

How can this be done?
0
Hi Experts,

I'm middle of converting my Access BE database to MS SQL Server and at the first glance users realized filtering forms are much slower.

A- Is there a magic/trick to have things run faster (or at least how they used to work) upon conversion?
B- Can someone help me convert this Access query to SQL to run it in the most efficient matter?

SELECT Skilled_Nursing_Visit_Note.ID, SNV_Printed_History.VendorsID, SNV_Printed_History.SNV_ID, Skilled_Nursing_Visit_Note.Client_Last_Name, Skilled_Nursing_Visit_Note.Nurse_Signature_Last_Name, Skilled_Nursing_Visit_Note.Visit_Date, Skilled_Nursing_Visit_Note.Shift_From_Hour, Skilled_Nursing_Visit_Note.Shift_To_Hour, Skilled_Nursing_Visit_Note.Date_Signed, Skilled_Nursing_Visit_Note.Nurse_Name_Stamp_SNV, Skilled_Nursing_Visit_Note.Nurse_User_ID_num_SNV, SNV_Printed_History.ReviewedBy, SNV_Printed_History.ReviewedDate, SNV_Printed_History.PrintedDate, SNV_Printed_History.PrintedBy, SNV_Printed_History.PrintedDate, Skilled_Nursing_Visit_Note.Client_First_Name, Skilled_Nursing_Visit_Note.Nurse_Signature_First_Name, Skilled_Nursing_Visit_Note.Date_Of_Birth, Skilled_Nursing_Visit_Note.Shift_From_Minute, Skilled_Nursing_Visit_Note.Shift_To_Minute, SNV_Printed_History.ReviewedDate, Skilled_Nursing_Visit_Note.Treatments_Administered, CInt(IIf([duration]=0,1440,[duration])/60) AS DurationHr, SNV_Printed_History.NoPrint, Skilled_Nursing_Visit_Note.Status, Skilled_Nursing_Visit_Note.Client_Last_Name_Init, 

Open in new window

0
Microsoft Access 2016
I want to define a parameter query to ask for a selection but return all records if left blank.
I am using Like [Enter Location] .... this works fine. but there are times I want to run the report for all locations.
I tried like [Enter Location] or Like * but this did not work.

Any suggestions would be greatly appreciated.
thanks
Wayne
0
Determine the Perfect Price for Your IT Services
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Access:
Need my query to ask for input. I would like for this to pop open a box where I can input a variable for tbl_Tasks.New_Crafts.

TRANSFORM Count(tbl_Tasks.New_Crafts) AS CountOfNew_Crafts
SELECT tbl_Tasks.TASKSDescriptionCorrection, tbl_Tasks.TaskCombo, Count(tbl_Tasks.TaskCombo) AS CountOfTaskCombo
FROM tbl_Tasks
WHERE (((tbl_Tasks.New_Crafts)="e"))
GROUP BY tbl_Tasks.TASKSDescriptionCorrection, tbl_Tasks.TaskCombo
PIVOT tbl_Tasks.New_Crafts;
0
I have an extract of data which I've imported into access which represents represents login/logoff activity of users/pc's. We have some suspicions that officers may be sharing accounts & passwords, which is a breach of policy. I wanted a way in access to query the data , to list all rows of data, ordered by date, whereby on the same day, the same account is logged into more than 1 unique machines. At present the key columns are field1 (the date, in the format dd/mm/yyyy), field 6 (the PC the account is logged onto, which in the format PC12345 for example), and field3 (the users account, in the format of text, username, e.g. jbloggs). If the account is logged into more than one unique PC, on the same day, I would like to see those records for further investigation. What would be the best way to filter the data to list all rows which fit this criteria.
0
I seem to have gotten what I wanted from earlier question, I have created below a table where I want to store two strings:
Tbllinkstring, see attached table

Now I want to use DLookup to be pick the string from the table, how do I use the IF and THEN and ELSE from the code below so that it evaluate and use the correct string, do not worry about the rest, I will take care of the rest, see the shaded area in the attached table:

Public Function SqlLinker()
Dim db As Database
Set db = CurrentDb
Dim tdef As TableDef
Dim constr As Variant
constr = "ODBC;DRIVER=SQL Server; " & _
"SERVER=USER\SQLExpress;DATABASE=Accounting;Trusted_Connection=Yes"
For Each tdef In db.TableDefs
If InStr(tdef.Connect, "ODBC") Then
tdef.Connect = constr
tdef.RefreshLink
End If
Next
MsgBox "Re link completed Successfully", vbOKOnly, "CA Premier Accounting Package"
End Function
For me this will be the best solution because before deploying the application I will be required only to change one of the links above in the table called Tbllinkstring to the correct host name, this means that I will still maintain my existing laptop links which allow me to compile and make an ACCDE copy without an error. Once the application is deployed to a new host name again it will simply drop my existing links after evaluating and use the new one. The advantage is that the users will not be required to select the correct link at all, the system will handle that for them.
On the shaded areas I want to …
0
Trying to convert Access syntax to SQL (Update Query)

UPDATE dbo.tbl_DriveSalesSidhilMatrix
Set tbl_DriveSalesSidhilMatrix.EnteredBy = case when OperatorName is null then Operator else OperatorName
From dbo.view_Z_SalesOrderAmmendmentsSums INNER JOIN
                         dbo.tbl_DriveSalesSidhilMatrix ON dbo.view_Z_SalesOrderAmmendmentsSums.Operator = dbo.tbl_DriveSalesSidhilMatrix.EnteredBy LEFT OUTER JOIN
                         dbo.tbl_Syspro_Operators ON dbo.view_Z_SalesOrderAmmendmentsSums.Operator = dbo.tbl_Syspro_Operators.OperatorCode
WHERE (dbo.tbl_DriveSalesSidhilMatrix.RecordSource = N'Sidhil')

I'm getting "Incorrect syntax near the keyword 'From'."

Can anyone help?
0
Hello Experts!

I have create a macro called AutoExec to run the function below:
Public Function SqlLinker()
Dim db As Database
Set db = CurrentDb
Dim tdef As TableDef
Dim constr As Variant
constr = "ODBC;DRIVER=SQL Server; " & _
"SERVER=USER\SQLExpress;DATABASE=Accounting;Trusted_Connection=Yes"
For Each tdef In db.TableDefs
If InStr(tdef.Connect, "ODBC") Then
tdef.Connect = constr
tdef.RefreshLink
End If
Next
MsgBox "Re link completed Successfully", vbOKOnly, "CA Premier Accounting Package"
End Function

Well the function links the tables in SQl Server at runtime and if the ODBC 17 path is correct a message with Re link completed Successfully pop up. All is well as long as I continue using my laptop the problem comes up if I install the same database on a different server client stand alone network not connect with my laptop because the new path is supposed to be:
constr = "ODBC;DRIVER=SQL Server; " & _
"SERVER=PETER\SQLExpress;DATABASE=Accounting;Trusted_Connection=Yes"

Now how best I can be changing the path below to be reference the correct server name? For example if the same name changes to CHRIS is there a way to alter the path below:

constr = "ODBC;DRIVER=SQL Server; " & _
"SERVER=USER\SQLExpress;DATABASE=Accounting;Trusted_Connection=Yes"

Example where there is USER change to CHRIS,the challenge here is how to change the code manually because we always send an accde format to clients. Some are suggesting to  put this string below in …
0
Previous Day Closing as Opening for the next day. A simple query or report with a cutoffdate to get current balance. Access Database

Below is my table and expected result for 3 day mix transaction:

TableInitial
Tdate       Trxn           Amt
-----------------------------------------
Day1        Initial           100

TblCStock
Tdate       Trxn         Amt
------------------------------------------
1Day        Issue          50
1Day        Issue          10
1Day        Issue           5
1Day        Recieved    40
1Day        Recieved    50
2Day        Issue           10
2Day        Issue           10
2Day        Received    80
3Day        Issue           20
3Day        Issue           20
3Day        Received    10
3Day        Received    10


 
Qry/RptTblCStock
Addl row to be added below each nextday 1st  transaction date.
Legend:

1Day =
Result = Initial - Issue + Receive

2Day = Result - Issue + Receive
and next day

Tdate       Trxn         Amt
1Day        Initial        100
1Day        Issue          50
1Day        Issue          10
1Day        Issue           5
1Day        Recieved    40
1Day        Recieved    50
2Day     Result            125
2Day        Issue           10
2Day        Issue           10
2Day        Received    80
3Day     Result           185
3Day        Issue           20
3Day        Issue           20
3Day        Received    10
3Day        Received    10
4Day     Result            165

Tnx in …
0
Getting an error message: 'Microsoft Outlook - Sorry, something went wrong.  You may want to try again'

This only appears when sending to one specific client from MS Access, all other client emails work fine.

Any ideas how I could fix this?
0
Experts,

I routinely build Access Databases as desktop processes to analyze data. I'm looking to understand the best way to handle the following situation:
Every query I run joins tables on Cost Center. My Cost Centers are 10 Char Alphanumeric. I've already learned from previous questions that I should create a Cost Center master table and use the Autonumber (or other short numeric) field as a CostCenterID for use in joins because its more efficient.
So now let’s say I have a
1.      Cost Center Table with 2000 cost centers and a CostCenterID (Autonumber) field that I’ll use as my primary key, and a
2.      Transaction Table with 1mil records that I want to join on the Cost Center table, but the CostCenterID does not yet exist in the Transaction Table.

How is performance effected if I:
1. Use an Update Query to add the CostCenterID from the Master Cost Center table as a foreign key to any Transaction Tables, so Cost Center and Transaction tables can be joined directly, or
Use Interim Query/Tables
2. Use a Select query to create an interim relationship.
SELECT tbl_Trans.CostCenter (alphanumeric), tbl_CostCenter.CostCenterID (Autonumber)
FROM tbl_Trans INNER JOIN tbl_CostCenter ON tbl_Trans.CostCenter = tbl_CostCenter.CostCenter

Open in new window

3.      Create an interim table with a make table query
SELECT tbl_Trans.CostCenter (alphanumeric), tbl_CostCenter.CostCenterID (Autonumber) INTO tbl_Bridge
FROM tbl_Trans INNER JOIN tbl_CostCenter ON tbl_Trans.CostCenter = tbl_CostCenter.CostCenter

Open in new window


One opinion I was given is that option 1 is best. I just want to make sure as I will be basing many processes going forward off of this method

Thanks!!
0
I am not sure where to place my coding. I have a text box is being used as a less or greater than statement to pull values from two fields to filter data on the next page or form.

I put the following statement in the new page where it suppose to filter the data, written as following and I have criteria set in query that is making the list box display blank.

Private Sub Form_LOad()
        If Len(Form_002_Criteria.TxtExp & vbNullString) > 0 Then
            Me.Filter = "ExpMin <= " & Form_002_Criteria.TxtExp & " AND ExpMax >= " & Form_002_Criteria.TxtExp
            Me.FilterOn = True
        Else
            Me.Filter = ""
            Me.FilterOn = False
        End If
End Sub

Open in new window

0
10 Tips to Protect Your Business from Ransomware
LVL 1
10 Tips to Protect Your Business from Ransomware

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.

Is there a way to change the background colour of the main database window in Access 2016? I'm referring to the overall window with the ribbon on top. In Access 2010, there were three options for its colour scheme: blue, silver, black.

Thanks in advance.
0
I have a continuous form where I have a bound checkbox which I want to use for allowing users to select records and press a button to open a form with the selected records. After the second form is open then I clear the checkboxes. The form works perfectly when one user is using it, but when several users are using it does not work properly- different users' selections are mix and matched. How can I solve this problem?
0
Hi Experts,

I have the following code which loops thru all fields from a text data file (csv), reads the value and constructs a string.
Would like to have the following modification.
For each field containing a date/time value, check if the time is midnight like "2018-08-01 00:00", in that case should change the time to either 24:00 or 00:00 AM

For Each ColumnName In columnsName

                    fieldName = Replace(ColumnName, " ", "")

                    fieldValue = Mid(columns(c), 2, Len(columns(c)) - 2)

                    patient(fieldName) = fieldValue

                    c = c + 1
Next

objHTTP.SetTimeouts 0, 0, -1, -1
objHTTP.Send JsonConverter.ConvertToJson(patient)

Open in new window

0
Is it possible to run some VB.NET code from within MSAccess!   MSaccess is my main frontend and I have
a VB.net module I'd need to run!  This is a service to access a webservice and I suppose I could write equivalent code in VBA
but was hoping to start adding VB.net code gradually to later convert everything to VB.net!  I've never use VB.net
but want to start learning it  piece by piece!

This is a snippet of code to give you an idea!  I ask because there are visual studio .NET libraries available so I assume I can!

 
Dim manualWebClient As New System.Net.WebClient
            manualWebClient.Headers.Add("Content-Type", "application/soap+xml;  charset=utf-8")
            Dim bytArguments As Byte() = System.Text.Encoding.ASCII.GetBytes(
                "<soap12:Envelope xmlns:xsi='http: //www.w3.org/2001/XMLSchema-instance' xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:soap12='http://www.w3.org/2003/05/soap-envelope'>

Just looking for a few good suggestions to point me in the right direction!

Pete
0
I get this error when i try to connect an access database on the network to WinSQL

sqlError.JPG
0
I have this SQL code that is the record source for a datasheet form:

SELECT tblEstimateProofs.Quantity, tblProofTypesLU.Description
FROM tblEstimateProofs LEFT JOIN tblProofTypesLU ON tblEstimateProofs.ProofType = tblProofTypesLU.ProofTypeID
WHERE (((tblEstimateProofs.EstimateID)=[Forms]![frmEstimates]![txtEstimateID]));

Open in new window


If I use the form on a report is ends up looking like this:

Datasheet Example
But on the report I want to display:

3 Baseball + 4 Football + 1 Soccer Ball + 2 Ping Pong Ball + 5 Some Other Kind of Ball

(Note the + marks between each item)

How can I do this?
0

Microsoft Access

220K

Solutions

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.