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 dilemma 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')" & _ 
"[b][COLOR=red]and[/color][/b] 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')" & _ 
"[b][COLOR=red]or[/color][/b] 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

Thanks in advance for your help.

 

[Andrzejek]

Do yourself a favor, try this:

[green]'*****Open the recordset***** [/green]
[b]Debug.Print strSQL[/b]
rs.Open strSQL, conn, adOenKeyset, adLockOptimistic

and see if your SQL makes sense. Copy it from Immediate Window into Access and see what is going on.

Have fun.

---- Andy

 

[trezraven]

My initial SQL made sense b/c it was giving me information. I saw on another site where Nz (null value) was used, so I tried changing the SQL statement to the following:

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, #1/1/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 "

Now I get a compile error error that says Sub or Function not defined and Nz is highlighted.

 

[PHV]

Nz is a method of the Access.Application object and thus unknown to Word.
A workaround is to use the IIf and IsNull functions.
Nz(variable, value) is the same as IIf(IsNull(variable), value, variable)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[trezraven]

I changed my SQL to:

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('" & IIf(IsNull(DCAMacro.txtStart.Value), #1/1/100#, DCAMacro.txtStart.Value) & "', 'mm/dd/yyyy') and to_date('" & IIf(IsNull(DCAMacro.txtEnd.Value), #12/31/9999#, DCAMacro.txtEnd.Value) & "', 'mm/dd/yyyy')" & _
"or Mandate_Type Like '" & IIf(IsNull(DCAMacro.txtMandate_Type.Value), "*", DCAMacro.txtMandate_Type.Value) & "'" & _
"Order by Appellant "

and that fixed it. Thanks PHV.