Query Works But Same Where Condition Does Not With OpenForm

Aug 23, 2013

I am creating a search function to search a name. The DB stores it as [First Name] , [Last Name].... This query works

Code:
SELECT *
FROM tblVolunteers
WHERE ((([First Name] & " " & [Last Name]) Like "*bob j*"));

It will show me records for Bob Johnson, Bob James and Bob Jones....

I am trying to duplicate this with a DoCmd.OpenForm statement... Replacing the static name with a field variable...

Code:
Dim whereClause As String
whereClause = "[First Name]" & " " & "[Last Name] Like " '*" & volunteerName & "'*""
DoCmd.OpenForm "Volunteers", acNormal, , whereClause, , , False

View Replies


ADVERTISEMENT

Where Condition Of OpenForm Command Not Working

Jan 19, 2005

The scenario is... also apologies if this seems a lot. Simple really.

An edit button on a subform is to open a separate form for editing, and I obviously want to apply a where condition to display the relevant record. So I have

DoCmd.OpenForm "editReviewPEST", acNormal, , ReviewID=Me.ReviewID, acFormEdit, acWindowNormal, False

The Record Source for editReviewPEST uses an INNER JOIN...

SELECT Reviews.Year, Reviews.ReviewID, Reviews.LesseeID, Lessee.DateOfOrigSanction, Reviews.Exposure, Reviews.ExpoCurrency, Reviews.SentToCredit, Reviews.Approved, Reviews.Completed, Reviews.Comments, Reviews.LevelOfLastSanction, Reviews.CreditManager, Reviews.TypeOfReview, Reviews.DateOfLastCreditReview, Reviews.DateOfNextCreditReview FROM Lessee INNER JOIN Reviews ON Lessee.LesseeID = Reviews.LesseeID WHERE (((Reviews.LesseeID)=[Forms]![Reviews]![SelectLessee])) ORDER BY Reviews.ReviewID DESC;

So I get the form to open with all the records relvant to the lessee, but to filter it further to exact ReviewID, I thought I could include ReviewID=Me.ReviewID (an Autonumber PK) as the Where Condition to the DoCmd.OpenForm. Instead I get a form which looks like Add Record Form.

View 4 Replies View Related

Subquery Works Alone, But Not With Query.

Oct 4, 2006

Hi,

I have written a subquery that works fine alone, but it returns -1 when with query.

The subquery is supposed to return a total of type currency.

Any help/suggestions will be very much appreciated.

SELECT tblSite.Name, tblPhase.Phase_No, tblVariation_Order.Customer_No, Exists (SELECT Sum(tblVariation_Order.VO_Price)
FROM tblCustomer INNER JOIN tblVariation_Order ON tblCustomer.Customer_No=tblVariation_Order.Custome r_No
GROUP BY tblCustomer.Customer_No;) AS Expr1
FROM (tblCustomer INNER JOIN tblVariation_Order ON tblCustomer.Customer_No = tblVariation_Order.Customer_No) INNER JOIN (tblSite INNER JOIN (tblPhase INNER JOIN tblHouse ON tblPhase.Phase_No = tblHouse.Phase_No) ON tblSite.Site_No = tblPhase.Site_No) ON tblCustomer.Customer_No = tblHouse.Customer_No
WHERE (((Exists (SELECT Sum(tblVariation_Order.VO_Price)
FROM tblCustomer INNER JOIN tblVariation_Order ON tblCustomer.Customer_No=tblVariation_Order.Custome r_No
GROUP BY tblCustomer.Customer_No;))=True));

View 5 Replies View Related

Query Works But With Errors?

Jun 25, 2007

I've got a number of different append and delete queries running on command on one of my forms, in which it makes a copy of all of the data on that form and included subforms, copies them into another table, and then deletes all of the data from that record. When this runs, I get an error saying "record deleted" and then another error message. Now it is deleting the records and it is moving them, however when i go onto one of the subforms, all the fields in the subform show "Deleted#" until i go back to the previous record or forward to the next, then they clear, but everytime it brings up the other error message it says "end or Debug" which I don't want it doing.

Is this the query? Or the coding?

Thanks

View 7 Replies View Related

Query Only Works Through Clicking On Icon

Oct 1, 2007

i posted about this the other day, thought i sorted it and now it keeps coming back to haunt me

