Dear Access Mates:
Code:
=NZ(DCount("[DEGREE]","JUNE2005ALL","[DEGREE]='MSED' AND 'MS' AND [DEPARTMENT]='EDUCATION' "),0)
This statement is in an unbound text box but it does not return the proper total. It is counting only the "MSED's" and not "MS's".
This information is from a table.
Is the above syntax correct?
Many thanks to all.
Regards,
Dee
I am using the “count function” on an MS Access 2003 Form field. The count function seems to be working fine except it is not placing the result into the table field.
Does anybody know what I am doing wrong?
Can someone show me the proper format for using the count function used on a form field, where the result is to be placed in a table field?
Please Help!!!
Seti UNIVERSE
http://www.setiuniverse.com/
All of the SETI (The SEARCH for EXTRA--TERRESTRIAL INTELLIGENCE) disciplines in one place.
(Radio, Optical, Microwave, and Amateur SETI)
Stop by Seti UNIVERSE and find out, what you didn't know about SETI.
I can't figure out what I have wrong in my code. I've searched all over the forums and the help files in Access. I'm sure it's something simple. Could someone please take a look?
=DSum([DefectQty],"d_sub1_DefectReport","CodeID= "" & [tblDefectCodes].[CodeID] & "" AND ""[InspectDate] >= #" & [Forms]![frmDefectDetailReport]![txtStartDate] & "#" And "[InspectDate] <= #" & [Forms]![frmDefectDetailReport]![txtEndDate] & "#")
I'm trying to get a sum of all defect quantities between a date range from a user form. Rather than summing all the defect quantities for a defect code, it's summing up the total quantites for each quantity. I'm sure that doesn't make sense so here's an example:
InspectDate CodeID DefectQty DSum Should Be
6/1/06 100 1 3 5
6/2/06 100 2 4 5
6/3/06 105 1 3 1
6/4/06 103 1 3 1
6/5/06 100 2 4 5
The domain is a query because the dates come from a different table than the defect quantities, so the query is pulling all the relevant data to be summed.
Hi All,
I have Header form and subform subform in it with following fields
Subform fields are :
1. MQE_No : (Text field as it will be like MG-120, MG230), Duplicate OK)
2. RPO_No : (Number field – Double, Duplicate OK ) The are project no.
3. ForemanNo (Number field – Long Int – Duplicate OK)
Header form fields are ;
1.ForemanNo – Long Int – No Duplicate
2.Other fields…
The subform field have ForemanNo One-To-many relationship with Header form.
Following project MG-411 need to enter (assign) to foreman no. 641
Actual data to store in subform
MG-411 (MQE_NO)
5421654 (RPO_NO)
641 (FOREMANNO)
I started to enter data into subform selecting the ForemanNo at header so records to enter in specific foremans account. Everything is fine here.
What I need is:
I would like to select / add another Foreman into the header and start entering same above data for another Foreman. Bcz sometimes we need to do like this in case previous Foreman may go on to a week vacation. We can not stop project for a week. So need to assign project to other foreman.
When selected the Project (MG-411) thru a combo to assign it with other foreman, it should give a short msg to user that this project has already been assigned to “XYZ” forman. Need to assign again “ (Y/N box )
If Y then data entered otherwise cancelled.
To achieve this, I did the following but only 50% success. (Used Cmbo CboRPO2 select MQE_NO). Tried to satisfy both condition but no use.
Private Sub CboRPO_AfterUpdate()
If DCount("*", "T_RPO_Footer", "RPO_No = " & RPO_No) > 0 And DCount("*", "T_RPO_Footer", "ENO = " & ENO) Then
MsgBox "RPO ALREADY ASSIGNED TO SOMEONE / FOREMAN", vbOKCancel, "WARNING!!!"
Me.Undo
Exit Sub
Else
Dim Msg, STYLE, TITLE, HELP, CTXT, Response, MYSTRING
If DCount("*", "T_RPO_Footer", "RPO_No = " & RPO_No) > 0 And DCount("*", "T_RPO_Footer", "ENO <> " & ENO) Then
Msg = "RPO ALREADY EXIST WITH OTHERFOREMAN, ASSIGN AGAIN TO ANOTHER ?"
STYLE = vbYesNo + vbInformation + vbDefaultButton2
TITLE = "!! ATTENTION !!"
HELP = "TEST FILE"
CTXT = 1000
Response = MsgBox(Msg, STYLE, TITLE, HELP, CTXT)
If Response = vbYes Then
Me.MQE_NO = Me.CboRPO.Column(0)
Me.RPO_No = Me.CboRPO.Column(1)
Me.WORKSHEET_NO = Me.CboRPO.Column(2)
Me.WORKORDER_NO = Me.CboRPO.Column(3)
Me.WORK_DESC = Me.CboRPO.Column(4)
Me.PL = Me.CboRPO.Column(5)
Me.PipeLineKM = Me.CboRPO.Column(6)
Me.DiaMeter = Me.CboRPO.Column(7)
Me.PipeLength = Me.CboRPO.Column(8)
Me.PipeLineArea = Me.CboRPO.Column(9)
Me.P = Me.CboRPO.Column(10)
Me.RPO_AMOUNT = Me.CboRPO.Column(12)
Me.INV_AMOUNT = Me.CboRPO.Column(13)
Me.Status = "WIP"
Me.StatusID = 2
Me.CboStatus.SetFocus
Else
Me.Undo
Exit Sub
End If
Else
Me.MQE_NO = Me.CboRPO.Column(0)
Me.RPO_No = Me.CboRPO.Column(1)
Me.WORKSHEET_NO = Me.CboRPO.Column(2)
Me.WORKORDER_NO = Me.CboRPO.Column(3)
Me.WORK_DESC = Me.CboRPO.Column(4)
Me.PL = Me.CboRPO.Column(5)
Me.PipeLineKM = Me.CboRPO.Column(6)
Me.DiaMeter = Me.CboRPO.Column(7)
Me.PipeLength = Me.CboRPO.Column(8)
Me.PipeLineArea = Me.CboRPO.Column(9)
Me.P = Me.CboRPO.Column(10)
Me.RPO_AMOUNT = Me.CboRPO.Column(12)
Me.INV_AMOUNT = Me.CboRPO.Column(13)
Me.Status = "WIP"
Me.StatusID = 2
Me.CboStatus.SetFocus
End If
End If
End Sub
I tried with following code also
'Dim cdn As String
'cdn = "[MQE_NO] = '" & Nz(CboRPO, "") & "'"
'cdn = cdn & " And ENO = " & ENO
'If DCount("*", "T_RPO_Footer", cdn) > 0 Then
'MsgBox "THIS RPO ALREADY ASSIGNED TO THIS FOREMAN", vbOKOnly, "WARNING!!!"
'Me.Undo
'Exit Sub
'Else
‘do something
‘End if
Can somebody help it please.
With kind regards,
Ashfaque
Hey all, I have a strange problem that I can't seem to find the solution for...
At my work we have a number of computers running W2k with Office 97.
We daily have to import excel spreadsheets into Access.
This is never usually a problem as obviously as soon as you click import the import spreadsheet wizard appears.
But on one particular machine it doesn't.
Basically on this computer Access will import the excel file, but won't bring up the wizard that lets you say what to import, whether the first row contains a header, the data type for each field etc, it just asks you to select what sheet you want, then imports it, no options.
Now the ironic thing is that usually this is not a problem as the file is still imported without any problems, but the other day we had a file that once imported was all messed up, all fields all over the place.
When done again on a different computer there were no problems so I'm assuming that this peculiar way of importing is to blame for messing up the data.
Does anyone know how I can resolve this? I have tried dragging the accwiz.dll over the regsvr32.exe and it says it's registered successfully, and I have checked that it is the correct version number, what am I missing?
It would make more sense if it just didn't import, then you could better understand how to fix it, but it's the fact that it does import, just not in the 'usual' way that bothers me. How can I make it bring up the proper import spreadsheet wizard?
Kind Regards
rd_boon
Hi,
I'm working in Access 97 and I've got a form that I want to use to create filters with.
There are a couple of things that I'm not clear about and I'd appreciate any suggestions that you can make:
1. The form seems to remember the last filter, so when I click on the Filter by form button it shows the last filter by default. I've included the following in the click event:
RunCommand (acCmdRemoveFilterSort)
Me.FilterOn = False
RunCommand (acCmdFilterByForm)
Surely either of the first two statements should remove the filter, shouldn't they? and if not then what are they for?
2. I can't get the 'OR' tabs to show at the bottom of the filter by form screen unless I apply the filter once and then click on the filter by form button again. Is there any way of forcing these tabs to appear?
Thanks for any help/advice you can offer.
Regards,
Bernard D
I have a query where I'm selecting fields from two tables - Students and Attendance. I have selected option 2 in my Join Properties, i.e "Include ALL records from Students and only those from Attendance where the joined fields are equal". The tables are joined by StudentID. Either I'm misinterpreting what the join is supposed to be doing or it's not working.
I need to see every record in Students regardless of whether or not they have a record in Attendance. I need to calculate absences and tardies. If there's a matching record in Attendance, I subtract the date absent from the total days. If there's no matching record in Attendance, I still need to calculate total days and show it. As it is now, if there's no matching record in Attendance, it doesn't do anything. How can I get it to do what the join says, "Show ALL records in Students as well as those that match in Attendance?
Thanks
I have a 'yes' check box and a 'no' check box. When the 'yes' check box is ticked, it enables a text box so I can enter a date and disables the 'no' box. Great, how it should work.
However, for some reason, whenever a date is entered into the text box when the 'yes' box is checked, every 'no' box afterwards is greyed/disabled out.
The thing is, it doesn't happen when no data is entered into the text box, when the 'yes' box is checked. So I can have the yes box checked, the no box will grey out as it should, and the text box will be enabled, as it should. But as soon as I enter a date, the above problem happens.
If that's not too confusion, can anyone help?
I've tried attached the database, but apparently it's too big, so i've posted the code underneath.
Cheers
Dan
EDIT: - i've just figured out that this problem is solved when I close the form and re-open it. But obviously I don't want to keep doing this every time I have to enter a date in a record.
Code:Private Sub chkSuccessful_AfterUpdate()If chkSuccessful = True ThentxtDateAppointed.Enabled = TrueEnd IfIf chkSuccessful = True ThenchkSuccessfulNo.Enabled = FalseEnd IfIf chkSuccessful = False ThenchkSuccessfulNo.Enabled = TruetxtDateAppointed = FalseEnd IfEnd SubPrivate Sub chkSuccessful_Enter()If chkSuccessful = True ThentxtDateAppointed.Enabled = TrueEnd IfIf chkSuccessful = True ThenchkSuccessfulNo.Enabled = FalseEnd IfIf chkSuccessful = False ThenchkSuccessfulNo.Enabled = TruetxtDateAppointed = FalseEnd IfEnd SubPrivate Sub chkSuccessfulNo_AfterUpdate()If chkSuccessfulNo = True ThentxtDateAppointed.Enabled = FalseEnd IfIf chkSuccessfulNo = True ThenchkSuccessful.Enabled = FalseEnd IfIf chkSuccessfulNo = False ThenchkSuccessful.Enabled = TrueEnd IfEnd SubPrivate Sub chkSuccessfulNo_Enter()If chkSuccessfulNo = True ThentxtDateAppointed.Enabled = FalseEnd IfIf chkSuccessfulNo = True ThenchkSuccessful.Enabled = FalseEnd IfIf chkSuccessfulNo = False ThenchkSuccessful.Enabled = TrueEnd IfEnd Sub
I have a form "NewMetalF" that has three combo boxes. The first one is used to choose "Precious Metal" or "Base Metal". The second one shows all the metals based on the first combo box. When I open the form in form view alone the combo boxes work flawlessly. I run the form "NewMetalF" within another form "NewExternalPartF" by clicking a button "AddMetal". When the button is clicked "NewMetalF" opens. The first combo box is selected, and when the second combo box is clicked the error "Enter Paramater Value" "Forms!NewMetalF!cboPreciousOrBase".
View 13 Replies View RelatedI have a report that is to count the number of times a field has been selected.
Some of my fields are in option boxes, some are individual boxes. The option boxes do not add up. On my report I get an *error* on all the option box totals. If I request a record from a certain date, the query is correct but the option boxes are not adding.
In the properties of the field on the report (can you tell I am a newbie?) I have
Dcount=("[fieldname]","[Query]","[fieldname]='1'"])
Why won't it print the total on the report?
I've had it before, but can't find it.
basically something like this --
DateDiff("w", StartingDate, EndingDate)
that also makes sure date is not in tblHolidays.
anyone knows how to acomplish this ?
I have a database that I maintain the history of our football league in. What I am trying to do is count each team and the number of wins in each year
The query works fine except in the cases where the team had ZERO wins. Then of course it returns nothing and screws up the other queries when there is a hole for a year.
2 Tables in the database
INFORMATION
TEAM
YEAR
(other fields not relevant)
SCORES
TEAM
YEAR
RESULT (either WIN, LOSS or TIE)
(other fields not relevant)
I want to count the number of wins and return it to this query EVEN IF THE NUMBER OF WINS IS ZERO (0).
When I run the query I receive
The query returns this:
2000 Westside 6
2001 Westside 3
2003 Westside 1
2004 Westside 3
In 2002 Westside was 0 wins -11 losses and the query not finding any WINS in 2002 did not return a record.
How do you get the query to show
2002 Westside 0
==========================
Current SQL Query
SELECT Information.Year, Information.Team, Count(Scores.Result) AS CountOfResult, Scores.Result
FROM Scores INNER JOIN Information ON (Scores.Year = Information.Year) AND (Scores.Team = Information.Team)
GROUP BY Information.Year, Information.Team, Scores.Result
HAVING (((Information.Year)>2000));
I think i am missing the obvious but cannot seem to get this one....
I am trying to get my form to return a value of the amount of working weeks in a month i.e. a 4 week month (like june - 4 mondays) or a 5 week month (like may - 5 mondays).
i can calculate the number of full weeks using the control source..
=Int(DateDiff("d", Date1, Date2) / 7)
but this does not work for the 5 week months?
Any help would be greatly appreciated.
I have a table with two fields named as "Month" and "Bank Holidays". I have put names of the month in this table for 2014 and the corresponding bank holidays of each month. Now I want to write down the piece of code that will execute when a button is pressed and check the current month of the year and then display the total working days ( After subtracting bank holidays of that month and weekends of that month from the total working days of that month).
View 2 Replies View RelatedNeed help and advice desperately,
I am trying to update about 10 tables each time a visitor visits my site..
however there are often missing counts, meaning the total data in each table are not the same frequently They are supposed to be since all the tables get updated each time.
Im using MS Access 2003.
What could be the problem?
Below is an example of one of the function that updates one of the tables in my DB.
************************************************** *****
function GetIdRes(sName)
'Get ResID
sUserID = Request("UserID")
sSQL = "SELECT ResID, ResName, Total, UserID FROM Resolutions WHERE UserID = '"&sUserID&"' AND ResName = '" & sName & "'"
rs.Open sSQL,,,adCmdTable
if rs.eof then
rs.AddNew
rs("ResName") = sName
rs("Total") = 0
rs("UserID") = sUserID
end if
rs("Total") = rs("Total") + 1
rs.update
GetIdRes = rs("ResID")
rs.close
end function
************************************************** ****
I've got another 10 similar tables. The Column "Total" is often different among the tables.
Please kindly advise...
Thanks
Hi,
When i use this
between #01/07/2005# and #01/09/2005#
It all works ok and pull outs what i need.
But when i do
between #01/07/2005# and DateAdd("m",3,#01/07/2005#)
I have also tried it without the # and with 2 instead of 3.
No luck :(
Any ideas?
Thanks
k0r54
Hey everyone
This issue is holding me back now on 2 areas of my project :(
I need to know correctly how to reference a subform in a query.
Can someone please give me a working example for this:
main form: training courses
sub form control: course_dates subform control
sub form: course_dates subform
text box: course_date
I have tried:
[forms]![training courses]![course_dates subform control]![course_dates subform]![course date]
in the criteria box, but every time the query runs i get a parameter box "forms!training courses!course_dates subform control!course_dates subform!course date" asking for the info.
Seriously I am totally stuck on this and i think all i need is one perfect example... please help!!!
Hi.
I am running a form on top of a query with around 1500 records in it. The query mostly uses tables that are joined with referential integrity.
The form has buttons and conditional formatting.
It was fine until today, when suddenly the form has stopped loading properly. It looks all fragmented on the screen and I have to scroll down and scroll back up again for it to look right.
I have searched everywhere to find out what is causing this and how it can be fixed, but no luck yet. Can you help?
Thanks.
I had a look on Microsoft Support and found this article: http://support.microsoft.com/kb/242889/en-us but I don't understand what it's saying.
Basically, I am trying to get the message formatted as it suggests in the help file (Access 2002) i.e. first line bold, second line normal, third line normal and paragraph space, by using the @ symbol. However, it is not recognising this as a format command, just plain text.
MsgBox shows:
Please enter a date range.@Hint: to show all records use a date like 1/1/1990.
instead of:
Please enter a date range.
Hint: to show all records use a date like 1/1/1990.
The MS Support file above seems to suggest that this is simply no longer available in later versions of Access, and offers two work-arounds. However, I have no clue about the second one because I'm not really into programming, and the first one seems to suggest exactly what I am currently doing.
Can anyone help me step through what I am doing wrong? Or is it just a case that I would have to use the code? (In which case could someone help me set it up?)
Thanks.
I created an MDE file for my application it's a split db when I hit the exit command button on the MDB version it quits the application correctly but when I do the same thing on the MDE version, it leaves the database window open on the taskbar.
View 1 Replies View RelatedI'm using access 2003 sp2 with all the current updates. I've got several tables linked to a sql server 2000 DB. We use it for an app called Altiris. Most of the queries i've written from these various tables work fine. However, a couple of them for no apparent reason, do not display the correct data in the data sheet view when the query is ran.
for example, I have a simple query with two tables linked. The first field is asking for the "name" column of one of the tables. In the criteria of that column, I put something in to limit the names of the programs i'm looking for. Lets say I use LIKE '%adobe acrobat 7.0%' When I run the query, it returns a certain number of records, but in the name column, it has all wrong info. I just ran it and it came up with 4 records: "webcast....", "abacast client", "Ibm websphere", and something else. Nothing even close to "adobe...."
So then I export the query to an excel spreadsheet and then open. Guess what? It has the correct info. Four records of adobe acrobat. The other column I have is from the other table and is just listing pc names. They displayed properly in both datasheet and excel file.
does anyone have any ideas what could cause this? I've deleted the table and linked to it several times. I've used this table for many other queries and it works most of the time. I just have no idea what is causing it to do this for only a few queries.
Thanks in advance for any help.
fisk
Hi -
First some background. A few months ago I replicated an Access 2003 database so that folks in various locations could all enter data. There is one master and 3 copies. One of these copies was operating on a computer with Access 2000 and it also happened to be the copy of the database that gave me some headaches (never with the synching) which I am almost positive were related to the version difference.
So, despite those other problems this copy of the database synched up fine throughout the summer. Now all the sudden I have found a few recently entered records in the copy which are not being transferred during synchronization. And from what I have been able to tell so far they seem to be from a particular data entry session on a particular date. I have already looked through the records in question to see if they are missing an important field of data or something and they seem to be complete and legitimate records.
I have tried a number of things and at this point am at a loss as to where to go next. Does anybody have experience with replicated databases and potentially run into this problem before? Any advice on what to look at next to figure out the casue and the fix?
Thanks for any help.... Steph
Hi,
I am trying to multiply £127.65 by 1.175 to produce and store the answer £149.99 in my “answer” field.
I have spent more than a few hours testing different methods, but none so far seem to be suitable.
I used to use the currency data type, however - although the field would show £149.99 – it is stored as £149.98875. So when it comes to adding up each of the “answers”, I am sometimes a penny out because it is adding up the stored number and not the two decimal place answer.
I then changed the data type to “number”, set format to currency, and set the scale and decimal place settings to 2. However this is now not rounding the numbers up correctly and the answer produced is £149.98.
Is there a way I can round up the numbers, and store them as £149.99?
Any help would be greatly appreciated.
Thank you for your time.
Dean
I run a Query based on some data in a "drop-down" box. It works in just datasheet view, but when I print it on a report, it displays the position that the data is in the drop-down box.
How do I display the data, not the position on the report? Here's an example:
My drop down box contains:
apples
oranges
bananas
My query returns apples, but in the report view it displays the number "1" instead of my data "apples". If the query returned oranges, the report would display "2" instead of "oranges" and so on.
Any ideas?
Jen
I have a query which is filtering by date as criteria...but it won't filter right.There is a date field from a table in another query which has date/times in it.I have used Format([Field], "Short Date") in that query to display just the date.I am now doing another query which points to that query (It's a SQL Union query if that matters) and it has the following for the Criteria:Between "6/1/2007" And "6/30/2007"However, it is showing anything in the month of June for the years 2005, 2006 and 2007, instead of just 2007. Any ideas why it is doing this? It doesn't make sense to me that it will filter for the month/date but not the year.I also tried changing the format to Format([Field], "mm/dd/yyyy") and criteria of "06/01/2007", etc.
View 11 Replies View RelatedOkay, here's a question that should hopefully be simple. I have a form that I am setting the default value of two text boxes based on the form previous. Here's basically the rundown of what happensfrmForm1Input Month: FebruaryInput Year: 2012OK - Opens frmForm2 and then closes this one---------frmForm2Month_Default_Value: #Name?Year_Default_Value: 2012---------And this always happens for every month and every year. So could you tell me what could possibly be wrong with my "Month" default value? Here's the code...Private Sub Form_Load()Month_Query = [Forms]![frmStatementDialog]![Month]Year_Query = [Forms]![frmStatementDialog]![Year]Me.Month.DefaultValue = Month_QueryMe.Year.DefaultValue = Year_QueryEnd SubThanks!--Mike
View 1 Replies View Related