I know someone out there will be able to help me, so here go’s
I have an access database, with date entry fields, when I press a button on the form It uses an sql insert command to throw the data into a table, it was all working fine until I was given a new profile, it now screws the dates about, it should be dd/mm/yy I can see it’s being fed into the SQL command corectly as I can see it in the imediate window, but when it ends up in the table it’s yyyy/mm/dd, I can’t see where to change it , I know it’s related to my regional settings but I don’t have rights on this machine to change it.
Someone help, I’m unable to do a big chunk of my work till it’s fixed and the IT section here are crap!
What’s the date format set to in your regional settings? I know access has a habit of changing the date format to that of your regional settings regardless of whether you force it or not.
here’s the doozy, I can’t get to the regional settings on this machine, I’m down to being just a user so don’t have access rights. I think the regional settings are wrong which is why I’m getting the error, either that or the regional settings being pushed by the DC are set to US
is it going into the sql wrong causing issues with applications when reading the date, or are they coping. If they are not coping and you have access to them, just do a FORMAT(%s, “DD/MM/YYYY”) on read from the table.
Of course, I may have completely misunderstood the issue.
The Format command will not work if he regional settings are set to a different format.
Now you could do this:
Make that Table Point a Text Field
Then you will be able to send anything into that. If you are using it for calc’s on other pages/forms then that will need to be tweaked. I had this happen one time where the stupid ADMIN of the international company just couldn’t understand the issue of DATE formatting.
I also remember a regedit hack for changing it but wow I guess a Google would handle that.
Can you perhaps format it in VBA before it goes into the SQL statement? Or bonk one of the IT guys over the head until he changes the regional settings on your computer?
I ended up doing it the long way, I had a form that had data entry areas, both submitting to diferent tables, I just had butons that did the insert. I’ve now created a pair of subforms that relate directly to the tables so no need for the sql command and so problem worked arround