Help with SQL statement

[trezraven]

I have created a form using Word 2007 that queries an Access
database. I want my users to be able to search the database by either
entering the type of mandate or a specific date range. My dilema is
my SQL will not let them do one or the other.

For this SQL statement they have to enter both. If they enter an
incorrect mandate type or date range they get the error message.

strSQL = "Select Mandate_Type, Parm1, Agency_Description, CaseNo,
Appellant, Appellee, Lt_Cases, Opinion_Date, Chief_Judge, Mandate_Date
" & _
"From CMS.V_Macro4mandate " & _
"Where Date_Mandate_Released between to_date('" &
DCAMacro.txtStart.Value & "', 'mm/dd/yyyy') and to_date('" &
DCAMacro.txtEnd.Value & "', 'mm/dd/yyyy')" & _
"and Mandate_Type = '" &
DCAMacro.txtMandate_Type.Value & "'" & _
"Order by Appellant "

However, for this SQL statement they can enter one or the other, but
information is returned for a mandate type even if a date range is
entered that is not in the database. For example, if the user enters
a mandate type of MA1 and a date range of 11/1/2007 - 11/30/2007, all
mandate types of MA1 will return even though there is no information
for that particular date range.

strSQL = "Select Mandate_Type, Parm1, Agency_Description, CaseNo,
Appellant, Appellee, Lt_Cases, Opinion_Date, Chief_Judge, Mandate_Date
" & _
"From CMS.V_Macro4mandate " & _
"Where Date_Mandate_Released between to_date('" &
DCAMacro.txtStart.Value & "', 'mm/dd/yyyy') and to_date('" &
DCAMacro.txtEnd.Value & "', 'mm/dd/yyyy')" & _
"or Mandate_Type = '" & DCAMacro.txtMandate_Type.Value
& "'" & _
"Order by Appellant "

'*****Open the recordset*****
rs.Open strSQL, conn, adOenKeyset, adLockOptimistic

'*****Get the data if not end of the record set*****
If rs.EOF Then
MsgBox "No information in the database! Please verify your mandate
type or date range.", vbCritical, "ERROR!"
End If

rs.MoveFirst
If Not rs.EOF Then
Do Until rs.EOF
DCAMacro.txtAppellant = rs.Fields("Appellant").Value & " "
DCAMacro.txtAppellee = rs.Fields("Appellee").Value & " "
DCAMacro.txtCaseNumber = rs.Fields("CaseNo").Value & " "
DCAMacro.txtLowerTrib = rs.Fields("LT_Cases").Value & " "
DCAMacro.txtOpinionDate = rs.Fields("Opinion_Date").Value & " "
DCAMacro.txtStart.Value = " "

'*****Hide the form so the document can come up*****
DCAMacro.Hide

Please help!

 

[Douglas J. Steele]

Try:

strSQL = "Select Mandate_Type, Parm1, Agency_Description, CaseNo,
Appellant, Appellee, Lt_Cases, Opinion_Date, Chief_Judge, Mandate_Date
" & _
"From CMS.V_Macro4mandate " & _
"Where Date_Mandate_Released between to_date('" &
Nz(DCAMacro.txtStart.Value, #01/01/100#) & "', 'mm/dd/yyyy') and to_date('"
&
Nz(DCAMacro.txtEnd.Value, #12/31/9999#) & "', 'mm/dd/yyyy')" & _
"and (Mandate_Type LIKE '" &
Nz(DCAMacro.txtMandate_Type.Value, "*") & "'" & _
"Order by Appellant "

Realistically, though, it's probably better to have your code check for
which of the text boxes have values, and custom-tailor your WHERE clause.

 

[trezraven]

Try:

strSQL = "Select Mandate_Type, Parm1, Agency_Description, CaseNo,
Appellant, Appellee, Lt_Cases, Opinion_Date, Chief_Judge, Mandate_Date
" & _
"From CMS.V_Macro4mandate " & _
"Where Date_Mandate_Released between to_date('" &
Nz(DCAMacro.txtStart.Value, #01/01/100#) & "', 'mm/dd/yyyy') and to_date('"
&
Nz(DCAMacro.txtEnd.Value, #12/31/9999#) & "', 'mm/dd/yyyy')" & _
"and (Mandate_Type LIKE '" &
Nz(DCAMacro.txtMandate_Type.Value, "*") & "'" & _
"Order by Appellant "

Realistically, though, it's probably better to have your code check for
which of the text boxes have values, and custom-tailor your WHERE clause.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)















- Show quoted text -

Thanks for your response. However, now I get a compile error saying
Sub or Function not defined and Nz is highlighted.

 

[trezraven]

Sorry: missed the Word 2007 part.

Rather than

Nz(DCAMacro.txtStart.Value, #01/01/100#)
Nz(DCAMacro.txtEnd.Value, #12/31/9999#)
Nz(DCAMacro.txtMandate_Type.Value, "*")

use

IIf(IsNull(DCAMacro.txtStart.Value), #01/01/100#, DCAMacro.txtStart.Value)
IIf(IsNull(DCAMacro.txtEnd.Value), #12/31/9999#, DCAMacro.txtEnd.Value)
IIf(IsNull(DCAMacro.txtMandate_Type.Value), "*",
DCAMacro.txtMandate_Type.Value)

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

Thanks for your response. However, now I get a compile error saying
Sub or Function not defined and Nz is highlighted.

- Show quoted text -

Thanks a million Doug!!! That fixed it.