Search results

Hi All, I have a query which I need to refine. Currently it calculates a percentage across all records in the results. It actually needs to calculate percentage based on total volume by month AND area. SaleYN is discrete and has possible values 'Yes', 'No' and '' *** Mas TABLE *** DateStart...

Hi All, I have this query for counting number of records per week: SELECT DatePart("ww", [DateStart]) AS weekno, count(*) FROM MAS WHERE DateStart Between #11/01/2017# AND #01/30/2018# GROUP By DatePart("ww", [DateStart]); This works great and the results look like: WEEKNO COUNT 49...

Hi All, I have a query which gives me a count of each value for Pathway SELECT NTH.Pathway, Count(NTH.Pathway) AS [Count] FROM NTH WHERE DateStart Between #01/07/2017# AND #10/11/2017# GROUP BY NTH.Pathway; This works nicely. I am wonderng if there is a way to add a colum which calculates...

Hi Guys, I am trying to use DatePart("mm", [MAS.DateStart]) in : SELECT DatePart("mm", [MAS.StartDate]) As Month FROM MAS; Some results are blank, which is fine, but the majority are #Func! The DateStart column is set to data type date. I am in NZ so we use dd/mm/yyyy. Any ideas? Cheers AL

Hello, I want to create a query to sum by state - the data doesn't have state, it has city (in this data there is only a few cities per state). So I presume I need a table to Map city to state? The data looks like: TABLE MAS: ID | COLOR | CITY | 0 | Blue | San Deigo | 1 | Blue...

Hi All, I have a query which gets a list of records from a table. Each record will, among other things, contain the details of a person and in most cases this will include a Social Security type number (SSN) for the format [0-9]{6-12} (thats all numeric length 6-12). I need to have a...

Hello, I understand access tries to be smart with dates. Here in the south pacific we use dd/mm/yyyy. The net result is access is not smart and across my forms I seem to have a bunch of dates that end up in US format and if it gets a date where it "wont compute" for mm/dd/yyyy then it will put...

Hi All, I have a datasheet view and wanted to display a little more information about the current record the mouse is over. I was going to try doing this with controltiptext/hover but I think after some research maybe this only applies to buttons etc? Can anyone clarify if this is possible...

Hi, I have a query which works fine for some records and not others. It is a very simple query: SELECT * FROM MAS WHERE systemNo = 'B123456' The records that don't work the error is: Runtime Error 3049 Cannot Open Database ". It may not be a database that your application recognises To...

Hi Guys, I am stuck on this one. If I try the SQL: SELECT * FROM MAS I get the expeced 1500 odd records. If I try: Dim rs As DAO.RecordSet Dim db As DAO.Database Dim strSQL As String strSQL = "SELECT * FROM MAS" Set db = CurrentDb Set rs = db.OpenRecordset(strSQL)...

HI, I have a query recordset that I can not update - the little message in the status bar says something to the effect of this when I try to update in the form. My database is split frontend/backend and I can update the data tables on there own from the frontend. I have also tried this from...

I have data in MS Access like: +------------+-----------+ | DateStart | PATIssued | +------------+-----------+ | 12/12/2004 | Yes | +------------+-----------+ | 13/12/2004 | No | +------------+-----------+ | 14/12/2004 | No | +------------+-----------+ |...

Hello, I have been battling this for a few days now, and there has to be a better way. I want a button on a form to copy a record from Table 1 DB1 to Table2 DB2. The tables structure is identical. There is a Auto Primary Key ID on both Table1 and 2. Both Table 1 and 2 have a large number of...

Hiya People, I am trying to check if a record exists before an INSERT statement. Easy enough on the same database and I see a few answers already here, but what if you want to chek if the record exists in a seperate database? I tried something like: Private Sub btn_transfer_Click() On Error...

Hi, I have a main form with a subform for a search functionality using combobox lookup. The combo boxes on the main form control which is displayed on the subform (datasheet). One of the colums on the datasheet is set to be a calculated field based on an expression that looks like: =Date...

Hello, Why is it that this query is OK: SELECT * FROM DATABASE WHERE ( Child1DOB Between DateStart And DateAdd("yyyy",-6,DateStart) OR Child2DOB Between DateStart And DateAdd("yyyy",-6,DateStart) OR Child3DOB Between DateStart And DateAdd("yyyy",-6,DateStart) OR Child4DOB Between DateStart And...

Hi Access peeps, I am looking to make my database faster Some information: The database has about 10,000 records (and growing). It has one main table and the users front end is split from it - they access it across a enterprise LAN with nominally about 5 users at a time in the DB. The...

Hello, I have a form based a recordset of TableA which is a list of Events. The Form simply displays the current event in the recordset and there is a dropdown where the user can select what happens to the event. If a Value 'Intervene' is selected on the dropdown where you select what happens...

Hi People, I am after some input on how I should design an area on my form for a log style window of notes. what I am after is a scrollable readonly text area with another text area below that is not read only where people can append further notes to the log. The read only area should have not...

I am in MS Access 2013 trying to set the Reply To address for a olMailItem with Outlook 2013. (https://msdn.microsoft.com/en-us/library/office/ff862985.aspx) My current code is giving me: Run-time error '287' Application-defined or object-defined error Private Sub email_button_click() ' ***...