Help Required Urgently
Jul 26, 2007
Commission: IIf([Amt]<=10000,30), IIf(Int([Amt]/1000 Between 10000 And 50001)*3.5+3.75)
What I required to do is that when amount is greater than or equal to 10000 than Amt multiply by 30 simply.
and when amount is between 10000to 50001 than Amt/1000 multiply by 3.5+3.75
means that when amount is 15000 than according to per 1000 it will be calculated.
please dear help me out in this I required it very very urgently.
Please Please Please Please. ....................................
View Replies
ADVERTISEMENT
Jan 2, 2005
Hey guys. I'm an A level ICT student (british exam) and I am currently working on a patient management database in Access as a project for the A2. So far all has been going well. The problem lies within one of my forms. The form (called frmTreatment) is a form with a subform. The main form contains patient and treatment data, whilst the subform allows for costs to be added to the system. The problem is that when making a combo box to select a patient by the option "Find a record on...combo box", the combo box does absolutely nothing. It brings up an empty list, whereas it should bring up Patient ID, Patient Name and Patient Surname. I suspect its a problem with the relationships. What the combo box should do is bring up patients from tblPatients and subsequently present data on them in the rest of the form's fields.
The second problem is with the subform. I want to add a combo box that allows the user to select the cost from the tblCosts, much in the same way with the afforementioned Patients combo box. Yet subforms won't allow new objects to be added and I can't see any way of making Cost ID perform such a function.
Can someone please help me by having a look at my project, and in particular the frmTreatment? I can't seem to pinpoint the error out and everything seems fine. I've done normalisation and everything already. The project is due after the Xmas holidays, plus its difficult to get in touch with my ICT instructor now. So any help will be appreciated.
Thanks in advance,
Elh
PS: If you're willing to help give me your e-mail so I can send a zipped version of the project to you in order for you to help.
View 8 Replies
View Related
Nov 13, 2005
I am creating a database and have ran into a problem I can't seem to fix. Whenever I try to make a query, it says 'type mismatch in expression'. Please could somebody help me!!
View 3 Replies
View Related
Jun 14, 2007
Hi,
i have got a database which consists of table, queries, forms and reports.
I have one problem now is that I wan to do a query that will be able to bring the figure from another field to the current one.
ok say i have got Ref_No, Event_Date, Balance_BF, Adjustment, Balance.
so if say this is the first event for this particular ref_no (1234) it will bring the amount from another table (Principle_Table) linking the same ref_no (1234) to the Balance_BF fields,
If this is not the first event for this particular ref_no (1234) it will then bring the amount from the previous event Balance field to the current event Balance_BF field.
I am very lost how to do this. Is there a way doing this using query?
This is wat i mean
Ref No Event Date Balance_BF Adjustment Balance
06/00014 31-May-06 115140 -5,757.00 $109,383.00
06/00014 31-Aug-06 109383 -5,757.00 $103,626.00
06/00014 30-Nov-06 103626 -5,757.00 $97,869.00
06/00014 28-Feb-07 97869 -5,757.00 $92,112.00
U can see that the Balance_BF is actually brought down figure from the previous Balance starting from the second events. For the first event, the Balance_BF is actually draw down from another table (Principles_Table)
Is there a way to do this using query?
View 2 Replies
View Related
May 21, 2004
Good day.
I really need some help! Someone here at our offices deleted a Access database
file. I retrieved the file, but now it doesn't want to open!!!!!!
It gives me the following error : Unrecognized database format (.mdb)
Will someone please let me know what I need to do to make this database to work as soon as possible!!!
Thank you
Shane
shaneo@neda.co.za
View 14 Replies
View Related
Jan 22, 2005
I have been trying to solve this problem for two weeks and am almost tearing my hair out! I have attached a scaled down version of the database I am working in - could some kind person have a look at it and help me, please?
I am trying to make a combo box on my form that will allow me to click on the CID (CustomerID) field, let me click on my customer, and have the rest of the fields filled in automatically.
I have followed the instructions in 'Step by Step - Microsoft Access 2000' to do this, but although it shows the fields, I can't actually select them on the form....
View 5 Replies
View Related
Mar 11, 2006
SELECT (Max(ReferenceDescription.ReferenceID))
FROM (ReferenceDescription)
WHERE (ReferenceDescription.ReferenceID >= (ReferenceDescription.CategoryID * ReferenceDescription.DepartmentID) AND ReferenceDescription.ReferenceID <= (INT(ReferenceDescription.CategoryID * ReferenceDescription.DepartmentID * 0.9999))
View 1 Replies
View Related
Nov 3, 2014
I have made a form based on related tables. it requires me to fill out every field, which I don't want. I didn't make them required. Why does it do that?
View 3 Replies
View Related
Jul 26, 2007
Commission: IIf([Amt]<=10000,30), IIf(Int([Amt]/1000 Between 10000 And 50001)*3.5+3.75)
What I required to do is that when amount is greater than or equal to 10000 than Amt multiply by 30 simply.
and when amount is between 10000to 50001 than Amt/1000 multiply by 3.5+3.75
means that when amount is 15000 than according to per 1000 it will be calculated.
please dear help me out in this I required it very very urgently.
Please Please Please Please. ....................................
View 1 Replies
View Related
Apr 4, 2005
Hi All,
I am new to Access and trying to create a database for employee's leave record.
I created table 1 for departments i.e., A, B, C and so on
There is table 2 for employees i.e., Z, X, Y and linked with table 1 for their departments.
There is table 3 where first I have to select department (from table 1) and then employee (table 2).
All I want (in table 3) is:
Once I select department, the next field for employee show its respective employees.
Thanks is advance.
View 1 Replies
View Related
Feb 24, 2008
I am fairly new to Access and may very well be trying to walk before I can run.
I have an Excel Spreadsheet that is currently used to produce a Nominal Roll that is listed by the following:
Surname
Enlisted Month
Enlisted Year
POP Month
POP Year
I know how to transfer it across to Access.
I then need to be able to produce lists (The listings order needs to be Enlisted Month/Year then POP Month/Year) that can then be merged into tables 4 columns wide (where at the top of each table is a General Comment) and the names run down in alphabetical order then move across to the next column.
(Basically for example I have 24 names that needs to be divided by 4)
hopefully that is as clear as mud, any help would be greatly appriciated
View 4 Replies
View Related
Feb 25, 2006
Hi all this is my first post so go easy
OK i have a project to do about a garage
these are the unormalised attributes
Customer_ID, First_Name, Surname, Address, Telephone_No, Postcode, Employee_ID, First_Name, Surname, Hours_worked, Service_ID, Name_of_service, Cost_of_service, Car_registraion, Engine_size, Colour_of_Car, Car_manufacturer, Invoice_No, Amount_due, Amount_paid, Outstanding_amount, VAT, Cost_of_service, Booking_code, Date_of_booking
these are the entities
CUSTOMER
SERVICE
CAR
PAYMENT
EMPLOYEE
BOOKING
ive never done normalisation before, and read online a bit about it but cant for the life of me understand it. anyone willing to give me a little help. on converting the above to 1NF, 2NF and 3NF?
Thanks in advance
alison
x
View 2 Replies
View Related
May 8, 2006
Hi. I want to have all my fields on my form to be required and so that the user cant close the form if they are empty (error message). In the table properties I have them set to Required an have tried putting in a validation rule of IS NOT NULL but it still dont work. any help appreciated. thanks
View 1 Replies
View Related
Nov 19, 2006
First of all sorry for the stupid question. I am opening a new business and will recieve a specializes CMS system using Access as the DB. Do I have to buy Access to run it?
View 2 Replies
View Related
Aug 1, 2005
How do I set the Required property for field that already exists via SQL. I'm thinking along the lines of: ALTER TABLE table1 ALTER COLUMN field1 text(50) NOT REQUIRED.
But this doesn't work, any clues?
View 5 Replies
View Related
May 11, 2006
I'm having problems with the required field option in my table. I have a form that writes to the table and has a validation of the fields built in the code. If the validation is true, then it will allow the record to be saved.
However, I've also set those same fields in the table to be a required field. Whenever I enter data in those fields in the form and click the save command button, I get a message back telling me one of the required fields cannot be null. The thing is, it is not null. I entered data into the field through the form. Whenever I change the required field option to no in the table the problem goes away, but shows up on the very next field in the list that is set to yes.
Validation occurs within the form that writes to the table, but I still would like to have the security of knowing the fields are required in the table as well, just in case someone tries to be sneaky and go directly into the table to enter/modify data.
Is there something I'm missing? Is this a bug? I've actually set up another database the same way and don't have any problems with it. I'm not sure what's going on. Has anyone else had this problem?
Thanks,
cbdcolga
View 1 Replies
View Related
Apr 9, 2008
I want an access database to manage my correspondence after my motorbike accident.
I am sort of unsure on tables and correct normalisation so here's my plan so far.
Table 1
Company address details
Table 2
Contact name details
(I have a lot of different people from different departments in each company) so a contactID to table 1
Table 3
Media Type - letter, email, phone call etc
Table 4
Category - Legal, Financial etc
Also I want to link pdf images to each entry.
Does this sound okay or can tables 1 and 2 be merged? dunno if it would be easier to add names to an existing company rather than possibly adding the whole thing again.
Thanks all for looking
View 1 Replies
View Related
Apr 23, 2007
After researching this issue I have not found a satisfactory solution to this issue.
I currently have 1 Access query that is the basis for my VBA code_ Sql statement. both Query statements work when debugged. However, I am getting this error on execution of the sql statement in my vba.
Dies here: '<<<<<<<<<<<<<<<<
Public Sub ConnectCMIS(spar As String)
Dim sConn As String
Dim oConn As ADODB.Connection
Dim lCnt As Long
Dim sSql, strSQL As String
Dim rstOra As ADODB.Recordset
Dim rsAccess As New ADODB.Recordset
Dim fld As ADODB.Field
On Error GoTo ErrorHandler
DoCmd.SetWarnings False
sConn = _
"Driver={Microsoft ODBC for Oracle};Server=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS =(PROTOCOL=TCP)" _
& _.;......
strSQL = "SELECT " & _
"MEASNO, FTEMNOMENCLATURE, NOMENCLATUREMODEL, " & _
"EquipID As EQUIPMENT_ID, MULTIPLE_ID, JOB_GROUP, " & _
"PROJECT, PRIORITY, IIf(Len(Trim(COMPLETE_BY_DATE)) > 0, Mid(COMPLETE_BY_DATE, 3, 2) & ""/"" & Mid(COMPLETE_BY_DATE, 5, 2) & ""/"" & Mid(COMPLETE_BY_DATE, 1, 2), Null) AS COMPLETEBYDATE, " & _
"RequestorId As REQUESTOR_ID, " & _
"CALIBRATION, REPAIR, MODIFICATION, ACCEPTANCE, EVALUATION, " & _
"MAINTENANCE, SUPPORT, CMIS_LAB, SERVICE_LAB, WORK_CODE, " & _
"CHARGE_NUMBER, DISPOSITION, ReqComments as REQUESTORCOMMENTS, INPUT_RANGE_MIN, " & _
"INPUT_RANGE_MAX, INPUT_UNITS, OUTPUT_RANGE_MIN, OUTPUT_RANGE_MAX, " & _
"OUTPUT_UNITS, GAIN, CUTOFF_FREQ, INPUT_FREQ, REF_FREQ, REF_VOLTAGE, " & _
"EXCIT_VOLTAGE, EXCIT_ENABLED, FTIR_ACCURACY, OFFSET, OFFSET_ENABLED, " & _
"REQ_EMO1, REQ_EMO2, REQ_EMO3, REQ_EMO4, REQ_EMO5, REQ_EMO6, " & _
"SPARECODE, CALIBRATION_ID " & _
"FROM QS_SRUpdatetoCMISdrt " & _
"WHERE job_group = " & Chr(39) & spar & Chr(39) & ""
Set rsAccess.ActiveConnection = CurrentProject.Connection
rsAccess.CursorType = adOpenStatic
Debug.Print strSQL
rsAccess.Open strSQL'<<<<<<<<<<<<<<<<
If rsAccess.EOF = False Then
Set oConn = New ADODB.Connection
oConn.Open sConn
Set rstOra = New ADODB.Recordset
rstOra.ActiveConnection = oConn
rstOra.CursorType = adOpenKeyset
rstOra.LockType = adLockOptimistic
rstOra.CursorLocation = adUseServer 'default
rstOra.Open "CMIS.UDV_RFS_SR"
Do While rsAccess.EOF = False
rstOra.AddNew 'Then where you add the Oracle record instead of individual assignments you have
On Error Resume Next
For Each fld In rsAccess.Fields
rstOra(fld.Name).Value = fld.Value
Next
rstOra.Update
rsAccess.MoveNext
Loop
End If
strSQL = "UPDATE CMIS.UDV_RFS_SR SET PROCESSED_IND = 'S' WHERE job_group = '" & spar & "'"
oConn.Execute strSQL, lCnt
DoCmd.RunSQL _
("UPDATE TA_SR SET PROCESSED_IND = 'S' WHERE Job_Group='" & spar & "'")
rstOra.Close
Set rstOra = Nothing
oConn.Close
Set oConn = Nothing
Call MsgBox("Submittal to CMIS has been processed.", vbInformation, "Process Submittal Complete")
SubExit:
On Error Resume Next
If Not oConn Is Nothing Then
Set oConn = Nothing
End If
rsAccess.Close
Set rsAccess = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error Number = " & Err.Number & "-> " & Err.Description, vbExclamation, "CMISStatus"
Resume SubExit
End Sub
1st query:"QS_SRUpdatetoCMISdrt"
SELECT QS_TT_GeneralInfo.BEMS AS RequestorId, TA_SR.FTEMNomenclature, TA_SR.NomenclatureModel, tblEquipListingPerJobGroup.MeasNo, IIf([Primary]=True,[Equipment_ID],Null) AS EquipID, IIf([Additional]=True,[Equipment_ID],Null) AS Multiple_ID, TA_SR.Job_Group, TA_SR.Project, TA_SR.Priority, TA_SR.Complete_By_Date, TA_SR.Calibration, TA_SR.Repair, TA_SR.Modification, TA_SR.Acceptance, TA_SR.Evaluation, TA_SR.Maintenance, TA_SR.Support, TA_SR.Cmis_Lab, TA_SR.Service_Lab, TA_SR.Work_Code, TA_SR.Charge_Number, TA_SR.Disposition, TA_SR.Input_Range_Min, TA_SR.Input_Range_Max, TA_SR.Input_Units, TA_SR.Output_Range_Min, TA_SR.Output_Range_Max, TA_SR.Output_Units, TA_SR.Gain, TA_SR.Cutoff_Freq, TA_SR.Input_Freq, TA_SR.Ref_Freq, TA_SR.Ref_Voltage, TA_SR.Excit_Voltage, TA_SR.Excit_Enabled, TA_SR.FTIR_Accuracy, TA_SR.Offset, TA_SR.Offset_Enabled, TA_SR.REQ_EMO1, TA_SR.REQ_EMO2, TA_SR.REQ_EMO3, TA_SR.REQ_EMO4, TA_SR.REQ_EMO5, TA_SR.REQ_EMO6, TA_SR.SpareCode, TA_SR.CALIBRATION_ID, First(TA_SR.RequestorComments) AS ReqComments, "S" AS PROCESSED_IND, Now() AS LAST_UPDATE_DATE
FROM (TA_SR LEFT JOIN tblEquipListingPerJobGroup ON TA_SR.Job_Group = tblEquipListingPerJobGroup.Job_Group) LEFT JOIN QS_TT_GeneralInfo ON TA_SR.Requestor_ID = QS_TT_GeneralInfo.RequestorId
WHERE (((TA_SR.Job_Group)=[Forms]![FE_SRForm]![JobGroup]) AND ((TA_SR.SubmittedSR)=0))
GROUP BY QS_TT_GeneralInfo.BEMS, TA_SR.FTEMNomenclature, TA_SR.NomenclatureModel, tblEquipListingPerJobGroup.MeasNo, IIf([Primary]=True,[Equipment_ID],Null), IIf([Additional]=True,[Equipment_ID],Null), TA_SR.Job_Group, TA_SR.Project, TA_SR.Priority, TA_SR.Complete_By_Date, TA_SR.Calibration, TA_SR.Repair, TA_SR.Modification, TA_SR.Acceptance, TA_SR.Evaluation, TA_SR.Maintenance, TA_SR.Support, TA_SR.Cmis_Lab, TA_SR.Service_Lab, TA_SR.Work_Code, TA_SR.Charge_Number, TA_SR.Disposition, TA_SR.Input_Range_Min, TA_SR.Input_Range_Max, TA_SR.Input_Units, TA_SR.Output_Range_Min, TA_SR.Output_Range_Max, TA_SR.Output_Units, TA_SR.Gain, TA_SR.Cutoff_Freq, TA_SR.Input_Freq, TA_SR.Ref_Freq, TA_SR.Ref_Voltage, TA_SR.Excit_Voltage, TA_SR.Excit_Enabled, TA_SR.FTIR_Accuracy, TA_SR.Offset, TA_SR.Offset_Enabled, TA_SR.REQ_EMO1, TA_SR.REQ_EMO2, TA_SR.REQ_EMO3, TA_SR.REQ_EMO4, TA_SR.REQ_EMO5, TA_SR.REQ_EMO6, TA_SR.SpareCode, TA_SR.CALIBRATION_ID, "S", Now();
View 2 Replies
View Related
Sep 13, 2007
I need to see the records of a specific date very often. I have designed a simple query (Field : My date field; Criteria : Enter the Date) and it is giving info I want. However, sometimes it shows no records making me wonder where all the data had vanished. On checking up further, I discover that the date I specified was a Sunday, a holiday, when no data entry takes place. What I now want is that if the query returns no records, it should also give a message that "the date you specified could be a holiday. please check" so that I would not panic. Is it possible?
Alternatively, the query can look up the day of the specified date and return the message. I shall be grateful for help for any of these query designs.
View 2 Replies
View Related
Nov 14, 2007
Hi,
I found a couple of short threads on the forum discussing the above. None of them go sufficiently far enough into detal to help me solve my (relatively simple) problem.
My boss has asked me to produce both mean and mode calculations for the number of pallets we ship to our branches on the 30+ routes that we service.
So far I have managed to implement mean but mode seems to have me stumped.
I'm aware there actually isn't a mode function in Access as such but like mode, is there a way to calculate the most frequent occurrence of a number over a time period?
My query is already producing Min, Max, Sum (and now mean) results for the pallets we ship, can someone offer me help on this?
All the code that I have retrieved so far seems to be based on something similar to this:
select top 1 with ties your_column
from your_table
group by your_column
order by count(*) desc
I have very little to no idea about how to implement this into my query. Any help would be appreciated.
Thanks In Advance.
sausagefingers
View 11 Replies
View Related
Mar 7, 2005
Is there any way that you can have required fields in a form, but access will not look for the required fields until you try to exit the form. Basically; i have a form that users keep track of there work hours on specific parts. they leave the form minimized for most of the day. After they enter a record, they sometimes need to go back and edit a previous record, only they are on a new record and access will not let them go to a previous record unless they have the record fields entered.
View 2 Replies
View Related
Mar 28, 2005
I have a form that looks up data through a query. The form works but after it queries and displays the data I get this error "424 Object Required" What does this mean. Here is my code if need be
Me.txtSPR = DLookup("[SPRNum]", "[qry_FIX]", "[projectname]= [cboProjectName]")
Me.TxtSystemsImpacted = DLookup("[SystemsImpacted]", "[qry_FIX]", "[projectname]= [cboProjectName]")
Me.txtReleaseDate = DLookup("[ReleaseDate]", "[qry_FIX]", "[projectname]= [cboProjectName]")
Me.CboStatus = DLookup("[Status]", "[qry_FIX]", "[projectname]= [cboProjectName]")
Me.txtCSI = DLookup("[CSIPM]", "[qry_FIX]", "[projectname]= [cboProjectName]")
Me.txtBPM = DLookup("[BPM]", "[qry_FIX]", "[projectname]= [cboProjectName]")
Me.txtImplemented = DLookup("[Implemented]", "[qry_FIX]", "[projectname]= [cboProjectName]")
Me.cboStakeholder = DLookup("[StakeHolder]", "[qry_FIX]", "[projectname]= [cboProjectName]")
Me.cboIBR1 = DLookup("[IBR1]", "[qry_FIX]", "[projectname]= [cboProjectName]")
Me.cboIBR2 = DLookup("[IBR2]", "[qry_FIX]", "[projectname]= [cboProjectName]")
Me.cboIBR3 = DLookup("[IBR3]", "[qry_FIX]", "[projectname]= [cboProjectName]")
Me.txtObjective = DLookup("[Objective]", "[qry_FIX]", "[projectname]= [cboProjectName]")
Me.txtSMERequirements = DLookup("[SMERequirments]", "[qry_FIX]", "[projectname]= [cboProjectName]")
Me.cboPhase = DLookup("[Phase]", "[qry_FIX]", "[projectname]= [cboProjectName]")
Me.txtMemo1 = DLookup("[Memo1]", "[qry_FIX]", "[projectname]= [cboProjectName]")
Me.txtdate1 = DLookup("[date1]", "[qry_FIX]", "[projectname]= [cboProjectName]")
View 1 Replies
View Related
May 8, 2006
Hi. I want to have all my fields on my form to be required and so that the user cant close the form if they are empty (error message). In the table properties I have them set to Required an have tried putting in a validation rule of IS NOT NULL but it still dont work. any help appreciated. thanks
View 1 Replies
View Related
Nov 27, 2007
Dear All
I am a user of Access 2007. I have desinged a access tabel
that have these fields.
Gross amount Discount Net amout
25000 2000 23000
Then I design a form using form wizard.The form have desined easily and when I enter data in form fields the records
were updated in the tabel.But when I use the Expression
builder with this formula in Net Amount Text Box.[Gross Amount]- [Discount] and open the form and enter data in
Gross amount and discount field, It shows result in Net amount field but not updated the Access table.This is the problem.
If any one help me, I will be higly thankful to him
Regards
Moon
View 5 Replies
View Related
May 11, 2005
Hi all,
Hope this is the correct forum, and I am not breaking any rules of etiquette. :rolleyes:
I am after some guidance on how to start to tackle a problem, rather than any advice on the details of how to do it. That I want to work out for myself in order to try and improve my mastery of Access.
I have a team responsible for completing a number of scheduled reports.
These reports are scheduled for different frequecies ie :-
some are scheduled Weekly on a particular day,
some are scheduled Monthly on a particular date,
some are scheduled Monthly say on the 1st Monday of the month.
My goal, amongst others, is to be able to, from the switchboard,:-
a. Print a list of reports scheduled to run in the current work week.
b. Print a list of reports scheduled to run between any 2 inputted dates.
This is just part of what I want to acheive from this database, but as we all know Rome wasn't built in a day!! ;)
Grateful for any help / advice / directions,
Cheers,
fos605
View 1 Replies
View Related
Jun 24, 2005
hi. I am trying to write a code to sum the total cost of all jobs that are selected as 'yes' in a combo box.
Once the user selects 'yes' in a combo box, they enter in a cost for that specific job. Each client may have 10s of jobs. I need to write a code that will sum up all the 'yes' selected jobs for a client. I know it should be an 'if' statement but I can't seem to get it to work. I need this value for a report. How would I do this and where would i put the code.
Help is appreciated.
View 1 Replies
View Related
