Microsoft Access Help

i have a query
SELECT AssignedOwnerQ.[Assigned Owner Group], CInt(Nz([CountOfWorkOrder],0)) AS Denominator, CInt(Nz([Count LEW PMCOMP],0)) AS Numerator, IIf([Denominator]=0,0,[Numerator]/[Denominator]) AS Pcnt
FROM (AssignedOwnerQ LEFT JOIN [Copy Of CountPmCompDenominatorQ] ON AssignedOwnerQ.[Assigned Owner Group] = [Copy Of CountPmCompDenominatorQ].[Assigned Owner Group]) LEFT JOIN [Copy Of CountPmCompNumerator] ON AssignedOwnerQ.[Assigned Owner Group] = [Copy Of CountPmCompNumerator].[Assigned Owner Group];
which gives me the results i need but when i divide 0/0 i want the results to display 100%
is there a way to do that.

Microsoft Access

2 Comments

I have a new client that their IT company has the FE running on a VM Windows 7 OS. He also has 35 users using the same single copy of the FE. I am going to put a FE copy on each users separate folder or possibly on their own H drive.

The BE is a SQL server 2000. I will be changing it to a new version once I get them stabilized.

My question is Does Access perform better on a Win 7 os or Win 2012 R2 or Win 2016?

Microsoft Access

9 Comments

I was in the process of installing office 16 on my windows 10 laptop. I had already installed access 16 and my app wich uses the access 16 runtime. I got this message:

Some of you will know that I have had serious issues with access version 2002 and windows 10. Could it be that the issue really is with runtime and access or office 16

18 Comments

html table is not keeping borders around null value cells in table

' 2/27/2019 update
strhtml = "<HTML><Body><table border= '1' width='50%'><tr><th>Sku</th><th>PRODUCT_DESCRIPTION</th><th>QTY</th>" & _
"<th>TARGET_PRICE</th><th>TARGET_PRICE_PROFITABILITY</th><th>CURRENT_PRICE</th><th>HIGH_IMPACT_SKUS</th></tr>"

if "HIGH_IMPACT_SKUS" IS A null value then the borders are disappearing in the html table ?

Thanks
fordraiders

3 Comments

Macro or vba code to do a special menu options

Is there a way to have access do the following:
select <Paste>
Select <paste special>
Select <text>
copy data
from the clipboard ?

Thanks
fordraiders

9 Comments

LVL 6

Question by

bfuchs

2019-03-04Solved

Converting text data into Int field type.

Hi Experts,

We have an Access file that needs to be imported into SQL table, there is a text field named SNV_ID which contains letters and numbers, we would like to store the numbers in an int field in SQL.

Currently trying the following and getting #Num!

CInt(Replace([V_Visit_Note_Export].[SNV_ID],"snv",""))

Open in new window

Data is something like "SNV37067"

Thanks

7 Comments

Access: I have a table with 1.7 million records of tasking and each line of tasingk has a New_Interval (number) and a Tag_Number associated with it. I need to delete some records.
I need identify the records to delete by using the Tag_Numbers that have a New_Interval < 1001
Then I need to also delete all records associated with those same Tag_Numbers.

Some Tag_Numbers have tasking with a New_Interval <1001 and tasking with a New_Interval >1001

Microsoft Access

5 Comments

I have a FORM that opens with the ALLOWEDITS property set to false. I've given the user a button to change to "EDIT MODE", the button simply changes
me.allowedits = true.

It works fine, except there is a SUBFORM on the main form, and it does not allow any records to be added after going into "EDIT MODE"

Private Sub Command502_Click()
 Dim colorDetail As Long
 colorDetail = RGB(255, 230, 153)
Me.Detail.BackColor = colorDetail
Me.AllowEdits = True
Me.First.SetFocus
Me.Command502.Visible = False

Me.frmContactRelationshipsSUB.AllowEdits    '<<   This of course didn't work, or anything else I've tried

Open in new window

Thanks for any help.

13 Comments

moving focus to main form from subform after running a function in the AfterUpdate event in the subform.

Still getting the pencil icon showing its in edit mode and wont move focus to the main form ?

SendKeys "{ESC}" ' using this to save the edits i just did in subform. then moving focus to the main form control,

[Forms]![main]![CALLER_RACFID].SetFocus

Thanks
fordraiders

10 Comments

I am getting a Run-Time Error '3274' every time I open my database. The error is coming from this line of code.
'External table not in expected format"

If I delete the PROBLEM_LOG.xls the error does not occur. Also, certain desktops do not get these error. For years, I never had this error.
I assume that the Xcel file is being saved in a newer format or something like that.

Is there a change that I can make that will accept all types of files?

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ProblemLog", CurrentProject.Path & "\" & "PROBLEM_LOG.xls", True

Open in new window

This step is to delete and replace a newer copy of Problem_Log.xls

