If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query Criteria
I am using a field from a form as the criteria for a query. The field in the
form is a text field. It contains transaction numbers that can be a total of 10 character or less. This field contains values like 12345-0, 1234567-0 and C 12345-0. When I execute the query there are no values returned. If I manually set the criteria to be " 12345-0", then it returns the expected data. Any ideas on how to get the critera to work in the query? Ted |
#2
|
|||
|
|||
Query Criteria
Rather than having Forms!MyForm!MyTextbox as the criteria, try Like "*" &
Forms!MyForm!MyTextbox & "*" -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "TD11" wrote in message ... I am using a field from a form as the criteria for a query. The field in the form is a text field. It contains transaction numbers that can be a total of 10 character or less. This field contains values like 12345-0, 1234567-0 and C 12345-0. When I execute the query there are no values returned. If I manually set the criteria to be " 12345-0", then it returns the expected data. Any ideas on how to get the critera to work in the query? Ted |
#3
|
|||
|
|||
Query Criteria
Declare the parameter as Text. It will be interpreted as an arithmetical
expression otherwise: PARAMETERS [Forms!YourForm!YourControl] TEXT ( 255 ); SELECT etc. Incidentally its also as well to do this with date parameters too as a date parameter entered in short date format such as 03/08/2006 could also be interpreted as an arithmetical expression. In this case it would actually evaluate to 30 December 1899 00:00:16, that date being day zero in Access's implementaion of date/time data. Ken Sheridan Stafford, England "TD11" wrote: I am using a field from a form as the criteria for a query. The field in the form is a text field. It contains transaction numbers that can be a total of 10 character or less. This field contains values like 12345-0, 1234567-0 and C 12345-0. When I execute the query there are no values returned. If I manually set the criteria to be " 12345-0", then it returns the expected data. Any ideas on how to get the critera to work in the query? Ted |
#4
|
|||
|
|||
Query Criteria
I appreciate your quick response. I have tried the formatting it as you
suggested, but still don't get the results I expect. I have a form (!Invoice) that displays multiple transaction numbers: 12345-0 100.00 15426-0 200.00 18449-0 150.00 I want to open a second form (!InvDet)that shows me the detail info for a selected transaction. However, when the 2nd form displays it shows me all of the detail for all of the transactions. It is not imited to only the transaction selected. I have other forms that work this way, but for some reason I can't get this one to limit to only the selected transaction. Any ideas would be appreciated. Ted |
#5
|
|||
|
|||
Query Criteria
How are you trying to open the second form?
-- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "TD11" wrote in message news I appreciate your quick response. I have tried the formatting it as you suggested, but still don't get the results I expect. I have a form (!Invoice) that displays multiple transaction numbers: 12345-0 100.00 15426-0 200.00 18449-0 150.00 I want to open a second form (!InvDet)that shows me the detail info for a selected transaction. However, when the 2nd form displays it shows me all of the detail for all of the transactions. It is not imited to only the transaction selected. I have other forms that work this way, but for some reason I can't get this one to limit to only the selected transaction. Any ideas would be appreciated. Ted |
#6
|
|||
|
|||
Query Criteria
Ted:
Rather than referencing the first form's control as a parameter in the second form's query have you tried filtering the second form to the first form's current record's transaction number by means of the WhereCondition argument of the OpenForm method: Dim strCriteria As String strCriteria = "TransactionNumber = """ & Me.TransactionNumber & """" DoCmd.OpenForm _ FormName:="InvDet", _ WhereCondition:= strCriteria If the code is being executed outside of the first form's module fully reference the control: strCriteria = "TransactionNumber = """ & Forms.Invoice.TransactionNumber & """" Ken Sheridan Stafford, England "TD11" wrote: I appreciate your quick response. I have tried the formatting it as you suggested, but still don't get the results I expect. I have a form (!Invoice) that displays multiple transaction numbers: 12345-0 100.00 15426-0 200.00 18449-0 150.00 I want to open a second form (!InvDet)that shows me the detail info for a selected transaction. However, when the 2nd form displays it shows me all of the detail for all of the transactions. It is not imited to only the transaction selected. I have other forms that work this way, but for some reason I can't get this one to limit to only the selected transaction. Any ideas would be appreciated. Ted |
#7
|
|||
|
|||
Query Criteria
Ted:
Rather than referencing the first form's control as a parameter in the second form's query have you tried filtering the second form to the first form's current record's transaction number by means of the WhereCondition argument of the OpenForm method: Dim strCriteria As String strCriteria = "TransactionNumber = """ & Me.TransactionNumber & """" DoCmd.OpenForm _ FormName:="InvDet", _ WhereCondition:= strCriteria If the code is being executed outside of the first form's module fully reference the control: strCriteria = "TransactionNumber = """ & Forms.Invoice.TransactionNumber & """" Ken Sheridan Stafford, England "TD11" wrote: I appreciate your quick response. I have tried the formatting it as you suggested, but still don't get the results I expect. I have a form (!Invoice) that displays multiple transaction numbers: 12345-0 100.00 15426-0 200.00 18449-0 150.00 I want to open a second form (!InvDet)that shows me the detail info for a selected transaction. However, when the 2nd form displays it shows me all of the detail for all of the transactions. It is not imited to only the transaction selected. I have other forms that work this way, but for some reason I can't get this one to limit to only the selected transaction. Any ideas would be appreciated. Ted |
#8
|
|||
|
|||
Query Criteria
I'm am openning the form ( Inv_Detail) with a marco that is an event
property. The form uses a data source of a query (Inv_Display) with a criteria of (Like "*" & [Forms]![Slsm_IDept_Total_Form]![Inv_Nbr] & "*") for the invoice field. I have done this on several other forms as this is a drilldown sequence. The 1st form (Slsm_Totals) displays salesman totals. When you click on a particular salesman it opens another form (Slsm_IDept_Totals) that displays sales broken down into product departments/groups. When you click on a particular product group, it opens another form (Slsm_Inv) that displays invoice totals for the selected product group. The last form (Inv_Detail) should be to click on a particluar invoice and have a form display all of the individual lines on the selected invoice. However, when I get to the last form, all lines on all invoices display for that selected salesman and product group. Ted "Douglas J. Steele" wrote: How are you trying to open the second form? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "TD11" wrote in message news I appreciate your quick response. I have tried the formatting it as you suggested, but still don't get the results I expect. I have a form (!Invoice) that displays multiple transaction numbers: 12345-0 100.00 15426-0 200.00 18449-0 150.00 I want to open a second form (!InvDet)that shows me the detail info for a selected transaction. However, when the 2nd form displays it shows me all of the detail for all of the transactions. It is not imited to only the transaction selected. I have other forms that work this way, but for some reason I can't get this one to limit to only the selected transaction. Any ideas would be appreciated. Ted |
#9
|
|||
|
|||
Query Criteria
Are you sure that it's getting the correct value for
[Forms]![Slsm_IDept_Total_Form]![Inv_Nbr]? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Ted" wrote in message ... I'm am openning the form ( Inv_Detail) with a marco that is an event property. The form uses a data source of a query (Inv_Display) with a criteria of (Like "*" & [Forms]![Slsm_IDept_Total_Form]![Inv_Nbr] & "*") for the invoice field. I have done this on several other forms as this is a drilldown sequence. The 1st form (Slsm_Totals) displays salesman totals. When you click on a particular salesman it opens another form (Slsm_IDept_Totals) that displays sales broken down into product departments/groups. When you click on a particular product group, it opens another form (Slsm_Inv) that displays invoice totals for the selected product group. The last form (Inv_Detail) should be to click on a particluar invoice and have a form display all of the individual lines on the selected invoice. However, when I get to the last form, all lines on all invoices display for that selected salesman and product group. Ted "Douglas J. Steele" wrote: How are you trying to open the second form? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "TD11" wrote in message news I appreciate your quick response. I have tried the formatting it as you suggested, but still don't get the results I expect. I have a form (!Invoice) that displays multiple transaction numbers: 12345-0 100.00 15426-0 200.00 18449-0 150.00 I want to open a second form (!InvDet)that shows me the detail info for a selected transaction. However, when the 2nd form displays it shows me all of the detail for all of the transactions. It is not imited to only the transaction selected. I have other forms that work this way, but for some reason I can't get this one to limit to only the selected transaction. Any ideas would be appreciated. Ted |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Cross tab query construction with Subqueries | Steven Cheng | Running & Setting Up Queries | 7 | February 13th, 2006 06:52 PM |
query criteria entered via a form | Lori | Running & Setting Up Queries | 8 | January 31st, 2006 06:58 PM |
Crosstab query with criteria, dynamic columns and crosstab report question | joshblair | Running & Setting Up Queries | 5 | January 13th, 2006 09:34 PM |
criteria in a query | Ulcom | Using Forms | 1 | December 16th, 2004 08:49 PM |
Big number gives error! | Sara Mellen | Running & Setting Up Queries | 8 | October 11th, 2004 02:48 AM |