Search Between Dates

[Freemo]

Hello

I have a vb program with a access database.

I am trying to search between two seperate dates, but when i search say 16/11/2005 to 17/11/2005, i also get 16/11/2005.

I have set the column in the access database to Date/Time format, and i have formated the texts dd/mm/yyyy and also have dim the content as Date.

I am out of ideas, any help please

Thank You

 

[HarleyQuinn]

Hi,

I'm not sure what you're asking.

Do you want to search between two dates but not include the said dates in the results?

Or is it a date format problem?

I only ask about the date format option as I'm not sure if

16/11/2005 to 17/11/2005, i also get 16/11/2005

is a typo.

Could you please clarify for me?

Cheers

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Freemo]

Hello

Sorry is a typo, i meant i also receive the 16/12/2005. And yes i think there is a problem with the date format, but i can't see where as i have formatted the access database for date, the datagrid for displaying and the search criteria.

 

[HarleyQuinn]

Can you show us some code to demonstrate how you're currently doing your queries?

Cheers

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[strongm]

Looks to me like it is checking between strings rather than dates

 

[Freemo]

Yeah certainly, here you go

Dim path As String
Dim SearchD As Date
Dim searchE As Date
Dim sqlInsurer As String

path = "G:\systems\Renewals System\Renewals.mdb"
cn.Open "provider=microsoft.jet.oledb.4.0;data source=" & path & ";jet oledb:database password=NIM"
cn.CursorLocation = adUseClient

If rs1.State = adStateOpen Then
rs1.Close
End If

SearchD = Format(Text2.Text, "dd/mm/yyyy")
searchE = Format(Text3.Text, "dd/mm/yyyy")

sqlInsurer = "SELECT * FROM Renewals_Extract WHERE PolicyEndDate BETWEEN #" & SearchD & "# AND #" & searchE & "#"

rs1.Open sqlInsurer, cn, adOpenDynamic, adLockOptimistic
Set DataGrid1.DataSource = rs1

 

[gmmastros]

I suggest you change....

SearchD = Format(Text2.Text, "dd/mm/yyyy")
searchE = Format(Text3.Text, "dd/mm/yyyy")

to

SearchD = Format(Text2.Text, "[red]yyyy-mm-dd[/red]")
searchE = Format(Text3.Text, "[red]yyyy-mm-dd[/red]")


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Freemo]

Good suggestion but this still collects the same contents as before, i even tried this by changing the criteria to string instead of date. But no joy.
Thanx anyway

 

[Sumitdev]

Have you tried formatting it 'mm-dd-yyyy'? We have to convert our UK string dates to their US equivalents.

 

[Golom]

Your code looks OK so I'm wondering if there's some problem with the text strings that are being converted to dates. Rather than using text boxes, I recommend calendar controls that return dates. If you need to stick with text boxes then do some debugging to verify that the dates are in fact, being interpreted as the dates you are expecting.

SearchD = Format(Text2.Text, "yyyy-mm-dd")
searchE = Format(Text3.Text, "yyyy-mm-dd")

sqlInsurer = "SELECT * FROM Renewals_Extract WHERE PolicyEndDate BETWEEN #" & SearchD & "# AND #" & searchE & "#"
[b]Debug.Print Text2.Text, SearchD 
Debug.Print Text3.Text, SearchE
Debug.print sqlInsurer[/b]

 

[petermeachem]

yyyy-mmm-dd" or "dd mmm yyyy". So long as there is no ambiguity in the date, Access and SQL produce the right result

http://www.accuflight.com

http://www.lettersdirect.co.uk

http://www.ourhouseinportugal.co.uk/

 

[BobRodes]

What's wrong with

SearchD = CDate(Text2.Text)

? Seems to me that makes the formatting, in the words of the great Douglas Adams, an "SEP".

 

[Freemo]

Hello thanx for all your suggestions and i apolize for the late response, but got carried away on other programs.

The responses have been brilliant but i have tried all your suggestions with no prevail.

Everything looks correct and even if i transport the query to access and run it, it works perfectly.

I just don't get it.

 

[Sumitdev]

Does using a >= <= rather than Between work?

sqlInsurer = "SELECT * FROM Renewals_Extract WHERE PolicyEndDate >= #" & SearchD & "# AND PolicyEndDate <= #" & searchE & "#"

or using the DateValue function

sqlInsurer = "SELECT * FROM Renewals_Extract WHERE PolicyEndDate BETWEEN DateValue('" & SearchD & "') AND DateValue('" & searchE & "')"

 

[MrSatan]

Date field for a database is usually system dependence, so for comparing the dates, I normally set the search text and the field to the same format.

In your case, set PolicyEndDate, SearchD, and SearchE to the same format "yyyy-mm-dd". And simply using <, <=, >, >=, or =.

Hope this help.

 

[RoyVidar]

The two "search" variables are dimentioned as Date - which means that when doing a

[tt]SearchD = Format(Text2.Text, "dd/mm/yyyy")
' or
SearchD = Format(Text2.Text, "yyyy-mm-dd")[/tt]

What you're actually doing, is formatting what might be a valid date to a string, which is then implicitly cast back to date again. I think, depending on locale, that might cause amusement;-), though since the latter version is unambiguous, SearchD should probably be assigned correct date.

DateSerial or CDate or something when assigning to Date variables?

Now - the second challenge here, is that there's no formatting of the date when concatenating them into the SQL string, which means that regardless of implicit casting/formatting etc when assigning to the date variable, the date that gets concatenated into the string, is according to locale, which seems UK here.

So - when doing this dynamic SQL stuff, the place to do the formatting is when concatenating into the sql string (or perhaps use string "search" variable, and format into those), not when assigning Date variables.

[tt]sqlInsurer = "SELECT * FROM Renewals_Extract WHERE PolicyEndDate BETWEEN #" & _
format$(SearchD, "yyyy-mm-dd") & "# AND #" & format$(searchE, "yyyy-mm-dd") & "#"[/tt]

Do try doing a
[tt]debug.print sqlInsurer[/tt]
then check it out (ctrl+g) both before and after these suggested alterations.

But - why not look into utilizing the parameters collection to avoid all this mess. Have a look at Chiph's faq faq709-1526, whith one approach ...

Roy-Vidar

 

[dglienna]

I think you need > and <=

WHERE PolicyEndDate > SearchD AND PolicyEndDate <= SearchE

David