i have an append query in my database it is based on 3 other queries

when the query is run using vb

DBEngine(0)(0).Execute "QRY_ImportDuplicatesTopLevel", dbFailOnError

or by putting its sql into a string and executing using CurrentProject.Connection.Execute strSQL

i get an error message saying too few paramters - expected one

i'm fairly used to these messages which mean that in the query is a non existent or misspelt field and i find them by running the query in the query viewer and letting access highlight the missing / misspelt field

the weird part is that when i run this query as a stored object by clicking on its icon i get no error and the query does the job it is supposed to do

does anyonw have any idea why this situation could occur?

View 4 Replies View Related

Queries :: Less Than Parameter In Query Works Except When Value Is 100

Jun 27, 2014

I have a query that checks a table where there's a field that only has numbers from 0 to 100 (a grade), let's call that field "average" (note, the values 0 to 100 are actual numeric values, not percentages)

here's the problem:

when I filter the query using a parameter like <[value] on the average field, the query does show the expected records that have an average value that is less than the value that I input when prompted... except that it also includes the records on which the average field is 100 ... ... for some uknown reason.

to clarify:it won't show anything over the imput value, it just shows anything under the value I imput (good) and anything that has an average of 100

when I hard code the value for the parameter say <65 the query gives me the results expected (anything less than 65 in the average field) without including records with average equal to 100

some details:

the average field has this code: Average: CInt(Nz([Grade]))

the query looks like this:

SELECT [All Classes P1 Query].Class, [All Classes P1 Query].[Student ID], [All Classes P1 Query].[Full Name], [All Classes P1 Query].Subject, CInt(Nz([Grade])) AS Average, [All Classes P1 Query].Qualification, [All Classes P1 Query].[Student - Class - Grade].[Class Grading Period]
FROM [All Classes P1 Query]
GROUP BY [All Classes P1 Query].Class, [All Classes P1 Query].[Student ID], [All Classes P1 Query].[Full Name], [All Classes P1 Query].Subject, CInt(Nz([Grade])), [All Classes P1 Query].Qualification, [All Classes P1 Query].[Student - Class - Grade].[Class Grading Period]
HAVING (((CInt(Nz([Grade])))<[value]));

I'm on access 2007

View 4 Replies View Related

Query Works But Not With Command Button?

Oct 19, 2011

I have a query: qryNoSurgery with linked tables tblSurgery(many) to tblPatients(one) where tblSurgery!SurgeryDate criteria = Is Null. When I run the query by itself, it works perfectly. I get all the tblPatients data where SurgeryDate field is null.

I created a command button that opens a form, frmPatients, If I enter qryNoSurgery as the filter argument in the embedded macro and run it, I get a Msgbox which wants me to enter a parameter for qryNoSurgery!SugergyDate.

View 8 Replies View Related

ORDER BY Clause Works In Query But Not In Report?

Nov 7, 2012

I have a Report that uses a query as it's record source. I have the query ordered by a field, which works as expected in the table view of the query. In the report, however, the ORDER BY clause does not seem to carry through. The field is not sorted Ascending. What's going on?

View 5 Replies View Related

Queries :: ODBC Call Failed On Query But Form Works

Aug 7, 2013

When running a query in Access 2013 or 2010 we get an ODBC call failed. However when we run just the form, which the query connects to, it works just fine.

View 3 Replies View Related

Yes/no Condition For Query

Mar 1, 2006

i have a Yes/No checkbox to indicate whether a job has been done.. if it is done then i want it removed from the list.. so i think my query sql needs to be something like..

note..the bold bit is the bit i am questioning.

WHERE (((Booking_Main.Job_Date) Between Date() And 1+Date())) AND Booking_Confirm.Job_Done ="No"

or something like this??

View 3 Replies View Related

Format Query If Condition Is Met

Jun 21, 2005

hi ppl,
Could someone please help me with a query that I'm trying to create.
I need to create a query where once the DATE is overdue/expired how can i turn it into a red colour or something just to separate it from others.
e.g:

Length = 3
DateCleared = 31/01/2001
DateRenewal = 30/01/2004
--- obviously this date has expired and now i need to make it visible that it has expired. So I was thinking either turn it red.

