Hi, I know this is a fairly easy one. I really try to avoid asking answered questions, but I've honsestly searched through the forum with no results..
I have three statuses:
1 = Ongoing
2 = Complete
3 = Dropped
I want to force status 2, if IIf([EPsDL]/[TotalEps]=0;2;""), so that it would force on status 2, only when the calc. gives zero, and thus letting me change status freely, as long as the calc. is >0.
I don't know whether to use IIf, nor where to put it at all. Would it work in a query, and if so - where to put it?
I have a quick query question. I have a table that lists employees and workcodes and time spent at the workcode, by date. There are multiple workcodes and I'm trying to create a query that lists each employee and total time spent in each workcode. The poblem I'm having is this: Unless the agent spent time in all the workcodes, he doesn't show up on the query.
For instance: Agent A, spent 200 minutes in Code A, 220 minutes in Code B, and 10 minutes in Code C. Agent A shows up fine in the query. Agent B, though, spent all 430 minutes in Code A. He doesn't show up at all.
I think it's also worth mentioning the format the information comes to me in. It's an Excel file broken up into 4 columns, Date, Agent, Code, and Duration. As much as I would like it to be Date, Agent, Code A, Code B, ect, I have no control or say in its format.
I have an Access Application that has a simple username/password interface that controls user access. It can then track who does what and controls access to different screens. Very simple.
One terminal is used by multiple users and they forget to log-out the previous user as it is a Production envirnoment.
Is there a timer that I can add to the App that logs Access off automatically after a period of time??
So I have a table that does a count on ratings in a certain category. The problem is that I want a number to appear for each of the three categories, even when the count returns nothing. In other words, we have 3 rating types, "poor, good, and great," and I need a result for it, even if no one gave the category a poor rating. Now I figured that if I created a table (tblCateg) that just held the three categories (sCatDesc) and did a join on the other table (Ratings) with the column (IN_Category) that I needed, it would force the query to make all 3 ratings appear and a count for each. Unfortunately, this didn't happen. All that appeared was --------- Great 17 Good 7 ---------
I figure the query is just seeing the count for the Poor rating as a Null rather than a 0; anyone know how I can force it to see it as a 0 instead?
Here's the query I'm using:
SELECT tblCateg.sCatDesc, Count(Ratings.IN_Category) AS CountOfIN_Category FROM tblCateg LEFT JOIN Ratings ON tblCateg.sCatDesc = Ratings.IN_Category WHERE (((Ratings.RatingDT) Between #1/1/2007# And #3/31/2007#)) GROUP BY tblCateg.sCatDesc;
I have a report button on a form and the form is the way of inputting in to two tables. I need a way to force the update of the tables before the query that the report is based on, is run.
Is there a command that forces an update of the tables?
I have imported a large contacts table from an old database. The Name field has had all the names input as JOE BLOGGS etc. I can force the table to read joe bloggs etc using < in the format box in properties for this field. But I want the names to read in the Joe Bloggs format, first letter in each name being upper case. Is there an easy way of doing this.
Just got back in to databases after 15 years so wee bit lost at the moment.:o
This should be an easy one, but I’ve been know to say that before…
There are 2 tables (Experts and Education) I also have a form called Edit expert. On this form I have a tab control with 2 tabs. Each tab has a subform on it. The subform data entry property is set to yes. I want the user to enter the contact info of an expert on one tab, click the next tab and enter the education that the expert has. The 2 tables are in a one to many relationship with ExpertID being the primary key. So when you add a record to the education table you need the ExpertID. Well I want to be able to give it to the user. I have a control ExpertID on both subforms. On the first subform, the control source is ExpertID from the Experts table. On the second subform, the control source is set to the ExpertID on the previous subform. It works great until access tries to enter the record into education. Even though the correct expertID is in the box, it says that “you can not add or change a record because a related record is required in the table experts” so this leads me to think that access has not yet committed the Contact info record yet. What I want to know is can I force access to commit the record before the user moves onto the next tab? I have zipped a dumbed down version so you can hopefully understand what I’m rambling about =) all you need to do is pull up the Edit Expert form, enter a rec and then click on the Education tab. you'll see the correct ExpertID for the record you are about to enter, but after you enter the rec and try to click else where it yells at you. thanks for your time and effort!! - me
On my form, there is a button that will auto-populate the subform.
If this button is clicked before the autonumber ID is established the code crashes. I have tried saving the record using...
DoCmd.RunCommand acCmdSaveRecord
...but this does not save the record. After executing this command I can even go into the table where this record is supposed to have been saved and it is not there.
There must be something I'm missing here. Any ideas?
I have a form with a couple of sub forms, one of the sub forms only has a check box and a txt field and both are required before moving on to another record.
I have this following code that works partly, but it only works if one of the fields have info entered. It doesn't however stop the user from tabing through the sub form without entering 'any' data in either one.
---------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer) If Trim(Me!txtContactAgentName & "") = "" Then 'Validate Name Field MsgBox "Gotta Enter Your Name!" Me!txtContactAgentName.SetFocus Cancel = True ElseIf Me!ynCCContactLogLeft <> True Then 'Validate CheckBox MsgBox "Gotta Have a checkmark in Logged!" Cancel = True End If End Sub --------------------------------------------- Also the below throws up an error when I know it's got all the correct information. ------------------------------------ Private Sub txtContactAgentName_LostFocus() Forms.frmAllCustInfo.SetFocus Forms.frmAllCustInfo![AddNewCustomerRecord].SetFocus End Sub
I've gotten rid of all the linked tables from my frontend, so I'm fetching all my data from the backend with VBA code.
This works splendidly until I got to creating reports!
I can populate the report detail with the below code, by opening a recordset and filling in the appropriate text boxes when the report calls the Format_Detail subroutine, but the problem is, that opening a report with no recordsource sets the FormatCount property to 0, so it only runs through the format_detail once. Setting the .nextrecord to FALSE allows as many sub calls as I need, but I can't figure out how to force the report to generate a new detail line! It's looping through all the records in the recordset, but it's only generating one line on the report
Any ideas?
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) On Error Resume Next
If tags.EOF = True Then
[txtfound] = tags.RecordCount tags.Close Set tags = Nothing Me.nextrecord = True Exit Sub
Set tags = backend.OpenRecordset("SELECT Tags.TagCode, Codes.Description, Count(Tags.TagCode) AS CountOfTagCode FROM Codes INNER JOIN Tags ON Codes.Code = Tags.TagCode GROUP BY Tags.TagCode, Codes.Description")
I have form that I've set up and I need to make sure the user does only uses certain fields. For instance, when the user presses the 'New Record' button, it should ask him/her "Which country? 1) UK 2) US"..Once the country field is populated, I need it to go to the City field and ask "Which city".
Does anyone know how to perform an exact match using file search. The code I have below searches for the users AcroRd32.exe file (I have done it this way to allow for different versions of the reader). The problem is that foundfiles returns also AcroRd32info.exe ie FoundFiles(1). i only want to search for the prior!
Set fs = Application.FileSearch With fs .NewSearch .LookIn = "C:Program FilesAdobe" .SearchSubFolders = True .FileName = "AcroRd32.exe"
If .Execute > 0 Then 'check if there is such a file
stAppName = .FoundFiles(1) 'then define this as the applicate string name stAppName = stAppName & " " 'append a space or file string not correct 'specify path name for location of file.pdf stlocation = GetPrivateProfileString32("C:WINDOWSSSI_PROGRS_DATABASE.ini", "DIR", "DATABASE_FILELOCATION") stpathname = stlocation & "file.pdf" Shell stAppName & stpathname, vbMaximizedFocus Else 'if no file is found then need to instruct user to install reader MsgBox "You need to install Adobe Acrobat Reader to open this file", vbExclamation End If End With
We have a large health database, with several data entry individuals, that has run for many years.I use input mask >aC to force the table entries to appear as though sex is F or M even if entered accidently or in the past as "f" or "m" and also >aCCCCCCCCCC to make all names consistently in capitals.
I use > in formatting to be sure reports from the table are printed as capitals.
Frustrating me is that when I export the Access database into my statictics program for analysis, physically what was originally entered is exported e.g Smith, SMITH, smith, smiTH. I want it to appear as only SMITH in my export.Currently to data clean, I remove the masks and physically go through the data to ensure consistency before exporting.
I use a Frm1 which updates my Tbl1. By clicking on this Bt1 of the Frm1 the record is duplicated on purpose having enabled and disabled fields. While Frm1 shows the duplicated record (so it is open) I can change it and save it as needed. However the Frm1 loads having an enabled Cbo1 which normally allows the regular user to pick any value A,B,C or D.
What I would like to have is a line code under the Bt1 click event able to set the value "D" on the duplicated record only. I can not write it under the load form event because I need value "D" set on the combo only when I click the Bt1. Regular user still should be able to select between A,B,C or D when he loads the Frm1 to input a new record.
I tried Me.cbo1="D", also tried Me.cbo1.column(1)="D" under the Bt1 click event and both of them failed. What have I done wrong? How can I get it done?
I have a query that is search for fields in a table that are either
1 - High 2 - Medium 3 - Low
I have a query that counts these and then puts the results into pie charts on a report.
However, when there is no "1 - High" value in the table against a paricualr criteria, obviously the quiery has nothing to look for an does not show a 0 value against the criteria but simply omits it (correctly) form the results.
This does affect the pie charts though which I want to show red for High, yellow for Medium and green for Low.
Therefore I need the query to show all criteria search results include 0 values, or to understand how I can colour code the series rather than the segments on the report.
I have been working on this for over a week in Access 2010 and I know that I am almost there, but need getting over this last hurdle as I have a severe case of tunnel vision on this while I am trying to get it to work.
I have a Main Report based on my Element table and a subreport based on my OTC table. There is a one-to-many relationship between the Element and the OTC table.My Master / Child Fields between the two reports are as follows: ElementID;GroupNum
My SQL for the Main report is:
Code: SELECT Val(DCount("*","Element","ElementID=" & [ElementID] & " AND Step <" & [Step]))2 AS GroupNum, Element.ElementID, Element.ProcessID, Element.ModelID, Element.Step, Element.ElementDescription, Element.RevDate, Element.GPCFS, Element.TrainingGate FROM Element
[code]...
As an example in testing this report, I am looking at a specific Element with 33 records and a total of 72 OTC records. Each of the 33 records can have 1 or 2 or 3 or up to 4 OTC records but the report is only 17 pages which tells me that the page break is occuring based on the ElementID and not on the OTC records....
i have a form with various tabs and a number of fields. If I could I would just select all fields to "required=Yes" in the table design mode. But some fields should only have data entered if another field has a certain value. So, I think I just want all visible fields for this data requirement (I set some fields to visible=false if I don't want them to have a value).
So far I tried to do this on click of the Submit Record button which I created using the "docmd.gotorecord, , acnewrec" statement, but I can't seem to make it work when combining it with if then msgbox statements. Plus I typed an if then statement for every required field.
Here's my code below:
Private Sub AddNewRecord_Click() On Error GoTo Err1 DoCmd.GoToRecord , , acNewRec Me.Label216.Visible = False Me.CM_2A.Visible = False
I have a report which is grouped by employee number - each group can have one or many records displayed - it works fine, but can look untidy sometimes when there are more records than will fit without wrapping onto another page; this is particularly prevalent when there are two or three groups - the third one may run over onto the next page, without group headers.
Is there anyway to force a page break at the bottom of a group if that group is not going to fit on the current page in its entirety?
I have a form that a user fill out to populate fields in my database...how can I make it so that user MUST fill in certain fields before they are allowed to save data?
I need to stop users from taking shortcuts when entering data, and skipping a lot of fields. So i would like to be able to specify the minimum fields, and an error message must show when they try save incorrectly to alert them to this.
I've been avoiding this project but it has come up again. I have a file given to me each month. It contains a member number and status. The status could be any of the reason codes the user comes up with. So far there are ten. I imported the file into an access table. The users need a report summarizing the status changes if any. If there are any changes; the reason code will have a X under the reason code which pertains to them. I ran the unmatched query; but that gives me if the person was added or deleted. How do I search the reason code fields to check if there have been changes. If I ask for the nulls or is not null for each field; How would I know if it was null or not null in the beginning. I need a way to check to see if for example: Smith did not have a X under reason code 1 for the month of June but does in the month of July. I hope I didn't confuse anyone. Please help Thanks
Does anyone know how turn the status bar off with code. I need to turn it off when the main form opens. Then when it closes I need it to come back on...
