Query by form problems

  • Thread starter Thread starter rpbsr
  • Start date Start date
R

rpbsr

I've run across a great QBF technique in Microsoft "Help and Support"
(Article ID: 304428). It uses the following general query criteria:

Forms!FormName!ControlName Or Forms!FormName!ControlName Is Null
and also:
Between Forms!FormName!StartDate And Forms!FormName!EndDate Or _
Forms!FormName!StartDate Is Null You can use this criteria statement to
query a date field by using Start Date and End Date

My text data type fields in the form produce expected results. Two numeric
fields
(age & income) produce results as expected, but cannot use boolean
operators, e.g., > in the form. I get an error "The value you entered isn't
valid for this field..."

I have one numeric field (family size) that returns all records, regardless
of the number I put in the form field.

I really would like to use boolean operators with this to get maximum
utility from this form.

Appreciate any help on this.

Thanks.
 
I really would like to use boolean operators with this to get maximum
utility from this form.
There is one way I can can think of to do it. Do not enter the '>' or '<'
in the same text box but have a list box with two columns (value & display)
to select from. In the query use both the text box and list box for criteria.

SELECT YourTable.*, [Forms]![YourForm]![ListBox] AS X
FROM YourTable
WHERE (((YourTable.debit)=[Forms]![YourForm]![TextBox]) AND
(([Forms]![YourForm]![ListBox])=1)) OR
(((YourTable.debit)>[Forms]![YourForm]![TextBox]) AND
(([Forms]![YourForm]![ListBox])=2)) OR
(((YourTable.debit)<[Forms]![YourForm]![TextBox]) AND
(([Forms]![YourForm]![ListBox])=3));

List box default to 1 for = (equal); 2 for > (greater than); and 3 for <
(less than). You can have more selections such as >= and <= byt using more
numbers.
 
Karl,
I'm a little confused. Should I add a list box next to my current text box
or replace it with a two column list box? Am I using the current query or
does the list box use a query such as you have listed below? Added to its row
source? Could we use a real example, e.g., using the income field in my
tblMembers. The current text box is named WhatIncome and uses the query
"QBF_members_query". Please forgive these basic questions, but I'm new at
this.

Thanks
Robert

KARL DEWEY said:
utility from this form.
There is one way I can can think of to do it. Do not enter the '>' or '<'
in the same text box but have a list box with two columns (value & display)
to select from. In the query use both the text box and list box for criteria.

SELECT YourTable.*, [Forms]![YourForm]![ListBox] AS X
FROM YourTable
WHERE (((YourTable.debit)=[Forms]![YourForm]![TextBox]) AND
(([Forms]![YourForm]![ListBox])=1)) OR
(((YourTable.debit)>[Forms]![YourForm]![TextBox]) AND
(([Forms]![YourForm]![ListBox])=2)) OR
(((YourTable.debit)<[Forms]![YourForm]![TextBox]) AND
(([Forms]![YourForm]![ListBox])=3));

List box default to 1 for = (equal); 2 for > (greater than); and 3 for <
(less than). You can have more selections such as >= and <= byt using more
numbers.
--
KARL DEWEY
Build a little - Test a little


rpbsr said:
I've run across a great QBF technique in Microsoft "Help and Support"
(Article ID: 304428). It uses the following general query criteria:

Forms!FormName!ControlName Or Forms!FormName!ControlName Is Null
and also:
Between Forms!FormName!StartDate And Forms!FormName!EndDate Or _
Forms!FormName!StartDate Is Null You can use this criteria statement to
query a date field by using Start Date and End Date

My text data type fields in the form produce expected results. Two numeric
fields
(age & income) produce results as expected, but cannot use boolean
operators, e.g., > in the form. I get an error "The value you entered isn't
valid for this field..."

I have one numeric field (family size) that returns all records, regardless
of the number I put in the form field.

I really would like to use boolean operators with this to get maximum
utility from this form.

Appreciate any help on this.

Thanks.
Click to expand...
Click to expand...
 
Add a two column list box to the form for numbers & symbols that are displayed.

Post your query SQL and sample data.

