Microsoft Access

218K

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

I had this question after viewing Import XML File Into Access Table Using VBA???.
Hi everyone,
I need help,
  I have one textbox and I use one function  =Nz(DSum("[Total]","Total_Customer_Qarz","[Customer_ID] = " & [Customer_ID]),0)
so when having records it's showing me correct but when have no record will give to me #ERROR!
0
Concerto Cloud for Software Providers & ISVs
LVL 5
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Hi

Is there a way to round off figures in ms access with the format function , for example

9.333 X 15.1111  =  141.0318963  now if I round off on the format property of a form to 2 decimal places  it gives me 141.03 , my control on the other subform require the users to balance off with this 141.0318963, if it does not balance off then the data on the form cannot be saved.

Because of the decimals remaining the data cannot be saved , then how do I format both controls so that if the users captures 141.03  not 141.0318963 the data can be saved

Regards

Chris
0
My main work machine crashed earlier in the week and I am setting up on another computer in the office.  With help from EE, I was able to create a DSN to re-link the SQL backend tables in my Access 2013 application.

The next step is to create a connection string used by the routines that call stored procedures.

To create the connections string on other computers I’ve set up I open one of the linked tables is design mode.  The I click the ‘property sheet’ button.  From the property sheet screen I copy the ‘Description’ property.  In this case
“ODBC;DSN=JTSDSN32;Description=JTS 32Bit DSN;Trusted_Connection=Yes;APP=Microsoft Office 2013;DATABASE=JTSConversion;TABLE=dbo.vInstallPay_Schedule”

To create the connection screen used by my stored procedure calls I remove the ‘Table’ option.  In this case that gave me
“ODBC;DSN=JTSDSN32;Description=JTS 32Bit DSN;Trusted_Connection=Yes;APP=Microsoft Office 2013;DATABASE=JTSConversion”

This is the connections string I use in my stored procedure calls.  Up until setting up this machine that has always worked perfectly but that is not happening on this machine.

When I run a stored procedure command I am getting the error in the pic