Public Function CreateProblemLog()
DoCmd.SetWarnings False
DoCmd.OpenQuery "qMakeProblemLogTable"
DoCmd.OpenQuery "qCreateOeReportDueDate"
DoCmd.OpenQuery "qUpdateProblemLogMonthDayYear"
CurrentDb().TableDefs("ProblemLog").Fields("ScrapRecordTag").Name = "DIAMTracking"
CurrentDb().TableDefs("ProblemLog").Fields("SkpiProblemLog_Date").Name = "Occurance Date at Customer"
CurrentDb().TableDefs("ProblemLog").Fields("MyMonth").Name = "Month"
CurrentDb().TableDefs("ProblemLog").Fields("MyDay").Name = "Day"
CurrentDb().TableDefs("ProblemLog").Fields("MyYear").Name = "Year"
CurrentDb().TableDefs("ProblemLog").Fields("TagNumber").Name = "Customer Claim #"
CurrentDb().TableDefs("ProblemLog").Fields("QPRQPINumber").Name = "Other Tracking #'s (RMA#, SLIPS#, e-CIMS, etc)"

Open in new window

…

4 Comments

Hi

Kindly see how you can assist in another cumulative column under commission, I have done it for one column called sales is now showing cumulative with two criteria. How do I add another column called commission as cumulative as well?

SELECT T1.IDLine, T1.Monthend, T1.ProductCode, T1.Qty, T1.Commision, (SELECT Sum(tblLinedetails.QTY) As Total
FROM tblLinedetails
WHERE  (tblLinedetails.Monthend <= T1.Monthend) AND tblLinedetails.ProductCode = T1.ProductCode) AS Total
FROM tblLinedetails AS T1;

Open in new window

Well Dale Fye advised clearly the problem of slowness with ELookups & Dsum , I now totally agree with him that the way forward is to settle on Subqueries.
Regards

Chris

4 Comments

I have an access database that is .accdb. I need to make a copy and convert it back to .mdb. When I do a Save database as, the choice for .mdb is not an option.

How do I convert it to .mdb. Both Access 2003 and 2010 are installed on the server.

Microsoft Access

4 Comments

I currently use a .bat file to distribute the Access frontend to each users folder on the server. Every time a new user is hired or leaves, I have to manually add or remove the folder and update the bat file.

I was wondering if anyone has a better method? Such as a GPO

16 Comments

Embedding a .bmp image in a Report

Unfortunately I am using a .mdb file NOT a .accdb file so I have given up on embedding different fonts in a report.

However I still want to embed a .bmp or .jpg..

The report has an underlying table from which the Report text is originating.
I added a OLE Object Field and called it [Signature]
My report now has a field Called [Signature] originating from that table

I captured a signature using the Snipping Tool and stored it as C:\......\Signature.bmp
Question: How do I get the Signature Image to appear in my report?
In the control source of the Bound Object Frame in the report I typed C:\......\Signature.bmp but the report can't find it!

What am I doing wrong? (Remember this is a .mdb file)

21 Comments

There are some questions I have regarding creating a summary query in a Microsoft Access database:

Question 1: Please note sample below provided:

With the query: qQuestion1a - I put in it to show me all time that is not considered "ST" - which is standard time. My criteria is <> "ST"
However I'm missing data as qQuestion2a shows there are some where the TimeType hasn't been filled out - how come those don't show in qQuestion1a?
Seems like a bug on Microsoft Access' part....

Question 2:

Basically I'm building a query that will give me all non-billable time for each employee. Some employees have non-billable time - others do not. My attempt with the qSummaryOfNonBillableperDisney only shows the employees that have non-billable time. However I want a query that lists each and every employee regardless if they have non-billable time or not.

How do I make such a query that would do this:

UserName NBT
Donald Duck        29
Mickey Mouse      27
Minnie Mouse      13
"George Geef ""Goofy"""
Daisy Duck

instead of this:

UserName      SumOfWorkHours
Donald Duck        29
Mickey Mouse      27
Minnie Mouse      13

Just to explain further - how I would attempt to do this...is creating a query that made a table of all Disney employess with the name and another field that has the total NBT hours. Then I would run an update query that would load in the values. Is this the correct method of doing this? Do you have to take these series of steps to create …

7 Comments

Below is an excerpt of the XML code used to create the Ribbon in Access.

The bit I can't get to grips with is where are the images held for each button? It is defined here: imageMso=""FileCheckIn"
So is the image "FileCheckIn" held within Access? In which case, how do I find the list of images that I can use? Or do I have to import them in?

The code is:

