Results 1 to 5 of 5
  1. #1
    archie1_za is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    48

    Help Required With Formatting Dates: Novice User

    Hi

    I have about 40 date fields in various tables in my db. Some are static and some are created using a query. They all obviously have different column/field names.

    I understand that Access stores dates in a numeric value and shows it in the US format mm/dd/yyyy by default. If I use UK settings on the local PC it displays the date correctly but all my calculations are thrown out of whack. When set back to US - all good. I have read quite a few posts but still don't understand how to get ALL the dates in my db to display as dd/mm/yyyy irrespective of the Locale setting and also keep my calculations intact.

    I have read up Allen Brownes post and understand the logic but I have no idea on how to implement it.

    This is his code:

    'strWhere = "[Holiday] >= #" & Format(startDate, "yyyy\/mm\/dd") & "# " & _
    '"AND [Holiday] <= #" & Format(endDate, "yyyy\/mm\/dd") & "#"


    '"[Holiday] = #" & Format(dateCurrent, "mm\/dd\/yyyy") & "# "


    Do I create a separate function and how would I tell it that it must format ALL dates (not just holiday). Where would I do this?



    How would I tell static date fields to use this function & how would I tell calculated date fields in a query?

    How would I call it in SQL?

    Please excuse the novice questions - but I really need the help.

    Could you please provide the code

    Thank You In Advance

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    Format (I mean formatting here) doesn't effect the value of data stored in table field or being displayed in form control - it changes how this data is displayed. So when you want to compare 2 dates, just do it.

    'strWhere = "[Holiday] >= [startDate] AND [Holiday] <= [endDate]"

    FORMAT() function converts data (date, or numeric) into text string. So in your formula you compared date with strings.

  3. #3
    archie1_za is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    48
    SELECT TS.TDate,


    IIf(TS.[TDate] Is Not Null,Weekday(TS.TDate,5)) AS PSSDate,
    IIf(PSSDate Is Not Null,Weekday(PSSDate,1)) AS PSCDate



    FROM TS INNER JOIN ADates ON TS.ID=ADates.POID;

    ======
    This is my SQL code. I have a function (weekday) that excludes weekends and holidays
    If my PC is set to US it calculates it correctly. If set to UK it doesn't.

    I want to be able to calculate & display it in UK format irrespective of the locale setting. I don't know if that is the correct way to phrase it.

    So if the PC is set to US it will calculate it properly but I want it displayed as UK and if the PC is set to UK it must calculate it as US and display as UK. Again, I hope that makes sense.

    With Regards to the code in my initial post, I just copied and pasted (sorry)

    Regards

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    The main suspect is your Weekday() function. MS VBA can have problems with some date functions when computer's date format is not US.

    Set windows time format to UK, mark some row at top of your function (Not variable declaration!) for debugger, and run the function step-wise, checking every calculation in every step at Watch expression window.

  5. #5
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    The general rule of thumb for dates if you are UK based is - If it's in the query designer you don't need to worry about formatting, Access will sort it out.
    If it's a VBA created query then you have to format to US stylee. Hence the function Allen provides.

    Things not to try are;
    Don't get into fiddling with your regional setting to make things work. Your end users won't - so neither do you.
    Don't store dates as text fields. Just don't. Ever.
    Don't call any date field simply Date !

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help me, please... (novice user)
    By De Riva Giana in forum Queries
    Replies: 1
    Last Post: 04-29-2015, 10:09 AM
  2. Design Help for Novice Access User
    By jk1809 in forum Database Design
    Replies: 3
    Last Post: 08-07-2012, 10:20 AM
  3. Novice User Help Please
    By bmschaeffer in forum Access
    Replies: 18
    Last Post: 08-26-2011, 09:12 AM
  4. Very Novice User needs HELP with Form
    By Insanity Queen in forum Forms
    Replies: 1
    Last Post: 12-02-2010, 01:20 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums