Query Criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TD11 - 08 Mar 2006 20:20 GMT

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

Reply to this Message

Douglas J. Steele - 08 Mar 2006 22:48 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

>I am using a field from a form as the criteria for a query. The field in
>the
[quoted text clipped - 9 lines]
>
> Ted

Reply to this Message

TD11 - 09 Mar 2006 15:29 GMT

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

Reply to this Message

Douglas J. Steele - 09 Mar 2006 23:07 GMT

How are you trying to open the second form?

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

>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
[quoted text clipped - 17 lines]
>
> Ted

Reply to this Message

Ted - 13 Mar 2006 22:50 GMT

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?
>
[quoted text clipped - 19 lines]
> >
> > Ted

Reply to this Message

Douglas J. Steele - 13 Mar 2006 23:22 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(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
[quoted text clipped - 43 lines]
>> >
>> > Ted

Reply to this Message

Ken Sheridan - 10 Mar 2006 18:30 GMT

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

> 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)
[quoted text clipped - 14 lines]
>
> Ted

Reply to this Message

Ken Sheridan - 09 Mar 2006 00:02 GMT

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

> 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
[quoted text clipped - 6 lines]
>
> Ted

Reply to this Message