--
KARL DEWEY
Build a little - Test a little


rpbsr said:
Karl,
I'm a little confused. Should I add a list box next to my current text box
or replace it with a two column list box? Am I using the current query or
does the list box use a query such as you have listed below? Added to its row
source? Could we use a real example, e.g., using the income field in my
tblMembers. The current text box is named WhatIncome and uses the query
"QBF_members_query". Please forgive these basic questions, but I'm new at
this.

Thanks
Robert

KARL DEWEY said:
I really would like to use boolean operators with this to get maximum
Click to expand...
utility from this form.
There is one way I can can think of to do it. Do not enter the '>' or '<'
in the same text box but have a list box with two columns (value & display)
to select from. In the query use both the text box and list box for criteria.

SELECT YourTable.*, [Forms]![YourForm]![ListBox] AS X
FROM YourTable
WHERE (((YourTable.debit)=[Forms]![YourForm]![TextBox]) AND
(([Forms]![YourForm]![ListBox])=1)) OR
(((YourTable.debit)>[Forms]![YourForm]![TextBox]) AND
(([Forms]![YourForm]![ListBox])=2)) OR
(((YourTable.debit)<[Forms]![YourForm]![TextBox]) AND
(([Forms]![YourForm]![ListBox])=3));

List box default to 1 for = (equal); 2 for > (greater than); and 3 for <
(less than). You can have more selections such as >= and <= byt using more
numbers.
--
KARL DEWEY
Build a little - Test a little


rpbsr said:
I've run across a great QBF technique in Microsoft "Help and Support"
(Article ID: 304428). It uses the following general query criteria:

Forms!FormName!ControlName Or Forms!FormName!ControlName Is Null
and also:
Between Forms!FormName!StartDate And Forms!FormName!EndDate Or _
Forms!FormName!StartDate Is Null You can use this criteria statement to
query a date field by using Start Date and End Date

My text data type fields in the form produce expected results. Two numeric
fields
(age & income) produce results as expected, but cannot use boolean
operators, e.g., > in the form. I get an error "The value you entered isn't
valid for this field..."

I have one numeric field (family size) that returns all records, regardless
of the number I put in the form field.

I really would like to use boolean operators with this to get maximum
utility from this form.

Appreciate any help on this.

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Karl,

Query SQL:

SELECT tblMembers.LastName, tblMembers.FirstName, tblMembers.DateJoined,
tblMembers.Age, tblMembers.Race, tblMembers.MaritalStatus,
tblMembers.HomeCity, tblMembers.EmploymentStatus, tblMembers.Income,
tblMembers.PeopleNumber, tblMembers.PublicAssistance
FROM tblMembers
WHERE (((tblMembers.DateJoined) Between
[Forms]![QBF_members_form]![StartDate] And
[Forms]![QBF_members_form]![EndDate] Or
[Forms]![QBF_members_form]![StartDate] Is Null) AND
((tblMembers.Age)=[Forms]![QBF_members_form]![WhatAge] Or
[Forms]![QBF_members_form]![WhatAge] Is Null) AND
((tblMembers.Race)=[Forms]![QBF_members_form]![WhatRace] Or
[Forms]![QBF_members_form]![WhatRace] Is Null) AND
((tblMembers.MaritalStatus)=[Forms]![QBF_members_form]![WhatMarital] Or
[Forms]![QBF_members_form]![WhatMarital] Is Null) AND
((tblMembers.HomeCity)=[Forms]![QBF_members_form]![WhatCity] Or
[Forms]![QBF_members_form]![WhatCity] Is Null) AND
((tblMembers.EmploymentStatus)=[Forms]![QBF_members_form]![WhatEmployment] Or
[Forms]![QBF_members_form]![WhatEmployment] Is Null) AND
((tblMembers.Income)=[Forms]![QBF_members_form]![WhatIncome] Or
[Forms]![QBF_members_form]![WhatIncome] Is Null) AND
((tblMembers.PeopleNumber)=[Forms]![QBF_members_form]![WhatPeopleNumber] Or
[Forms]![QBF_members_form]![WhaPeopleNumber] Is Null) AND
((tblMembers.PublicAssistance)=[Forms]![QBF_members_form]![WhatAssistance] Or
[Forms]![QBF_members_form]![WhatAssistance] Is Null))
ORDER BY tblMembers.LastName;


