By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,351 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

Populating age range two text boxes

P: 68
Hi I have a database that I would like to generate age range.
I would like to include two text boxes like:

txtBeginningAge
txtEndingAge

With these text boxes, after updating them (entering the age range), I would like to include a button that will generate a report base on the age range.

I can run a query that pops up a dialogue box requesting the entry of beginning and ending age, but I want it to be on my report menu.

I have this that generates some of my reports as needed:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport "rptInServiceTeachersByIndividualCountyAndSchools", acViewReport, , "strCounty = '" & Me.cboCounty & "'"
Complements to my good friends here.

Can anyone help?

Thanks
Oct 22 '12 #1
Share this Question
Share on Google+
10 Replies


Rabbit
Expert Mod 10K+
P: 12,315
You can refer to form controls in the query editor Forms!formName!controlName
Oct 22 '12 #2

P: 68
Thanks Rabbit, but this is taking me to nowhere.
Oct 22 '12 #3

Rabbit
Expert Mod 10K+
P: 12,315
In what way? You just need to replace your parameters in your query with the form control reference.
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM tableName
  3. WHERE dateField 
  4. BETWEEN Forms!formName!txtStartDate 
  5. AND Forms!formName!txtEndDate
It's a fairly simple change from what I assume you already have with the parameter pop up box.
Oct 22 '12 #4

NeoPa
Expert Mod 15k+
P: 31,186
If that's taking you nowhere then you should be looking at your starting position ;-) If you ask a question that's vague then trying to find an answer for you is particularly difficult.

If I ignore most of your question post (as I must to make any sense of the question) then I focus on the question of handling age ranges in SQL. Personally, I prefer to use literals rather than control references, but the fundamentals of the issue are to use the Between option as Rabbit has illustrated. For a literal it might be something like :
Expand|Select|Wrap|Line Numbers
  1. With Me
  2.     strWhere = "([strCounty] = '%C') AND " & 
  3.                "([AgeField] Between %F And %T)"
  4.     strWhere = Replace(strWhere, "%C", .cboCounty)
  5.     strWhere = Replace(strWhere, "%F", .txtBeginningAge)
  6.     strWhere = Replace(strWhere, "%T", .txtEndingAge)
  7.     Call DoCmd.OpenReport(ReportName:="rptInServiceTeachersByIndividualCountyAndSchools", _
  8.                           View:=acViewReport, 
  9.                           WhereCondition:=strWhere)
  10. End With
Oct 23 '12 #5

P: 68
Thanks NeoPa, I take it that you didn't understand my question as so you are terming it as being vague. Well I am simply wanting to have ages entered into two fields in a form that depend on a table for an example:
Field1 = 20
Field2 = 30
Now, these ages will serve as the age range I would like to generate from a table I already have.
I would like to place a control button that will hold the code and when clicked, will generate the age range between 20 and 30.

I only posted the code in there to give you all an idea of answer you all helped in providing with selecting from a dropdown and using a control button to populate the query depending on the selection.

Thanks
Oct 23 '12 #6

NeoPa
Expert Mod 15k+
P: 31,186
Stoic:
Thanks NeoPa, I take it that you didn't understand my question as so you are terming it as being vague.
Indeed. I doubt I understood it fully as you intended it, due to the vague wording of the question, but hopefully I understood enough to give the important point of the answer (In fact it was Rabbit who first provided that info and I just added an extra example/illustration with a view to making it clearer).
Oct 23 '12 #7

P: 68
Thanks NeoPa, but it is not working for the result I want to have. Ok, this is it:
I have a form with two fields (Field1 and Field2); also on the form, I have a command button that will hold the code to generate the report.
Now, I would like to enter my data and generate a report from a query(AgeRange) in the following format:

Data Entry
Field1 = 15; Field2 = 20

Command Button OnClick Result:
Name Age
James 15
Frank 16
Smith 17
Matthew 18
Esther 19
Fred 20

This is what I have as a code for the command button.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport "rptWomenInPoliticsAndAge", acViewReport, , "strAge = '" & Me.cboBeginningAge & Me.cboEndingAge & "'"
  2.  
I am not really finding my way out.
Please help.
Thanks
Oct 24 '12 #8

P: 68
I have tried with this code but it is giving me an error:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport "rptWomenInPoliticsAndAge", acViewReport, , "strAge = '" & Me.cboBeginningAge & "strAge = '" & Me.cboEndingAge & "'"
Error message:
Run-time error '3075
Syntax error (missing operator) in query expression 'strAge = '35strAge = '59".
Oct 24 '12 #9

Rabbit
Expert Mod 10K+
P: 12,315
That's because your criteria is improperly quoted and syntaxed. But that point is moot.

Even if it were properly quoted and syntaxed, ie it looked like this someField = 'xx' And someField='yy', it still wouldn't work because the logic would be wrong. You need to use the BETWEEN operator that was mentioned before.
Oct 24 '12 #10

NeoPa
Expert Mod 15k+
P: 31,186
If you ask a question, then we give you an answer, the idea is to test out using that answer. Coming back saying it's not working when you're still using something completely different from what was suggested is not going to get anyone anywhere. How can we help you in a situation like this except by telling you to go back and try to follow the guidance already given.
Oct 24 '12 #11

Post your reply

Sign in to post your reply or Sign up for a free account.