date query problem

[pardeep3dec]

Hi friends,
I have problem of selecting recordset between a specified date. I am giving my coding detail below.

I took a field in Ms Access table
table name-travel
fieldname - doj
datatyoe - Text

and vbcoding

dim db as new adodb.connection
dim rc as new adodb.recordset
db.open"Provider=Microsoft.Jet.OLEDB.4.0; Data Source='d:\tmgmt.mdb'"

rc.open"select * from travel where braise='NO' AND Doj between #"& doj.value &"# AND #"& doj.value &"# order by doj desc;",db, adOpenDynamic, adLockBatchOptimistic

but it will not give recordset between above specified date or within date range.
i set the date format from control panel dd/mm/yyyy but not work. Please help

[GaryOC]

Hi there,
I would advise you to save your dates as Julian dates.

Assuming you don't know and put simply, Julian dates are numbers that are used to reference dates. The larger the number the more recent the date. Go here http://en.wikipedia.org/wiki/Julian_day for a more complete explanation but don't let it confuse you, I have simplified it as you will see.

What I do is convert all dates into Julian dates before I store them in my database and I convert them back to Georgian dates (the dates we use every day) when I need them in that form but, if you want to do calculations or queries like yours we just have to ask the data base to return all the records that have a value between the Julian dates.

I'm not going to do the calculations now but let us say that if January 1, 2008 was represented by the Julian date 123456 then the 4th of January would be three days later and the Julian date would be increased by three to 123459. If you wanted to return records between these dates you would query your database to return records that had a Julian date value > 123456 and <123459. It makes it so simple. If you think about it there is so much more you can do with them. VB's date handling is a pain to use, this makes it easy.

Here is the code I use for the conversions. The original code was written by Jay Tanner but I have simplified it to just supply simple dates. Simply call GetJDate with a legitimate date string to return your Julian date (or without any argument for todays Julian date) and call GetGDate with your Julian date value to return your legitimate Georgian date( or without an argument to get today's Georgian date). Remember to change the Format string in the GetGDate function to reflect the way you want your dates presented.

I use GetGDate without an argument to get today's date all the time. Check out the difference. The following two lines return today's date in the format I like.

tdate=GetGDate

or VB's way

tdate=Format(Now, "dd-MMM-yyyy")

I know which I prefer.

Just copy all the following into a module in your programme and I'm sure you'll find that handling dates becomes much simpler.

I hope that helps,
Gary O'Connor.

'***************************************************************************
' CALENDAR FUNCTIONS:
' The original code was by Jay Tanner - 12 Sep 2000 AD
' E-Mail: [e-mail]

'Highly modified by Gary O'Connor with apologies to Jay Tanner

Public Function GetJDate(Optional DateString As String = "") As String
'Supply a string with the required date in any acceptable date form or
'supply nothing to return the Julian Date for Today

'Convert a date into the actual Julian format

Dim M, D, Y
Dim JD As Double
Dim K As Integer
Dim ds As String
If IsDate(DateString) = False Then
DateString = Format(Now, "dd-MM-yyyy")
Else
DateString = Format(DateString, "dd-MM-yyyy")
End If
ds = Trim(UCase(DateString))
D = Val(Left(ds, 2))
M = Val(Mid(ds, 4, 2))
Y = Val(Right(ds, 4))
K = Int((14 - M) / 12)
JD = D + Int(367 * (M + (K * 12) - 2) / 12) + Int(1461 * (Y + 4800 - K) / 4) - 32113
GetJDate = Trim(str(JD))
End Function

Public Function GetGDate(Optional JDNum As String = "") As String
'Supply a Julian Date number to return it's equivalent in the Georgian Date format.
'supply an empty string to get today's Georgian date.
'Convert a date from the actual Julian format

Dim M, Y
Dim JD, D, S, T, U, V As Double

If JDNum = "" Then
GetGDate = Format(Now, "dd-MMM-yyyy")
'Change the format string part "dd-MMM-yyyy" to whatever format you want as a result.
Exit Function
End If

JD = Val(Trim(JDNum))
S = JD + 1524
T = Int((S / 365.25) - 0.3343)
U = Int(T * 365.25)
V = Int((S - U) / 30.61)

' Compute the raw, numerical calendar date elements
D = S - U - Int(V * 30.61)
M = (V - 1) + 12 * (V > 13.5)
Y = T - (M < 2.5) - 4716

GetGDate = Format(Trim(str(D)) & "-" & Trim(str(M)) & "-" & Trim(str(Y)), "dd-MMM-yyyy")
'Change the format string part "dd-MMM-yyyy" to whatever format you want as a result.
End Function

'************************************************************************

[ponnurangam]

Hi friends,
I have problem of selecting recordset between a specified date. I am giving my coding detail below.

I took a field in Ms Access table
table name-travel
fieldname - doj
datatyoe - Text

and vbcoding

dim db as new adodb.connection
dim rc as new adodb.recordset
db.open"Provider=Microsoft.Jet.OLEDB.4.0; Data Source='d:\tmgmt.mdb'"

rc.open"select * from travel where braise='NO' AND Doj between #"& doj.value &"# AND #"& doj.value &"# order by doj desc;",db, adOpenDynamic, adLockBatchOptimistic

but it will not give recordset between above specified date or within date range.
i set the date format from control panel dd/mm/yyyy but not work. Please help

If Data2.Recordset.Date >= Text2.text And Data2.Recordset.Date <= Text3.text Then
its valid query

[GaryOC]

I took a field in Ms Access table
table name-travel
fieldname - doj
datatyoe - Text

and vbcoding

dim db as new adodb.connection
dim rc as new adodb.recordset
db.open"Provider=Microsoft.Jet.OLEDB.4.0; Data Source='d:\tmgmt.mdb'"

rc.open"select * from travel where braise='NO' AND Doj between #"& doj.value &"# AND #"& doj.value &"# order by doj desc;",db, adOpenDynamic, adLockBatchOptimistic

Hi there,
I think we need to know a little more about your database.

For a start this line.....

rc.open"select * from travel where braise='NO' AND Doj between #"& doj.value &"# AND #"& doj.value &"# order by doj desc;",db, adOpenDynamic, adLockBatchOptimistic

seems to have a mistake which could be your problem....

' between #"& doj.value &"# AND #"& doj.value &" '

doj.value and doj.value are the same value and, as they are the same value, there can be nothing between them.

Can you give us the entire subroutine so that we can sort it out for you. Also explain what each field is and what type of value it is. (String, Integer, Date etc)

Gary O'Connor.