SAMPLE DATA:

First Name Last Name Date
Joined City Income PeopleNumber Age Race MaritalStatus EmploymentStatus PublicAssistance
Robert Boom 12/25/2007 West Chester $47,000.00 3 39 African
American Divorced Employed FALSE
Judy Bodtke 3/9/2005 Avon $17,000.00 2 28 White Single Looking for work TRUE



KARL DEWEY said:
Add a two column list box to the form for numbers & symbols that are displayed.

Post your query SQL and sample data.

--
KARL DEWEY
Build a little - Test a little


rpbsr said:
Karl,
I'm a little confused. Should I add a list box next to my current text box
or replace it with a two column list box? Am I using the current query or
does the list box use a query such as you have listed below? Added to its row
source? Could we use a real example, e.g., using the income field in my
tblMembers. The current text box is named WhatIncome and uses the query
"QBF_members_query". Please forgive these basic questions, but I'm new at
this.

Thanks
Robert

KARL DEWEY said:
I really would like to use boolean operators with this to get maximum
utility from this form.
There is one way I can can think of to do it. Do not enter the '>' or '<'
in the same text box but have a list box with two columns (value & display)
to select from. In the query use both the text box and list box for criteria.

SELECT YourTable.*, [Forms]![YourForm]![ListBox] AS X
FROM YourTable
WHERE (((YourTable.debit)=[Forms]![YourForm]![TextBox]) AND
(([Forms]![YourForm]![ListBox])=1)) OR
(((YourTable.debit)>[Forms]![YourForm]![TextBox]) AND
(([Forms]![YourForm]![ListBox])=2)) OR
(((YourTable.debit)<[Forms]![YourForm]![TextBox]) AND
(([Forms]![YourForm]![ListBox])=3));

List box default to 1 for = (equal); 2 for > (greater than); and 3 for <
(less than). You can have more selections such as >= and <= byt using more
numbers.
--
KARL DEWEY
Build a little - Test a little


:

I've run across a great QBF technique in Microsoft "Help and Support"
(Article ID: 304428). It uses the following general query criteria:

Forms!FormName!ControlName Or Forms!FormName!ControlName Is Null
and also:
Between Forms!FormName!StartDate And Forms!FormName!EndDate Or _
Forms!FormName!StartDate Is Null You can use this criteria statement to
query a date field by using Start Date and End Date

My text data type fields in the form produce expected results. Two numeric
fields
(age & income) produce results as expected, but cannot use boolean
operators, e.g., > in the form. I get an error "The value you entered isn't
valid for this field..."

I have one numeric field (family size) that returns all records, regardless
of the number I put in the form field.

I really would like to use boolean operators with this to get maximum
utility from this form.

Appreciate any help on this.

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
If you add a combo box cboSymbol next to your WhatIncome text box, with a
value list of

=;<>;>=;<=

setting it to a default of = (or whatever you wish) in the form's Open event
procedure with:

Me.cboSymbol = "="

and setting its ValidationRule property to:

Is Not Null

so that the user can select an operator the query would then go like this:

SELECT
<etc>
WHERE
<etc>
Forms!QBF_members_form!WhatEmployment Is NULL)
AND
(((Income = Forms!QBF_members_form!WhatIncome
And Forms!QBF_members_form!cboSymbol = "=" )
OR Forms!QBF_members_form!cboSymbol <> "=")
OR Forms!QBF_members_form!WhatIncome IS NULL)
AND
(((Income <> Forms!QBF_members_form!WhatIncome
And Forms!QBF_members_form!cboSymbol = "<>" )
OR Forms!QBF_members_form!cboSymbol <> "<>")
OR Forms!QBF_members_form!WhatIncome IS NULL)
AND
(((Income >= Forms!QBF_members_form!WhatIncome
And Forms!QBF_members_form!cboSymbol = ">=" )
OR Forms!QBF_members_form!cboSymbol <> ">=")
OR Forms!QBF_members_form!WhatIncome IS NULL)
AND
(((Income <= Forms!QBF_members_form!WhatIncome
And Forms!QBF_members_form!cboSymbol = "<=" )
OR Forms!QBF_members_form!cboSymbol <> "<=")
OR Forms!QBF_members_form!WhatIncome IS NULL)
AND
(PeopleNumber=Forms!QBF_members_form!WhatPeopleNumber
<etc>

Ken Sheridan
Stafford, England

rpbsr said:
Karl,

Query SQL:

SELECT tblMembers.LastName, tblMembers.FirstName, tblMembers.DateJoined,
tblMembers.Age, tblMembers.Race, tblMembers.MaritalStatus,
tblMembers.HomeCity, tblMembers.EmploymentStatus, tblMembers.Income,
tblMembers.PeopleNumber, tblMembers.PublicAssistance
FROM tblMembers
WHERE (((tblMembers.DateJoined) Between
[Forms]![QBF_members_form]![StartDate] And
[Forms]![QBF_members_form]![EndDate] Or
[Forms]![QBF_members_form]![StartDate] Is Null) AND
((tblMembers.Age)=[Forms]![QBF_members_form]![WhatAge] Or
[Forms]![QBF_members_form]![WhatAge] Is Null) AND
((tblMembers.Race)=[Forms]![QBF_members_form]![WhatRace] Or
[Forms]![QBF_members_form]![WhatRace] Is Null) AND
((tblMembers.MaritalStatus)=[Forms]![QBF_members_form]![WhatMarital] Or
[Forms]![QBF_members_form]![WhatMarital] Is Null) AND
((tblMembers.HomeCity)=[Forms]![QBF_members_form]![WhatCity] Or
[Forms]![QBF_members_form]![WhatCity] Is Null) AND
((tblMembers.EmploymentStatus)=[Forms]![QBF_members_form]![WhatEmployment] Or
[Forms]![QBF_members_form]![WhatEmployment] Is Null) AND
((tblMembers.Income)=[Forms]![QBF_members_form]![WhatIncome] Or
[Forms]![QBF_members_form]![WhatIncome] Is Null) AND
((tblMembers.PeopleNumber)=[Forms]![QBF_members_form]![WhatPeopleNumber] Or
[Forms]![QBF_members_form]![WhaPeopleNumber] Is Null) AND
((tblMembers.PublicAssistance)=[Forms]![QBF_members_form]![WhatAssistance] Or
[Forms]![QBF_members_form]![WhatAssistance] Is Null))
ORDER BY tblMembers.LastName;


SAMPLE DATA:

First Name Last Name Date
Joined City Income PeopleNumber Age Race MaritalStatus EmploymentStatus PublicAssistance
Robert Boom 12/25/2007 West Chester $47,000.00 3 39 African
American Divorced Employed FALSE
Judy Bodtke 3/9/2005 Avon $17,000.00 2 28 White Single Looking for work TRUE



KARL DEWEY said:
Add a two column list box to the form for numbers & symbols that are displayed.

Post your query SQL and sample data.

--
KARL DEWEY
Build a little - Test a little


rpbsr said:
Karl,
I'm a little confused. Should I add a list box next to my current text box
or replace it with a two column list box? Am I using the current query or
does the list box use a query such as you have listed below? Added to its row
source? Could we use a real example, e.g., using the income field in my
tblMembers. The current text box is named WhatIncome and uses the query
"QBF_members_query". Please forgive these basic questions, but I'm new at
this.

Thanks
Robert

:

I really would like to use boolean operators with this to get maximum
utility from this form.
There is one way I can can think of to do it. Do not enter the '>' or '<'
in the same text box but have a list box with two columns (value & display)
to select from. In the query use both the text box and list box for criteria.

SELECT YourTable.*, [Forms]![YourForm]![ListBox] AS X
FROM YourTable
WHERE (((YourTable.debit)=[Forms]![YourForm]![TextBox]) AND
(([Forms]![YourForm]![ListBox])=1)) OR
(((YourTable.debit)>[Forms]![YourForm]![TextBox]) AND
(([Forms]![YourForm]![ListBox])=2)) OR
(((YourTable.debit)<[Forms]![YourForm]![TextBox]) AND
(([Forms]![YourForm]![ListBox])=3));

List box default to 1 for = (equal); 2 for > (greater than); and 3 for <
(less than). You can have more selections such as >= and <= byt using more
numbers.
--
KARL DEWEY
Build a little - Test a little


:

I've run across a great QBF technique in Microsoft "Help and Support"
(Article ID: 304428). It uses the following general query criteria:

Forms!FormName!ControlName Or Forms!FormName!ControlName Is Null
and also:
Between Forms!FormName!StartDate And Forms!FormName!EndDate Or _
Forms!FormName!StartDate Is Null You can use this criteria statement to
query a date field by using Start Date and End Date

My text data type fields in the form produce expected results. Two numeric
fields
(age & income) produce results as expected, but cannot use boolean
operators, e.g., > in the form. I get an error "The value you entered isn't
valid for this field..."

I have one numeric field (family size) that returns all records, regardless
of the number I put in the form field.

I really would like to use boolean operators with this to get maximum
utility from this form.

Appreciate any help on this.

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Works perfectly Ken. Thanks for taking the time to show how I can apply to
this and similar cases.

Ken Sheridan said:
If you add a combo box cboSymbol next to your WhatIncome text box, with a
value list of

=;<>;>=;<=

setting it to a default of = (or whatever you wish) in the form's Open event
procedure with:

Me.cboSymbol = "="

and setting its ValidationRule property to:

Is Not Null

so that the user can select an operator the query would then go like this:

SELECT
<etc>
WHERE
<etc>
Forms!QBF_members_form!WhatEmployment Is NULL)
AND
(((Income = Forms!QBF_members_form!WhatIncome
And Forms!QBF_members_form!cboSymbol = "=" )
OR Forms!QBF_members_form!cboSymbol <> "=")
OR Forms!QBF_members_form!WhatIncome IS NULL)
AND
(((Income <> Forms!QBF_members_form!WhatIncome
And Forms!QBF_members_form!cboSymbol = "<>" )
OR Forms!QBF_members_form!cboSymbol <> "<>")
OR Forms!QBF_members_form!WhatIncome IS NULL)
AND
(((Income >= Forms!QBF_members_form!WhatIncome
And Forms!QBF_members_form!cboSymbol = ">=" )
OR Forms!QBF_members_form!cboSymbol <> ">=")
OR Forms!QBF_members_form!WhatIncome IS NULL)
AND
(((Income <= Forms!QBF_members_form!WhatIncome
And Forms!QBF_members_form!cboSymbol = "<=" )
OR Forms!QBF_members_form!cboSymbol <> "<=")
OR Forms!QBF_members_form!WhatIncome IS NULL)
AND
(PeopleNumber=Forms!QBF_members_form!WhatPeopleNumber
<etc>

Ken Sheridan
Stafford, England

rpbsr said:
Karl,

Query SQL:

SELECT tblMembers.LastName, tblMembers.FirstName, tblMembers.DateJoined,
tblMembers.Age, tblMembers.Race, tblMembers.MaritalStatus,
tblMembers.HomeCity, tblMembers.EmploymentStatus, tblMembers.Income,
tblMembers.PeopleNumber, tblMembers.PublicAssistance
FROM tblMembers
WHERE (((tblMembers.DateJoined) Between
[Forms]![QBF_members_form]![StartDate] And
[Forms]![QBF_members_form]![EndDate] Or
[Forms]![QBF_members_form]![StartDate] Is Null) AND
((tblMembers.Age)=[Forms]![QBF_members_form]![WhatAge] Or
[Forms]![QBF_members_form]![WhatAge] Is Null) AND
((tblMembers.Race)=[Forms]![QBF_members_form]![WhatRace] Or
[Forms]![QBF_members_form]![WhatRace] Is Null) AND
((tblMembers.MaritalStatus)=[Forms]![QBF_members_form]![WhatMarital] Or
[Forms]![QBF_members_form]![WhatMarital] Is Null) AND
((tblMembers.HomeCity)=[Forms]![QBF_members_form]![WhatCity] Or
[Forms]![QBF_members_form]![WhatCity] Is Null) AND
((tblMembers.EmploymentStatus)=[Forms]![QBF_members_form]![WhatEmployment] Or
[Forms]![QBF_members_form]![WhatEmployment] Is Null) AND
((tblMembers.Income)=[Forms]![QBF_members_form]![WhatIncome] Or
[Forms]![QBF_members_form]![WhatIncome] Is Null) AND
((tblMembers.PeopleNumber)=[Forms]![QBF_members_form]![WhatPeopleNumber] Or
[Forms]![QBF_members_form]![WhaPeopleNumber] Is Null) AND
((tblMembers.PublicAssistance)=[Forms]![QBF_members_form]![WhatAssistance] Or
[Forms]![QBF_members_form]![WhatAssistance] Is Null))
ORDER BY tblMembers.LastName;


SAMPLE DATA:

First Name Last Name Date
Joined City Income PeopleNumber Age Race MaritalStatus EmploymentStatus PublicAssistance
Robert Boom 12/25/2007 West Chester $47,000.00 3 39 African
American Divorced Employed FALSE
Judy Bodtke 3/9/2005 Avon $17,000.00 2 28 White Single Looking for work TRUE



KARL DEWEY said:
Add a two column list box to the form for numbers & symbols that are displayed.

Post your query SQL and sample data.

--
KARL DEWEY
Build a little - Test a little


:

Karl,
I'm a little confused. Should I add a list box next to my current text box
or replace it with a two column list box? Am I using the current query or
does the list box use a query such as you have listed below? Added to its row
source? Could we use a real example, e.g., using the income field in my
tblMembers. The current text box is named WhatIncome and uses the query
"QBF_members_query". Please forgive these basic questions, but I'm new at
this.

Thanks
Robert

:

I really would like to use boolean operators with this to get maximum
utility from this form.
There is one way I can can think of to do it. Do not enter the '>' or '<'
in the same text box but have a list box with two columns (value & display)
to select from. In the query use both the text box and list box for criteria.

SELECT YourTable.*, [Forms]![YourForm]![ListBox] AS X
FROM YourTable
WHERE (((YourTable.debit)=[Forms]![YourForm]![TextBox]) AND
(([Forms]![YourForm]![ListBox])=1)) OR
(((YourTable.debit)>[Forms]![YourForm]![TextBox]) AND
(([Forms]![YourForm]![ListBox])=2)) OR
(((YourTable.debit)<[Forms]![YourForm]![TextBox]) AND
(([Forms]![YourForm]![ListBox])=3));

List box default to 1 for = (equal); 2 for > (greater than); and 3 for <
(less than). You can have more selections such as >= and <= byt using more
numbers.
--
KARL DEWEY
Build a little - Test a little


:

I've run across a great QBF technique in Microsoft "Help and Support"
(Article ID: 304428). It uses the following general query criteria:

Forms!FormName!ControlName Or Forms!FormName!ControlName Is Null
and also:
Between Forms!FormName!StartDate And Forms!FormName!EndDate Or _
Forms!FormName!StartDate Is Null You can use this criteria statement to
query a date field by using Start Date and End Date

My text data type fields in the form produce expected results. Two numeric
fields
(age & income) produce results as expected, but cannot use boolean
operators, e.g., > in the form. I get an error "The value you entered isn't
valid for this field..."

I have one numeric field (family size) that returns all records, regardless
of the number I put in the form field.

I really would like to use boolean operators with this to get maximum
utility from this form.

Appreciate any help on this.

Thanks.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Relation between tables in query and criteria 1
Pull criteria from a Form 2
Use Table field in query criteria 1
Query Question 3
Null Value for Blank Date Range 2
Query Criteria from Forms 1
Passing Criteria to a Query From a Form 5
Cross tab query 9

Back
Top