I'm far from being an Access expert, so forgive me if I don't make perfect sense. I have a database where questions and pages are entered on a daily basis. I have a table with three columns: data, pages, and questions. I have a query where I can sumarize the data by Month. (I created a query by using the wizard and I chose to summarize by Month). This returns all of the data, summarized by Month. I want to limit it further by year, so that it would return 12 months of data. I am at a loss! I've tried typing [Type Year] but I get no responses. I was able to create a limiting one where I type in the month and year [Type Month and Year] that works great, but it doesn't translate into the year only.
SELECT clvpertech.clvtech1 AS Technician, Sum(clvpertech.CLVCode) AS CLVs FROM clvpertech GROUP BY clvpertech.clvtech1;
This is returning a sum of all the procedures a technician does for the entire database. This is working.
I need to break this down by day, week , month, and year base off of the datecomp1 or datecomp2 or datecomp3 or datecomp4 fields having a date in them.
I made this query to get the daily results and it works SELECT clvpertech2.clvtech1 AS Technician, Sum(clvpertech2.CLVCode) AS CLVs FROM clvpertech2 WHERE (((clvpertech2.datecomp1)=Date$())) OR (((clvpertech2.datecomp2)=Date$())) OR (((clvpertech2.datecomp3)=Date$())) OR (((clvpertech2.datecomp4)=Date$())) GROUP BY clvpertech2.clvtech1;
How can I add different queries to get the results by breaking down the date field for year, week, and month?
I tried this for the month but I get Invalid Procedure Call when I try to run it???
SELECT clvpertech2.clvtech1 AS Technician, Sum(clvpertech2.CLVCode) AS CLVs FROM clvpertech2 WHERE (((clvpertech2.datecomp1)=DatePart("mmm",[datecomp1]))) OR (((clvpertech2.datecomp2)=DatePart("mmm",[datecomp2]))) OR (((clvpertech2.datecomp3)=DatePart("mmm",[datecomp3]))) OR (((clvpertech2.datecomp4)=DatePart("mmm",[datecomp4]))) GROUP BY clvpertech2.clvtech1;
I am trying to modify the output to only show discontinued items. While the query is in datasheet view, the text is read as "YES" or "NO" under discounted items. So, I tried entering "YES" in criteria while in design view, but keep getting an error message stating, "Data type mismatch in criteria expression".
whenever I run/execute a query in Access it is modifying the first record on the table in which it is calling the data from.For example if the first record might contain the following:
Record A: John Doe, Oct, 2014, Account is Active
And lets say I am running a query to pulling records from Nov 2014.The Month and Year Fields in the above example for Record A gets modified to the query search parameters for Nov.Is there some of of record lock or controls that I need to adjust to prevent this from occurring?
Hi, Self learning trying to modify a query fieldname and criteria thru code.
Have a small form with a button making a copy of a query/s (eventually making about 50 copies). Once these have been made, would like to open the query up, which I can do, then modify both the fieldname and the field criteria to suit my needs from parameters set in the form.
I have an existing query, created using the query wizard which works just fine - however, I would like to modify it to return only instances where there are 3 or more records appearing.
Essentially, its an employee history report for a particular action done by those employees, which returns all records between two dates as specified by the user. What I would like to do is only show those employees who have had more than three instances of this action in the given date period.
I am thinking along the lines of DCount? but how it would be phrased in the query?
I am still new to Access and am loving the learning process. I am stuck on a problem though. I have serached the forums here and various other places and haven't found the info I am looking for. I was wondering if there is a way to have a relpica (or a seperate database that can syncronize) that has a modied design. I want the main one to have everything on it, reports/forms/ add/delete/ect, but I want another one that is simply a data entry form, with only the option to input data. I would love to do this and avoid system security measures with usernames and passwords. From my reading it doesn't seem like I can do this, but I still hold in my heart a glimmer of hope. Please help, and if you can explain the process to me, all the better (its how I'm learning).
Hello, So far you guys have been helping me through my database that i am building. Thanks so far!But at the moment i need help on one more aspect of this Database Job. Currently i have a database that supports and holds records for jobs that are under contract. That means we have certain numbers and statistics of Jobs and there properties.what i have right now is a table with a couple fields. (there are more than this but this is just for example) project number "primary key" aerial units buried units planning units ICGS unitsThat part works fine with the current table. But since jobs are under contract the number of units might change further on during the design and construction. There may be more than one mod.Now the part that im stuck on is the most important. I need a table/query/form/report that i can input the changes to particular units. Like i said before there might be more than 1 mod. So i need to make it show ALL the mods ive done to a project.This is a huge part, and any help would be very much appriciated. ~Thanks so muchJon
Hi to all. I need to modify some existing error messages in my form so that I can create messages that will be more user friendly. I know that it is possible but searching for this issue was a bit confusing. How do I do this?
I have an Access database split into FE/BE. For the front end, each user has his own directory on the lan. The directory is named with the UserName.
I've created an installation program in Access. It basically installs the icon on the user's desktop using Environ("UserName"). It also installs an Excel file the program needs on the hard drive.
Here's the problem...Right now, the installation program grabs the shortcut off the lan and installs it, but that shortcut has one user's UserName hard coded as the target. That one piece of information needs to change for each person. Is there a way for the program to modify the target based on the username of the person running the installation program? Without this piece, I need to visit every user after he runs the installation program and modify the properties of the new icon to point to his specific directory.
Hi guys, me again. Have a few functions that I would like to put into action. 1) I currently have the following code that exports multiple queries to excel. All works great I am happy to say. Currently the date criteria are hard coded into the queries. Well now my users would like to enter date ranges instead. Currently when I click on my toggle button, the users are asked if they are sure they want to start export to excel. I still want that to happen, but how can I change my code to prompt for startdate and enddate before running the export. I would rather use an input boxes to enter the date range instead of using a form. Would this be possible?
2) Right now excel is displayed on the screen while the export is in process, how can I hide excel and then have it appear after its done with export. If the user clicks within excel during export, it sometimes freezes. And how can I apply my module for the progress bar to display the progress of the export. I have provided the code of the progress bar that I am using, that I found on this forum.
Many thanks for any suggestions.
Option Compare Database Option Explicit
Public Function ExportDataExcel() Dim strFilePath As String Dim strFileName As String Dim strFileTemplate As String Dim strMacroName As String
If (MsgBox("You are about to generate the LAR Monthly Report. Are you sure you wish to continue? You cannot cancel this procedure once started.", vbOKCancel) = vbCancel) Then Exit Function End If
'''''''''''''UPDATE THIS DATA WITH YOURS'''''''''''''''''''''''''''''' 'Fill in the following with your files and path strFilePath = "R:Call CenterCall Center DepartmentsMortgage DeptMortgage Statistics & Tracking" strFileName = "BigLarOutput.xls" strFileTemplate = "BigLarTemplate.xls" strMacroName = "DeleteBlank" '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''
'This deletes the old file Kill strFilePath & strFileName 'This recreates your file with the template FileCopy strFilePath & strFileTemplate, strFilePath & strFileName
'This is a custom function I built to set Excel as an object and you can access/export 'to a workbook programmatically. 'openexcel' is stored in a module called Functions. 'This will open the new file that was created previously
openexcel strFilePath & strFileName
'''''''''''''UPDATE THIS DATA WITH YOURS'''''''''''''''''''''''''''''' 'Export data is another function that will export your data. 'Update the query Names to your real Query Names 'Update the Sheet Names accordingly, with the Query it is assiged to ExportData "qryHoeqDotApproved", "HOEQ DOT APPROVED" ExportData "qryHoeqDotReceived", "HOEQ DOT RECEIVED" ExportData "qryHoeqDotDenied", "HOEQ DOT DENIED"
xl.ActiveWorkbook.Save 'The Application.Run will run the Macro(s) that you saved in your spreadsheet xl.Application.Run "'" & strFileName & "'!" & strMacroName xl.ActiveWorkbook.Save
'Uncomment/Comment these to close out the workbook 'xl.ActiveWorkbook.Close 'xl.Quit
Set xl = Nothing
End Function
Private Function ExportData(strQuery As String, strSheet As String) Dim intR As Integer Dim rs As Recordset
'After you open that Object/Workbook, you refer to that workbook now as 'xl'. You will 'use it later, but now you have to access your queries through this code and to do so 'you need to use a recordset.
'strQuery is the name of the Query that you passed with the Function. You can also 'use an SQL string. Set rs = CurrentDb.OpenRecordset(strQuery) rs.MoveLast 'moves to the last record rs.MoveFirst 'moves back to the first record
'You can use record count to make sure there are records in your Query/Recordset If rs.RecordCount < 1 Then 'There are no records MsgBox "There are no records for " & strQuery Else 'There are 1 or more records. Now Select the sheet that you will be exporting to xl.Sheets(strSheet).Select
'Now you need to loop through the records. 'intR' was dimmed at beginning of this 'function and will now use it to create a loop or 'For, Next'
'Starts with record 1 and gets the count of records in the recordset so it knows where 'to stop. For intR = 1 To rs.RecordCount 'Now we need to export the recordset/query to the workbook/object we opened earlier. 'Remember 'rs' refers to the recordset & 'xl' refers to the workbook
'xl.cells(ROW,COLUMN).VALUE = rs.fields(INDEX). 'This is how you will fill in the value of a cell on the workbook. For the ROW you 'will want to add + 1 if you have Headings on your sheet. The INDEX for rs.fields 'refers to the columns of the recordset/query. The first column of the recordset 'starts with the index of zero.
If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then If Forms(strFormName).CurrentView <> conDesignView Then IsLoaded = True End If End If
Exit_IsLoaded: Exit Function
Err_IsLoaded: MsgBox Err.Description, , " Service Operations" Resume Exit_IsLoaded
End Function
Public Function Pause(NumberOfSeconds As Variant) On Error GoTo Err_Pause
Dim PauseTime As Variant, Start As Variant
PauseTime = NumberOfSeconds Start = Timer Do While Timer < Start + PauseTime DoEvents Loop
I have a report I am generating with the design wizard. It uses 1 Query and all the information I want and order is correct when its finished. However, there is more information then what I want to display (I choose it to sum up everything, but I want to get rid of the thing that says 'summary of blah : 3 records) and I want to move the sum label to the far right and change it to total.
However I am finding out that any attempt to modifying the report in any way is changing my information. When its correct and working the report is 24 pages, each client has 3 sub types that come up and shows totals. However, if I make something invisible, move a label, change the text in a label or ANYTHING, then my report is completly screwed up, it drops to 3 pages, and loses all but 5 clients and only 1 sub type appears.
I have a bunch of charts in my forms, and my boss wants to be able to adjust axes. I have added a context menu that brings up a little form and takes values that are then thrown into the graph by way of a function. URL....Here's the current code:
Code: Public Function AdjustTREFAxis() 'See form fTREFaxis Dim objChart1 As Object Dim objAxis1 As Object
[code]....
notes: -ignore the global vars, they are a temporary measure. -"Graph19" I know, not good. I never expected to call it in code though , I'll fix it later -the while loop keeps the rest of the code from running until the axes have been submitted.
Ok, so this code works for the x and y1 axes. I assumed that Axes(3) would iterate to the Y2 axis, it does not...I also found that after you change the axis the changes are persistent for the graph even upon repaint/requerying it. This is problematic and I need a way to set an On Load event that resets the graph to "autoscale"
Lacking that, how to access the Y2 axis, or how to reset the axes to autoscale that would be keen.
I have a a database with a large number of email addresses in it. I also have a list of email addresses stored in an excel file.
I would like to take the excel file and use it to check for the same email addresses in the access database and if they are present in the database add content to another field in the database to flag it.
I have a table that has a field that holds a total. Through historic reasons the total in a (relatively) small number of cases is wrong. I have analysed and worked out what the difference should be and have created a table with the value that is missing. I basically want to add the 2 together.
So I have
Table1 Field1 Value1 Table2 Field2 Value2
I want table 1 to be
Table1 Field1 (Value1+Value2)
Programming wise I would just have Value1 = Value1 + Value2
How do I go about doing this in a query or some other whizzy way?
Is there away in which I can protect an access 2007 database modification with a password.I'm doing this because I would like to prevent my client from modifying or having acces to the database's back end system (db), I would just like him to use forms and nothing else.
I want to update my date field in my database everytime when the record is changed. I do not want to do this by using a macro and I cannot make it word by using the before update function. In the before update I have tried the following without success (nothing happens):
Private Sub Modified_Date_BeforeUpdate(Cancel As Integer) Me![Modified Date] = Now() End Sub
I have created a database for 2/3 users (will progress to more once the system has been smoothed over) who will have the db.file open from 9-5 and will be consistently adding and changing data within 2/3 forms. Most of the time but not always a message comes up saying,
"You do not have exclusive access to this database at this time. Any changes you make may not be allowed to be saved later"
I can understand that it can create data corruption if multiple users are allowed to edit and modify the same data at the same time but surely a database can't be that limited. I thought that splitting the database into a front end on a local server and then a back end on my computer would solve the problem but everyone who is using the front-end still experiences the same problem.
How to be able to overcome issues with multiple users access the same forms and tables? Even when another user is editing one form and another is editing a different form the message will still appear.
I have been struggling with modifying a graph that I have made into a counting graph, I have wasted over 4 hours trying to do this modification unsuccessfully.
I am attaching 4 pictures, The Depth_Ranges Table Visual is the query which creates graph 1 you can also see in the additional attached picture of the design view the code that creates this graph 1.
Now I need to add a field in the Design view of that query in order to have a result of the graph 2 that you see in the picture.
Basically, creating counter that will count how many values are in each bin labels that you can see on the picture of graph 2.
I am new to Access and this is likely a very elementary question. I recently created a database to track status of projects including progress and funding. The table contains several fields that record various aspects of funding. These fields are added (very simply) and create a calculated field that is a summation of those fields. The table currently contains approx 60 records. The calculated field functions for 2 or 3 records and will not calculate further. I have tried this in a form as well as a query. The form will change the same number, 2 or 3 records. When the query is run the result shows only the 2 or 3 records. What I am doing that is only reading and modifying those records?
I have a database for quotations. The database automatically generates a new quotation number every time a new quote is started. This works great, and I am very happy. My next task is to allow the employee to pull up a quote that has already been generated and edit it. I would like for the new quotation process to be followed step by step, but with all of the information already filled in.
This will allow for any edits that need to be made, and keep from having to re-enter a lot of data. I want one thing to change, which is the QuotationNumber. It is currently formatted by "yyyymmdd-01" for the first quote generated on that day. I want the edited quote to have a QuotationNumber formatted by "yyyymmdd-01a". For every edit that letter change going through the alphabet in order. How would this new QuotationNumber code differ from that of the Other?