ID	RibbonName	RibbonXML
1	Dashboard	"<customUI xmlns=""http://schemas.microsoft.com/office/2006/01/customui"">
  <ribbon startFromScratch=""true"">
    <tabs>
      <tab id=""tabDashboard"" label=""Dashboard"" visible=""true"">
        <group id=""grpJobSheet"" label=""Job Sheet"">
          <button id=""btnArtwork"" size=""large"" label=""Artwork"" imageMso=""PhotoAlbumInsert"" onAction=""mcrgBillableTaskRecent.OpenBillableTaskRecentAP""/>
          <button id=""btnWeb"" size=""large"" label=""Web"" imageMso=""ViewWebLayoutView"" onAction=""mcrgBillableTaskRecent.OpenBillableTaskRecentW""/>
          <button id=""btnIn_houseProduction"" size=""large"" label=""In-house Production"" imageMso=""FileCheckIn"" onAction=""mcrgOrderProduction.OpenOrderProductionNoSupplier""/>

Open in new window

9 Comments

Help me to understand why I ‘m getting

“undefined function Dlookup in expression error”, it’s very strange in Ms Access. Where I’m going wrong here?
Net: DLookUp(" [Qty] ","tblLinedetails"," [IDLine] =" & [IDLine]-1)
Regards
Chris

Microsoft Access

7 Comments

Hi,

Please let me know why is below DoCmd export from MS Access makes all the dataset central alignment on excel spreadsheet?

DoCmd.TransferSpreadsheet acExport, 10, "QryABCD", "C:\aa\Rprts\abcd.xlsx", True, "tabname_abcd"

Open in new window

How can I change the alignment of dataset into a generic one (if character datatype then left aligned,number and date datatype needs to be right aligned) in the code below.
By the way, heading needs to be center aligned.

Public Sub All()
Dim ObjExcel
   
        
    Set ObjExcel = CreateObject("Excel.Application")
    ObjExcel.Visible = False
    ObjExcel.Workbooks.Open "C:\aa\Rprts\abcd.xlsx"
    
    Set ObjSheet = ObjExcel.ActiveWorkbook.Worksheets("tabname_abcd")
    ObjSheet.Activate


         ObjSheet.Range("1:1").HorizontalAlignment = xlCenter
         ObjSheet.Range("1:1").VerticalAlignment = xlCenter
         
         ObjSheet.Range("C2").Select
         ObjExcel.ActiveWindow.FreezePanes = True
         ObjExcel.ActiveWindow.DisplayGridlines = False
         'Link: https://social.msdn.microsoft.com/Forums/en-US/09336fa1-736a-4569-ae15-d6726be06838/excel-freeze-panes-from-access-vba-off2000?forum=isvvba
         ObjExcel.Columns.AutoFit
ObjExcel.ActiveWorkbook.Save
    ObjExcel.ActiveWorkbook.Close
    ObjExcel.Quit
    
    Set ObjExcel = Nothing
    Set ObjSheet = Nothing

    
End Sub

Open in new window

Thank you
A

13 Comments

Access o365, VBA, copying a ".accdb" file to same location with a new file name.
The routine works unless someone is using that particular file.
Is it possible to get around that?

If the file is in use I get a runtime error 70 "permission denied".
If the file is not in use, it copies fine.

16 Comments

Can someone spot the syntax issue I have in the following? I'm getting a "missing operator" error.

strSQL = strSQL & "WHERE tblEstimate.EstimateID = " & lngEstimateID & " AND (tblTypesLU.Description) = '*ABC*'"

Microsoft Access

18 Comments

Hi Experts
I am working on a 20+year old legacy system written in vb6 and uses Access 95 database. I am going to rewrite this application using Visual Studio(latest) and latest Access Database.
I am trying to find a way to upgrade my databases. we have an mdb file for each job. so conversion has to be done pragmatically. I was thinking of writing up a utility that exports mdb table to .csv file. and Read that .csv file into the latest Access database.. Am i on the right path? if so, how would i go about it? I am thinking o sticking to vb.net to reduce the learning curve. I will highly appreciate any advice/suggestion.
Thank you

40 Comments

I have the following code:

DoCmd.TransferText transferType:=acImportDelim, SpecificationName:="ImportWithholdingNoticeData", TableName:="RawData", FileName:=filepath, hasfieldnames:=True

when I run into I get an error that the text file specification does not exists even though it does. Any ideas what the issue is?

Microsoft Access

4 Comments

I have a value for a field that is -1021.00.

The field datatype is number/decimal

I am getting an error when trying to insert into that field (via an import spec: Type Conversion Failure

Microsoft Access

6 Comments

I have an Access database (Office 365 version) that has line breaks in one field I need to eliminate before exporting to for excel. I've tried multiple times and various ways to eliminate them but can't seem to make it happen. Can you help?

7 Comments

I have a sub-form that is a continuous form. It has 7 text boxes on it. No matter what I try the fields will not grow even though I have them set to Can Grow = Yes. The fields are not positioned next to another field. The are positioned right below each other in a vertical column. Why will they not grow. I've run into this before. So frustrating.

Microsoft Access

2 Comments

Microsoft Access

Related Topics

Microsoft Access

Related Topics