problem with sql dates 1

[lorca]

hi

I've got an access database with a linked sql table transaction_table, which has a column entry_date of data type datetime.

I've built up the following query in code (because i was having problems with it as an access query) to search the sql table between dates.
in my example i'm using between 28/12/2005 and 29/12/2005
the 28th returns 1 record, the 29th returns 78 records. combined it returns 1442 records ! should be 79.
I have tried everything. in the access query it was swapping the dates round as 12/28/2005 for example. in the code it looks fine now but still no joy !

any help appreciated
jacq

str = "select Format([Entry_Date],'ddmmyyyy') AS Field2, Format([Entry_Time],'hh:nn:ss') AS Field3, TrueAccount_Reference AS Field1,[Amount]*100 AS Field11, Narrative AS Field13, IIf([Machine_Code]='WS',Right([Transaction_Reference],6),Right([Transaction_Reference],8)) AS Field4, Fund_Code from dbo_transaction_table where fund_code ='mf' and format([Entry_Date],'dd/mm/yyyy') between #" & CStr(Format(Me.Text1, "dd/mm/yyyy")) & "# and #" & CStr(Format(Me.Text2, "dd/mm/yyyy")) & "#"

Set rst = dbs.OpenRecordset(str, dbOpenDynaset, dbSeeChanges)
rst.MoveLast
rst.MoveFirst
MsgBox str
strcount = rst.RecordCount

 

[PHV]

Perhaps this ?
... AND [Entry_Date] Between #" & Format(Me!Text1, "yyyy-mm-dd") & "# And #" & Format(Me!Text2, "yyyy-mm-dd") & "#"

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

 

[lorca]

thanks

i'd tried that but still no joy...............

 

[PHV]

And this ?[tt]
... AND Format([Entry_Date],'yyyymmdd') Between '" & Format(Me!Text1, "yyyymmdd") & "' And '" & Format(Me!Text2, "yyyymmdd") & "'"[/tt]

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

 

[lorca]

hurrah !

thank you very much..........