Solved: Date Range Query Problem

[itipu]

I have a fairly complex query, in the end I just add a date range, i.e from - to based on the values of 2 drop down boxes, however its not displaying results correctly.. somehow it looks like it limits results to 2.. and if one is filled in and the other isn't it displays randoms dates... Database attached, would be much appreciated. You need to click RESET button first.

Thanks a lot

Mike

SQLStr = "SELECT Client.ClientID, Client.Title, Client.LastName, Client.DateOfBirth, Client.FirstName, Client.MaritalStatus, Client.City, Client.Country " & _
"FROM Client " & _
"WHERE (((([MaritalStatus]=[Forms]![Main Form]![cboMaritalStatus] Or [Forms]![Main Form]![cboMaritalStatus] Is Null)=True) " & _
"AND (([City]=[Forms]![Main Form]![cboCity] Or [Forms]![Main Form]![cboCity] Is Null)=True) AND (([Country]=[Forms]![Main Form]![cboCountry] Or [Forms]![Main Form]![cboCountry] Is Null)=True) " & _
"BETWEEN (([DateOfBirth]=[Forms]![Main Form]![cboFrom] Or [Forms]![Main Form]![cboFrom] is Null)=True) AND (([DateOfBirth]=[Forms]![Main Form]![cboTo] Or [Forms]![Main Form]![cboTo] is Null)=True))"

[itipu]

I got to it work with the following.. BUT

"WHERE (((([MaritalStatus]=[Forms]![Main Form]![cboMaritalStatus] Or [Forms]![Main Form]![cboMaritalStatus] Is Null)=True) " & _
"AND (([City]=[Forms]![Main Form]![cboCity] Or [Forms]![Main Form]![cboCity] Is Null)=True) AND (([Country]=[Forms]![Main Form]![cboCountry] Or [Forms]![Main Form]![cboCountry] Is Null)=True) " & _
"AND ([DateOfBirth] BETWEEN " & Format(Me.cboFrom, "\#dd-mm-yyyy\#") & " AND " & Format(Me.cboTo, "\#dd-mm-yyyy\#") & "))"

This only works if the dates are selected, if they are empty you get a syntax error ;((((

Thanks, Mike

[OBP]

Mike, you obviously need to intercept the blank Date fields before running the creating the SQL string.
Do you want to run it anyway or force the user to enter the dates?

[undunk]

Hi dkarr,Thanks for your reply. Sorry for my delay. I have found the problem the fetch cause problem due to composite keys. Now I have managed to retrieve the values.Thanks The code goes like this....StringBuffer sBuffer = new StringBuffer"FROM BinSet binset ";sBuffer.append" WHERE binset.clientId=? and binset.customerSubCode=?";Query query = getSession.createQuerysBuffer.toString;query.setLong0,12345;query.setLong1, 98765;