I need help with a VBA search code

[Steve Taber]

I've set up a database that uses a search box to look up reports. I borrowed
some code from a training book, but it isn't working right. The information
fields within the search box are populated. When I hit the search button it
does open the form I want, but it doesn't contain any information., Here is
the code:

Private Sub Command5_Click()
Dim strWhere As String, varItem As Variant
' Request to edit items selected in the list box
' If no items selected, then nothing to do
If Me!List14.ItemsSelected.Count = 0 Then Exit Sub
' Loop through the items selected collection
For Each varItem In Me!List14.ItemsSelected
' Grab the CompanyID column for each selected item
strWhere = strWhere & Me!List14.Column(0, varItem) & ","
Next varItem
' Throw away the extra comma on the "IN" string
strWhere = Left$(strWhere, Len(strWhere) - 1)
' Open the companies form filtered on the selected companies
strWhere = "[Date] IN (" & strWhere & ")"
DoCmd.OpenForm FormName:="Incident Report", WhereCondition:=strWhere
DoCmd.Close acForm, Me.Name
End Sub

[Date] is the field I am using as a reference from my query. Incident Report
is the form name.

I have almost no idea what I am doing with VBA. Anyone have any ideas?
Thanks,
Steve

 

[Steve Taber]

I did have an additional entry to one line:

strWhere = "[Date] IN (" & strWhere & ") And (Inactive = False)"

This would pull up the report, but with all of the records. I hope this
narrows down the problem.
Please help,
Steve

 

[Dirk Goldgar]

I've set up a database that uses a search box to look up reports. I
borrowed some code from a training book, but it isn't working right.
The information fields within the search box are populated. When I
hit the search button it does open the form I want, but it doesn't
contain any information., Here is the code:

Private Sub Command5_Click()
Dim strWhere As String, varItem As Variant
' Request to edit items selected in the list box
' If no items selected, then nothing to do
If Me!List14.ItemsSelected.Count = 0 Then Exit Sub
' Loop through the items selected collection
For Each varItem In Me!List14.ItemsSelected
' Grab the CompanyID column for each selected item
strWhere = strWhere & Me!List14.Column(0, varItem) & ","
Next varItem
' Throw away the extra comma on the "IN" string
strWhere = Left$(strWhere, Len(strWhere) - 1)
' Open the companies form filtered on the selected companies
strWhere = "[Date] IN (" & strWhere & ")"
DoCmd.OpenForm FormName:="Incident Report",
WhereCondition:=strWhere DoCmd.Close acForm, Me.Name
End Sub

[Date] is the field I am using as a reference from my query. Incident
Report is the form name.

I have almost no idea what I am doing with VBA. Anyone have any ideas?

Are these date values that you're getting from the list box and
stringing together into strWhere, and is [Date] a date/time field? If
so, you need to surround each value with '#' characters. Also, to
ensure the correct interpretation of each date value, you should make
sure they are formatted into MM/DD/YYYY format. Try this version of the
line that builds the strWhere string:

strWhere = strWhere & _
Format(Me!List14.Column(0, varItem), "\#mm/dd/yyyy\#") & _
","

If that doesn't fix it, you may be having problems because of the field
name "Date". That's a bad choice of names, because it's also the name
of both a data type and a built-in function. It's good that you wrapped
the square brackets around it, but you might have to qualify it with the
table name -- I'm not sure.

 

[Steve Taber]

Dirk,
No dice. I added the code and it had the same effect. I have a sterilized
version of my DB that is only 1.85mb. Can I send it to you to look at?
Steve

I've set up a database that uses a search box to look up reports. I
borrowed some code from a training book, but it isn't working right.
The information fields within the search box are populated. When I
hit the search button it does open the form I want, but it doesn't
contain any information., Here is the code:

Private Sub Command5_Click()
Dim strWhere As String, varItem As Variant
' Request to edit items selected in the list box
' If no items selected, then nothing to do
If Me!List14.ItemsSelected.Count = 0 Then Exit Sub
' Loop through the items selected collection
For Each varItem In Me!List14.ItemsSelected
' Grab the CompanyID column for each selected item
strWhere = strWhere & Me!List14.Column(0, varItem) & ","
Next varItem
' Throw away the extra comma on the "IN" string
strWhere = Left$(strWhere, Len(strWhere) - 1)
' Open the companies form filtered on the selected companies
strWhere = "[Date] IN (" & strWhere & ")"
DoCmd.OpenForm FormName:="Incident Report",
WhereCondition:=strWhere DoCmd.Close acForm, Me.Name
End Sub

[Date] is the field I am using as a reference from my query. Incident
Report is the form name.

I have almost no idea what I am doing with VBA. Anyone have any ideas?

Are these date values that you're getting from the list box and
stringing together into strWhere, and is [Date] a date/time field? If
so, you need to surround each value with '#' characters. Also, to
ensure the correct interpretation of each date value, you should make
sure they are formatted into MM/DD/YYYY format. Try this version of the
line that builds the strWhere string:

strWhere = strWhere & _
Format(Me!List14.Column(0, varItem), "\#mm/dd/yyyy\#") & _
","

If that doesn't fix it, you may be having problems because of the field
name "Date". That's a bad choice of names, because it's also the name
of both a data type and a built-in function. It's good that you wrapped
the square brackets around it, but you might have to qualify it with the
table name -- I'm not sure.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk,
No dice. I added the code and it had the same effect. I have a
sterilized version of my DB that is only 1.85mb. Can I send it to you
to look at? Steve

strWhere = "[Date] IN (" & strWhere & ")"

Debug.Print strWhere

DoCmd.OpenForm FormName:="Incident Report", _
WhereCondition:=strWhere

Then run the code after selecting a few items in the list box, and check
in the Immediate Window afterward and post back with the value that is
printed for strWhere.

 

[Steve Taber]

Here are the values from the immidiate window:

[Date] IN (#03/04/2005#)
[Date] IN (#03/04/2005#)
[Date] IN (#03/13/2005#)
[Date] IN (#03/09/2005#)
[Date] IN (#03/25/2005#)

Dirk,
No dice. I added the code and it had the same effect. I have a
sterilized version of my DB that is only 1.85mb. Can I send it to you
to look at? Steve

strWhere = "[Date] IN (" & strWhere & ")"

Debug.Print strWhere

DoCmd.OpenForm FormName:="Incident Report", _
WhereCondition:=strWhere

Then run the code after selecting a few items in the list box, and check
in the Immediate Window afterward and post back with the value that is
printed for strWhere.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Here are the values from the immidiate window:

[Date] IN (#03/04/2005#)
[Date] IN (#03/04/2005#)
[Date] IN (#03/13/2005#)
[Date] IN (#03/09/2005#)
[Date] IN (#03/25/2005#)

So you ran it 5 times, selecting just one date each time? If that's the
case, then those results look okay. What is the "Incident Report"
form's Record Source (from the Data tab of the form's property sheet in
design view)? If it's a table, please tell me its name, and verify that
it contains a field actually named "Date". If the Record Source is a
query, please post the SQL of the query.

 

[Steve Taber]

The record source is a query. Here is the SQL, it's pretty big.

SELECT [Incident Report].Date, [Incident Report].[Time of Incident],
[Incident Report].[LEO Call Time], [Incident Report].[LEO Arrival Time],
[Incident Report].[Responding Officer / Agent], [Incident Report].[Responding
Agency], [Incident Report].[Location of Incident], [Incident Report].Other,
[Incident Report].Name, [Incident Report].[Date of Birth], [Incident
Report].Address, [Incident Report].City, [Incident Report].State, [Incident
Report].[Zip Code], [Incident Report].[Place of Birth], [Incident
Report].Phone, [Incident Report].[Type of Indentification], [Incident
Report].[State of Issue], [Incident Report].[ID #], [Incident Report].[Date
of Issue], [Incident Report].Selectee, [Incident Report].[Airline / Flight
#], [Incident Report].[Flight Itinerary (From / To)], [Incident Report].[Gate
#], [Incident Report].[Departure Time], [Incident Report].[Flight Delay],
[Incident Report].[# of Passengers Delayed], [Incident Report].[Type of
Incident], [Incident Report].[Other 1], [Incident Report].[Checkpoint
Closed], [Incident Report].[Firearm Loaded], [Incident Report].[Weapon
Discharged], [Incident Report].[Rounds Fired], [Incident Report].[Ammo or
Rounds], [Incident Report].Make, [Incident Report].Caliber, [Incident
Report].[Condition of Firearm], [Incident Report].[Local Notifications],
[Incident Report].[Other 2], [Incident Report].[Other 3], [Incident
Report].[Other 4], [Incident Report].[Other 5], [Incident Report].[Other 6],
[Incident Report].[Passenger NCIC Ran], [Incident Report].[Warrants Clear],
[Incident Report].[Citation Issued], [Incident Report].[Travel Allowed],
[Incident Report].[Passenger Arrested], [Incident Report].[Passenger Removed
From The Airport], [Incident Report].[Airline GSC Notified], [Incident
Report].[Passenger Released], [Incident Report].[Other 7], [Incident
Report].[Description of the Incident], [Incident Report].[Individual
Completing the Report], [Incident Report].[Screening Manager Notified],
[Incident Report].[Time Notified]
FROM [Incident Report]
ORDER BY [Incident Report].Date, [Incident Report].[Location of Incident];

Here are the values from the immidiate window:

[Date] IN (#03/04/2005#)
[Date] IN (#03/04/2005#)
[Date] IN (#03/13/2005#)
[Date] IN (#03/09/2005#)
[Date] IN (#03/25/2005#)

So you ran it 5 times, selecting just one date each time? If that's the
case, then those results look okay. What is the "Incident Report"
form's Record Source (from the Data tab of the form's property sheet in
design view)? If it's a table, please tell me its name, and verify that
it contains a field actually named "Date". If the Record Source is a
query, please post the SQL of the query.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

The record source is a query. Here is the SQL, it's pretty big.

SELECT [Incident Report].Date, [Incident Report].[Time of Incident],
[Incident Report].[LEO Call Time], [Incident Report].[LEO Arrival
Time], [Incident Report].[Responding Officer / Agent], [Incident
Report].[Responding Agency], [Incident Report].[Location of
Incident], [Incident Report].Other, [Incident Report].Name, [Incident
Report].[Date of Birth], [Incident Report].Address, [Incident
Report].City, [Incident Report].State, [Incident Report].[Zip Code],
[Incident Report].[Place of Birth], [Incident Report].Phone,
[Incident Report].[Type of Indentification], [Incident Report].[State
of Issue], [Incident Report].[ID #], [Incident Report].[Date of
Issue], [Incident Report].Selectee, [Incident Report].[Airline /
Flight #], [Incident Report].[Flight Itinerary (From / To)],
[Incident Report].[Gate #], [Incident Report].[Departure Time],
[Incident Report].[Flight Delay], [Incident Report].[# of Passengers
Delayed], [Incident Report].[Type of Incident], [Incident
Report].[Other 1], [Incident Report].[Checkpoint Closed], [Incident
Report].[Firearm Loaded], [Incident Report].[Weapon Discharged],
[Incident Report].[Rounds Fired], [Incident Report].[Ammo or Rounds],
[Incident Report].Make, [Incident Report].Caliber, [Incident
Report].[Condition of Firearm], [Incident Report].[Local
Notifications], [Incident Report].[Other 2], [Incident Report].[Other
3], [Incident Report].[Other 4], [Incident Report].[Other 5],
[Incident Report].[Other 6], [Incident Report].[Passenger NCIC Ran],
[Incident Report].[Warrants Clear], [Incident Report].[Citation
Issued], [Incident Report].[Travel Allowed], [Incident
Report].[Passenger Arrested], [Incident Report].[Passenger Removed
From The Airport], [Incident Report].[Airline GSC Notified],
[Incident Report].[Passenger Released], [Incident Report].[Other 7],
[Incident Report].[Description of the Incident], [Incident
Report].[Individual Completing the Report], [Incident
Report].[Screening Manager Notified], [Incident Report].[Time
Notified]
FROM [Incident Report]
ORDER BY [Incident Report].Date, [Incident Report].[Location of
Incident];

Here are the values from the immidiate window:

[Date] IN (#03/04/2005#)
[Date] IN (#03/04/2005#)
[Date] IN (#03/13/2005#)
[Date] IN (#03/09/2005#)
[Date] IN (#03/25/2005#)

So you ran it 5 times, selecting just one date each time? If that's
the case, then those results look okay. What is the "Incident
Report" form's Record Source (from the Data tab of the form's
property sheet in design view)? If it's a table, please tell me its
name, and verify that it contains a field actually named "Date". If
the Record Source is a query, please post the SQL of the query.

Try changing the WhereCondition to explicitly specify the table as well
as the field name, like this:

strWhere = "[Incident Report].[Date] IN (" & strWhere & ")"

If the problem has been confusion on the part of the database engine as
to whether "Date" refers to the field or the current date (as returned
by the Date function, that should fix it.

I take it you've verified that there are actually records in the table
for the dates you selected?

 

[Steve Taber]

No change. There is data in the table. Here are the results form the
immidiate window.

[Incident Report].[Date] IN (#03/06/2005#)
[Incident Report].[Date] IN (#03/10/2005#)

Sorry I'm wasting your day with this.

The record source is a query. Here is the SQL, it's pretty big.

SELECT [Incident Report].Date, [Incident Report].[Time of Incident],
[Incident Report].[LEO Call Time], [Incident Report].[LEO Arrival
Time], [Incident Report].[Responding Officer / Agent], [Incident
Report].[Responding Agency], [Incident Report].[Location of
Incident], [Incident Report].Other, [Incident Report].Name, [Incident
Report].[Date of Birth], [Incident Report].Address, [Incident
Report].City, [Incident Report].State, [Incident Report].[Zip Code],
[Incident Report].[Place of Birth], [Incident Report].Phone,
[Incident Report].[Type of Indentification], [Incident Report].[State
of Issue], [Incident Report].[ID #], [Incident Report].[Date of
Issue], [Incident Report].Selectee, [Incident Report].[Airline /
Flight #], [Incident Report].[Flight Itinerary (From / To)],
[Incident Report].[Gate #], [Incident Report].[Departure Time],
[Incident Report].[Flight Delay], [Incident Report].[# of Passengers
Delayed], [Incident Report].[Type of Incident], [Incident
Report].[Other 1], [Incident Report].[Checkpoint Closed], [Incident
Report].[Firearm Loaded], [Incident Report].[Weapon Discharged],
[Incident Report].[Rounds Fired], [Incident Report].[Ammo or Rounds],
[Incident Report].Make, [Incident Report].Caliber, [Incident
Report].[Condition of Firearm], [Incident Report].[Local
Notifications], [Incident Report].[Other 2], [Incident Report].[Other
3], [Incident Report].[Other 4], [Incident Report].[Other 5],
[Incident Report].[Other 6], [Incident Report].[Passenger NCIC Ran],
[Incident Report].[Warrants Clear], [Incident Report].[Citation
Issued], [Incident Report].[Travel Allowed], [Incident
Report].[Passenger Arrested], [Incident Report].[Passenger Removed
From The Airport], [Incident Report].[Airline GSC Notified],
[Incident Report].[Passenger Released], [Incident Report].[Other 7],
[Incident Report].[Description of the Incident], [Incident
Report].[Individual Completing the Report], [Incident
Report].[Screening Manager Notified], [Incident Report].[Time
Notified]
FROM [Incident Report]
ORDER BY [Incident Report].Date, [Incident Report].[Location of
Incident];

Here are the values from the immidiate window:

[Date] IN (#03/04/2005#)
[Date] IN (#03/04/2005#)
[Date] IN (#03/13/2005#)
[Date] IN (#03/09/2005#)
[Date] IN (#03/25/2005#)

So you ran it 5 times, selecting just one date each time? If that's
the case, then those results look okay. What is the "Incident
Report" form's Record Source (from the Data tab of the form's
property sheet in design view)? If it's a table, please tell me its
name, and verify that it contains a field actually named "Date". If
the Record Source is a query, please post the SQL of the query.

Try changing the WhereCondition to explicitly specify the table as well
as the field name, like this:

strWhere = "[Incident Report].[Date] IN (" & strWhere & ")"

If the problem has been confusion on the part of the database engine as
to whether "Date" refers to the field or the current date (as returned
by the Date function, that should fix it.

I take it you've verified that there are actually records in the table
for the dates you selected?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Ken Snell [MVP]]

PMFJI.... how did you create the data that are stored in the Date field? Was
it done by using Now() function? If yes, then you've stored the time in that
field as well, and your WHERE expression will not find a match in that case.
--

Ken Snell
<MS ACCESS MVP>

No change. There is data in the table. Here are the results form the
immidiate window.

[Incident Report].[Date] IN (#03/06/2005#)
[Incident Report].[Date] IN (#03/10/2005#)

Sorry I'm wasting your day with this.

The record source is a query. Here is the SQL, it's pretty big.

SELECT [Incident Report].Date, [Incident Report].[Time of Incident],
[Incident Report].[LEO Call Time], [Incident Report].[LEO Arrival
Time], [Incident Report].[Responding Officer / Agent], [Incident
Report].[Responding Agency], [Incident Report].[Location of
Incident], [Incident Report].Other, [Incident Report].Name, [Incident
Report].[Date of Birth], [Incident Report].Address, [Incident
Report].City, [Incident Report].State, [Incident Report].[Zip Code],
[Incident Report].[Place of Birth], [Incident Report].Phone,
[Incident Report].[Type of Indentification], [Incident Report].[State
of Issue], [Incident Report].[ID #], [Incident Report].[Date of
Issue], [Incident Report].Selectee, [Incident Report].[Airline /
Flight #], [Incident Report].[Flight Itinerary (From / To)],
[Incident Report].[Gate #], [Incident Report].[Departure Time],
[Incident Report].[Flight Delay], [Incident Report].[# of Passengers
Delayed], [Incident Report].[Type of Incident], [Incident
Report].[Other 1], [Incident Report].[Checkpoint Closed], [Incident
Report].[Firearm Loaded], [Incident Report].[Weapon Discharged],
[Incident Report].[Rounds Fired], [Incident Report].[Ammo or Rounds],
[Incident Report].Make, [Incident Report].Caliber, [Incident
Report].[Condition of Firearm], [Incident Report].[Local
Notifications], [Incident Report].[Other 2], [Incident Report].[Other
3], [Incident Report].[Other 4], [Incident Report].[Other 5],
[Incident Report].[Other 6], [Incident Report].[Passenger NCIC Ran],
[Incident Report].[Warrants Clear], [Incident Report].[Citation
Issued], [Incident Report].[Travel Allowed], [Incident
Report].[Passenger Arrested], [Incident Report].[Passenger Removed
From The Airport], [Incident Report].[Airline GSC Notified],
[Incident Report].[Passenger Released], [Incident Report].[Other 7],
[Incident Report].[Description of the Incident], [Incident
Report].[Individual Completing the Report], [Incident
Report].[Screening Manager Notified], [Incident Report].[Time
Notified]
FROM [Incident Report]
ORDER BY [Incident Report].Date, [Incident Report].[Location of
Incident];


:

Here are the values from the immidiate window:

[Date] IN (#03/04/2005#)
[Date] IN (#03/04/2005#)
[Date] IN (#03/13/2005#)
[Date] IN (#03/09/2005#)
[Date] IN (#03/25/2005#)

So you ran it 5 times, selecting just one date each time? If that's
the case, then those results look okay. What is the "Incident
Report" form's Record Source (from the Data tab of the form's
property sheet in design view)? If it's a table, please tell me its
name, and verify that it contains a field actually named "Date". If
the Record Source is a query, please post the SQL of the query.

Try changing the WhereCondition to explicitly specify the table as well
as the field name, like this:

strWhere = "[Incident Report].[Date] IN (" & strWhere & ")"

If the problem has been confusion on the part of the database engine as
to whether "Date" refers to the field or the current date (as returned
by the Date function, that should fix it.

I take it you've verified that there are actually records in the table
for the dates you selected?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

No change. There is data in the table. Here are the results form the
immidiate window.

[Incident Report].[Date] IN (#03/06/2005#)
[Incident Report].[Date] IN (#03/10/2005#)

Sorry I'm wasting your day with this.

Not to worry; I do this for fun. But I think we've reached the point
where I'd better look at a copy of your database. I have a slow dial-up
connection, so I don't want the whole thing, just a cut-down copy
containing only the elements necessary to demonstrate the problem.
After cutting it down, *compact* it, and then zip it using a
zip-compression tool so that it's less than 1MB in size (preferably
much smaller). E-mail that to me, and I'll have a look at it, time
permitting. You can send it to the address derived by removing NO SPAM
from the reply address of this message. Or, if that address isn't
available to you, get it from my website at the URL in my signature. DO
NOT post my real e-mail address here in the newsgroups!

 

[Dirk Goldgar]

PMFJI.... how did you create the data that are stored in the Date
field? Was it done by using Now() function? If yes, then you've
stored the time in that field as well, and your WHERE expression will
not find a match in that case.

Hey, great guess, Ken! That could be it. His table does have both a
[Date] field and a [Time of Incident] field, so that didn't occur to me.

 

[Dirk Goldgar]

Okay, I've looked at your database and found the problem. It stems from
the fact that your field [Date] is not a date/time field at all -- it's
a text field. I did ask about this in my first reply to your original
post, and in the absence of an answer to my question, I assumed you'd
checked it.

There are two ways to fix your problem.

(1) Change the field [Date] from text to date/time data type. That's
what I would do. The date/time data type is designed for storing and
manipulating dates. If you do that, the code you have now will work --
at least, it works for me.

(2) If you don't want to change the field's data type, you can change
the code that builds strWhere, so that it builds a list of text-format
date values that are identical in format to the format of your [Date]
field. It would look like this:

strWhere = strWhere & _
Format(Me!List14.Column(0, varItem), "\'mm/dd/yyyy\'") & _
","

Either of those changes -- but not both together! -- should fix your
problem. I'd recommend changing the field's data type, though. Then
you aren't dependent on the exact format of the text field.

While you're at it, I would change the name of the field from "Date" to
something like "IncidentDate", in the table and wherever else it is
used. It's a bit of a pain now, but it'll save you a lot of pain later.

Also, you don't actually need a separate field for the time of the
incident, because the one date/time field can hold both the date and the
time. You may not want to do it that way, though, as it would mean
changing some of your queries. If you're not that comfortable with
Access and SQL, you may want to leave this alone.

 

[Ken Snell [MVP]]

Based on your reply elsethread, not the right guess, though!
< g >

--

Ken Snell
<MS ACCESS MVP>

PMFJI.... how did you create the data that are stored in the Date
field? Was it done by using Now() function? If yes, then you've
stored the time in that field as well, and your WHERE expression will
not find a match in that case.

Hey, great guess, Ken! That could be it. His table does have both a
[Date] field and a [Time of Incident] field, so that didn't occur to me.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Steve Taber]

Uhhg...such an easy fix. Thank you for your help Sir.