SQL Multiselect Query and Unexpected Results

[Jhust1]

Greetings, I am working on my second Database and need help with this query. The query is attached to a Filter Form with a Hidden_Box, which allows the user to select a single or multiple states from a List Box, then choose a beginning and ending date range from a popup calendar.

The filter, however, is not providing the correct results. It will locate the States, but include all records from those states without regard to the date range.
I am new to Access and VBA, (as you can tell from the code) but I am trying. Any help and guidance would be appreciated, thanks.

Here is the code---------------->

Private Sub Filter_Click()

Dim stDocName As String
Dim stLinkCriteria As String
Dim IFNo As Byte

stDocName = "RptInformation" 'Name of Report

On Error GoTo PrintSelectedRecords_Err

If IsNull(Me.txtFirstDate) Then
Me.txtFirstDate.SetFocus
MsgBox "Please enter a beginning date", , "Need beginning date"
Exit Sub
End If
If IsNull(Me.txtSecondDate) Then
Me.txtSecondDate.SetFocus
MsgBox "Please enter an ending date", , "Need ending date"
Exit Sub
End If


'Tried using the following with poor results
'stLinkCriteria = "(" & Me![Hidden_Box] _
'& &quot AND (cDate[Arrival_Date]" _
'& " Between #" & Me![txtFirstDate] _
'& "# And #" & Me![txtSecondDate] & "#)"

'Moved to the below code upon suggestion - still poor results.

If Len(Me.Hidden_Box) > 0 Then
stLinkCriteria = Me!Hidden_Box & " AND "
Else
stLinkCriteria = ""
End If
stLinkCriteria = stLinkCriteria & "cDate[Arrival_Date] >= #" & Me![txtFirstDate] & _

&quot;# AND cDate[Arrival_Date] <= #&quot; & Me![txtSecondDate] & &quot;#&quot;

Debug.Print stLinkCriteria ' used to check my query
MsgBox stLinkCriteria

DoCmd.OpenReport &quot;RptInformation&quot;, acPreview, , stLinkCriteria

DoCmd.Close acForm, &quot;FrmState_Filter&quot;

PrintSelectedRecords_Exit:
Exit Sub

PrintSelectedRecords_Err:

Dim Answer As Byte

Answer = MsgBox(&quot;It appears your &quot; & _
&quot;Query Request was not processed. Would you like to re-try &quot; & _
&quot;your filter request?&quot;, vbInformation + vbYesNo + _
vbDefaultButton1, &quot;Query Not Completed&quot

If Answer = vbYes Then

Dim DocName As String
Dim LinkCriteria As String
DocName = &quot;FrmVehicle_Information&quot;
DoCmd.OpenForm DocName, , , LinkCriteria
DoCmd.Restore

Else
If Answer = vbNo Then
DoCmd.Close acForm, &quot;FrmState_Filter&quot;
Dim DName As String
Dim LCriteria As String
DName = &quot;FrmVehicle_Information&quot;
DoCmd.OpenForm DName, , , LCriteria
DoCmd.Maximize
End If
DoCmd.Close acForm, &quot;FrmState_Filter&quot;
Resume PrintSelectedRecords_Exit
End If
End Sub

 

[dhookom]

Could you provide any information from
Debug.Print stLinkCriteria ' used to check my query
MsgBox stLinkCriteria
also, we have absolutely no idea regarding the value of Me![Hidden_Box].

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Jhust1]

Hello,

the Hidden_Box is located on my Search Form to store the values of the List Box (List_States) and I use the AfterUpdate code below.

The Debug.Print will display for example the code
*--------------------------------------------------*
| [Arrival_State] IN (1) AND cDate[Arrival_Date] |
| >=#2/01/2004# AND cDate[Arrival_Date]<=#2/4/2004 |
*--------------------------------------------------*

(to search for Alaska (1) between the listed date ranges.) :

Private Sub List_States_AfterUpdate()

On Error Resume Next
Dim varItem As Variant
Dim txtTemp As String

txtTemp = &quot;&quot;

For Each varItem In Me.List_States.ItemsSelected
txtTemp = txtTemp & Me.List_States.ItemData(varItem) & &quot;, &quot;

Next

If Len(txtTemp) > 0 Then
txtTemp = &quot;[Arrival_State] IN (&quot; & Left(txtTemp, Len(txtTemp) - 2) & &quot&quot;

End If

Me.Hidden_Box = txtTemp

End Sub

 

[dhookom]

I overlooked the obvious. Where did you come up with
cDate[Arrival_Date]
If [Arrival_Date] is a date field then remove the cDate. If it needs to be converted to a date value then use:
cDate([Arrival_Date])

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Jhust1]

Thanks for your quick reply, I had previously unsuccessfully used ([Arrival_Date]) without the cDate - as this is a Date/Time Field and does not need to be converted ----- but I was following some advice to use cDate because I was told:

&quot;On dates, even if you don't enter time, Access will fill in decimal places way out. To help compare dates better, you can use cDate([Arrival_Date]), which should truncate your values. Access stores dates in floating point form so they are not always valid for comparing.&quot;

 

[dhookom]

If you only want the Date part of a date/time value, use:
DateValue([Arrival_Date])
CDate([Arrival_Date]) will not get rid of the time portion.

Does this work now without the CDate? If not, what is your debug.print returning?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Jhust1]

The change was made as you suggested - but the report is still not retrieved.

The Debug.Print now displays:

*------------------------------------------------------*
[Arrival_State] IN (1) AND DateValue[Arrival_Date] >=#2/01/2004# AND DateValue[Arrival_Date]<=#2/4/2004
*------------------------------------------------------*

only to return no matches.

 

[dhookom]

Why do you continue to use [] (square brakets)? My messages have used ()s (parens: shift+9 and shift+0). All functions use ()s. The square brackets are generally used for surrounding object names that contain spaces or are reserved words.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Jhust1]

The problem was/ is not the [] - thanks anyway. I figured it out on my own.