thankyou.. :( :confused:
Ps. In order to create DateRenewal I used a formula by using the Length to calculate three years from the DateCleared.

View 3 Replies View Related

Unmatched Query With Condition

Jan 9, 2006

Hi there, I currently have the below tables:

tbl_stores
ID NAME
123 Store A
456 Store B

tbl_returns
ID NAME RETURN
123 Store A 9999
123 Store A 8888
789 Store C 9999

I want to find the stores that exist in the stores table, but not in the returns table WHERE the RETURN is equal to 9999. This would hopefully return 456 Store B

My problem is when I put a condition in my query it returns nothing as the condition is based on the left joined table.

SQL
SELECT tbl_stores.store, tbl_stores.name
FROM tbl_stores LEFT JOIN tbl_returns ON tbl_stores.store = tbl_returns.store
WHERE tbl_returns.store Is Null AND tbl_returns.survey_id=2
GROUP BY tbl_stores.store, tbl_stores.name, tbl_returns.store

Any ideas? Thanks for your help with this one!

Cheers

Tony

View 2 Replies View Related

Multiple Condition Query

Feb 4, 2008

Im trying to build a query that checks four fields for blanks. If I try to run the query with the criteria Is Null for all four fields, I only get 27 records. However, if I run the query with the condition only on one field I get the right number of records around 1000. I have been having to run four independant queries and then merge them in Excel. What am I doing wrong?

View 7 Replies View Related

Open Query If Condition Is True

Jun 15, 2006

I have a database with 3 tables. Each table has an account number field however the tables are completely different as are the account numbers that populate them. To go with the tables are queries that will bring in desired information. What i would like is for the user to input an account number on a form and have some code in the background that will find the account number in the table it is in and open the corresponding query. Is this possible? Thanks for the help. I have been trying to write code with DLookup but can't seem to get it to do what I want.

View 7 Replies View Related

Time Data In Query Condition

Mar 25, 2008

Hello,

I've faced a - for me - tricky problem when I've created a query from code. I have an MS SQL backend and in it there is a table which has a long time format field. I want to make an sql query where the condition is a time data. Unfortunately when I enter a time which is surely in the table, there is no result of the query. I've tried to solve it on several ways, time formatting, etc., but I had always the same result, to wit no result.
Does anyone idea, how could I solve it?
Any tips are appreciated very much!

Many thanks in advance!
VoiD:confused:

View 1 Replies View Related

Queries :: Append Query Where A Condition Has To Be Met

Feb 26, 2014

I am trying to copy notes from one table to another table where a condition has to be met and I can't figure out how to do that in an APPEND query.

Each record has a unique number that comes into my Initial Table in my Access database from a construction program I download. In this particular table both the [DNJTNo] and [DNVersionNmbr] fields can have duplicate numbers. However, when I run the query I want it to add a record to the second table only after the query checks that the [DNJTNo] in combination with the [DNVersionNmbr], is not already in the table. If it is, I do NOT want it appended to the second table. (I have attached a picture of the query in APPEND design stage).

View 2 Replies View Related

Modules & VBA :: Label ID - Set If Condition In Query

Oct 24, 2013

Is it possible to set any label id as query parameter in sql code or access?

View 5 Replies View Related

Queries :: How To Use OR Condition In Query Builder Wizard

Aug 19, 2014

I have to three fields in my query wizard, lets call them A and B and C. The condition for the query is either A or B is Null and C is Null. Im not sure how to do this though becuase A, B and C are different fields. I wrote

Code:
Is Null

.

under the criteria of A and B and C, but this specifies that A,b and C is Null, how can I make it so that C must be Null and either A or B or Both.

View 1 Replies View Related

General :: String Fields - Add Next Condition To Query

Mar 17, 2015

I have a query which working fine:

Code:
strSQL = "Select [nazwa], [id_wycena_pre], [Format_(X)], [Format_(Y)], [kolory p], [kolory t], [nazwa_id], [iloscstron], [numerarkusza], [naklad_pracy], [Kolory P], [Kolory T] from tblGoraZleceniaNowaWyceny where [id_wycena_pre]=" & Forms!frmWycenyObszarRoboczy!ID_wycena_pre & " and [nazwa]='" & "środek" & "' or [nazwa]='" & "okładka" & "'"
Set rst = CurrentDb.OpenRecordset(strSQL)...

But now I would like to add next condition to this query

Code:
where [kolory p] is not null or [kolory t] is not null

- these fields are string

I don't know how to write this condition and add to my exiting strSQL string

View 4 Replies View Related

Help With Openform

Oct 19, 2005

I am trying to open a second form that is linked to the first form but i cant get it to work it keeps coming up with a syntex error

DoCmd.OpenForm "frmstudentperformancepoorprevious", , , popUPID = '" & Me.StudentPerformanceID & "'"

Any ideas?? trying everything to get this form to link and everything is failing even the wizard which works if you just enter the record... but if you go back to it later and use the button it brings up a blank record

View 13 Replies View Related

OpenForm

Jan 22, 2006

Hello,

Im very new with access, and require a little help

what im trying to do is the following

I require a open form button, i can do this with no problems...

When the form opens, i need it to lookup a REP_ID value in a table to see if any records exist for the REP, if there is, i need it to display the form, with details.

If there are no details for REP_ID in the table, i require the form to open just blank, so input can be added!

How do i do this? Do i do it via code, macro?

Any help would be greatly appreciated

View 1 Replies View Related

Queries :: Multiple Query Criteria IIF Statement - True Condition

Feb 24, 2015

I'm trying to have a single or multiple query criteria based on what the user checks on a form.

I can't get the True condition to work at all, I get no records. Here is what I'm using

IIf([Forms]![FrmAttendanceLogsRpt]![BlkFilter]=-1,[TempVars]![EID] Or 86,[TempVars]![EID])

If I just put
[TempVars]![EID] Or 86
in the Criteria it works just fine.

View 14 Replies View Related

Openform With Criteria

Nov 8, 2005

I open a form with a criteria set but when the form opens it has every record, rather than restricting the records to those matching the criteria. I wonder if anyone has had this problem before? I use a query in the called to get the underlying recordset.

This is my code:

Dim CritText As String

CritText = "[ID1] = '" & Me.ID1 & "'"
DoCmd.OpenForm "frmTestAll", acFormDS, , stCritText

View 5 Replies View Related

OpenForm Filter

Nov 25, 2005

Hi All,

I have a problem and I can't figure out what I am doing wrong, I have a code:

Private Sub Button4_Click()
Dim compl As String
compl = name1
DoCmd.OpenForm "form1", , , "Field1=" & name1
DoCmd.Close acForm, "form2"
End Sub

Now everytime I click on this button and I enter a value in textbox(name1) it askes for a parameter! This is the only field in the form (form1) where I have this problem by the way. With the other fields it doesn't ask for a parameter. Does anyone know what might be the problem?

View 2 Replies View Related

OpenForm Filter

May 21, 2006

Access 2003
I'm sure this is incredibly simple, but I can't make it work, and can't find an answer in the forums.

:confused: I want to use OPENFORM to open a form showing only the records where a CHECKBOX is checked. I've tried using a query, using WHERE, using the OpenArgs, etc., but I'm clearly not formatting it right.

so... elements are:
btnActiveJobs (command button)
frmJob (form with job records)
chkActive (check box on frmJob that shows whether job is active)

The underlying Jobs table sets the Active field to yes/no

I'd like to do all this in the event code for the button, but I do also have a query (Query1) that produces the right jobs. Including this as the FilterName criterion has no effect.

View 4 Replies View Related

DoCmd.OpenForm Arguments

Jan 24, 2005

Hi,

I have got an unbound access form, and in this form I gather 2 dates (i.e. through txt fields). Once I have these fields, I want to open a form that has one list box on it. This list box should populate based on the dates that I gather on the previous form. How do I specify this through the DoCmd.OpenForm arguments? Currently I have the following code, but it is not working:

Form 1:
Dim whereClause As String
whereClause = "SELECT * FROM qryInvoice WHERE tblInv.InvDate Between #" & txtStartDate & "# And #" & txtEndDate & "#" & ";"
DoCmd.OpenForm "frmInvoiceFax", acNormal, , , , , whereClause

Form 2:
public Sub Form_Load(args As String)
MsgBox args
lstInvoice.Rowsource = whereClause
End Sub

I know I am not doing it the right way (because it is not working), but I can't actually find how to do it. Help!

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved