When I try to open the recordset with this, it says there is an error in the WHERE clause. I've tried changing all sorts of things but nothing works. I've tried with the WHERE clause commented out and it all works fine then.
I have a listbox that is populated with data from a table. I would like to use one of the columns data as the where clause of a sql string that will populate another form, how do I get the selected items column data that I need into a variable? This is in Access 2010 vba.
I was looking for some help. I am trying to setup a table with a field for web address. People are entering www.website.com etc however I need them to make sure it starts with http:// Is their any way I can put validation on the field to make sure that this is entered? Or maybe I could use an input mask?
and i needed a return (WHERE clause) when job_id is the same (i.e 2), and the crew_id is repeated like 4,4 and 33, how would i write it. I am trying to only sum the rows when this condition is fulfilled. Can anyoen help me
Could anyone explain me the following sysntax : ... where ((cond1 and cond2) and (fld1<>'54')<>'60') the syntax did not return any error while executing the query.
I have an select statment that pulls data from a server. This server is really slow, and there is quite a bit of data. This statement has the following where clause.
"WHERE upc_number = '" & strPikWhere & "' AND ((dbo_root_description_type.description_name)='PFM S' Or " & _
"(dbo_root_description_type.description_name)='Full tech') AND ((dbo_root_price.zone) Not Like '8*') " & _
I was wondering if the order of these where statements would make a difference, and if so how?
I have a query using the between clause with dates #01/01/2006# and #01/01/2007#. I have been reading a little on the between clause and it differs from SQL program to SQL program. What are its characteristics in MS Access is it >=#01/01/2006# AND <=#01/01/2007# or >=#01/01/2006# AND <#01/01/2007#.
When I do a Debug Print for where I get cardnumber = 1111111 The card number is a string because it might have a letter in it don't i need it to be cardnumber = "1111111"
I have a table "addresses" that has the following fields....... id - type - street1 - street2
and type can be one of the follwoing graduation,home,work
I am trying to write a query that gives the graduation address if it exists or if graduation doesnt exist it gives the home address..... I was thinking of an If clause but do not know how to do it.... can i write a module...??? Any help on this is appreciated..... Any other method also can be suggested....
In one of my queries, I'd like to reuse a calculated field as criteria in the Where clause. SQL seems reluctant to want to do this. You can RESTATE the calculation in the Where clause, but can't refer to it by Name. You can Order by it, but it can't be in the Where clause.
If the query result set consists on a set of Named columns, why can you refer to a calculated column by Name in the "Order By" clause but not in the Where clause?
Why is this OK?
Select FldA As FirstField, FldB As SecondField, FldA - FldB As TheDifference <-- Just a calculated field From SomeTable
And this is OK?
Select FldA As FirstField, FldB As SecondField, FldA - FldB As TheDifference From SomeTable Order By TheDifference <-- Order By is OK
And this in not OK?
Select FldA As FirstField, FldB As SecondField, FldA - FldB As TheDifference From SomeTable Where TheDifference > 100 <-- Can't use TheDifference in Where clause
Don't want to repeat the calculation.
Select FldA As FirstField, FldB As SecondField, FldA - FldB As TheDifference From SomeTable Where FldA - FldB > 100 <-- I know this works, but ... Order By TheDifference
hi, is it possible to have a form where you enter a word in a text box and it searchs a table and brings up all records which match it, but it doesnt have to be an exact match.
Basically, my database holds records of sites in the UK, and I have a select query (qry_SumGIS3) that opens up a summary report (rptSumGIS3) when a button is clicked on the menu page. The report opens perfectly, showing me all the information I need to know about all the sites in my database. However, it is ignoring the Order By Clause I have specifically told it to do. I want to order the records that appear in the report by a calculated field (OBJ_WS_OVERALL_CALC) in descending order (highest to lowest percentage). Instead, it is ordering it by the site id (RTP_ID). How can I stop it doing this?
This is my code (that was created in the SQL view of the query design): "SELECT MEASURE1.OBJ_WS_OVERALL_CALC, [Tab 1: Factual].RTP_ID, OBJECTIVES.OBJ_ID, OBJECTIVES.OBJ_NAME, MEASURE1.MEASURE_ID, MEASURE.MEASURE_NAME, MEASURE1.MEASURE_SCORE, MEASURE1.MEASURE_WEIGHT FROM [Tab 1: Factual] INNER JOIN (OBJECTIVES INNER JOIN (MEASURE1 INNER JOIN MEASURE ON MEASURE1.MEASURE_ID = MEASURE.MEASURE_ID) ON OBJECTIVES.OBJ_ID = MEASURE.OBJ_ID) ON [Tab 1: Factual].RTP_ID = MEASURE1.RTP_ID ORDER BY MEASURE1.OBJ_WS_OVERALL_CALC;"
The red line is the part of the code that I think is being ignored.
I have the following SQL statement. The COUNT(b.BookingID) AS TB, gives me the number of bookings. I need to ensure that the number of bookings + PrebookCapacity is less than v.Capacity. I do this by:
COUNT(b.BookingID) AS TB + PrebookCapacity < v.Capacity in the WHERE CLAUSE. But when I run this in MS Access 2003, it says Enter Parameter Value for b.TB (It does not recognise b.TB as b.TB is not a table field). If I omit AND ((b.TB + PrebookCapacity < v.Capacity AND SessionStatus = 1) OR (b.TB < v.Capacity AND SessionStatus = 0)), from the WHERE clause, it works fine, also giving me the TB.
As I need to do my check in the WHERE clause, how can I check to make sure that the COUNT(b.BookingID) AS TB + PrebookCapacity is < v.Capacity, without any problems.
SELECT CourseName, Format([s.SessionDate]+[s.StartTime],"dd/mmm/yyyy hh:ss AM/PM") & " - " & Format([s.EndTime],"hh:ss AM/PM") AS [DateTime], s.SessionID, PrebookCapacity, v.Capacity, COUNT(b.BookingID) AS TB FROM tblCourses AS c, tblSessions AS s, tblVenues AS v, tblbookings AS b, qrySessionsAccepted AS q WHERE c.CourseID = s.CourseID AND s.SessionID = 85 AND q.SessionID = s.SessionID AND b.SessionID = s.SessionID AND v.VenueID = s.VenueID AND ((b.TB + PrebookCapacity < v.Capacity AND SessionStatus = 1) OR (b.TB < v.Capacity AND SessionStatus = 0)) GROUP BY CourseName, Format([s.SessionDate]+[s.StartTime],"dd/mmm/yyyy hh:ss AM/PM") & " - " & Format([s.EndTime],"hh:ss AM/PM"), s.SessionID, PrebookCapacity, v.Capacity;
Hi I'm new to query writing and I'm needing help with a query that will search a field that either contains a name or an address and only return fields with addresses.
Select address1 From Booktable, Where (Left([address1],1) = Like "[A-Z]"
SELECT Field1, Field2 from table1 where table1.Field2 in (1,2,3)
and now i need to parameterize this query.... I tried with
SELECT Field1, Field2 from table1 where table1.Field2 in ([Parameter])
Now the problem is... it is working fine with only one value For example if I input 1, it provide the required result. however, if I input 1,2 it doesnt provide a single recordset
I changed the datatype of parameter from number to text and also tried different way of inputting number like "1,2" , 1,2, but still problem is the same...
Can someone please guide me where I am going wrong
This probably sounds stupid but I need to ask because well, I'm stupid. I have created a separate table of partnumbers that DO NOT get included in a SELECT Query for another table (tblmain). How can I use this table in my WHERE clause? Below is my SQL statement and the table with the partnumbers is tblreportpartnumbers.
strSQL = "SELECT SUM([Yield]) AS [My Sum] FROM tblmain" strSQL = strSQL & " WHERE(([Area]='Cell 1 Supfina' AND " strSQL = strSQL & "([Date entered]=#" & dtmdate & "#" strSQL = strSQL & " AND([Part Number]=???????)))" strSQL = strSQL & " Group By [Date entered];"
It all works pretty much fine, however, the problem occurs because the iif statement tells the code to use either the fixed_price_list table or the price_list table depending on the value of Use_fixed. the iif works fine, its the WHERE clause. because the iif statements tell the query to look at one table or the other, the WHERE clause includes statements from both tables e.g.
queryString2 = queryString2 & " AND price_list.min_quantity <= part_select.quantity " queryString2 = queryString2 & " AND fixed_price_list.min_quantity <= part_select.quantity "
but some parts dont have information within the fixed_price_list table, and thus are not being found in the results due to the WHERE clause. can anyone help?? i tried using a left join but it didnt work how i wanted. in a perfect world i would be able to include a iif statement in the where clause e.g.
queryString2 = queryString2 & " iif Use_fixed =1, AND price_list.min_quantity <= part_select.quantity, AND fixed_price_list.min_quantity <= part_select.quantity) "
OK I have code to make a where clause this is working fine. Now I have to add another piece of code to filter it even more, this is where I am having trouble
Code that works fine
'construct a where clause as required for the list box 'if the Manufactur box is blank then all games will show up on the list If Mfg = "0" Then Mfg = "*" If Denom <> "" Then where = "DenomFix LIKE '" & Denom & "*' AND " If Mfg <> "" Then where = where & "MFRCode LIKE '" & Mfg & "' AND " If Search <> "" Then where = where & "Description LIKE '*" & Search & "*' AND " 'this will filter the list with only the approved games Dim Aprvd As String Aprvd = "Approved" If Me.AprrovedCheck = True Then where = where & "Approved LIKE'" & Aprvd & "' AND "
'remove the trailing "AND ", if it exists If Right(where, 4) = "AND " Then where = Left(where, Len(where) - 4) 'and if there is any text in the where clause, add the word 'WHERE ' If where <> "" Then where = "WHERE " & where 'apply the SQL to the rowsource of the List box to Pick a game
Me.List8.RowSource = _ "SELECT Approved, ReelStops As [Corp ID], DenomFix as Denom, Description As Theme, Par, MaxCoins As [Max Coin], PayLines As [Pay Lines]" & _ "FROM MachineTypeQuery " & _ where & _ "ORDER BY Description;"
Now what I need to add code to make a where clause that says something like DVIA is greater then 0 (DVIA >0) but I want to do this by its self and add it back into the where clause
Example Private Sub ApplyPropertyWhere() If PropertyGlobal = "DVIA" Then PropertyWhereGlobal = "DVIA > 0"
I would then put a line of code in the where clause like this 'This will filter the list with only the property they are from If Me.PropertyCheck = True Then where = where & PropertyWhereGlobal
First I just designed my first database and all seems to work fine except that my if I try and use a where clause in my query it won't work. Example below:
This query works fine but as soon as I add this it won't work. SELECT Assets.EmployeeName FROM Assets
SELECT Assets.EmployeeName FROM Assets WHERE Assets.EmployeeName='Lucky Kruse';
If needed I can send the database as I really need help on this. Thanks for the help.
SELECT tblRelease.ID, tblRelease.Application, tblRelease.Date, DateAdd("d",tblRelease.DateVariance,tblRelease.Date) AS ToDate, tblRelease.Time, tblRelease.Description FROM tblRelease WHERE (DateAdd("d",tblRelease.DateVariance,tblRelease.Date)>=[Enter Effective Date]) ORDER BY tblRelease.Application, tblRelease.Date;
I've chopped out a few fields to make it a bit smaller and easier to read.
The problem is that whilst the DateAdd() in the SELECT clause produces exactly the date that I want, the DateAdd() in the WHERE clause does not seem to work. I always get all records no matter what date I enter for [Enter Effective Date]. If I change the WHERE clause to
WHERE (tblRelease.Date>=[Enter Effective Date])
then everything works perfectly and I get different numbers of records depending on the date I enter.
I am entering the date in the form 01/08/2005 and this works for the second variation just fine. It's dd/mm/yyyy in case that's important (my local default).
I was wondering if anyone can help me: I have for example Pupils that are being entered into a table (called log). A query then counts the number of entries for each pupil I then want it ONLY to show the pupils that have been entered in 10 times on todays date (using DATE()). I know this doesnt work but this is what I want to do:
SELECT log.pupilNo, log.Forename, log.Surname, Count(log.pupilNo) AS Demerits FROM log, Tally WHERE (log.Date)=Date() AND COUNT(log.pupilNo)>9 GROUP BY log.pupilNo, log.Forename, log.Surname, log.pupilNo;
Apparenty you cannot have an Aggregate function in a where clause. Does anyone know a solution, any help would be appreciated Cheers Bikeboardsurf
