SELECT (Max(ReferenceDescription.ReferenceID))
FROM (ReferenceDescription)
WHERE (ReferenceDescription.ReferenceID >= (ReferenceDescription.CategoryID * ReferenceDescription.DepartmentID) AND ReferenceDescription.ReferenceID <= (INT(ReferenceDescription.CategoryID * ReferenceDescription.DepartmentID * 0.9999))
SELECT Fields
FROM table1 RIGHT JOIN table2 ON table1.Pubid = table2.PubID
WHERE (table1.PubType=[Forms]![frmCirculationByTitle]![comMemberType] Or [Forms]![frmCirculationByTitle]![comMemberType] Is null)
AND (table2.lAuditPeriod=[Forms]![frmCirculationByTitle]![comStartPeriod] Or [Forms]![frmCirculationByTitle]![comStartPeriod] IS null)
And (table2.lAuditPeriod=[Forms]![frmCirculationByTitle]![comEndPeriod] Or [Forms]![frmCirculationByTitle]![comEndPeriod] is null)
AND (table2.Unfinancial=[Forms]![frmCirculationByTitle]![chkCurrent] Or [Forms]![frmCirculationByTitle]![chkCurrent] IS null);
even if i only have one parameter (a member type, which should bring back at least 20 for each type of member) it returns nothing. if i do the same sql statement in a query and hard codee the value in i get data. it seems to be ignoring whatever is on the form.
*shrug*
%simon
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 RelatedHi,
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?
I am trying to do a partial duplication query. this is the code from the SQL view in accessSELECT filtered1.Company_NameFROM filtered1WHERE (((filtered1.Company_Name) In (SELECT [Company_Name] FROM [filtered1] As Tmp GROUP BY [Company_Name], Left([Company_Name],7) HAVING Count(*)>1 And Left([Company_Name],7) = Left([filtered1].[Company_Name],7))))ORDER BY filtered1.Company_Name;the table filtered1 has 1300 records, under the field Company_Name.the 7's apparently should get it to compare the first 7 letters, and if the same show me them. A whats going wrong, its not showing me any, asthough there are none, (which i know there are) and B. How would i get it to compare the first 7 letters across tables, with fields of the same name?thanksalex
View 3 Replies View RelatedPrivate Sub Save_Click()
Dim sSemester As String
sSemester = Semester1.value
DoCmd.RunSQL "INSERT INTO Hours (Semester) " & _
" VALUES('" + [sSemester] + "'); "
End Sub
keep getting syntax error
basically Semester1 is a text box with an input mask : LLL???" '"00;0;
"Microsoft JScript runtime error 'null' is null or not an object line = 126, col = 7 (line is offset from the start of the script block). Error returned from property or method call." what does that mean?
View 1 Replies View RelatedAs state above that whats wrong with the following formula?
=iff([Product]=Home,1,0)
:confused:
trying to create a table using a query, and I'm not sure whats wrong with this query:
CREATE TABLE FYIAdmin
(
FYI_ID COUNTER CONSTRAINT PRIMARY KEY,
FYI_Title varchar(100),
FYI_Text memo,
FYI_Date date
)
Sorry it its noobish :(
I placed a combo box named CboSearchItem on form to select and bring the record onto the form. Its working smoothly.
Set rst1 = CurrentDb.OpenRecordset("Select * From Product_Master " & _
"where Productname= " & """" & CboSearchItem & """")
But when data in field 'ProductName' contains " mark, it produces error 3075 :
Syntax error in string in query expression "ProductName = "Hose White 3/4"'"
When I replaced all " mark from the data / record, it works fine.
For example Product Name are as follows:
Elbow 2 Way
ABC Pipe 3"
Shower Pipe 1 1/5 Meter
Reading all the above record but not the second one because it contains " mark.
I can not tell my client not to use " symbol while entering the product name. There should be a solution.
Please extend your help.
With kind regards,
Ashfaque
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....
CurrentDb.Execute "INSERT INTO tblArchivepayment
(EntryDate,MemberID,PCNumber,ApplicationType,Timei n,TimeOut,UsedTime,
Amount,PaymentType) SELECT (EntryDate,MemberID,PCNumber,ApplicationType, TimeIn ,
TimeOut, UsedTime,Amount,PaymentType) FROM tblPayment WHERE MemberID = '" & MemberID & "'"
i got syntx in comma error
In the following if...then statement, even though the Debug.Print prints a "Null"
the Else part of the statement is executed. Any help is appreciated
Debug.Print Forms!frmReport!txtCustom1
'if id combo box is null then
If Len(Forms!frmReport!txtCustom1) = Null Then
Debug.Print "Then"
'Filter Null strCompanyName records
rst.Filter = "strCompanyName <> """""
Else
Debug.Print "Else"
' filter on portfilio id
rst.Filter = "strJobID = """ & Forms!frmReport!txtCustom1 & """"
End If
Output:
Null
Else
I can't seem to get this to work. This issue was addressed in a previous thread, but it's not working for me. Help
Private Sub append_data_Click()
Dim strSQL As String
DoCmd.SetWarnings False
strSQL = "INSERT INTO sob_org_item_Mon11_Step1 ( ORG, Item, [Set of Books] )" & _
"SELECT [Mon10 Perpetual 01400 Qty_Amt].ORG, [Mon10 Perpetual 01400 Qty_Amt].Item, SOB_FX_Rates.[Set of Books]" & _
"FROM SOB_FX_Rates RIGHT JOIN [Mon10 Perpetual 01400 Qty_Amt] ON SOB_FX_Rates.Org=[Mon10 Perpetual 01400 Qty_Amt].ORG" & _
"GROUP BY [Mon10 Perpetual 01400 Qty_Amt].ORG, [Mon10 Perpetual 01400 Qty_Amt].Item, SOB_FX_Rates.[Set of Books];"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
I've tried this IIF statement in both a report and a query with the same result. I have another one that is almost identical that is correct. Here is the IIF in my query:Section 3 Total T&E: IIf([Section 3 Subotal]>"6000","$6000.00",[Section 3 Subotal])Here are the results:Section 3 Subotal -- Section 3 Total T&E$4,727.20 -- 4727.2$12,064.13 -- 12064.13As you can see, the results are not correct since 12,064.13 is greater than 6000.Any idea what I'm doing wrong?Thanks for your help!Mare
View 6 Replies View RelatedCREATE TABLE Jobs
(jobname LONGTEXT(1000) NOT NULL, [desc] LONGTEXT(1000) NOT NULL,
[req] LONGTEXT(1000) NOT NULL, [specialreq] LONGTEXT(1000) NOT NULL)
Thanks!
Good evening
I am trying to write an sql statement but its not producing the results I want.
This is the statement I am trying to use.
SELECT [Table Jan 1999].[Bkg No], [Table Jan 1999].[Reporting To], [Table Jan 1999].[Start Date], [Table Jan 1999].[End Date], [Table Jan 1999].[Reason for Booking], [Table Jan 1999].[Temp Name], [Table Jan 1999].[Cost Code], [Table Jan 1999].[Assignment Job Title], [Table Jan 1999].Department, [Table Jan 1999].Status
FROM [Table Jan 1999]
WHERE ((([Table Jan 1999].[End Date]) Is Null)
AND (([Table Jan 1999].[Cost Code])="DE600149"
Or ([Table Jan 1999].[Cost Code])="DG6022820" Or ([Table Jan 1999].[Cost Code])="600414") OR ((([Table Jan 1999].[Cost Code])="DG6022820"))
OR ((([Table Jan 1999].[Cost Code])="600415")) OR ((([Table Jan 1999].[Cost Code])="DG6022821")) OR ((([Table Jan 1999].[Cost Code])="DG6022832"))
OR ((([Table Jan 1999].[Cost Code])="600421")) OR ((([Table Jan 1999].[Cost Code])="600432")) OR ((([Table Jan 1999].[Cost Code])="600433"))
OR ((([Table Jan 1999].[Cost Code])="600435")) OR ((([Table Jan 1999].[Cost Code])="600576")) OR ((([Table Jan 1999].[Cost Code])="600713"))
OR ((([Table Jan 1999].[Cost Code])="600724")) OR ((([Table Jan 1999].[Cost Code])="600965")) OR ((([Table Jan 1999].[Cost Code])="602716"))
OR ((([Table Jan 1999].[Cost Code])="602741")) OR ((([Table Jan 1999].[Cost Code])="602744")) OR ((([Table Jan 1999].[Cost Code])="602788"))
OR ((([Table Jan 1999].[Cost Code])="602789")) OR ((([Table Jan 1999].[Cost Code])="602810")) OR ((([Table Jan 1999].[Cost Code])="602811"))
OR ((([Table Jan 1999].[Cost Code])="602821")) OR ((([Table Jan 1999].[Cost Code])="602835")) OR ((([Table Jan 1999].[Cost Code])="602949"))
OR ((([Table Jan 1999].[Cost Code])="602969")) OR ((([Table Jan 1999].[Cost Code])="602980")) OR ((([Table Jan 1999].[Cost Code])="602988"))
OR ((([Table Jan 1999].[Cost Code])="602991")) OR ((([Table Jan 1999].[Cost Code])="603005")) OR ((([Table Jan 1999].[Cost Code])="500DW"))
OR ((([Table Jan 1999].[Cost Code])="500KP")) OR ((([Table Jan 1999].[Cost Code])="500LG")) OR ((([Table Jan 1999].[Cost Code])="501TE"))
OR ((([Table Jan 1999].[Cost Code])="502M3")) OR ((([Table Jan 1999].[Cost Code])="502Q0")) OR ((([Table Jan 1999].[Cost Code])="504KU"))
OR ((([Table Jan 1999].[Cost Code])="505BG")) OR ((([Table Jan 1999].[Cost Code])="506J1")) OR ((([Table Jan 1999].[Cost Code])="506J2"))
OR ((([Table Jan 1999].[Cost Code])="506J4")) OR ((([Table Jan 1999].[Cost Code])="506J5")) OR ((([Table Jan 1999].[Cost Code])="507FQ"))
OR ((([Table Jan 1999].[Cost Code])="507GC")) OR ((([Table Jan 1999].[Cost Code])="507Q4")) OR ((([Table Jan 1999].[Cost Code])="508SR"))
OR ((([Table Jan 1999].[Cost Code])="509Q5")) OR ((([Table Jan 1999].[Cost Code])="509RM")) OR ((([Table Jan 1999].[Cost Code])="DG2820")));
My problem is with this part
WHERE ((([Table Jan 1999].[End Date]) Is Null)
I thought by having Is Null would give me only results that have no value, But....when I run the query I am getting results with values.
Can anyone tell me what I am doing wrong?
TIA
ok, not sure if this belongs here on the the code help bit so I've posted it in both.
my code reads as follows:
Private Sub cmdNextStep_Click()
If PageIndex = 0 Then
pgeStepTwo.Visable = True
pgeStepTwo.SetFocus
pgeStepOne.Visable = False
Else
pgeStepThree.Visable = True
pgeStepThree.SetFocus
pgeStepTwo.Visable = False
End If
End Sub
Now the button works perfectly to go from Step One to Step Two but it will not go from Step Two to Step Three.
I've also tried "else if page index = 1" but still nothing happens when I click the button.
Any help will be much appreciated.
Katrina
Hi I had been trying to use the "Select" statment of the sql in Access.But how do I actually execute the sql command?
For example,
Dim sql As String
sql = "select * from Name"
What is the next line i should add to execute this in Access?
Thanks
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.
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. ....................................
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
Hello,
I have a database which every week I have to import an excel spreadsheet. Now this spread sheet will always have rows that repeat and will have somewhere between 700-1500 rows.
What is my best option to delete the rows that repeat? Meaning if a rows 1-10 have the same data then delete 9 of them. It will vary but I only need the unique record and the others can be deleted.
Would this be best way using a query? VB or something else?
There are 6 columns that will always be in the spreadsheet with the same column names:
ACCT_NUMBER
SHORT_ACCOUNT_TITLE
CONTACT_COMMENTS
CONTACT_TYPE_TEXT
ENTERED_BY
INITIAL_CONTACT_DATE
DATE_ENTERED
There will be rows that will repeat on every spreadsheet everytime. The only column that will not repeat will be CONTACT_COMMENTS. This column is needs to be deleted everytime.
I guess I didnt know if when I import the spreadsheet if something was possible that would:
Delete the CONTACT_COMMENTS then delete the duplicate rows upon importing or even after since this will have to be done everytime.
Thanks.
After being a member on this site for a while now I would like to get some opinions on "cost of application development" I know the forum covers a wide geographic area and the members cover a wide spectrum of degrees of expertise on Access and application development. So the question is this..What would your rates be and how would you determine your charges for developing Access applications for a business?PS... Please include your location if not in your profile.
View 13 Replies View RelatedIf I am going to get a Invoice/Statement programme made for me should it be in MS Access or Visual Basic, Thanks for your thoughts, Bob Vance
View 2 Replies View RelatedHello,
Dont understand what is controlling these decimals. I dont need any decimals for this database. The currency is talking bout money in 1000's and the probability is whole numbers.
Here is where I cant get the decimals to stop:
http://img80.imageshack.us/img80/3685/untitled2pl2.png
http://img77.imageshack.us/img77/6276/untitled3pa6.png
http://img208.imageshack.us/img208/2047/untitled3lk8.png
I have tried different things but no luck...anyone have any suggestions.
Thanks.