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.
I have an Access form where different images are loaded to a control. Is there a way to stretch he image to a larger size that looks the same no matter what size the original image is?
Values to be identified as duplicates will be in the second (B) column (with header). I will need to identify rows with duplicate values in that column, but preserve all values in the other columns of that row.
It would be good to insert the string, "Duplicate" (without quotes) in the column to the far left of the duplicates other than the 1st instance. For the 1st instance, mark as "First Instance", would be desirable. Then I can sort on that column. Non-duplicates should not be marked at all.
Spreadsheet has 75,000 rows.
Using an Inline Function would probably be preferred if possible, rather than a nested VBA loop type macro. But please do what you think best. I'm unable to find a reasonable solution searching the web. -- BTW: CONDITIONAL FORMATTING hangs the program with too many ROWS.
Sample Content: (sorry, but the "Indent" tags are not working). The point is that the values in other columns of those rows do not affect the logic.
Column A is Blank
I have a table named tblPotentialAppointmentDatesAndTimes. In that table are three fields... AppointmentID (the primary key) and Date and Time. When I click a button on a form I want the table to fill with records based on the number of doctors in another table named tblDoctors. The field have to populate with a date/time every 15 minutes starting at 08:00 am and going through 05:00 pm
How to modify the following query to add a total column of TaskSelected?
TRANSFORM COUNT(T.TaskSelected)
SELECT R.TAID, T.TaskSelected
FROM RBWS R
LEFT JOIN TaskSelected T ON R.TaskSelectedID=T.ID
GROUP BY R.TAID
PIVOT T.TaskSelected
I have a field in my table bm_ID that I need to query and filter. I'm unsure how to pull it out but if the string has 2019 as such '3E: 100820191245 | B' or '3E: 3Q19' or '3E: 20190927093335' or '3E:093020190116' then I need to pull it out of the table for my report but again, I'm not quite sure how I can accomplish this...Any suggestions and examples would be greatly appreciated in accomplishing this...Thanks...
Hi
(1) I have a simple select query below, now what I want is to have the cumulative balances by employee under cumtotal
(2) A moving value difference which is achieved by subtracting the cumulative value in the month of Feb by Jan, example see below:
Emp Mth Sal Cum Diff
James Jan 5,000 5,000 0
Feb 5,000 10,000 5,000
Beth Jan 4,500 4,500 0
Feb 4,500 9,000 4,500
Annie Jan 3,500 3,500 0
Feb 3,500 7,000 3,500
Chris Jan 3,650 3,650 0
Feb 3,650 7,300 7,300
I do not want to use the domain function, I have enough of poor performance, I understand the answer to this are sub queries, this is where I’m trapped, I real need your help. Our pay as you earn works on cumulative basis, that is the reasons why I need this.
I will appreciate if can be done, unfortunately access does not work like excel, otherwise this is very thing in excel.
See actual query:
SELECT tblstaff.Id, tblstaff.FirstName, tblstaff.LastName, tblstaff.JobTitle, tblpayslips.SalaryDate, tblSalaries.Gross, Sum(([tblSalaries].[Gross])) AS CumTotal, 0 AS DiffFROM (tblstaff INNER JOIN tblpayslips ON tblstaff.Id = tblpayslips.EmployeeName) INNER JOIN tblSalaries ON tblstaff.Id = tblSalaries.IdGROUP BY tblstaff.Id, tblstaff.FirstName, tblstaff.LastName, tblstaff.JobTitle, tblpayslips.SalaryDate, tblSalaries.Gross, 0ORDER BY tblstaff.Id, tblpayslips.SalaryDate;
I have an Access DB, running on an office PC.
I'd like have a webpage that displays data from this DB.
I have a shared drive that can be used so others can see this webpage - without directly addressing Access.
This page will be designed as a dashboard, and will have a wide variety of links, tables, charts.
How do I setup a connection & display the data?
Thank you in advance.
Russ
I have two queries, contain_keyword, and doesNotContain_keyword, by using like and not like in criteria.
total record is 149, contain_keyword shows 115, and doesNotContain_keyword shows 5
149 /= 115 + 5
do you know why the query: "contain_keyword" does not show all records?
As you can see from my screen shot, I’m using the following sub routine to link various worksheets from ExcelFile.xlsx into my database. As shown below, I’ve linked only four worksheets. However, my actually worksheet has many more tabs that I need to link. Also, the tab names are not constant. In other words, I could have three worksheets named like: 1111, 2222, 3333. Then at a later time for the exact same file, I could have four worksheets with multiple other names like: AAAA, BBBB, CCCC, DDDD.
Right now, it’s a manual process for me to update this code each time a sheet name changes or is removed. I’d like to modify this code to be more dynamic by accomplishing the following:
1. First delete all database table objects that have a string of 4 characters in their name (e.g. 1111, 2233, AABC)
2. Link all worksheet tabs that have a string of 4 characters in their name from ExelFile.xlsx
I'm using MS office 2016. Any Expert suggestion on this is greatly appreciated! =)
Private Sub LinkSpreadSheets()Dim Fpath As String, _ XLname1 As String, _ tb1 As String, _ tb2 As String, _ tb3 As String, _ tb4 As StringFpath = Environ("USERPROFILE") & "\Documents\databases"XLname1 = "\ExcelFile.xlsx"tb1 = "1001"tb2 = "2001"tb3 = "3001"tb4 = "F008"With DoCmd .DeleteObject acTable, "1001" .DeleteObject acTable, "2001" .DeleteObject acTable, "3001" .DeleteObject acTable, "F008" .TransferSpreadsheet acLink, , tb1,
Hi,
I would like to calculate the process duration and how long does it take excluding Weekend ( Friday and Saturday), I have used below function but it does not work specially if End date is "empty" and the process not completed... what i what to do is calculate the duration ( End date-Start date) excluding weekend(fri-Sat) and if end date is empty, (up-to-date - start date)
any help please
Public Function WorkdayDiff(ByVal d1 As Date, ByVal d2 As Date) As Long
Dim diff As Long, sign As Long
Dim wd1 As Integer, wd2 As Integer
diff = DateDiff("d", d1, d2)
If diff < 0 Then
'* Effectively swap d1 and d2; reverse sign
diff = -diff
sign = -1
wd1 = Weekday(d2)
Else
sign = 1
wd1 = Weekday(d1)
End If
wd2 = (wd1 + diff - 1) Mod 7 + 1
If (wd1 = 1 And diff = 0) Or (wd1 = 7 And diff <= 1) Then
WorkdayDiff = 0 '* Both dates are on same weekend
Exit Function
End If
'* If starting or ending date fall on weekend, shift to closest weekday
'* since the weekends should not contribute to the sum.
'* This shift is critical for the last If condition and arithmetic.
If wd1 = 7 Then
wd1 = 1 '* Shift to Monday
diff = diff - 1
ElseIf wd1 = 6 Then
wd1 = 1 '* Shift to Monday
diff = diff - 1
End If
If wd2 = 1 Then
diff = diff - 1 '* Shift to Friday
ElseIf wd2 = 7 Then
diff = diff - 2 '* Shift to Friday
End If
'* If difference goes beyond weekend boundary then...
…
I would like to possibly run a query (or something) so that the table above becomes:
PRNo Stage Status
1 1 3
1 2 0
1 3 0
1 4 0
and so on...
I have used below code but it is for 2 field only, any help please?
also if possible when run the code every time, i need to update the existing record not to duplicate it, is it possible?
Thanks in advance.
Sub TransposeType()
Dim rs As DAO.Recordset, rs1 As DAO.Recordset
Dim i As Integer, s, fldArr()
Set rs = CurrentDb.OpenRecordset("StagesQ") 'change the name to the actual name of table
Set rs1 = CurrentDb.OpenRecordset("Invoices")
If rs.EOF Or rs.BOF Then
MsgBox "no records"
Exit Sub
End If
rs.MoveFirst
For i = 0 To rs.Fields.Count - 1
ReDim Preserve fldArr(i)
fldArr(i) = rs.Fields(i).Name
Next
Dim j
Do Until rs.EOF
For j = 1 To UBound(fldArr)
With rs1
.AddNew
!PRNo = rs("PRNo")
!Discerption = rs.Fields(fldArr(j))
.Update
Hi all, i have 2 unbound text boxes costA and CostB and 1 bound text box rate in continuous form. now i am writing vba code to get rate value depending on costa and costb values.
if costa and costb values are null then rate = 0
if costa has value then rate = costa or
if costb has value then rate = costb
please note that in continuous form we will be having either costa or costb value. we don't have both values same time. one value will be always null
whatever code i am trying to modify it is either working for costa or costb but not for both.
If (IsNull(CostB) = True And IsNull(CostA) = True) Then
Rate.Value = 0
ElseIf (IsNull(CostB) = False And IsNull(CostA) = True) Then
I use a report with grouping and would like to add a group subtotal on a column which is computed the following way:
=GetTitelGV([TypeOfInvestment];[QTY];[EINSTAND];[xMarkt]) -> name of the column is xTitelGV
at the end of each Subsection I would like to show the subtotal using =Sum([xTitelGV])
However, this does not work. I don't want to use =Sum(GetTitelGV([TypeOfInvestment];[QTY];[EINSTAND];[xMarkt])) as I am using external API calls which are pretty slow.
Thank you so much for any help. Best regards Michael
Need help with reading line by line a large .nc file converted .txt using Microsoft Access DAO Recordset.
I'm providing a sample database as to my attempts to read a linked txt file that I basically renamed from a .nc data file.
If you take a look - there is a module: modEExchange with a sub: PullNonValidTagLineItemsOnly that I'm trying to get working correctly.
To test you open the immediate window and enter: Call PullNonValidTagLineItemsOnly("0001130310-19-000016", 7) per eeexample2.png.
Basically I've identified some tags within this file that don't seem to find the ending </us-gaap and now I'm taking that and trying to go to that exact line no within that text file and put in a separate table until I come across either that ending </us-gaap or - the next 10 line items within that text file at least
I understand doing this can be tricky cause text files have limitations when you are looking at this line by line and the string limitations as to width. I made sure though the data file is long text for the Field1 and have linked the text file with the advanced link specifications in image: eeexample1.png.
Per eexample2.png - the first tag to come up is CommitmentsAndContingencies on line number: 19175 within the text file..based on the sSQL looping through the DAO.REcordset ... once I grab the first tag I loop through the linked table: 0001130310-19-000016 and am now trying to go to that exact line no - to start pulling that tag - into table: …
Hi
In Access if I set Enabled to False on a TextBox it turns it to grey. Is there a way to make it look
better? I want the user to be able to read the text clearly on either a more enhanced text colour or less
enhanced background colour
Thanks
I'm trying to sum values in the report footer. The value I'm trying to sum is txtQty1 where a field named QLBreakoutSortOrder is null. I don't know how to write the control source. Can someone help?
Hi, can someone please tell me why access linked table is not allowing to edit(can't update, delete,insert)?
i have couple of sql tables which are linked in access form. all my tables can be edited except 1 table.
don't know reason why..i can edit table in sql db but not in access db
please note that sql table is having primary key as well
This question involves the list box control. The attached file contains the code. The list box control in my database (EE_1140419.mdb) is lstTimes, which is on the frmMainMenu. An image of the row source for the list box control (lstTimes) is Query1.
The form load event code for the frmMainMenu form is: Me!lstTimes.Selected(0) = True. The problem is that only the primary key field is displayed on the frmMainMenu form when the form loads (see the OnlyPrimaryKey screen shot.). How do I correct the error?
I am using with the Contacts database from the "Microsoft Access 2010 Inside Out" book as an example. I'm not going to upload the .accdb file from the book's CD because I think it is a copyright violation. The list box control (lstCName) that I am working with (using as an example) is on the Company List form (frmCompany List) in the Contacts database.
An image of the row source for the list box control (lstCName) is qlkpCompanies. The form load event code for the frmCompany List form is: Me!lstCName.Selected(0) = True. EE_1140419.mdb
I have the a ComboBox whose RowSource is shown in the query below, So far the "Holder" column is the only one shown in the ComboBox. How do I also show the "Licence" information
Hi Experts,
I have few problems about a List Box in a form. There is a Form1 in my DB that is a search form with a list box. Attached is my DB. my problems that I need help are:
1). The "Clear" button for some reason, it's only clear the Text box but not the List Box which I need to clear the data too when click on it.
2). when I search "tes", it show everything that has "tes", I want to be able to click on each record in the ListBox the open the form that associate with the Class and TagNo (for example: if the record the Class is "Computer" then open "Computer Form with the same TagNo so it opens the correct record with the correct form, if the "Class" is Property then opens "Property Form" with the TagNo associate with the form. I was able to open the form but not with the correct TagNo and correct form.
3). Is possible that when the records show on the ListBox, whatever the IPNo is assigned to a user, the IP address will be shows Red, if the IP address is unassigned then is Black in the ListBox?
4). Whenever Form1 first Opens, the ListBox should be blank. (if there is noting in the Text box, Form1 ListBox should be blank)
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.