Using CDate function for MS Access table 1

[isha]

I am developing a small software using VB6 and MS Access2000.
In one of the tables there is an datetime type column. I want to compare the value of this column with the date given in an combobox. For doing this I have given the following code. But it is not working. It is showing EOF as true. Anyone who can help me is most welcome.

Private Sub Command4_Click()
Dim rs1 As ADODB.Recordset
Set rs1 = New ADODB.Recordset
Dim dno As Integer
dno = CInt(Trim(Text1.Text))
Dim mydate As Date
mydate = CDate(Trim(Combo1.Text))

rs1.Open "select letter_no from tbldiary where diary_no= " & dno & " and diary_date= " & "" & dote & "", con, adOpenDynamic, adLockOptimistic

If Not rs1.EOF Then
Text3.Text = rs1(0)
End If
End Sub

 

[aliinal]

I'm not sure on ADO but try RecordCount property and then MoveLast and First. DAO needs you to movelast and first before reading it!

If Not Rs1.RecordCount = 0 Then
   Rs1.MoveLast
   Rs1.MoveFirst
   'Do whatever you want with Rs here
End If

hope this helps...

 

[johnwm]

try
....diary_date= #" & mydate & "#", .....

Access dates in queries need to delimited with # Let me know if this helps
________________________________________________________________
If you are worried about how to post, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'

 

[isha]

Dear Johnwm

I used ....diary_date= #" & mydate & "#", .....as suggested by you. It is working fine if the dd(day portion of date(dd/mm/yyyy))starts with 2 or 3 (e.g.20/10/2002,25/09/2002,31/09/2001) but if the dd starts with 0 or 1(e.g. 05/10/2002,10/10/2002,11/10/2002) it is showing end of file.I am using MS Access database.The code is as follows:

Private Sub Combo1_click()
Set rs = New ADODB.Recordset
Dim dno As Integer
dno = CInt(Trim(Text1.Text))
Dim mydate As Date
If Not Combo1.Text = "" Then
mydate = CDate(Combo1.Text)
End If
rs.Open "select * from tbldiary where diary_no= " & dno & " and diary_date=#" & mydate & "#", con, adOpenDynamic, adLockOptimistic

If Not rs.EOF Then
Command1.Enabled = True
Command2.Enabled = True
Frame1.Enabled = True
Text3.Text = rs!letter_no


Kindly Help.

Isha

 

[CCLINT]

Actually you should be formating it as mm/dd/yyyy

[/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[johnwm]

Looks like your International settings in Control panel are set differently to your usage.
CDate will always interpret "20/5/2002" or "5/20/2002" as 20th May, but "5/10/2002" will be interpreted as 5th October or 10th May depending on your International settings.

Just force the format of mydate to match the way your database sees dates.
eg
... & dno & " and diary_date=#" & format(mydate,"d,mmm,yyyy&quot & "#", con .....

If in doubt, look up format$ in VBHelp Let me know if this helps
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'

 

[isha]

Dear Johnwm,
Your suggestion worked. Please tell me why to use "d,mmm,yyyy" why three characters mmm for the month? I am confused.
Isha

 

[johnwm]

If you do a ?format(date,"dd,mmm,yyyy&quot in the immediate window you will see that three characters for month in the format string force date to show as 11,Oct,2002. This can only be interpreted as a date in one way! Let me know if this helps
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'

 

[CCLINT]

That was my argument.

You can do it two ways and it will always be interpeted correctly, no matter what the international settings are (unless MS makes a mistake with date values, as has happened in the past, but then no format is safe except US format). The CDate only converts a date to the local format, but US format is needed.

The date only needs to be in: 1) US format, or 2) Local Format based on the settings in the control panel. The user input should always be checked to see if it is one of these formats, or, hard coded values should actually be in US format, because you may NEVER know what format the control panel is set in.

So, if the variable "TheDate" is in US Format, or in the same format as in the control panel, long or short, then both of these will always work and there will be no mis-interpetation:

"diary_date=#" & Format$(TheDate,"mm/dd/yyyy&quot & "#"

Or, if the provider allows it as is with Jet:
(note the single quotation marks)

"...diary_date=DateValue('" & TheDate & "')"

In the last case, Jet will convert the Date to US format based on the settings in the control panel.

You can make it a little easier by doing this:

Public Const gcSQLDATE = "\#mm\/dd\/yyyy\#"

"diary_date=" & Format$(TheDate,gcSQLDATE)

or create a function using the above:

"diary_date=" FormatDate_SQL(TheDate)


Then you you'll save a little typing.... [/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[CCLINT]

>So, if the variable "TheDate" is in US Format, or in the >same format as in the control panel, long or short, then >Yboth of these will always work and there will be no mis->interpetation:

>"diary_date=#" & Format$(TheDate,"mm/dd/yyyy&quot & "#"


That was a copy error:

"diary_date=#" & Format$(TheDate,"mm\/dd\/yyyy\&quot & "#"
[/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[johnwm]

CCLINT,
I like the function.
I guess I've been caught too many times!
I now always use mmm and haven't been caught out by it yet, but I bet either MS or a user somewhere will find a way of getting me.... Let me know if this helps
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'

 

[CCLINT]

Oh, and johnwm, your format will not work in foriegn languages.

SQL format is strict. And what that format does is sends the date in a non-US format.

Because October is spellt differently in other languages, you are sending something that cannot be interpeted unless it is in English:

format(mydate,"d,mmm,yyyy&quot
will turn out in German to be:
5, Okt. 2002

This will not work. The provider, Jet in this case, will not know what "Okt" means.

You can only use the digits, and it must be in mm/dd/yyxx format, along with those date separators, when working with with settings in the control panel other than US or English language.
Or use the DateValue Inside of the Select string - or what ever date conversion the provider allows.


[/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[CCLINT]

johnwm. I went down that long hard road also.....and still goof up. But the function will help and remember: In code use US Format, and on the UI force the user to use the settings in the control panel (or US format).

And my statement:
>"In the last case, Jet will convert the Date to US format based on the settings in the control panel."

needs to be taken with care....In this case I am assuming the provider is on the client PC (which it is in the case of Jet). So the VBAJet.Dll will use the control panels settings. If the provider is on a server, a date conversion function just may not work as expected when the client is set differently...
[/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[johnwm]

CCLINT,
No matter how long we tread this road, there is always something new to snag us!
Thanks for your input Let me know if this helps
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'