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

Search form to pull up records with values between two numbers

P: 6
Okay, so I'm working on a database application with a search form that covers a ton of fields. And everything worked fine, until I tried to set it to pull records that have a value between two numbers. These numbers are years, but the fields aren't in a date format, just a normal integer format. The field in the table is called ActiveDate, and the search form has numActiveFrom and numActiveTo. I can't seem to get the code to run it, and I can't figure out what's wrong with the damned thing. The text fields all work fine, but not the number fields specified above. Below is the bit of code that relates to this field. I'm also including the bit just before and the bit just after it for context. Any help would be very much appreciated. Please let me know if you need any other details.

Expand|Select|Wrap|Line Numbers
  1.     ' Do Public Knowledge next
  2.     If Not IsNothing(Me.txtPublic) Then
  3.         ' .. add to the predicate
  4.         varWhere = (varWhere + " AND ") & "[PublicKnowledge] LIKE '*" & Me.txtPublic & "*'"
  5.     End If
  6.  
  7.     ' Check Active From first
  8.     If Not IsNothing(Me.numActiveFrom) Then
  9.         ' .. build the predicate
  10.         varWhere = (varWhere + " AND ") & "[ActiveDate] >= #" & CInt(Me.numActiveFrom) & "#"
  11.     End If
  12.  
  13.     ' Then, do Active To
  14.     If Not IsNothing(Me.numActiveTo) Then
  15.         ' .. add to the predicate
  16.         varWhere = (varWhere + " AND ") & "[ActiveDate] <= #" & CInt(Me.numActiveTo) & "#"
  17.     End If
  18.  
  19.    ' Check to see that we built a filter
  20.     If IsNothing(varWhere) Then
  21.         MsgBox "You must enter at least one search criteria.", vbInformation, gstrAppTitle
  22.         Exit Sub
  23.     End If
  24.  
  25.     ' Open a recordset to see if any rows returned with this filter
  26.     Set rst = db.OpenRecordset("SELECT * FROM [tblCharactersMain] WHERE " & varWhere)
  27.     ' See if found none
  28.     If rst.RecordCount = 0 Then
  29.         MsgBox "No characters meet your criteria.", vbInformation, gstrAppTitle
  30.         ' Clean up recordset
  31.         rst.Close
  32.         Set rst = Nothing
  33.         Exit Sub
  34.     End If
  35.  
Jul 17 '10 #1
Share this Question
Share on Google+
11 Replies


Expert 100+
P: 1,240
If all the fields are integers, the problem is you need to remove the "#" from your code. You don't use the # delimiter unless you are working with date/time fields.

Jim
Jul 17 '10 #2

ADezii
Expert 5K+
P: 8,703
Assuming your [From] and [To] Fields represent Years (Integers), then the General Syntax would be:
The 1st Day of the from Field to the Last Day of the To Field, namely:
Expand|Select|Wrap|Line Numbers
  1. "SELECT * FROM YaDa-YaDa WHERE [ActiveDate] BETWEEN #" & DateSerial(Me![numActiveFrom], 1, 1) & _
  2. "# AND #" & DateSerial(Me![numActiveTo], 12, 31) & "#"
Jul 17 '10 #3

P: 6
jimatqsi, I tried using the same code with the ' instead, as I've done with my other fields, but it didn't work then either. Is there something else I should be doing?

ADezii, I tried using the code you provided (with the proper adaptations, of course). But when I try to run a search it tells me there are no records that fit the search, no matter what years I enter. Also, I need the search form to work even if the person only enters the start year, or if they only enter the end year. I did something similar to this several years ago (I'm really out of practice with this), but it was with date fields, not plain number fields, so the code doesn't translate across quite right.

Thanks so much for trying to help, guys, but I'm just not quite there yet. Any other suggestions?
Jul 17 '10 #4

ADezii
Expert 5K+
P: 8,703
@TheKimmy
I tested the code before I sent it to you and it does work. Your Integer Field does represent Years, correct (2000, 2010, 2005, 1942, etc.)?
Jul 17 '10 #5

P: 6
@ADezii
Those are the types of things that are entered, yes. It's just a general number field, so it's not specific to just years, any kind of number could be entered. Maybe it was the alterations I had to make to make that search string part of the main search string? It's possible that I did that wrong. Here's how it looks when I do it:

Expand|Select|Wrap|Line Numbers
  1.     ' Do Public Knowledge next
  2.     If Not IsNothing(Me.txtPublic) Then
  3.         ' .. add to the predicate
  4.         varWhere = (varWhere + " AND ") & "[PublicKnowledge] LIKE '*" & Me.txtPublic & "*'"
  5.     End If
  6.  
  7.     ' Check Active From First
  8.     If Not IsNothing(Me.numActiveFrom) Then
  9.         ' .. build the predicate
  10.         varWhere = (varWhere + " AND ") & "[ActiveDate] BETWEEN #" & DateSerial(Me![numActiveFrom], 1, 1) & _
  11. "# AND #" & DateSerial(Me![numActiveTo], 12, 31) & "#"
  12.     End If
  13.  
  14.    ' Check to see that we built a filter
  15.     If IsNothing(varWhere) Then
  16.         MsgBox "You must enter at least one search criteria.", vbInformation, gstrAppTitle
  17.         Exit Sub
  18.     End If
  19.  
  20.     ' Open a recordset to see if any rows returned with this filter
  21.     Set rst = db.OpenRecordset("SELECT * FROM [tblCharactersMain] WHERE " & varWhere)
  22.     ' See if found none
  23.     If rst.RecordCount = 0 Then
  24.         MsgBox "No characters meet your criteria.", vbInformation, gstrAppTitle
  25.         ' Clean up recordset
  26.         rst.Close
  27.         Set rst = Nothing
  28.         Exit Sub
  29.     End If
  30.  
Jul 17 '10 #6

P: 6
Has nobody had any luck figuring this?
Jul 21 '10 #7

NeoPa
Expert Mod 15k+
P: 31,769
What do you have at the moment Kimmy?

This shouldn't be too difficult, but I expect some confusion is getting into the picture via the communication over a web page.

If both the search items and the item being searched are in the form of integers then hashes (#) will not be required. What will be (almost certainly) is the "Between" form of criteria. Something like :
Expand|Select|Wrap|Line Numbers
  1. strWhere = strWhere & " AND " & _
  2.            "([ActiveDate] Between " & Me.numActiveFrom & _
  3.                             " And " & Me.numActiveTo & ")"
Check out Example Filtering on a Form and post back if you find you're still struggling. I'm confident we can find your solution assuming we can fully understand the precise problem.
Jul 21 '10 #8

P: 6
@NeoPa
The very first post has the code for what I started with, and my last post before yours has the code for what ADezii had suggested, with a few alterations I had to make that I was afraid might have screwed up his code.

I tried the code that you provided, which worked beautifully when both the From and the To dates were entered, which is a far sight further than I've gotten before. The only problem with that is that I also need to be able to only enter the From or To years by themselves. For example, if I put 1980 in the To field with nothing in the From field, I want to be able to pull up every record at or before 1980. Or, if I put 1960 in the From field with nothing in the To field, I want to pull up every record at or after 1960.

Would that be possible, or am I dreaming?
Jul 21 '10 #9

ADezii
Expert 5K+
P: 8,703
@TheKimmy
It is definately possible, but unfortunately I'm on Vacation right now. If no one comes up with the answer, I'll provide it to you as soon as I get home.
Jul 21 '10 #10

P: 6
@ADezii
Thanks much. I don't mean to be a noodge, but this is for my boyfriend so I'm trying to be brilliant beyond my habits. Enjoy your vacation!
Jul 21 '10 #11

NeoPa
Expert Mod 15k+
P: 31,769
For more flexibility in this I could do no better than to point you to a similar thread Is it possible to have flexible formatting on date in filter? I'm currently involved in.

Let us know if you find enough in there or if you need further explanation.

by its very nature this is more complex than a simple From/To situation, so don't be surprised if the code is somewhat more complicated.
Jul 22 '10 #12

Post your reply

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