Listbox and SQL

[steve711]

Could someone look at this and help with the proper formating?

strWhere = strWhere & " (tblEntry.tblPilot) Like '*" & Me.cboPilot & "*' AND & (tblEntry.tblDate) like '#" & Me.txtStartDate & "#' and"


I want the user to enter a pilot name (from a dropdown) and a date as his search criteria. Then using those 2 pieces of information search my table for any and all occurances and fill a list box with those results.

I can easily find all pilot names which match but I cannot quite figure out how to incorporate the second search criteria into the mix.

Any help would be great. I did spend some time searching the FORUM for some help on this but as you can see I am here now asking for help.

 

[bonekrusher]

try using it in a query

 

[steve711]

Is it necessary to have another query for something like this? Once I find a match I will then let the user double click on an entry in the listbox to EDIT the entry.

I would rather deal strictly with the table for such a thing.

I could be wrong in my thinking, as I am only a pure amateur when it comes to programming.

 

[Bat17]

You base the list box on an SQL string or on a query, it is effectivly the same.
Requery the listbox on the afterupdate event of the Combobox and Date Field so that when they make thier selections the listbox automaticaly updates.

strWhere = strWhere & " (tblEntry.tblPilot) Like '*" & Me.cboPilot & "*' AND (tblEntry.tblDate) = #" & Me.txtStartDate & "# and"

Job to tell exactly what you need as the line of code you are pasting is obviously part of a more complex where statement.

Peter

 

[Mile-O]

steve,

I know bat17 has corrected it in his reply but be aware that you can't use the Like operator with anything other than strings. If you aren't going to do a wildcard query then use the equals sign rather than use the Like operator.

Also, dates are not strings, so there's no need to delimit them as if they are. Numbers aren't delimited at all.

 

[steve711]

Fellas thanks for the help, however, it still doesn't fill the listbox.

So I am thinking there may be another error in my code. Here is the whole thing now for your review and holes blasting.

'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim qryDef As QueryDef

'Constant Select statement for the RowSource
strSQL = "SELECT tblEntry.tblPilot, tblEntry.tblDate " & _
"FROM tblEntry"

strWhere = "WHERE"

strOrder = "ORDER BY tblEntry.tblDate;"


'Set the WHERE clause for the Listbox RowSource from information that has been entered into the date and pilot name
If Not IsNull(Me.cboPilot) Then '<--If the textbox txtSearchName contains no data THEN do nothing
'strWhere = strWhere & " (tblEntry.tblDate) Like '*" & Me.txtStartDate & "*'  AND"
strWhere = strWhere & " (tblEntry.tblPilot) Like '*" & Me.cboPilot & "*' AND (tblEntry.tblDate) = #" & Me.txtStartDate & "# and"


End If

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the SQL to the RowSource of the listbox

Me.lstPilot.RowSource = strSQL & " " & strWhere & "" & strOrder

 

[pono1]

It looks like you're stripping away 5 characters when you should only be stripping away 4.

" and" = 4 characters.

To check for certain, type the keyword Stop in your code (see below) and then when your code stops, press Ctrl-G to bring up your immediate window (assuming it's not already visible). In the immediate window, type "debug.Print strWhere" and press Enter to see what your clause looks like...

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Test
[B]STOP[/B]

'Pass the SQL to the RowSource of the listbox
Me.lstPilot.RowSource = strSQL & " " & strWhere & " " & strOrder

Also, it's not clear what your QryDef variable is doing in this block of code...but maybe there's more to it than meets the eye...

Regards,
Tim

 

[Bat17]

Try it this way. I am assuming that the date text box is formatted as Date!

'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim qryDef As QueryDef

'Constant Select statement for the RowSource
strSQL = "SELECT tblEntry.tblPilot, tblEntry.tblDate " & _
"FROM tblEntry"

strWhere = "WHERE"

strOrder = "ORDER BY tblEntry.tblDate;"

'Set the WHERE clause for the Listbox RowSource from information that has been entered into the date and pilot name
If Not IsNull(Me.cboPilot) Then '<--If the textbox txtSearchName contains no data THEN do nothing
strWhere = strWhere & " (tblEntry.tblPilot) Like '*" & Me.cboPilot & "*' and"
    End If
If Not IsNull(Me.txtStartDate) Then
    strWhere = strWhere & " (tblEntry.tblDate) = #" & Format(Me.txtStartDate, "MM/DD/YYYY") & "# and"
End If
'Remove the last AND from the SQL statment
strWhere = Left(strWhere, Len(strWhere) - 4)

'check if there is a 'where'
If Len(strWhere) > 1 Then
'Pass the SQL to the RowSource of the listbox
    Me.lstPilot.RowSource = strSQL & " " & strWhere & " " & strOrder
Else
    Me.lstPilot.RowSource = strSQL & " " & strOrder
End If

Peter

 

[steve711]

As always you guys are life savers.

I guess to keep it simple it is best to pass two WHERE instead of lumping into one. Or maybe this is just not possible. I am afraid the formating escapes me all together but I got close as it seems.

Thanks again to all who helped.

 

[Bat17]

I did it as two where's as I thought that you were trying to allow the user to either select one or both criteria.

Peter

 

[steve711]

The way I have it set up the user actually must select a pilot name and a date to search for.

The new problem I have is in the situation where there are two entries for that single pilot name on the same date. I get two results in the list box but when they double click on either entry it brings up the first entry that was made for that pilot on that day and not each individual.

Example:

Pilot Steve messed up after entering 2 separate flights on the same day. He needs to fix one of those entries.

So when he goes to edit his mistake he selects his name and the date of the mistake.

How can I get the second or third or fourth entry to be the one that he edits?

Here is the line I use to open the form for editing.

     DoCmd.OpenForm "frm_EditFlt", , , "[tblPilot] = '" & Me.lstPilot & "' and [tblDate] = #" & lstPilot.Column(1) & "#"

I am sorry to all but I posted a help me post on this same subject just a few minutes ago...

 

[Bat17]

This line
strSQL = "SELECT tblEntry.tblPilot, tblEntry.tblDate " & _
wants the unique identifier for the record adding.
strSQL = "SELECT tbl Entry.ID, tblEntry.tblPilot, tblEntry.tblDate " & _

Then you combo want its properties altering so that its column count is 3 and add 0; to the start of the column widths line.

Now the combo will look the same to the users but return the ID number rather than the date when selected so you need to ammend this line
DoCmd.OpenForm "frm_EditFlt", , , "[tblPilot] = '" & Me.lstPilot & "' and [tblDate] = #" & lstPilot.Column(1) & "#"
to
DoCmd.OpenForm "frm_EditFlt", , , "[id] = " & Me.lstPilot

This assumes that you have a unique numerical id in tblEntry

HTH

Peter

 

[steve711]

Thanks for the help, again, Peter.

I modified what you gave me a little bit and had to change the listboxes bound column but all is good now.