M$ CREATE TABLE DATE Issue - Microsoft Access / VBA

Greg McLennan

I am writing some Visual basic code that needs to create a M$ Access DB
with a table that has a DATE ROW with dd/mm/yyyy in the Format as you
would see in the general tab of the table-view.

e.g sql = "CREATE TABLE DailyTbale ([R_Date] DATE(dd/mm/yyyy)"

That type of thing. ??
Any Takers ??

Jul 3 '06 #1

Subscribe Reply

1755

Allen Browne

Greg, you cannot set the Format of the date field in a DDL query statement.
For that, you must use DAO (or set the format manually.)

However, if you have your Regional Settings set correctly in the Windows
Control Panel, Access will use that date format in your interface, without
needing to set the Format property of the field.

Of couse, literal values in VBA code or SQL statements still need to be in
the native JET format of mm/dd/yyyy.

More info:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html
That's my experience with using Access in Australia for 13 years.

Post a reply if you want more info on how to programmaticall y
CreateProperty( ) on the Field in the TableDef, so as to define a specfic
format.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Greg McLennan" <we******@iinet .net.auwrote in message
news:44******** **************@ per-qv1-newsreader-01.iinet.net.au ...

>I am writing some Visual basic code that needs to create a M$ Access DB
with a table that has a DATE ROW with dd/mm/yyyy in the Format as you would
see in the general tab of the table-view.

e.g sql = "CREATE TABLE DailyTbale ([R_Date] DATE(dd/mm/yyyy)"

That type of thing. ??
Any Takers ??

Jul 3 '06 #2

Greg McLennan

Allen Browne wrote:

Greg, you cannot set the Format of the date field in a DDL query statement.
For that, you must use DAO (or set the format manually.)

However, if you have your Regional Settings set correctly in the Windows
Control Panel, Access will use that date format in your interface, without
needing to set the Format property of the field.

Of couse, literal values in VBA code or SQL statements still need to be in
the native JET format of mm/dd/yyyy.

More info:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html
That's my experience with using Access in Australia for 13 years.

Post a reply if you want more info on how to programmaticall y
CreateProperty( ) on the Field in the TableDef, so as to define a specfic
format.

Thanks for the info. I will look into it today further

Jul 3 '06 #3

Similar topics

4084

Loop to create an array from a dynamic form.

by: Nick | last post by:

Loop to create an array from a dynamic form. I'm having trouble with an application, and I'll try to explain it as clearly as possible: 1. I have a form with two fields, say Apples and Oranges. The user selects from a drop down menu, between 1-10 of each and clicks submit. The resulting page will display a NEW form, with rows and a list of fields for the amount of each items selected.

ASP / Active Server Pages

7595

Create report from Crosstab query w/dates as column headings

by: Donna Sabol | last post by:

First, I should start by saying I am creating a database to be used by some very impatient, non-computer literate people. It needs to be seameless in it's operation from their point of view. I want them to do as little as possible when they run their reports. I have a crosstab query that displays usage of items for each month. It looks pretty much like this: ITEM DESC UM 12/02 1/03 2/03 3/03 ...ETC. 1 Solution ...

Microsoft Access / VBA

3364

how to create a table with 1 staffid wtih multiple time slots daily until end of month ?

by: poohnie08 | last post by:

i have a excel spreadsheet showing staff name, date,work hour, ot hour, slot1, slot2, slot3, slot4 and others). The "()" will keep repeating from day 1 until end of month. eg in excel spreadsheet, ============================================================================== A1 |A2 A3 A4 A5 A6 A7 A8 |A9 A10 A11 | 01/02/04 |02/02/04 StaffName |Work Hr OT Hr Slot1...

Microsoft Access / VBA

13935

Syntax error in Create Table statement? Why

by: Alicia | last post by:

Does anyone know why I am getting a "Syntax error in Create Table statement". I am using Microsoft Access SQL View to enter it. Any other problems I may run into? CREATE TABLE weeks ( weekstart datetime not null primary key, weekend datetime not null )

Microsoft Access / VBA

3125

Attempting to create well-designed database

by: flkeyman | last post by:

Work in legal office. Trying to create solid designed database structure. This is list of tables w/fields and primary keys. Any comments/advice greatly appreciated. tbl-Defendants CaseNumber (primary key) FirstName MiddleName LastName

Microsoft Access / VBA

3504

Create Table?

by: GCM | last post by:

Hi, I have a table that has the following fields; LName, FName and MName. Also there are other fields in the table. I need to create another table with those fields combined as "Trim( & " " & & " " & ). How can I create this table? Thanks in advance... GCM

Microsoft Access / VBA

3802

How to create ForeignKeyConstraint?

by: max | last post by:

Hello, I am a newbye, and I'm trying to write a simple application. I have five tables with three columns; all tables are identical; I need to change some data in the first table and let VB updates the same data in all other four tables in the right places. I know it would be possible by using the ForeignKeyConstraint object. I have created the tables using the DataSet Visual Tool and I know it doesn't create any ForeignKeyConstraint obj....

Visual Basic .NET

3067

Problems copying query results to table - novice issue

by: Kelii | last post by:

I've been trying to get this piece to work for a few hours, but have given up. I hope someone out there can help, I think the issue is relatively straightforward, but being a novice, I'm stumped. Below you will find the code I've written and the error that results. I'm hoping that someone can give me some direction as to what syntax or parameter is missing from the code that is expected by VBA. Overview: I'm trying to copy calculated...

Microsoft Access / VBA

4110

Create Weekly and Monthly Flag in database/table

by: Lucas_london via AccessMonster.com | last post by:

Hi I have set up a database/tables in Access based on daily timeseries data. However I would like to create two additional columns in the table as a basis to pull the data on a weekly and monthly series basis. Currently, in excel I am using the weekday and EOMONTH functions to create the flags/indicator in my excel dataset: (Cells in column A contains daily dates)

Microsoft Access / VBA

9714

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

9594

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

10600

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

10350

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

10351

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

9174

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

6866

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

5673

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

3002

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General