Answering VB to Access DB query once and for all 
Author Message
 Answering VB to Access DB query once and for all

Hi All

Just to confirm, is it true that when working with dates in a VB6 to ADO to
Access DB (and vice versa) you really need to post into the db as dd/mm/yy
and you can format in vb as dd/mm/yy, but you MUST do your SQL queries as
mm/dd/yyyy??

I've gone round the bend with this, but this combo **seems** to work.

Thanks



Tue, 31 Jan 2012 02:12:57 GMT  
 Answering VB to Access DB query once and for all


Quote:
> Hi All

> Just to confirm, is it true that when working with dates in a VB6 to ADO
> to
> Access DB (and vice versa) you really need to post into the db as dd/mm/yy

define "post into the db"

Quote:
> and you can format in vb as dd/mm/yy,

You can format the results for display to the user any way you want

Quote:
> but you MUST do your SQL queries as
> mm/dd/yyyy??

that works; using yyyy-mm-dd format is often better to avoid confusion


Tue, 31 Jan 2012 02:43:16 GMT  
 Answering VB to Access DB query once and for all


Quote:
> Hi All

> Just to confirm, is it true that when working with dates in a VB6 to ADO
to
> Access DB (and vice versa) you really need to post into the db as dd/mm/yy
> and you can format in vb as dd/mm/yy, but you MUST do your SQL queries as
> mm/dd/yyyy??

> I've gone round the bend with this, but this combo **seems** to work.

Not necessarily. Dates are stored in MSAccess as a Double - no date-string
formats are stored in the database. So when ever you present a string-format
to MSAccess for storage it makes its best guess as to what you meant. This
string to date conversion is very dependent on locale settings, or sometimes
not - it all depends on where and with what you are presenting it.
#mm/dd/yyyy# usually works with us Americans, but can have surprising
results elsewhere.

However, the best, surest way to making sure Jet does the right thing is to
use the
    "#yyyy-mm-dd hh:mm:ss#" format

-ralph



Tue, 31 Jan 2012 02:57:44 GMT  
 Answering VB to Access DB query once and for all
Mnay thanks guys

Rgds

Robbie



Quote:
> Hi All

> Just to confirm, is it true that when working with dates in a VB6 to ADO
to
> Access DB (and vice versa) you really need to post into the db as dd/mm/yy
> and you can format in vb as dd/mm/yy, but you MUST do your SQL queries as
> mm/dd/yyyy??

> I've gone round the bend with this, but this combo **seems** to work.

Not necessarily. Dates are stored in MSAccess as a Double - no date-string
formats are stored in the database. So when ever you present a string-format
to MSAccess for storage it makes its best guess as to what you meant. This
string to date conversion is very dependent on locale settings, or sometimes
not - it all depends on where and with what you are presenting it.
#mm/dd/yyyy# usually works with us Americans, but can have surprising
results elsewhere.

However, the best, surest way to making sure Jet does the right thing is to
use the
    "#yyyy-mm-dd hh:mm:ss#" format

-ralph



Tue, 31 Jan 2012 03:15:59 GMT  
 Answering VB to Access DB query once and for all

Quote:
> Just to confirm, is it true that when working with dates in a VB6 to ADO
> to
> Access DB (and vice versa) you really need to post into the db as dd/mm/yy

Define "post." You post to a newsgroup; you might post to a Web page; you do
not post to a database.


Tue, 31 Jan 2012 03:33:55 GMT  
 Answering VB to Access DB query once and for all
If you are assigning to fields from a Date variable, then there is no
problem. If you are assigning a String containing a date string, VB
implicitly converts it to Date using Control Panel settings before assigning
it to the field.

If you are using SQL statements, it depends on how you format them. Using
##, it's always in US format for Jet. If you use DateValue() function, it
will use Control Panel settings. See "When you specify the criteria
argument, date literals must be in U.S. format":

WHERE Clause (Microsoft Access SQL)
http://msdn.microsoft.com/en-us/library/bb221200.aspx

Microsoft Access SQL Reference:
http://msdn.microsoft.com/en-us/library/bb245488.aspx

For MS SQL Server, see this page:

Writing International Transact-SQL Statements
http://msdn.microsoft.com/en-us/library/aa172787(SQL.80).aspx

Transact-SQL Reference(MS SQL Server)
http://msdn.microsoft.com/en-us/library/aa299742(SQL.80).aspx



Tue, 31 Jan 2012 03:40:40 GMT  
 Answering VB to Access DB query once and for all

Hi All

Just to confirm, is it true that when working with dates in a VB6 to ADO to
Access DB (and vice versa) you really need to post into the db as dd/mm/yy
and you can format in vb as dd/mm/yy, but you MUST do your SQL queries as
mm/dd/yyyy??

I've gone round the bend with this, but this combo **seems** to work.

The other option is to use Command object Parameters (assuming Date data types) so you don't have to
worry about the date format issue.

Paul
~~~~
Microsoft MVP (Visual Basic)



Sun, 05 Feb 2012 02:09:52 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. ADO to Access DB, DB is closed on second query

2. Querying an access 2000 db using VB

3. SQL update query on an access db in VB

4. passing parameters to an access db's query from VB

5. Converting Access 2.0 DB to Access 97 DB in VB code

6. Answer: Create Access DB From VB5

7. Answer: User Defined Function in Query called from outside Access

8. Exporting Access DB to DB III thru VB?

9. (VB) Accessing Modem once com is open

10. Query Against Access DB over the Web

11. querying an access DB to populate a textbox

12. Execute parameterized query stored in ACCESS-2 DB thru VBA

 

 
Powered by phpBB® Forum Software