vb to MsAccess date problem

[aliz]

hello

i connect vb to msaccess using adodb.
now i want a query which will display my table details between two dates.

what is the format for giving dates in a query

plz help me

[seenu_1st]

when u input the value use the date format something like this

.Fields(1).Value = Format(Text1.Text, "dd/MMM/yyyy")

[seenu_1st]

oh sorry u r asking for query, i think something like this

Select * From <table name> Where <datefield> Between #1/1/2009# And #2/2/2009#

[si_the_geek]

Welcome to VBForums

Thread moved to 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)


In addition to post #3 above, you will probably find it useful to read the article How do I use values (numbers, strings, dates) in SQL statements? from our Database Development FAQs/Tutorials (at the top of this forum)

when u input the value use the date format something like this

.Fields(1).Value = Format(Text1.Text, "dd/MMM/yyyy")

There are two big issues in that line of code... the first is an implicit (and highly unreliable) conversion from String to Date, which is then explicitly converted back to a String.

The next is a potential implicit conversion (depending on the data type of the field) from String back to Date - and as it is language dependent, there is a high chance of errors based on Windows language settings.

[seenu_1st]

thanks for ur info, then which is the right way for date format?

[si_the_geek]

If the field is a Date based data type, the value should be Date based too, like this:

.Fields(1).Value = DateVariable

If the user is going to input the date, it should come from a control designed for it (such as the DateTimePicker etc).

If there is no choice but to get it from a String, it should be converted using the kind of code shown at the end of Why are my dates not working properly?. Note that CDate is unsafe, as it does the same as the implicit conversion (the article explains the problems).


Within SQL statements, the info is in the link I gave in my previous post.

[seenu_1st]

thanks for ur info