EE Connect Error - SP
This is the code I am using the call the stored procedure
Public Sub buildJerrysReportTempRecordset(Optional passedMuniCode As Variant = Null, _
                                          Optional passedSchoolDistrictNum As Variant = Null, _
                    

Open in new window

0
I created a cross tab query that is working the way I wanted. The only issue is for every new record it creates another line. I am trying to list one part # across with each months qty. When I run the query if there is a qty in each month it will create a new line so there are multiple of the same part #. I attached a photo to help explain better than I can. Any idea how to show one line with the qty for each month?
                     
End goal is to look like this
                            01/17      02/17     03/17
02A0009-03          132             0            5


SQL
TRANSFORM Sum(PK1_TX.TX_QTY) AS SumOfTX_QTY
SELECT PK1_IM.IM_KEY
FROM PK1_IM INNER JOIN PK1_TX ON PK1_IM.IM_KEY = PK1_TX.TX_IMKEY
WHERE (((PK1_IM.IM_KEY) Like "02A*" Or (PK1_IM.IM_KEY) Like "02B*" Or (PK1_IM.IM_KEY) Like "07A*") AND ((PK1_TX.TX_DATE) Between #12/14/2016# And #12/14/2017#) AND ((PK1_TX.TX_TYPE)="I"))
GROUP BY PK1_IM.IM_KEY, PK1_IM.IM_ON_HAND, PK1_TX.TX_DATE, PK1_IM.IM_UNIT_S, PK1_IM.IM_UNIT_I, PK1_IM.IM_UNIT_R, PK1_TX.TX_TYPE, PK1_TX.TX_MOSJOB, PK1_TX.TX_MOSLOT
PIVOT Format([TX_DATE],"mm\/yyyy");
example-of-cross-tab-query.PNG
0
We have search boxes that when triggered with an After Update event run the following code segment to filter the list based on what has been entered. The issue being anytime we search for something that includes a ' it errors out into debug. I believe that's most likely because of how we're filtering the data, and the filter is getting confused because there's an extra ' in the code. How can we achieve the same results as listed below, and fix the issue we can't use symbols in the search parameters?

Private Sub ApplyFilter()
  Me!TaskIDTextBox = ""
  Dim filter As String
  filter = "(1=1)"
  If Nz(Me!uxStatus) <> "" Then filter = filter & " and Status='" & Me!uxStatus & "'"
  If Nz(Me!uxPriority) <> "" Then filter = filter & " and Priority='" & Me!uxPriority & "'"
  If Nz(Me!uxEmployee) <> "" Then filter = filter & " and [Assigned To] = '" & Me!uxEmployee & "'"
  If Nz(Me!uxCompany) <> "" Then filter = filter & " and [Company Name] = '" & Me!uxCompany & "'"
  If Nz(Me!NotesFilterText) <> "" Then filter = filter & " and [Customer Notes] like '*" & Me!NotesFilterText & "*'"
  If Nz(Me!InternalNotesFilterText) <> "" Then filter = filter & " and [Internal Notes] like '*" & Me!InternalNotesFilterText & "*'"
  [Worklog-All].Form.filter = filter
  [Worklog-All].Form.FilterOn = True
End Sub

Open in new window

0
Hi,

We placed an old mdb file on the server, users can click on the file and work on it. One of the people receives "Mot a valid file name"

any ideas? thanks
0
I am trying to see if I can create an input mask for a textbox on my form. Right now I have this control set up as a number datatype. The numbers are 4 decimal places out. I want to see if there's a way I can create an input mask to also add 2 text characters at the end. The two text characters would either be "PC" or "FT".

The user will enter values like this. These are just examples below. The numbers can be any value. I want to do this to keep the syntax the same for all my users. Some like to enter things differently so I want to uniform this control. So if a user only enters .010 PC, I want that to change to .0100/PC. Or if a user enters .01 /FT, I want that to change to .0100/FT. Would the input mask be the most logical place to set this up?

.0100/PC
.0100/FT
.0250/FT
.0250/PC
0
Hi there.

I am trying to convert some pivot queries from MS Access to MS SQL Server. I'm almost there, but not completely as it has to be.

This is the SQL code from MS Access:

TRANSFORM Min(q3TreatmentRelevant.TreatStart) AS MinOfTreatStart
SELECT q3TreatmentRelevant.CoursePatID, Min(q3TreatmentRelevant.TreatStart) AS FirstTreatStart, Count(q3TreatmentRelevant.SiteID) AS NumberOfTreatments
FROM q3TreatmentRelevant
GROUP BY q3TreatmentRelevant.CoursePatID
PIVOT q3TreatmentRelevant.TreatmentDrugHighLevel;

Open in new window


And here's what I got to with SQL Server:

SELECT * FROM
(SELECT 
	dbo.view_3TreatmentRelevant.CoursePatID, 
	MIN(dbo.view_3TreatmentRelevant.TreatStart) AS FirstTreatStart, 
	dbo.view_3TreatmentRelevant.PatSiteID,
	dbo.view_3TreatmentRelevant.TreatmentDrugHighLevel 
FROM 
	dbo.view_3TreatmentRelevant
GROUP BY
	dbo.view_3TreatmentRelevant.CoursePatID, dbo.view_3TreatmentRelevant.PatSiteID, dbo.view_3TreatmentRelevant.TreatmentDrugHighLevel 
) AS BaseData
PIVOT (
	MIN(BaseData.FirstTreatStart) FOR TreatmentDrugHighLevel IN ([Check-point-inhib]
,[Cytokine]
,[Cytostatic drug]
,[Targeted])
	) AS PivotTable
ORDER BY CoursePatID

Open in new window


The result looks like this (sorry for lack of allignment):

CoursePatId	NumberOfTreatments	Check-point-inhib	Cytokine		Cytostatic drug		Targeted
a		1			2010-10-27 00:00:00.000	NULL			NULL			NULL
a		2			NULL			NULL			2010-03-15 00:00:00.000	NULL
b		1			2010-06-23 00:00:00.000	NULL			NULL			NULL
c		1			2010-07-01 00:00:00.000	2008-12-01 00:00:00.000	NULL			NULL

Open in new window


1)  I want to group by CoursePatId only and get only 1 line for each, but it split up for several ones like the "a".
2)  I don't like using static values inside the "PIVOT  FOR  IN" clause. I would prefer dynamic values from a SELECT statement, but that doesn't work. How can I do this?
3)  All of it gotta be a view. Is that possible considering 2), or will I have to make a stored procedure?

Best regards

Ulrich
0
I've got an Access database that I've placed onto a network drive.  For years, several of us have used the d-base in its current configuration without any problems.  Just recently, however, I modified an existing form  (added a few sub-forms, tabs, etc), and now the header combo box (used to find records) does not work on anyone's machine...except mine.  Clicking the combo box (on another machine) produces an error that reads "Object or class does not support the set of events."  After acknowledging this error message, another pop up window appears (attached).  Any ideas on what's wrong?  Thanks, Ken
AccessError.jpg
0
Hi,

