Hi there, I'm returning to this forum for the second time and I would like to say thanks for the great help provided. I've encountered a new problem now and hope that you will be able to help me again.
I'm currently implementing a container monitoring system in MSAccess07 and I'm using a multiple search function to sort containers. I use several unbound text boxes and a list box.The user type's in partial search criteria in one or several text boxes. Or if they don't key in anything it returns all records. The matches are returned in a listbox.
I will show the code for 1 text box just to show how it works.
This is the code for the search button: -
Private Sub cmdShowCont_Click()
-
Dim MySQL As String, mycriteria As String, MyRecordSource As String
-
Dim argcount As Integer
-
Dim Tmp As Variant
-
mycriteria = " "
-
argcount = 0
-
MySQL = "SELECT * FROM qryContLife WHERE "
-
-
Addwtf [textbox], "[Arrival Date]", mycriteria, argcount, "textbox"
-
-
Debug.Print mycriteria
-
If mycriteria = " " Then
-
mycriteria = "True"
-
End If
-
-
MyRecordSource = MySQL & mycriteria
-
Me![listbox].RowSource = MyRecordSource
-
If Me![listbox].ListCount = 0 Then
-
MsgBox " There are no containers with this criteria. Sorry ", 48
-
Me!cmdClear.SetFocus
-
Else
-
Me![listbox].SetFocus
-
End If
-
-
End Sub
-
This is the code for the sub which builds the SQL statement and through which the values are returned to the listbox: -
Private Sub Addwtf(fieldvalue As Variant, fieldname As String, mycriteria As String, argcount As Integer, fieldo As String)
-
Dim wtf1 As String, wtf2, wtf3 As String
-
-
If fieldvalue <> "" Then
-
If argcount > 0 Then
-
mycriteria = mycriteria & " and "
-
-
End If
-
-
Select Case fieldo
-
-
Case "textbox"
-
wtf2 = Mid$(fieldvalue, 4, 2) & "/" & Mid$(fieldvalue, 1, 2) & "/" & Mid$(fieldvalue, 7, 4)
-
mycriteria = (mycriteria & fieldname & " = " & Chr(35) & wtf2 & Chr(35))
-
-
argcount = argcount + 1
-
End If
-
End Sub
-
The question is whether it is possible to make the search not for a specific date like the "Arrival Date" in this case (e.g. 01/12/2008) but for a specified range of dates like all values between 01/12/2008 and 01/01/2009. So that the code returns not just one value but all values in a specified range.
I hope I explained everything in a clear matter, if not please ask questions and I will try and make it more clear.
Thank you for spending your time of somebody else's problems.
Best regards, Eugene.
10 3972 ADezii 8,834
Recognized Expert Expert
To search for Records falling within a specified Date Range, use the BETWEEN operator making sure to delimit the Date Fields with the '#' qualifier. Assuming entry Fields of [txtBeginDate] and [txtEndDate], the general logic would be: - Dim MyDB As DAO.Database
-
Dim MyRS As DAO.Recordset
-
Dim strSQL As String
-
-
Set MyDB = CurrentDb()
-
-
If IsDate(Me![txtBeginDate]) And IsDate(Me![txtEndDate]) Then
-
If CDate(Me![txtBeginDate]) < CDate(Me![txtEndDate]) Then
-
strSQL = "Select * From tblTest Where [BirthDate] Between #" & Me![txtBeginDate] & _
-
"# And #" & Me![txtEndDate] & "# Order By [BirthDate];"
-
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
-
If Not MyRS.BOF And Not MyRS.EOF Then 'valid Records
-
Do While Not MyRS.EOF
-
Debug.Print MyRS![LastName] & " ==> " & MyRS![BirthDate] 'Test Loop
-
MyRS.MoveNext
-
Loop
-
Else
-
MsgBox "No Records meet your criteria", vbExclamation, "No Records"
-
End If
-
Else
-
MsgBox "End Date must be > Begin Date", vbExclamation, "Invalid Date"
-
Exit Sub
-
End If
-
Else
-
'simply fall through, don't process
-
Exit Sub
-
End If
-
-
MyRS.Close
-
Set MyRS = Nothing
P.S. - Any questions, feel free to ask.
Thanks for your attention. Right, is it possible to incorporate the example u gave me into the existing code? I mean so that I would be able to make searches using various criteria e.g. sort the data by the date range between 01/01/2008 and 01/02/2008 and some other piece of data like container size for instance.
I really hope I'm being clear. Unfortunately I don't have a chance to test ur example right now, but I'm going to do that first thing in the morning and inform u of the results.
thank u again ;)
ADezii 8,834
Recognized Expert Expert
Thanks for your attention. Right, is it possible to incorporate the example u gave me into the existing code? I mean so that I would be able to make searches using various criteria e.g. sort the data by the date range between 01/01/2008 and 01/02/2008 and some other piece of data like container size for instance.
I really hope I'm being clear. Unfortunately I don't have a chance to test ur example right now, but I'm going to do that first thing in the morning and inform u of the results.
thank u again ;)
Code has been tested, and seems to work quite well, given the existing parameters.
ADezii, I never used the Recordset function so I don't fully understand how the code works. In your example you have a table with birthdates and a form with 2 text boxes, this part I get, but this one scares me: -
-
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
-
-
If Not MyRS.BOF And Not MyRS.EOF Then 'valid Records
-
Do While Not MyRS.EOF
-
Debug.Print MyRS![LastName] & " ==> " & MyRS![BirthDate] 'Test Loop
-
MyRS.MoveNext
-
Loop
-
-
Else
-
-
MsgBox "No Records meet your criteria", vbExclamation, "No Records"
-
-
End If
-
The 5th line gives and error in the debugger, and how exactly it shows the retrieved data? does it open a new window or it requires a list box etc? And also where does the [Lastname] come from ?
NeoPa 32,579
Recognized Expert Moderator MVP
Debug.Print shows data in the Immediate Pane. Use Alt-F11 to switch to the debugger from your Access window, then Ctrl-G to open, and switch to, the Immediate Pane. Your original post includes similar code so one would have thought this was familiar to you.
MyRS![LastName] is a reference to a field (called LastName) from the recordset provided by the SQL in strSQL (See line #1).
If you have no such field then you need to adjust the code to match what you are working with.
ADezii 8,834
Recognized Expert Expert
Sorry Eugenio, but based on your original code, I made the false assumption that that you would fully comprehend the logic which I have posted. I completely apologize for this oversight. NeoPa provided the necessary explanations of my code in Post #6, and as far as a Recordset goes, in your case, it is a virtual representation of the SQL Statement represented by strSQL. P.S. - Thanks NeoPa.
thanks guys, i will be working on this and will post if I encounter any difficulties. Sorry for the confusion I caused. Thank you.
right, i've managed to create a search for a sepcific date range using your example and it looks like this: -
Dim MyRecordSource As String
-
Dim strSQL As String
-
-
-
If IsDate(Me![txtBeginDate]) And IsDate(Me![txtEndDate]) Then
-
If CDate(Me![txtBeginDate]) < CDate(Me![txtEndDate]) Then
-
strSQL = "Select * From tblTest Where [BirthDate] Between #" & Me![txtBeginDate] & _
-
"# And #" & Me![txtEndDate] & "# Order By [BirthDate];"
-
-
-
-
End If
-
End If
-
-
MyRecordSource = strSQL
-
Me![lstContlist].RowSource = MyRecordSource
-
If Me![lstContlist].ListCount = 0 Then
-
MsgBox " There are no containers with this criteria. Sorry ", 48
-
Me!cmdClear.SetFocus
-
Else
-
Me![lstContlist].SetFocus
-
End If
-
It shows the found values in the listbox, but is it possible to incorporate it into the existing multiple search function: -
Dim wtf1 As String, wtf2, wtf3 As String
-
-
If fieldvalue <> "" Then
-
If argcount > 0 Then
-
mycriteria = mycriteria & " and "
-
-
End If
-
-
Select Case fieldo
-
Case "myd1"
-
mycriteria = (mycriteria & fieldname & " Like " & Chr(39) & fieldvalue & "*" & Chr(39))
-
-
Case "myd2"
-
mycriteria = (mycriteria & fieldname & " Like " & Chr(39) & fieldvalue & "*" & Chr(39))
-
-
Case "myd3"
-
wtf2 = Mid$(fieldvalue, 4, 2) & "/" & Mid$(fieldvalue, 1, 2) & "/" & Mid$(fieldvalue, 7, 4)
-
mycriteria = (mycriteria & fieldname & " = " & Chr(35) & wtf2 & Chr(35))
-
-
Case "myd4"
-
wtf3 = Mid$(fieldvalue, 4, 2) & "/" & Mid$(fieldvalue, 1, 2) & "/" & Mid$(fieldvalue, 7, 4)
-
mycriteria = (mycriteria & fieldname & " <= " & Chr(35) & wtf3 & Chr(35))
-
-
Case "myd5"
-
mycriteria = (mycriteria & fieldname & " Like " & Chr(39) & fieldvalue & "*" & Chr(39))
-
-
Case Else
-
-
mycriteria = (mycriteria & fieldname & " Like " & Chr(39) & fieldvalue & Chr(39))
-
End Select
-
-
argcount = argcount + 1
-
End If
-
and allow it to search for records with the date range given and a specific status for example. in other words allow multiple search criteria?
P.s. I'm sorry in advance because this may sound stupid, but I'm using access for less than a year and still have a lot to learn.
ADezii 8,834
Recognized Expert Expert
right, i've managed to create a search for a sepcific date range using your example and it looks like this: -
Dim MyRecordSource As String
-
Dim strSQL As String
-
-
-
If IsDate(Me![txtBeginDate]) And IsDate(Me![txtEndDate]) Then
-
If CDate(Me![txtBeginDate]) < CDate(Me![txtEndDate]) Then
-
strSQL = "Select * From tblTest Where [BirthDate] Between #" & Me![txtBeginDate] & _
-
"# And #" & Me![txtEndDate] & "# Order By [BirthDate];"
-
-
-
-
End If
-
End If
-
-
MyRecordSource = strSQL
-
Me![lstContlist].RowSource = MyRecordSource
-
If Me![lstContlist].ListCount = 0 Then
-
MsgBox " There are no containers with this criteria. Sorry ", 48
-
Me!cmdClear.SetFocus
-
Else
-
Me![lstContlist].SetFocus
-
End If
-
It shows the found values in the listbox, but is it possible to incorporate it into the existing multiple search function: -
Dim wtf1 As String, wtf2, wtf3 As String
-
-
If fieldvalue <> "" Then
-
If argcount > 0 Then
-
mycriteria = mycriteria & " and "
-
-
End If
-
-
Select Case fieldo
-
Case "myd1"
-
mycriteria = (mycriteria & fieldname & " Like " & Chr(39) & fieldvalue & "*" & Chr(39))
-
-
Case "myd2"
-
mycriteria = (mycriteria & fieldname & " Like " & Chr(39) & fieldvalue & "*" & Chr(39))
-
-
Case "myd3"
-
wtf2 = Mid$(fieldvalue, 4, 2) & "/" & Mid$(fieldvalue, 1, 2) & "/" & Mid$(fieldvalue, 7, 4)
-
mycriteria = (mycriteria & fieldname & " = " & Chr(35) & wtf2 & Chr(35))
-
-
Case "myd4"
-
wtf3 = Mid$(fieldvalue, 4, 2) & "/" & Mid$(fieldvalue, 1, 2) & "/" & Mid$(fieldvalue, 7, 4)
-
mycriteria = (mycriteria & fieldname & " <= " & Chr(35) & wtf3 & Chr(35))
-
-
Case "myd5"
-
mycriteria = (mycriteria & fieldname & " Like " & Chr(39) & fieldvalue & "*" & Chr(39))
-
-
Case Else
-
-
mycriteria = (mycriteria & fieldname & " Like " & Chr(39) & fieldvalue & Chr(39))
-
End Select
-
-
argcount = argcount + 1
-
End If
-
and allow it to search for records with the date range given and a specific status for example. in other words allow multiple search criteria?
P.s. I'm sorry in advance because this may sound stupid, but I'm using access for less than a year and still have a lot to learn.
Here is where it would really become complicated. - You could use the following as your base Criteria String:
- strSQL = "Select * From tblTest Where [BirthDate] Between #" & Me![txtBeginDate] & "# And #" & Me![txtEndDate] & "# "
- then build on the strSQL, as in:
- strSQL = strSQL & "AND <specify other criteria here> AND <specify other criteria here> "
- Tack on a final Order By Clause, if needed:
- strSQL = strSQL & "Order By [BirthDate];"
- There are other Experts/Moderators who are much better than I in the SQL lingo, hopefully they will join the discussion, and provide you with a better response.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Dixie |
last post by:
I have a problem using Dev Ashish's excellent module to concatenate the
results of a field from several records into one record.
I am using the code to concatenate certain awards onto a certificate at the
end of the year. I have the code working fine, except for the fact that
when I want to restrict the entries to awards between certain dates, even
though I can use the restriction in the query that shows the actual records,
when the...
|
by: tshad |
last post by:
Can you do a search for more that one string in another string?
Something like:
someString.IndexOf("something1","something2","something3",0)
or would you have to do something like:
if ((someString.IndexOf("something1",0) >= 0) ||
((someString.IndexOf("something2",0) >= 0) ||
|
by: vonclausowitz |
last post by:
Repost from an VB group.
Hi All,
I'm looking for a way to search for multiple words in a database.
There is however one but. The words have to be within a certain range
of each other.
For example I would like to find records which contain the word MALE
and within for example 10 words of it the word BROWN.
|
by: blyxx86 |
last post by:
I am trying to filter multiple entries with only one search box. That way the user can type whatever they want and find all the values that contain what they enter.
Private Sub txtLookup_AfterUpdate()
Me.Filter = " Like '*" & & "*'" OR " Like '*" & & "*'"
Me.FilterOn = True
End Sub
It allows for variables and to filter within the form. I can't seem to find a way to make it actually search through multiple fields (Serial,...
|
by: Kosmos |
last post by:
Alright so I've got this Outlook code written in VBA in Access. The first part, which works, records information about appointment times based on the required days before notification of certain contracts and then it adds them to the outlook calendar of the current user. This code works and is nested within a bunch of if statements because it only needs to trap certain appointments. The table I create with this code is later used to attempt to...
| |
by: Rama Jayapal |
last post by:
hi
i am developing a web application where i have to read multiple XML feeds amd store their values to database
but i require the same type of fields from multiple XML feeds like
for example
|
by: Thurston Manson |
last post by:
Suppose I'm using an implementation where an int is 16 bits. In the
program below, what function is called in the first case, and what is
called in the second case? Also, if there is a difference between C89
and C99, I would like to know. I have tried with different compilers,
and I see some differences. Before I file a bug report with my C
vendor, I would like to know what the correct behavior is.
struct S
{
unsigned a:4;
|
by: Miro |
last post by:
Hi sorry for the quick question. I am on the right track but just cant seem
to find what to search for in my books / online for the answer I am looking
for.
I have created a dataset within my project.
In the dataset I created 2 tables - and set some fields on the tables.
In each table I set one of the fields to be a primary key / unique auto
incremenet.
So now I create a form, add a datagrid view to it and add to the dataset.
|
by: annemariearmour |
last post by:
I am using Crystal reports version 11.2 to create reports. The data source is SQL Server, and I am using views rather than reporting directly from tables.
I apply selection criteria to the incoming records at record level and at group level. For example, I may select all bill lines with a receipt date within a certain date range. This results in a list of bill lines within that date range.
The records are grouped and values on the records...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |