Microsoft Access Help

Question by

Biggles1

37m ago

Enabling/Disabling Breakpoints

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

6 Comments

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?

Microsoft Access

1 Comment

LVL 7

Question by

gudii9

16h ago

excel date time

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

2 Comments

LVL 52

Question by

Dale Fye

18h ago

can authentication service affect ADO communications?

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

3 Comments

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

4 Comments

LVL 3

Question by

Fordraiders

23h ago

Using a time stamp correctly in sql

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

12 Comments

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

8 Comments

I have two queries that return:

And I want the final result to look like this:

How can this be done?

Microsoft Access

4 Comments

LVL 6

Question by

bfuchs

1d ago

Help needed converting Access DB to MS SQL Server

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

…

23 Comments

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

Microsoft Access

3 Comments

Question by

ssblue

1d agoSolvedPrivate

Variable for crosstab query

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;

Microsoft Access

15 Comments

LVL 4

Question by

pma111

2d ago

multi access query based on date, user, device

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.

Microsoft Access

1 Comment

LVL 3

Question by

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc

2d agoSolved

How use DLookup within VBA

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 …

9 Comments

Question by

HKFuey

2d agoSolved

Access to SQL Syntax (Update query)

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?

2 Comments

LVL 3

Question by

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc

2d agoSolved

Linking Ms Access to Sql Server

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 …

5 Comments

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 …

5 Comments

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?

1 Comment

LVL 1

Question by

Davisro

2d agoSolved

Add Foreign Key to Table Directly or use Interim Table?

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!!

5 Comments

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

Microsoft Access

11 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.

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.

Microsoft Access

3 Comments

Question by

Sengul Topuz

3d ago

Continuous form checkbox issue

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?

Microsoft Access

9 Comments

LVL 6

Question by

bfuchs

3d ago

change midnight time format in code.

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

25 Comments

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

11 Comments

Question by

Aiysha

3d ago

Connecting to MS ACCESS from WinSQL

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

7 Comments

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:

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?

Microsoft Access

5 Comments

Microsoft Access

Related Topics

Microsoft Access

Related Topics