Attach is a sample DB. frmFirst is the first to open and then click Edit Record where Source field click that go to Search Source and then click on Add Source there the error is.

In Add Record it is working but in Edit Record is the one where ahead it gives error, any help how to resolve this?

Thank you.
Wsm93--1-.accdb
0
Free Tool: Path Explorer
LVL 11
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Hi

I have the following table :

ID  WEIGHT    PRICE
1       200          6.50
2       300          8.50
3     1000        10.00
4     5000        20.00

I'm trying to query this table in order to select the right delivery price according to max weights
i.e. for a 350grs parcel the shipping cost will be 10.00

I'm not sure how to formulate my query to obtain the correct value

Any ideas ?

thanks
yann
0
Short version:
How do I find the System DSN I created when trying to link to a SQL backend from the Linked Table Manager in Access?

Long Version:
My main work computer crashed yesterday and I'm trying to get things going on another computer in the office.  One of my Access 2013 apps uses a SQL Server back end.

SQL Server is loaded onto the machine and I restored the backup of the SQL DB from the crashed machine onto on the new machine.

I also restored all of my Access projects, most of which use an Access FE and BE.

I had someone else set up the SQL Access link on the old machine but I'm tyring to work my way thru it, with out any success.

Here's what I've done:
I created a DSN within the ODBC Date Source Administrator.  It  is a System DSN named 'JTSDSN',  

On the crashed machine the table were all linked to the SQL back end.  On my new machine I open the front end accdb, then the linked table manager.  I click the 'always prompt for new location' box, select all of the tables and click OK.

At that point I'm presented with a 'Select Data Source' box.  This selection box has two tabs 'File Data Source' Machine Data Source', not 'System Data Source.  The default location is the 'MyDocument' folder but my DSN is not shown there.  What directory do I have to navigate to, to find my DSN.  When I created the DSN I was given the opportunity to select the location where it was created, so it went wherever the default location is.

Any help or suggestions in…
0
I'm using the following code that produces an error message below.

Error.JPG
Option Compare Database
Option Explicit

Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) _
   As Long
Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) _
   As Long
Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, _
   ByVal dwBytes As Long) As Long
Declare Function CloseClipboard Lib "User32" () As Long
Declare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) _
   As Long
Declare Function EmptyClipboard Lib "User32" () As Long
Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, _
   ByVal lpString2 As Any) As Long
Declare Function SetClipboardData Lib "User32" (ByVal wFormat _
   As Long, ByVal hMem As Long) As Long

Public Const GHND = &H42
Public Const CF_TEXT = 1
Public Const MAXSIZE = 4096
'The following procedure illusrtrates how to send information to the Clipboard.
Function ClipBoard_SetData(MyString As String)
   Dim hGlobalMemory As Long, lpGlobalMemory As Long
   Dim hClipMemory As Long, X As Long

   ' Allocate moveable global memory.
   '-------------------------------------------
   hGlobalMemory = GlobalAlloc(GHND, Len(MyString) + 1)

   ' Lock the block to get a far pointer
   ' to this memory.
   lpGlobalMemory = GlobalLock(hGlobalMemory)

   ' Copy the string to this global memory.
   lpGlobalMemory = lstrcpy(lpGlobalMemory, MyString)

   ' Unlock the memory.
   If 

Open in new window

0
I have a query that returns 4 columns but I only want it to return certain key words from one of them (image attached) Is it possible to return a query with only keywords from the third column ie: Compression , Tubigrip
0
Twice a year, I am required to drive around our subdivision and inspect for working yard lights.  If the light is out, we send a preprinted post card notification.  If the light remains out after 30-days, we start a monthly fine violation which requires mailing, tracking, and follow up.

Keeping a updated lot owner address list is always a challenge.  Several subdivision addresses are rentals with different owners address.  Correspondence requires sending to the owner address with reference to the subdivision address violation.  Follow up inspections on the non-working yard lights and maintaining those records is work.  The whole process of printing address labels, keeping track of everything requires a lot of effort.  The subdivision has 400 homes and we typically have 50 to 70 non-working yard light violations to contend with.

I would like to find an off the shelf solution that would manage inspection record keeping and mailings.  Seems like it might be a Microsoft Access application however that would have to be developed.  Looking for suggestions.
0
I have a split database. I want to allow multiple users to read and write data to tables in the back end through a cloud-based option. Can I do this with Amazon? If not, is there another site that would work for this project?
0
Is there a way to call a function from a query criteria , for example let assume we have this function below:

