Microsoft Access

221K

Solutions

52K

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

Hi

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?

Thanks
0
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

Column-B-Title   Column-C-Title   Other-Columns-w-Title
5555
Some-Value
5555
Some-Other
5555
Not Important
3333
Some
3333
Thing
XXXXXX
xyz
XXXXXX
abc
Anything
pqr
Else
stu

(Note: Sorry about all the edits. Couldn't find a "Preview" button prior to publishing. Not familiar with this new interface.)
0
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

In other words like this:

 Date Time Example
0
trying to update a record in sql server
getting error message when a field is blank  ( = null)


These are unbound  textboxes on a form.

R.Edit
' 13 FIELDS SKU INFORMATION
      R![SKU] = Nz(Me("SKU_FRM"), "") ' SKU
      R![QTY] = Nz(Me("QTY_FRM"), "") ' QTY
      R![TARGET_PRICE] = Nz(Me("TARGET_PRICE_frm"), "") ' TARGET_PRICE
      R![TARGET_GP] = Nz(Me("TARGET_GP_FRM"), "") ' TARGET_GP
      R![CURRENT_PRICE] = Nz(Me("CURRENT_PRICE_FRM"), "") ' CURRENT PRICE
      R![CURRENT_GP] = Nz(Me("CURRENT_GP_FRM"), "") 'CURRENT_GP
      R![VENDOR_GUIDELINE_GP] = Nz(Me("VENDOR_GUIDELINE_GP_FRM"), "") ' FLOOR_GP
      R![PRODUCT_DESCRIPTION] = Nz(Me("PRODUCT_DESCRIPTION_FRM"), "") 'PRODUCT_DESCRIPTION
      R![TARIFF_ITEM] = Nz(Me("TARIFF_ITEM_FRM"), "") '  TARIFF ITEM
      R![RENEWAL_SKU] = Nz(Me("RENEWAL_SKU_FRM"), "")  '  RENEWAL SKU
      R![RENEWAL_OLVP_QUOTENUMBER] = Nz(Me("RENEWAL_OLVP_QUOTENUMBER_FRM"), "") '  RENEWAL OLVP ITEM
      R![APPROVED_PRICE] = Nz(Me("APPROVED_PRICE_FRM"), "") ' APPROVED PRICE                                                                             error
      R![APPROVED_GP] = Nz(Me("APPROVED_GP_FRM"), "") ' APROVED GP                                                                                               error

R.Update

Open in new window


i thought  NZ would take care of a blank value ?


These 2 fields may not have data in the textbox,
but i still need the code to finish the .update

Thanks
fordraiders
0
Hi

I have a sub form that needs to filter data based on a label in my main form. The label contains a key value
How and where would I do this?

Thanks
0
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
0
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...
0
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 Diff
FROM (tblstaff INNER JOIN tblpayslips ON tblstaff.Id = tblpayslips.EmployeeName) INNER JOIN tblSalaries ON tblstaff.Id = tblSalaries.Id
GROUP BY tblstaff.Id, tblstaff.FirstName, tblstaff.LastName, tblstaff.JobTitle, tblpayslips.SalaryDate, tblSalaries.Gross, 0
ORDER BY tblstaff.Id, tblpayslips.SalaryDate;

Open in new window



payrollcum.png
Regards

Chris
FinTest.accdb
0
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
0
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?

Database1.accdb
0
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 String

Fpath = 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,

Open in new window

0
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...
0
hi,
I would like to know if this is possible.
I have a data table arranged this way:

PRNo   Stage1 stage2  stage3  stage4.....stage9
1             3         0            0          0         …..    0

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
               
            End With
    Next
    rs.MoveNext
Loop
End Sub
0
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
     
        Rate.Value = CostB.Value
        Else
        Rate.Value = CostA.Value
     
        End If

can someone tell this is happening because of continuous form?
0
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
0
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:  …
0
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
1
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?
0
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
0
Hello ALL,

How to collect all the FileName's from a folder and put the FileName's into a Combo box?

Thanks in advance
0
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.
Company List form
qlkpCompanies
My Database Form
My Database Row SourceEE_1140419.mdb
0
Hi

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

1
2
0
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)

Many Thanks
Listbox110519.mdb
0
Is it possible to password protect the Navigation Pane (including F11) in Access?
0
Access 365 query question.

Table fields are:
   [Date of class]
   [Class Type]
   number of [Students Completed].

I need a query that shows,
   Year: DatePart("yyyy",[Date of Class])
   Count of [Class Type]
   [Class Type]
   Sum of [Students Completed]
0

Microsoft Access

221K

Solutions

52K

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.