Hi,
I have a huge problem!!!! Access seems to be picking up settings from somewhere which means that the Short date = mm/dd/yyyy and not dd/mm/yyy as set in the regional Settings!
Does anyone have any ideas why?!?!
Regards
H.
I am working in a constantly changing db. The new regime is trying to calculate an inmates remaining time to work on the freeway trash crew if: 1) They show up for work and 2) have no FTA's. This form field is unbound. I currently have a query that calculates the days if they did show up for work or they had served their time. Since there are over 2500 inmates in the program, and the man power to update that many files daily isn't an option, I think it would make better since if we documented the ones that didn't show up but at the same time credit the ones that did...can this be done? If so, any suggestions. Thanks in advance.
View 11 Replies View RelatedI'm new to my job and ms access and not very savvy in the latter. There are fixes for my problem on the internet, but I really don't understand them.
I'm used to dd-mm-yyyy format and it's tough for me to think in mm-dd-yyyy in, for example, MS outlook. If I change my regional settings, of course, outlook and access follow suit. The access database is shared and we print out records as cover sheets for our files. Thus, it's not an option to have me on dd-mm and others on mm-dd. Is there a way for me to enter mm-dd in when using the database but have dd-mm standard for every other aspect?
If you can help, which I would appreciate immensely, please pretend that I don't know what I'm doing.
Thanks
I want to be able to set text boxes so that if one enters a date and a time it displays in the format "dd/mm/yyyy hh:mm", but if one just enters a date is displays in the format "dd/mm/yyyy". Is this possible?
Stipulating "dd/mm/yyyy hh:mm" means that when you just enter a date it adds "0" values for the time e.g. entering "21/6/13" gives "21/06/2013 00:00".
General Date allows for an optional time, but it means that when you do enter a time it gives you seconds as well "dd/mm/yyyy hh:mm:ss" - and I don't want that.
how do i do this? i found an old thread concerning this but it must not apply either to vista or to the most recent version of office.
View 14 Replies View RelatedThis may be simple but I'm having a real issue with it. I've used
Format([Input Table]!Birth_Date,"yyyy") & Format(Format([Input Table]!Birth_Date,"y"),"000")
to go from a date to a julian but now I need to go from a 4 digit julian date to a mm/dd/yyyy.
For example ... Today is julian date 5356 "year 2005 and 356 days into the year" and I need to change this back to 12/22/2005.
How do you do this :confused: I would rather do it within the query than do any code.
After having to take on the work of someone else and finding that the dates were stored as text in the format yy.mm.dd I would like to change it so it is dd/mm/yyyy and stored as a Date.
I was thinking about using an update query rather than go through 10000 odd records!
Any ideas?
Hi everyone
I have 2 date fields date1 and date2 , and i have date/time data type but what i want to be inputed is in dd/mm/yyyy and not mm/dd/yyyy, what do i need to do in accees both date fields properties to make sure it is in UK date format. thanx
I have played with the format and input mask but i am lost dont know what to do.
Thanx
Hi people,
I could do with some help,
I have a combo box which holds a list of times which I have entered in it.
eg. 08:00, 08:30, 09:00 09:30, 10:00
I have set the format of the combo box to be short time 00:00
However, when I move the value that has been selected in the combo box to either an sql query or a text box say...
Me.txtbox1.value = me.combo.value
When the value is transferred I get a long looking time like this 08:30:00
Can anyone tell me why this is happening and how I can get it to just appear as a short time like 08:30
Many Thanks
Jay
I am creating an online post, similar to a guestbook. When a user submits an entry, I am storing the date and time in a column titled "Timestamp", which is formatted 8/4/2005 9:16:58 ("General Date"). I am running the table through a query which is then posted on the web.
What I would like to do is this: I would like to use Timestamp to display the date that the entry was submitted, but not the time (basically, "Short Date"). Can I exclude the time using a query without changing the actual data stored in each record of the table? I understand that the time stored is completely different than the time displayed, even in the table. How can I do this?
I'm having issues querying a short date field in my database as it returns no data no matter how i format the date
SELECT * FROM Maintenance_Dbf
WHERE [Date Logged] = #$date#
ORDER BY Maintenance_ID ASC
$date has been set to:
2007-05-21
2007-21-05
21-05-2007
05-21-2007
07-05-21
07-05-21
21-05-07
05-21-07
2007/05/21
2007/21/05
21/05/2007
05/21/2007
07/05/21
07/05/21
21/05/07
05/21/07
and no joy
If i use this on a medium date field it will work fine??
Regards
Liam
I have a query where one field is Date:Now() so that todays date is forced in. However, it is giving me the date and time. I only want the short date, how do I achieve this???
Thanks!
Hi im having issues runing a query on a short date field in my works database calls [Date Logged] I am actually running the query from PHP/ODBC but have tryed running it in acess also..
I am using this query:
SELECT * FROM Maintenance_Dbf
WHERE [Date Logged] = #$date#
ORDER BY Maintenance_ID ASC
Where $date is i have replaced with all of the following date types..
2007-05-21
2007-21-05
21-05-2007
05-21-2007
07-05-21
07-05-21
21-05-07
05-21-07
2007/05/21
2007/21/05
21/05/2007
05/21/2007
07/05/21
07/05/21
21/05/07
05/21/07
yet i cant get any results returned..
Here is an example date from RAW tables 26/04/2007 12:25:33
Any idea's? even the query builder in access doesn't pull up any records :S
Regards
Liam
I have a field defined as Date/Time Short Date which shows values like 8/01/2006, 8/10/2006, etc and I need to select dates in a range.
However, when I write a query with code like:
SELECT AMOUNT
FROM INVOICE_TBL
WHERE PICK_UP_DATE BETWEEN 08/01/2006 AND 08/31/2006
It doesn't pick up the records I also tried >= <= with the same results.
Any help would be appreciated.
Thanks,
Steve
Hi folks - I saw a thread last week re how to extract the year from the date field possibly using date serial but can't find it can anyone help - it is probably very simple but i can't get it to work.
Ta
Humph
Hi folks - I saw a thread last week re how to extract the year from the date field possibly using date serial but can't find it can anyone help - it is probably very simple but i can't get it to work.
Ta
Humph
On one of my forms two of the fields are "Date Contact Initiated" and "Date Contact Concluded". I have the property for this field set to Short Date.
These two fields autofill with the current date when the form is opened. When the date for both of these fields is the same there is no problem. When the "Date Contact Initiated" is different from the "Date Contact Concluded", the user has to manually go to the "Date Contact Initiated" and change the date ( most often to the previous day.)
What is causing some confusion is that when they go to the "Date Contact Initiated" field to change the information it displays not only the autofilled date but also the autofilled time. There is another field on the form where the time for the contact initiated is to be entered.
Is there a way to format this date field to only show the autofilled date and not the autofilled time?
Thanks for the help!
Chris :)
I have a totals query that displays the sum of what products we ship each day. A process in the system automatically assigns the date/time to a ShippingDate field when an item is marked as despatched.
The totals query allows users to view what products are shipped between a period specified by the user.
The problem I am getting is that when the user inputs dates into fields [txtStart] and [txtEnd], the query fires up but will not display any records as a start time 00:00:00 and end time 23:59:59 was not input.
I don't want users to have to do this but cannot think of a way around it. My criteria code in the query for the shipping dates is
Code:
Between [Forms]![Switchboard]![txtStart] And [Forms]![Switchboard]![txtEnd]
Can I append the start / end time onto the code above somehow or is there another method I can utilise that is probably so glaringly obvious I have missed.
Usually a query allows me manipulate a field property sheet but in this case there is nothing obvious to select like format.
I have a table called Packages, in this table I have multiple fields with just short dates example of fields (ID), (Home_1), (Home_2), (Home_3) and (Home_4). What I was wondering is there a way to comine all of the Home fields into one for the purpose of counting the number of dates for all the records. Total number of dates of all the Home fields? I am using Access 2010
View 1 Replies View RelatedSelect * from Table where CDate(CStr(Nz(AnnouncementDate,0))) >=CDate(10-10-2014) and CDate(CStr(Nz(AnnouncementDate,0))) <= CDate(01-10-2014);
Here AnnouncementDate Column is Short Text
It gives me a type mismatch error..
I have a table in access database which contains a text field 'EDate' that stores Date value in format (12-Apr-2013). Now I want to run a sql query on that field. User will give an input date. The sql query needs to fetch me all the records from access database whose Edate is less than or equal to the user input date.
I am using DateValue function to convert my text filed Edate into date. My query is something like this:
select * from table_name where DateValue(EDate)<='user_input_date'
I am able to perform above task if the system language settings are 'English'. But if system language settings are different say Turkish, then the query fails.
I searched a lot on web and found that DateTime function compares test data with the system date time format and gives the result. Thus it fails with different language settings.
I have some project run on MS-Access as front-end with database linked to MS-SQL Server. I have some column of table contain Date-Time data that store data as General Date format (ie 01/01/2005 08:00:00). I create some form for my staff to key in a data of lab test that they will be key in only time with out date value. On form, I show this value as time only too. But I want to use this data with Date value for some calculate as backgroud process.
So...
In case of new data, Database will be store my data as CurrentDate with Time that my staff key in.
In case of data update, Database will be store my data as ExistDate with Time that my staff may update.
What should I do for solve my problem?
I have some project run on MS-Access as front-end with database linked to MS-SQL Server. I have some column of table contain Date-Time data that store data as General Date format (ie 01/01/2005 08:00:00). I create some form for my staff to key in a data of lab test that they will be key in only time with out date value. On form, I show this value as time only too. But I want to use this data with Date value for some calculate as backgroud process.
So...
In case of new data, Database will be store my data as CurrentDate with Time that my staff key in.
In case of data update, Database will be store my data as ExistDate with Time that my staff may update.
What should I do for solve my problem?
Hello again,
I think my subject heading explains it. How would you add or subtract two short time formatted values and produce a short time value? Also, how would you change a short time value into number minutes. (i.e. 5:30 into 330) Thanks!
G
hey
i want to use the format mm/yyyy in my date field.
how i can do this ?
Hi all,
First post and hope that someone can help.
I need to create an auto generated key with this format.
[Auto Number]+[(Current)MM-YYYY]
[Auto Number] = 0000
[(Current)MM-YYYY] = Current Month + Year
Pls help.:(