Function pBuildSQLWhereFormat(pvarFldValue As Variant) As String
    If Not IsDate(pvarFldValue) Then
        ' For example, some default value.
        pvarFldValue = Date
    EndIf
    pBuildSQLWhereFormat = "'" & Format(DateValue(pvarFldValue), "yyyy\/mm\/dd") & "'"
End Function

Below is a parameter query form

Between [Forms]![frmsalesorder].[txtstartdate] And  [Forms]![frmsalesorder].[txtstartEnddate]

What if I call the function above like below:

  pBuildSQLWhereFormat(Between [Forms]![frmsalesorder].[txtstartdate]) And pBuildSQLWhereFormat( [Forms]![frmsalesorder].[txtstartEnddate])

Am right or wrong????????????


Regards

Chris
0
Now in the commonwealth we use the British date standard ,but now today after querying the TB I have discovered it is out of balance the report is not picking some transactions. After perusing through I have realized that we are posting our transactions in the UK date format DD/MM/YY instead of MM/DD/YY , how do I resolve this I do not want to use American format it is not known here in the commonwealth?????????


Regards

Chris
0
Hi I'm trying to summarize the aging of debtors , now below are important fields which i'm using:

- CDate  ( Represent the invoice post date)
- Date () ( Represent the cut off date)
- Debit ( Invoice value or receipts reversal)
- Credit (Receipts & sales returns)

Below is the code I'm using in the Expression builder of the report, by the way I have controls on the page footer as follow:

- 30 Days  60 days  90 days 120 days

Code:    IIF(DateDiff( Cdate,Date()) Between 1 and 30,Sum(([Debit]-[Credit])),Null)

but it is giving me an error ,where I'm going wrong ? this is a sure way of ageing a customer statement without disturbing the detailed report.

Regards

Chris
0
NEW Veeam Agent for Microsoft Windows
LVL 1
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

I have an application in Access 2010 that I deploy with SageKey which includes the Access 2010 Runtime.  I have hundreds of users  and have successfully deployed it many times on Windows 10.

In the last few days, I have had 2 users report that they can't run the application due this error.

Can anyone help?

Thanks in advance.

Clive
0
Hi Experts,

I have a field on a form that is Text 7 characters.  The value that must be entered has a pattern of ###.#:1.  e.g. 098.3:1.

The right 2 characters will always be ':1' as this is a ratio.

How do I specify the Pattern Matching (on the form and also using VBA in an Event) to ensure that the left 5 characters are always entered and in the specified format of 3 digits a decimal point and one digit with the right two characters always ':1'.

Thanks,
Bob C.
0
Hello,

Thanks in advance for any help someone may provide.  I am creating a front end ms access application with a back end sql db.  I would like to create a link with a DNS-less connection and need some help.

I have an article I found from Microsoft... here is the link: https://support.microsoft.com/en-us/help/892490/how-to-create-a-dsn-less-connection-to-sql-server-for-linked-tables-in

So far I created the module and now I am using a form as described... invoking the module on the open form... but since this is the first time I am working with modules, not sure how to proceed.  It seems to me that I have provide some parameters and not sure how to do this.

Can anyone help please.  Thanks again.

Joy Gomez
0
I have an Access table that I am trying to append records.
It has the following structure
Access tableI have read in the values for the variables Action, Person, myDate, Comment, Outcome and Acttno from an Excel table with values of
"Phone Call", "Huw", #14/12/2017#, "blank", "blank", "Ac TEGY-01 006"
but when I try to update the records it fails on the  execute line with message "no value given for one or more parameters"

 
strQuery = "INSERT INTO Actions ([Action], [Person],[Action Date],[Comment],[Outcome],[Act No]) " & _
            "VALUES (Action, Person, mydate, Comment, Outcome,Acctno);"
               

        cn.Execute strQuery

Open in new window


What am I doing wrong?
0
I have an access report with two columns, with a group header and the body. The group header has 4 subreports. I want the subreports to print in one section, not to be split one part in one column and one part in next column. Is there any way to keep each subreport in one column?
column.png
0
Hi

Is there a way to match payments to actual bills in the age analysis, currently I’m using this formula on my query:
Due 30: IIF([DaysOverdue] Between 1 And 30,[AmountDue],Null)
It works well, but I want to be matching to specific invoices, you know some clients pay part payments.

Regards

Chris
0

Microsoft Access

218K

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.