Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Date format (US / European) in VBA

Options
  • 10-12-2009 11:16am
    #1
    --amadeus--
    Closed Accounts Posts: 5,096 ✭✭✭


    Hi

    I have a client (US multi national) whose base Windows install seems to be with US Regional settings. They are pretty much all reset to European format but I am having a bit of a nightmare with dates...

    I have a database that connects through an Excel form driven front end. A dialog box has a drop down that filters from a list of meeting dates to only show meetings with a certain date range. It uses a SQL statement to populate a recorset and this in turn populates teh srop down. usually it workls fine.

    But occasionally the date switches to US format, causing an empty recordset to be returned and triggering an error. Wierdly closing down and reopening usually fixes it. Likewise I have a form in the database that creates an agenda based on a given meeting date. This selects dates for a drop down and then matches the selected date to a meeting table and returns the meeting items to populate teh agenda. Here is teh strange bit - I copied teh SQL into a new query window and even though I had written (for example) #12/11/2009# it ran for #11/12/2009#

    Is there any way I can force it to accept the dates in a given format? I can't force a transposition since it's not consistent and there may be meetings on (for example) 02/04 and 04/02. I can't switch the dates to Strings as my filters for historical and future meetings then fail.

    I'm totally lost! Any ideas much appreciated, thanks...


Comments

  • Options
    #2
    Seth Brundle
    Moderators, Politics Moderators Posts: 38,982 Mod ✭✭✭✭Seth Brundle


    What kind of DB and is it on the same machine as the Excel frontend?
    If you type a date into a new Excel workbook does it display as US or UK format?
    If you extract the dates from the DB are they in US or UK format?

    Could the data be inserted into the DB in the US format? In my experience, when using the likes of MS Access, many times the date value would need to be 'converted' to UK format by using a function.


  • Options
    #3
    --amadeus--
    Closed Accounts Posts: 5,096 ✭✭✭--amadeus--


    It's an Excel front end connecting to an Access backend (an exsisting form had a database nailed on the back, inelegant but effective).

    Anyhow it turns out that all dates in MS Access VBA must be in US format (according to a well buried item in the Help system). I have a little routine that can swap the digits and thats solved the immediate problem. I need to do some testing on different PCs with different formats and see how teh wrinkles get ironed out.

    *sigh* why can't clients have a common desktop install :(


Advertisement