Query Criteria

[Guest]

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

 

[Douglas J. Steele]

Rather than having Forms!MyForm!MyTextbox as the criteria, try Like "*" &
Forms!MyForm!MyTextbox & "*"

 

[Guest]

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

 

[Guest]

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

 

[Douglas J. Steele]

How are you trying to open the second form?

 

[Guest]

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

 

[Guest]

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]

Are you sure that it's getting the correct value for
[Forms]![Slsm_IDept_Total_Form]![Inv_Nbr]?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

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

How are you trying to open the second form?