Whats Wrong With This Sql Statement?
Sep 1, 2005
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
View Replies
ADVERTISEMENT
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
Jul 3, 2007
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 Related
Jan 19, 2005
Private 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;
View 6 Replies
View Related
Feb 25, 2005
"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 Related
Oct 23, 2005
As state above that whats wrong with the following formula?
=iff([Product]=Home,1,0)
:confused:
View 2 Replies
View Related
Aug 15, 2006
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 :(
View 2 Replies
View Related
Jul 11, 2006
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
View 6 Replies
View Related
Sep 30, 2005
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
View 14 Replies
View Related
Oct 12, 2004
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
View 1 Replies
View Related
Feb 3, 2006
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
View 1 Replies
View Related
May 18, 2007
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 Related
Jan 10, 2006
CREATE TABLE Jobs
(jobname LONGTEXT(1000) NOT NULL, [desc] LONGTEXT(1000) NOT NULL,
[req] LONGTEXT(1000) NOT NULL, [specialreq] LONGTEXT(1000) NOT NULL)
Thanks!
View 1 Replies
View Related
Jun 13, 2007
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.
View 4 Replies
View Related
Jan 2, 2006
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 Related
Aug 30, 2004
If 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 Related
Jan 5, 2007
Hello,
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.
View 10 Replies
View Related
Aug 17, 2007
I need to store a question like...
I felt scared...
[]Before I was on the roller coaster
[]When I was on the roller coaster
[]After I was on the roller coaster
I need to be able to query/search the database for people that were scared "before", "during", "after", "before and during", "before and after", "during and after". Then I can categorize these people. What would be the best way to do it? Should I have 3 "yes/no" fields in my table, or one text field with those 3 as the lookup field? What one would make organizing the people easier? :D Thanks!
View 7 Replies
View Related
Aug 17, 2007
I need to store a question like...
I felt scared...
[]Before I was on the roller coaster
[]When I was on the roller coaster
[]After I was on the roller coaster
I need to be able to query/search the database for people that were scared "before", "during", "after", "before and during", "before and after", "during and after". Then I can categorize these people. What would be the best way to do it? Should I have 3 "yes/no" fields in my table, or one text field with those 3 as the lookup field? What one would make organizing the people easier?
View 1 Replies
View Related
Jan 25, 2007
Hello,
My database is due tommorrow and my switchboard has gone crazy on me, it doesnt work anymore. When you scrolll your mouse it then goes through the links thats the only way to get to the section you want.
So question is this, whats the advantage of using it?
Cant I just set some hyperlinks to the items I want and it would be the same. Wondering cause dont know if I should spend time trying to fix it or just make hyperlinks...and its due tommorrow.
Thanks for any opinions.
View 12 Replies
View Related
Jun 22, 2006
Hi people,
Does anyone knows the difference between frontend and backend database? I look 4ward to hearing from any database expert.
black85
View 1 Replies
View Related
Feb 12, 2014
I have the following Select Statement:
SELECTTenant.ID, Tenant.[First Name], Tenant.[Last Name], Tenant.Address, Tenant.City, Tenant.State, Tenant.Zip, Tenant.[Home Phone], Tenant.[Cell Phone], Tenant.[Work Phone], Tenant.[Rented Unit],
Tenant.[Security Deposit], Tenant.[Move In], Tenant.[Move Out], Tenant.TenantID, Tenant.UnitID, Tenant.PropertyID, Tenant.OwnerID, Owner.Company, Owner.ID AS Expr1, Property.[Property Address],
[code]....
Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?
View 2 Replies
View Related
Oct 31, 2014
I have a query with the following criteria in one of the fields:
>=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom())
fom is a function for first of the current month. I need this query to be specific to what month it is when its ran so i want to only have this criteria if the month is > = october. If it isnt October or greater, i want the criteria to reflect this.
>=DateAdd("m",-12,fom()) And <=fom()
Which also works by itself. But when i add it to an iif statement it always produces no results. Below is the iif statement.
Iif(month(date())>=10, >=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom()),>=DateAdd("m",-12,fom()) And <=fom())
I have also added the column name to each expression and it still doesnt produce any results.
View 4 Replies
View Related
Apr 28, 2015
I am creating a multi-search form for a student database, where after I enter my search criteria I hit a "Run Query" command button and then it opens a query form with all of my criteria.So far I can search using last name, first name, and middle name. When I try to search with a start date and end date I am have issues.The start date and end date is for the class date. In the query form under the field, class date, for criteria I wrote:
Between IIf([Forms]![Search Form]![Start Date]="",1/1/10,[Forms]![Search Form]![Start Date]) And IIf([Forms]![Search Form]![End Date]="",4/25/15,[Forms]![Search Form]![End Date])
I want it when I write a date in the start date and end date I want it to give me a list of all the students who took the course between those dates. Also, if I leave the dates blank I want it to search all dates. The dates 1/1/10 and 4/25/15 are just the dates I gave because that is far back as my database goes.
View 11 Replies
View Related
Sep 21, 2005
Ok Guys,
Here goes...
all of a sudden now, when i open my form, its not displaying any of the previous data that i stored in it!! All the data is still in the table, and when i add data it goes into the table!! I checked properties and the record source and everything is ok there, what have i done wrong guys...HELP!!!!
View 3 Replies
View Related
Jun 9, 2006
I purchased a copy of Office 2003, on Ebay, for less than half the price it normally sells for. I created databases, which work well on my computer. However, when I copy them over to the office computer from my computer, via Flash memory stick, the program does not run. The message seems to indicate a problem with registration. When I installed Access, it asked me for a registration key, which I did provide. After that it worked-not activation process. Does this mean that you can only use the computer you created the database on to run it, or that I do not have a proper version of Access 2003? I cannot transfer, from my home computer, to the office computer. Is this Microsoft paranoia again?
Subsequent uninstalls and reinstalls of the program did not ask for the installation key.
What should happen? I would like to resolve this major problem and your help will be greatly appreciated. The program is flaky. Also, Switchboard does not work-must use Option Box.
These questions most user must know about initial installation and copying mdb files to other computers -please reply.
View 2 Replies
View Related
