Re: Date confusion


"Ace9x" <Ace9x@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:AB5E3A45-94AD-4D46-8E23-9CA35AC56FED@xxxxxxxxxxxxxxxx
> I have created a function in the Main module in Access, which when called
> with a Financial Year parameter creates 52 weekly entries in a table with
> period no as 1 to 52, start and end date. This seems okay until I view the
> new entries in the table. Some of the dates are displayed in dd/mm/yyyy
> format whilst others appear in mm/dd/yyyy format. There are no format or
> input masks present on the table itself.
> For the sql insert statement of the 2 dates I used :-
>
> "#" & format(dteStart,"dd/mm/yyyy") ",#" & format(dteEnd,"dd/mm/yyyy") &
> "#"
>
> Obviously something is wrong either with the insert SQL or the display of
> the dates in the table, can someone please advise appropriately.
>
> Cheers,
> Steve

The dates you are probably working with are a numeric value that the program displays in a date format like m/d/y. The actual date
value is a 5 digit Long Integer.

If you are working with a numeric date and you want the format DDMMYYYY, then you will need to store the dates you use as String
representations of a date in the Table. (The data type in the table will be text instead of date.) Visually this will look the
same to you, but to the program it will see a Text String and will keep it properly formatted to dd/mm/yyyy. If you need to perform
math on the dates then you will need to convert them back to date values. See below for both methods.

'--Convert a numeric date value to a string date value
Format(CStr(dteStart),"dd/mm/yyyy")

'--Convert a string date value formatted as DD/MM/YYYY to a numeric date value
DateValue(Mid(strDate,4,2) & "/" & Left(strDate,2) & "/" & Right(strDate,4))

Good luck, matt


.

Relevant Pages

  • Re: Moving a VBA function to SQL server
    ... SQL is a set-based language. ... "String Functions " in Books Online. ... This will produce a job header formatted like this: ... The format he specifies is stored in table 'Settings' - field 'JobHdrMask' ...
    (comp.databases.ms-access)
  • Re: ADP ignoring my format code
    ... we're talking VBA, not SQL, then all we need is Month. ... what the original poster wanted was a string ... representation of a complete date in US format. ... "Vadim Rapp" wrote in message ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Storing Doubles in SQL
    ... Also it's pointless to format the value before storing it. ... Dave Patrick ....Please no email replies - reply in newsgroup. ... string and format as in: ... >I can successfully read and write from/to sql using excel macros. ...
    (microsoft.public.excel.programming)
  • Re: Searching on DATETIME Fields
    ... So First try quoting the string, then ensure that the format of the date ... Wayne Snyder, MCDBA, SQL Server MVP ... >>> The field I am querying is of DATETIME data type, ...
    (microsoft.public.sqlserver.server)
  • Re: simple query not so simple
    ... If that is truly your SQL statement, you have just asked to find all ... In Oracle all dates contain 'century, year, month, date, hour, minute, ... string - if you can read it, it's a string, not a date - so use string ... either implicitly (if your string is in the session's date format) ...
    (comp.databases.oracle.misc)