Microsoft AccessSponsored by Jamf Now

219K

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 am trying using query designer to count the number of records that were entered in a table where the records entered = "Football" anywhere in the description of the record

Here is what I have but it is not working:  (I think it needs to be an expression because I have to add other fields to the query after I get this one working)

Count Syntax
0
Ultimate Tool Kit for Technology Solution Provider
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Excel 2010 vba

What I have:
I have a defined range that i need to import data into an Access table
In the excel data  the range is starting at  Column "K row 5  TO  S"
the rows in that data range are never the same


What I need:
I need to make sure the code captures and imports K5 to  S(whatever)

' Replace WorksheetName with the actual name of the worksheet
' in the EXCEL file
Set xls = xlw.Worksheets("WorksheetName")

' Replace K5 with the cell reference from which the first data value
' (no-header information) is to be read
Set xlc = xls.Range("K5") ' this is the first cell that contains data  and Goes to  S5



Set dbs = CurrentDb()

' Replace QueryOrTableName with the real name of the table or query
' that is to receive the data from the worksheet
Set rst = dbs.OpenRecordset("QueryOrTableName", dbOpenDynaset, dbAppendOnly)

' write data to the recordset
Do While xlc.Value <> ""
      rst.AddNew
            For lngColumn = 0 To rst.Fields.Count - 1
                  rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
            Next lngColumn
      rst.Update
      Set xlc = xlc.Offset(1,0)
Loop

rst.Close
Set rst = Nothing

dbs.Close
Set dbs = Nothing

Open in new window

0
Hi,

I have a two queries. One is for union and the other is for recent. From the recent query i want to do totals based on Criteria. The criteria is for same "Cou","Flow","Years","PType", Sum "Values" based on Pro. Attach is a sample DB in which you can see tblPro have different ID.The order for this tblPro is ID="1" is total for ID "200,201,300" and ID="2" is total for ID="300", so that total should be on the basis of Product with Sum of Values and group by other. i have try that in qry_total , but it's not working. Any help?

Attach is a sample DB.

Thank you.
error.accdb
0
As Microsoft announced to roll-out Office 2019 in fall of 2019 which will include MS Access 2019 as well. I need assistance in getting information regarding the following:

1. Existence of Backward compatibility with MS Access versions (2010 to 2016)
2. What unknown compatibility issues an MS Access 2010 app user and or maintainer should be aware of before making a decision to upgrade MS Access 2010 based app to MS Access 2019?

The ONLY thing I am sure at this moment is that MS Access 2019 will on runs on Windows 10.

Guidance and suggestions will be appreciated.
0
Hello experts. I inherited a front end ms access application which connects to a sql database. One of my forms it connects to a sql table called tbl_conversations. The table has no primary or foreign keys (if that matters). Here is my issue. I added a new column to the table (tbl_conversations) called track. I then re-open ms access and when I attempt to update the table in the linked table manager I received a message that says "The search key was not found in any record". I select ok and naturally I cannot see the field I want to add as a record source on the form. I tried a compact and repair and still no luck. Any ideas on how to resolve would be great.
0
Hi,

I have an Access FE and SQL 2014 BE and i encounter the following issue:
1. I have a bound form
2.  if the record is a NEW record, never saved, and I set some textbox to NULL explicitly (me.someTextbox = Null) and then I save the record  using Dirty=False, it gives a #deleted for the entire record
3. I've checked if it could be some trigger etc... but it doesn't sound so (i've disabled them all for testing)

Any help would be appreciated

Joseph
0
Is there a way to make a form open to the right hand side of the user's monitor?  Or do they have to manually move it every time they open it?
0
How can I have a task performed during the number of copies is printed?  Right now I have a combo box and the user selects number of copies, but in some instances I have a barcode on a label and the user will not want the same barcode on every label so I need it to change in between the number of copies selected.  Below is what I have now.  

docmd.printout,,,,Forms![main]![cbocopies]

Open in new window

0
The following VBA code in Access 2003, ends in an error:

DBEngine.CompactDatabase "G:\Delivrbl\Willby\Will2K_be.mdb", "G:\Delivrbl\Willby\Temp\Will2K_be.mdb"

Error:  "The Microsoft Jet Database Engine could not find the Object Databases.  Make sure the object exists and that you spell its Name and Path correctly". 3011

What am I doing wrong?  I have checked the Filename and Paths and they are OK.  Incidentally this whole program worked perfectly with different Paths!.

Thanks for any help and best regards,

Biggles1
0
Hello Experts:

It's been a while since I used "case statements" in MS-Access.

I need some assistance with using a wildcard (*) in a case statement.   Below is what I have:

Public Function Q8_If_Married_Convert(Q8_If_Married As Variant) As Variant

        Select Case Q8_If_Married
        
           Case "Resides with spouse":                                                                   Q8_If_Married_Convert = "Resides with spouse"
           Case "Separated":                                                                                   Q8_If_Married_Convert = "Separated"
           Case "Separated due to other reasons (e...g... deployed)":                    Q8_If_Married_Convert = "Separated due to other reasons"
           Case Else:                                                                                               Q8_If_Married_Convert = "No data available"
           
        End Select

End Function

Open in new window



In this example, the problem lies occurs for the 3rd statement.   Rather than having a hard-coded case equal to "Separated due to other reasons (e...g... deployed):", I prefer using the case "Separated due to other reasons*" (ending with the wild card).   The key challenge how the data entry person entered the reference to "deployment".   That is, in this case, too many periods were used in the "e.g." reference.   Alternatively, in other cases, the separation reason may not be "deployment" but could be "XYZ" reason.

Can anyone offer some assistance with a) recommendation how to tweak the case select statement or b) offering an alternative that allows me to convert all possible outcomes for the "Separated due to other reasons*" scenario?

