My code has worked before ... why not now?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following code in a similar database and it allows my dialog box
to work as a search function for as many or few criteria as my users would
like to enter. But when I tried to use the same format in my new database it
does not filter, but just pulls up all the results. I don't understand why?

DoCmd.OpenForm "BTT"


Forms!BTT.RecordSource = "SELECT BTT.[Name], BTT.[Gender], BTT.[Ethnicity],
BTT.[Age], BTT.[Industry], BTT.[Address], BTT.[Home Number], BTT.[Work
Number], BTT.[Cell Number], BTT.[Fax Number], BTT., BTT.[Biography],
BTT.[Strengths], BTT.[Weaknesses], BTT.[Company], BTT.[Title] " _
& "FROM BTT WHERE (BTT.[Name]=[Forms]![BTT Search]![BTT Name] OR
[Forms]![BTT Search]![BTT Name] IS NULL) AND (BTT.[Gender]=[Forms]![BTT
Search]![Gender] OR [Forms]![BTT Search]![Gender] IS NULL) AND (BTT.[Age]
BETWEEN '0' AND '12'=[Forms]![BTT Search]![Kids] OR [Forms]![BTT
Search]![Kids] IS NULL) AND (BTT.[Age] BETWEEN '10' AND '12'=[Forms]![BTT
Search]![Tweens] OR [Forms]![BTT Search]![Tweens] IS NULL) AND (BTT.[Age]
BETWEEN '12' AND '18'=[Forms]![BTT Search]![Teens] OR [Forms]![BTT
Search]![Teen] IS NULL) AND (BTT.[Age] BETWEEN '18' AND '22'=[Forms]![BTT
Search]![College] OR [Forms]![BTT Search]![College] IS NULL) AND (BTT.[Age]
BETWEEN '23' AND '30'=[Forms]![BTT Search]![YA] OR [Forms]![BTT Search]![YA]
IS NULL) AND (BTT.[Age] BETWEEN '30' AND '39'=[Forms]![BTT Search]![Thirty]
OR [Forms]![BTT Search]![Thirty] IS NULL) " _
& "AND (BTT.[Age] BETWEEN '40' AND '49'=[Forms]![BTT Search]![Forty] OR
[Forms]![BTT Search]![Forty] IS NULL) AND (BTT.[Age] BETWEEN '50' AND
'100'=[Forms]![BTT Search]![Over the Hill] OR [Forms]![BTT Search]![Over the
Hill] IS NULL) AND (BTT.[Industry]=[Forms]![BTT Search]![Industy] OR
[Forms]![BTT Search]![Industry] IS NULL) "



DoCmd.Close acForm, Me.Name
End Sub
 
(BTT.[Age] BETWEEN '50' AND
'100'=[Forms]![BTT Search]![Over the Hill] OR [Forms]![BTT Search]![Over the
Hill] IS NULL)
Click to expand...

HRUMPF.

Well, you wouldn't want an answer from a guy who's over the hill,
would you?

Seriously - one issue is that it appears that your Age field is Text.
This means that records will be sorted in the order 0, 1, 10, 11, 12,
...., 100, 101, ..., 128, 20, 21 - that is, alphabetically.

I wonder if the BETWEEN 'X' AND 'Y' criteria between them are simply
pulling up all records?

John W. Vinson[MVP]
 
I changed the age field to a number field, but all of the records are still
pulling up ... the age/text issue comes in where on my search form I have an
option group that allows users to search for people in certain age ranges, so
I have labeled those areas, but the name of my option group is Age, so should
I be saying Age1, Age2, etc. for each option in order as opposed to the name
for each individual label?

John Vinson said:
(BTT.[Age] BETWEEN '50' AND
'100'=[Forms]![BTT Search]![Over the Hill] OR [Forms]![BTT Search]![Over the
Hill] IS NULL)
Click to expand...

HRUMPF.

Well, you wouldn't want an answer from a guy who's over the hill,
would you?

Seriously - one issue is that it appears that your Age field is Text.
This means that records will be sorted in the order 0, 1, 10, 11, 12,
...., 100, 101, ..., 128, 20, 21 - that is, alphabetically.

I wonder if the BETWEEN 'X' AND 'Y' criteria between them are simply
pulling up all records?

John W. Vinson[MVP]
Click to expand...
 
I changed the age field to a number field, but all of the records are still
pulling up ... the age/text issue comes in where on my search form I have an
option group that allows users to search for people in certain age ranges, so
I have labeled those areas, but the name of my option group is Age, so should
I be saying Age1, Age2, etc. for each option in order as opposed to the name
for each individual label?
Click to expand...
An Option Group control returns an integer numeric value, not a text
string. Check the properties of the buttons in the option group to see
what each one's numeric value actually is. You'ld search using the
value of the option group itself, not each of its embedded controls
(which are not accessible to a query).

John W. Vinson[MVP]
 
I don't quite follow you ... where is the value under properties, and how
would my code distinguish between the different options?
 
Back
Top