Thank you,
EEH
0
Cloud Class® Course: C++ 11 Fundamentals
LVL 12
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Hi all I am back again with another problem. :) (Eye rolls everywhere) :)

I have created queries and built reports around those queries which I need to print. I have got it to a point where it is displaying the information but it is also displaying all the information for all the records. How can I get it to only print 1 record.

I have tried different things by using the WHERE statement and i have tried filtering and also tried recreating the report.

The reports that are required are in the following db

The reports are Test and TESTCS built on query1Test and queryTESTCS

Thanks for your help
0
I am working on a monthly sales form and it has a Litsbox named "Listbox1" on the form called "frmMonthlySales". I have a text box at the bottom called "txtGrandTotal". In this text box I want the sum of the 8th column of "ListBox1" I have tried several ways but no luck.

[Me].[Listbox1].[Column](8)

Open in new window


[Me]![Listbox1].[Column](8)

Open in new window


I know this code won't give me the sum but I can't get it to respond at all. It just says #Name. I placed the code in the source text box. =[Me].[Listbox1].[Column](8)
I have tried requery it and other methods.  I have used the Column method MANY times in the past for combo boxes. I just don't get it.
Thanks for the help.
0
Hi,

We are trying to move to SharePoint online. We have an access database which has a backend database and front end link. I copied all the files from the file server to the Sharepoint document library and connected it as a network drive to my PC. The front end is giving an error saying "Runtime Error 3043. Your network access was interrupted. To continue close the database and then open it again."

I tried to link the database as external data source but it gives an error saying cannot link to an internet-based source. Can someone advice how can we fix it?

Regards,

Ajoy
0
I have a form with 15 unbound text boxes into which I type sales order IDs. I then run a query which uses the form data as criteria. The criteria read like this:
Like "*" & [Forms]![SalesOrder]![ParentItemInput1] & "*" Or Like "*" & [Forms]![SalesOrder]![ParentItemInput2] & "*"

When I limit the criteria to just 2 inputs, the querie works just fine, but when I add others for Input3, Input4 and so forth, it returns completely unrelated sales orders. I can even clear the form and the query continues to return the same mysterious results.

Can someone please point out my error.

Thanks
0
I have a form with a date field on it named txtRecordDate. In another field named txtFirstOfWeek I want to display the first date of the week in the txtRecord Date.  So for example, if the txtRecordDate shows 6/14/2018 I want the field txtFirstOfWeek to display 6/10/2018.

What is the control source for the txtFirstOfWeek field?
0
In general, when you run an aggregate query in Access...how would you select the Min or Max of the results?
For example, I have the following sql
Lets try this again...I've changed the SQL slightly
SELECT DLPReport.[Employee - Key] AS EmpID, DLPReport.[Cost Center], Sum(DLPReport.[Total Comp]) AS [SumOfTotal Comp]
FROM DLPReport
GROUP BY DLPReport.[Employee - Key], DLPReport.[Cost Center]
HAVING (((DLPReport.[Employee - Key])=846843));

Open in new window

This gives me three rows of data in the results:
EmpID      Cost Center      SumOfTotal Comp
800003      4001              3,190.53
800003      4002               5,146.86
800003      4003                      -5.28

How would I return only the MAX of the [SumofTotal Comp], which is the middle record with 5,146.86?
0
I have a form that has a datasheet subform on it.  It the first field on the datasheet I have an unbound field named txtLineNumber.  When the form is opened I want the records to show the line number starting with "1".  So, it would look like:

1
2
3
4
5
6
etc.

How can I do this?  I've tried making the text field control source =1 and everything I've read says to make the Running Sum property and select Over All but I don't see a running sum option.  

FYI - the record source for the datasheet subform is a query if that makes a difference.
0
Microsoft Access query syntax is giving me unexpected results.
I have an invoice date field.
If the query is run on Monday, I want to see invoices from Friday and the weekend (ie, on Monday I want to see invoices from Friday, Saturday and Sunday).
If the query is run any other day, I want to see invoices from yesterday. (ie on Thursday only want to see invoices from Wednesday)

This query provides no results. If I remove the greater than sign, then I get results, but only for the exact day ( I want to include the weekend details when run on Monday).
     IIf(Weekday(Date())=2,>Date()-4,>Date()-1)

This query does work, but it isn't what I want because it excludes the weekend invoices on Monday.
     IIf(Weekday(Date())=2,Date()-4,Date()-1)

What am I missing about the '>' function ?
0
MS Access Query: how to add rows between two dates until end date is reached; for example, if a record contains the following:
PersonID, Start Date, End Date
123            1/1/18         1/5/18
Note: the PersonID could have multiple/different start and end dates.

Can anyone assist in  correcting my union query so that it adds rows until it reaches the End Date:
PersonID, Start Date, End Date
123            1/1/18         1/5/18
123             1/2/18         1/5/18
123             1/3/18         1/5/18
123            1/4/18         1/5/18
123             1/5/18         1/5/18

This is what I have so far but it returns only one additional date:

SELECT tblTable.[PersonID], tblTable.[Start Date], tblTable.[End Date]
FROM tblTable
UNION ALL SELECT  tblTable.[PersonID], DateAdd("d",1,[Start Date]), [End Date]
FROM tblTable
WHERE tblTable.[Start Date] < tblTable.[End Date];
0
Cloud Class® Course: Python 3 Fundamentals
LVL 12
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

On the form, I have a checkbox and the click event as shown below. On form load, I have the checkbox value set to false.

Private Sub Check2458_Click()
Check2458.Value = True
ConditionReceived.Value = "Received  and working " 

The checkbox does not get checked on the first click and the code did not get executed.
Any help resolving this issue is appreciated.
0
When scaling a section of screen or report It draws circles as circles regardless of scaling but does scale lines and boxes. I have this very strange ratio of 140 to 42:

    DrawWidth = 2
    Me.ScaleLeft = -10
    Me.ScaleTop = -10
    Me.ScaleWidth = 140
    Me.ScaleHeight = 42
A
With this:
DrawWidth = 2
    Me.ScaleLeft = -10
    Me.ScaleTop = -10
    Me.ScaleWidth = 140
    Me.ScaleHeight = 40
(Scale height only changed from 42 to 40) I Get
B
Its just a 15mm Rad circle in a 30 x 30 box

My question is what kind of ratio does 140 x 42 represent (that's the closest I can get for  first drawing) and is there a way to avoid this daftness?
    0
    Hi
    I have a form where I have a dropdown control to help filling up (or correcting) a field [fRefSeccao]. The form contains 2 other fields, [fRefRegiao] and [fRefSector] that must work as filters for this dropdown so the user will only be able to see the [RefSeccao] list of records that match the [RefRegiao] and [RefSector] table fields linked to the  [fRefRegiao] and [fRefSector]  controls.

    The form (in design mode) looks as below:

    Screen-Shot-2018-06-13-at-10.46.27.png
    On the [fRefSeccao] source table, I do indicate a Select, looking like this:

    SELECT qConfereSeccao.codRegiao, qConfereSeccao.codigoSector, qConfereSeccao.refSeccao, qConfereSeccao.nomeSeccao
    FROM qConfereSeccao
    WHERE (((qConfereSeccao.codRegiao)="13") AND ((qConfereSeccao.codigoSector)="12"));

    and it works perfectly.

    Now I want to replace the

           WHERE (((qConfereSeccao.codRegiao)="13") AND ((qConfereSeccao.codigoSector)="12"));

    by a dynamic search on the field forms [fRefRegiao] and [fRefSector], like

           WHERE (((qConfereSeccao.codRegiao)= fRefRegiao) AND ((qConfereSeccao.codigoSector)= fRefSector));

    but it does not work as it does not recognise them

    How do I do it?
    0
    MS Access VBA

    I am trying to set a variable called strPath to a value from my table.  Clearly i am doing something wrong.

    Dim rst As Recordset
    Dim db As Database
    Set db = CurrentDb
     
    Set rst = db.OpenRecordset("Select Path from Table1 where ID = " & "1" & "")
     
    Dim strTable As String
    Dim strBaseTable As String
    Dim strPath As String
     
    strTable = "Customer_Jobs1"
    strBaseTable = "Customer_Jobs"
    strPath = rst
    0
    I'm trying to get an ancient program running, which uses Access 97 runtime. Everything appears to be in place, but when I try to run it I get a message that no Access license file is found. Installing Access 2000 didn't do anything helpful. The program is long dead and support is completely unavailable.

    I have a large pile of install floppies for various versions, but no idea what the Access runtime license looks like (or why it didn't install when I ran the most recent set of floppies).

    Can anybody help me out with the name/structure/whatever for an Access97 runtime license file?
    0
    Hi There,
    I'm trying to pass a criteria in an MS access query from a text box in form. What I like to do is check exactly for the value user enter in the text box( i don't want similar value of what user enters, say if the user enters ABC, I want result eactly matching ABC, not like ABC such as ABC , ABCD, ABCDE etc, if they leave it blank( if needed I can use something like "ALL")  then pull everything.
    On the query I have it like this    [FORMS]![usrpartform]![usrPart] OR Like "*"   but  it always pulls everything.
    I would appreciate if you could tell me the exact syntec to be used on the query to get the result as desired.
    Thks
    0

    Microsoft AccessSponsored by Jamf Now

    219K

    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.