Setting ALL Dates To UK Format
Jun 28, 2006
My problem is, I'm using a form to build up a query using VBA / SQL, and part of the WHERE portion of my SQL statement includes start / end dates. If I then enter dates in the UK format (dd/mm/yyyy), access will, on saving the query, reverse dd and mm if both are 12 or below. I've managed a work-around using Format(strDate, "mm/dd/yyyy") on the strings that store the dates in VBA to convert them to US (mm/dd/yyyy) format before compiling the SQL statement, but I would like to avoid using US dates completely if possible.
my PC regional settings are UK, I have UK dictionary selected within access, what I'm interested to find out is wether there is any way to force access to only use UK format dates, or is this an inherent problem?
thanks in advance for any insights ;)
Bogzla
View Replies
ADVERTISEMENT
Jun 21, 2007
Hi everyone,
Please would someone be able to help me?
I have created a union query however, one of the columns, has not picked up the same format as it has in the tables. As in the tables it has this format
'00000'.
Please woud you be able to advise me how I can change the format on one of the 'columns' in my union query. As one column is 'numbers' and the other is 'text'. I need to change the number column so the format is '00000'.
Thank you in advance for your help.
Nats
View 5 Replies
View Related
Jan 10, 2008
Hello all,
I am a bit stuck in my query. I need to setup a Next Follow-up date based on the last follow-up date excluding weekends.
For example: Last Follow-up: 1/10/08.
Next follow-up should be 5 days after that. So it should be: 01/17/08.
I have a calculated field that calculates the number of days (Counting off weekends) but I can't seem to have the date setup correctly.
Thanks
View 7 Replies
View Related
Nov 1, 2005
im using asp with ms access. code is working fine with normal settings i.e US('mm/dd/yyyy') date format
my task is - select records from a table(CPST) between two dates which the user must enter dates in the format UK('dd/mm/yy'). user will enter dates FromDate and ToDate in Form1.asp. then i carry those two fields with request.form and store in two strings as strFromDate and strToDate.
then i used query to get the records in the second page- strQueryString="SELECT * FROM CPST where dt >= #" & strFromDate & "# AND dt <= #" & strToDate & "#"
so first i want to enter date in UK('dd/mm/yy') format and those entered dates should compare with table dates which are in US Format then i want to get records for the specified period and display the date in UK again in report. since ms access takes date in the US format('mm/dd/yyyy') normally. im able to give date in UK format but not able either to get records or display in UK format. i think u can understand my problem and give the solution. if u dont understand i will explain again.
problem is user must enter in 'dd/mm/yy' format only not even in 'dd/mm/yyyy'.
kishore
View 1 Replies
View Related
Dec 8, 2005
Hi,
An Access database table I'm looking at has a date column, where date is entered in UK format (dd/mm/yyyy). However, upon entering the database, the date is somehow converted into US format, despite me passing the date into the table as dd/mm/yyyy.
The trouble started when someone else worked on the database - leaving us now with all of the old records in UK format (correct) but all new entries to that column in US format.
Please note, when I open the Access table, all of the dates for the past 2 years or so are fine (dd/mm/yyyy) but recently, since someone else worked on the file, the new records are appearing as mm/dd/yyyy. Within the same table column we're getting apparently mixed formats.
I'm aware of using session.lcid=2057, but would prefer just to be able to have the dates appear in the right format in the database itself rather than having to use this (I'm concerned if I use lcid, it might balls up the older, correct records in the table).
How can this have suddenly changed, and more importantly, how can I get the records into the table in UK format?
Thanks very much for your help...
View 2 Replies
View Related
Jun 17, 2015
I have a report and i'm trying to set up the conditional format to highlight any dates within January red.
View 1 Replies
View Related
Jan 24, 2008
Hi, I am a longtime Excel user, and am now migrating some work to Access.
I have 2 fields dealing with dates in the format mm/dd/yyyy hh:mm, OPEN and CLOSE
I'd like to get the difference between the two in hours, but I have no idea how or where to do this in Access. I'm assuming it is put into the queries, but it would be easier if I could just put it in the table.
Here is an example
1/1/2008 1:00 1/2/2008 2:00
I want the result to be "25"
Thanks in advance!
View 9 Replies
View Related
Jun 21, 2013
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.
View 7 Replies
View Related
Oct 8, 2013
I upgraded to Access-2010 and the Text File Import function will not recognize dates in the format YYYY-MM-DD. The import dialogue sees enough to recognize the field as a date, but then every date encountered is written to the Import-Errors table. This is true whether the file has a .txt or .csv extension. The actual file format is .csv.
View 6 Replies
View Related
Feb 17, 2014
I wanted to assign the field "Number of magazine" with special format based on date/time format but showing only year and month in the format: "yyyy-mm".
So in property of this field in format I put yyyy-mm and in input mask I type 0000-00;;-
I also created the form based on the table containing above field and I defined format and input mask for corresponding formant in the same way like at the table.
But if I try to type date for example 2014-01 in text box of the form it comes up with the full date 2014-01-01. Why does it do like this? What do I do incorrectly?
View 2 Replies
View Related
Jul 30, 2013
I have a list of dates in the mm/dd/yyyy format and I am looking to get it into the fiscal format of yyyyww. I am able to do this with the datepart and format functions, but I need to make it so that the fiscal month begins in January but the first week starts if there are three or more days in the week. For instance if Jan 1st is a Friday then this stands as the first fiscal week, if it is a Saturday then it does not count as the first week.
datepart and format functions have the Use the first week in the year that has at least 4 days for the firstweekofyear option but I need it where it has at least three to make it work.
View 2 Replies
View Related
May 12, 2014
Any way to have a form with Dates as column headers to update a table where the dates are stored in rows???
The table set up is like this:
tblOpHdr
DiaryID (PK) - OpDate (Date)
tblOpDetail
DiaryID (FK) - CostCode - MachineNumber - MachineHours - etc
I'm just wondering if there's any way I can do this with a datasheet or a crosstab type setup?
It's Access 2010.
View 1 Replies
View Related
Aug 28, 2013
I have built a query to calculate the expiry dates of training courses but I am trying to input a criteria so that only dates within 90 days of todays date show. I am using Date()<90 but it doesn't return the correct information. What the criteria should be for this?
View 1 Replies
View Related
Jul 7, 2015
I want to format the text using format function. how i format the word apple to "apple" (With Quatation mark).
str = Format(Me.word, xxxx)
View 3 Replies
View Related
Apr 9, 2015
I have a table of records, which has within it two date fields (effectively, a 'start' and 'end' date for that particular record)
I now need to create a query to perform a calculation for each date between the 'start' date and the 'end' date
So the first step (as I see it anyway) is to try to create a query which will give me each date between the two reference dates, in the hope that I can then JOIN that onto another query to perform the necessary calculation for each of the returned dates.
Is there a way to do this?
So basically, if for a particular record, the 'start' date is 01-Apr-2015 and the 'end' date is 09-Apr-2015, can I produce a dataset of 9 records as follows :01-Apr-2015
02-Apr-2015
03-Apr-2015
04-Apr-2015
05-Apr-2015
06-Apr-2015
07-Apr-2015
08-Apr-2015
09-Apr-2015
(The *obvious* solution would be to create a separate table of dates, from which I could just SELECT DISTINCT <Date> Between #04/01/2015# And #04/09/2015# - but that seems like a dreadful waste of space, if that table is only required to generate the above? And it would have to cover all possible options; so it would either have to be massive, and contain every possible date - ever! - or maintained, adding new dates as necessary when they are required. Seems horribly inefficient!)
Is it possible to just select each date between the two reference dates? Or can you only query something which exists somewhere in a table?
View 4 Replies
View Related
Sep 7, 2006
Hiya-
I have a database with 5000 entries, corresponding to about 10 entries for about 500 people. Each of the entries is dated, and I need to calculate the time intervals between each person's sequential entries in the table.
One way of doing this is to create another column that contains the date of the previous entry. I can then use DateDiff to subtract one date from the other and give me the difference in days.
This approach falls down if I then work with only a subset of the entries - I would have to re-enter the previous entry dates as the time intervals would have changed.
What I really need is a way of subtracting the date from the date in the cell directly above it. Will Access let me do this, or is there a better way?
Many thanks, Jules.
View 3 Replies
View Related
Jul 8, 2014
I have two tables with dates. Between (!) every two following dates in table1, I want to know the number of dates in table2. How do I write an SQL query for this? The tables I have are up to a few hundred records in table 1 and a few thousand records in table2. So to prevent that this takes hours I need a fast query.
To explain the query I need, for example:
table1
01/01/2014
15/01/2014
17/01/2014
30/01/2014
table2
01/01/2014
02/01/2014
05/01/2014
17/01/2014
18/01/2014
20/01/2014
21/01/2014
25/01/2014
So the answer of the query would be 2,0,4.
Explanation:
Between 01/01/2014 and 15/01/2014 in table 1 there are 2 dates in table2 (01/01/2014 is not included between the dates)
Between 15/01/2014 and 17/01/2014 in table 1 there are 0 dates in table 2
Between 17/01/2014 and 30/01/2014 in table 1 there are 4 dates in table 2
View 2 Replies
View Related
Nov 15, 2011
I have a master table which shows all transactions per record (person) over a financial year.
Each record person has a seperate package period over which their spend needs to be measured. Therefore although I have all their transactions for the year, I only want to sum their transactions between their given [start date] and [end date] which are in columns.
I need to be able to create a field which sums all expenditure per record between the start and end dates
Name Start Date End Date Invoice Date Amount
Matt 15/5/11 15/9/11 1/11/11 £100
Matt 15/5/11 15/9/11 7/7/11 £200
Matt 15/5/11 15/9/11 12/12/11 £200
In this case I would only want to sum 7/7/11 as this is between the start and end dates
I want to write something like sumif([Invoice Date] is between [start date] and [end date] - not sure where or how exactly
(The start date and end date will always be the same per person)
Is this possible in access?
View 10 Replies
View Related
May 4, 2012
I am exporting data from access 2007 to Excel 2007 using VBA code. I have a whole number, which I want displayed as whole number. But after the export, the number is using the 1000's seperator in Excel. So for example if my original number in access table is 12000, it is showing up as 12,000 in the excel file.It has something to do with the NumberFormat property. I have tried doing this but doesn't work.
objSheet.Range("A1:A7").NumberFormat = "General"
View 2 Replies
View Related
Mar 2, 2012
We're using Access 2010 on Windows 7.We have a large database design where we send reports as e-mail attachments. We need to change the way the file is automatically created when sent. I goes to snapshot form every time we attach it to an e-mail. How can we get rid of the snapshot .snp file format? How can we change it to pdf or accbd?
View 3 Replies
View Related
Nov 3, 2005
Hi,
Please bear with me here as it's a little involved.
I'm doing a staff profile website which includes a section where they can enter their annual/other leave details.
I decided to store their leave in two fields Start_Date | End_Date rather than each individual date that they took - the short and wide approach vs long and narrow.
This has left me needing to do a query that would return all the dates between the start and end dates inclusive.
Example:
StaffID---Start_Date---End_Date
---1-----12/12/2004--14/12/2004
Returns:
StaffID---Leave_Dates
--1-------12/12/2004
--1-------13/12/2004
--1-------14/12/2004
I appreciate i could do this using some script to loop through a recordset and build an array of dates but i wondered/hoped that it could be done using SQL.
As it is an asp page i can't use user defined functions in a VBA module in Access so the solution would need to be pure SQL.
Is this possible?
Any help v.much appreciated.
TS
View 3 Replies
View Related
Apr 4, 2012
I have a scenario where the first three rows of date which have dates of 4/1, 4/4/ 4/6 with ndc 5513026701; next six rows that have dates from 4/8 to 4/20 with ndc 5513014801; next three rows that have dates from 4/25, 4/27, 4/29 with ndc 5513026701.
The issue I am having is I do not know how to have separate min/max dates for ndc 5513026701 since when I group by ndc 5513026701 min = 4/1 ; max = 4/29. I need to have min = 4/1 and max = 4/6 for one row and another row of min = 4/25 and max = 4/29.
Any easy way to sequentially create min/max for each ndc 5513026701? I wasn't sure how to verbalize this so I have attached a sample worksheet.....
View 2 Replies
View Related
Aug 18, 2014
I'm not sure if I am biting off more than I can chew. I have a text field in each record in my database (Inherited) The db has nearly 5,000 records. I would like to split the field into records in a seperate table. An Example of the table as is now;
Code:
MemberIDBoats
5882Opossum(78-80) (87-89) Otter(80-84) Opportune(91-93) Turbulent(97-00).
5883Astute Auriga Aeneas Affray Amphion
2407H34 O10 Porpoise Trenchant Tapir.
I want to create a table as follows;
Code:
MemberIDBoatFromTo
5882Oppossum19781980
5882Oppossum19871989
5882Otter 19801984
5882Opportune19911993
5882Turbulent19972000
5883Astute
5883Auriga
5883Aeneas
5883Affray
5883Amphion
Etc.
Is this possible in one hit or do I need to process the records without dates first and then run another process to split those with Dates? I say dates but the field is a text field. About 15-20% of the records contain dates which are always enclosed in parenthesis.
View 14 Replies
View Related
Jan 2, 2013
Is there a way in this program to create a list of dates between 2 dates?
i.e I have Arrival Date and Departure Date. Is there a function or expression that will list all the dates on and between?
View 2 Replies
View Related
Feb 15, 2007
Hi Guys,
ok I work a lot on databases but have recently been asked to set up an access database for my team. We are a marketing team within a large insurance company and need a small access database for MI and project management.
I have made a start on this and going well so far but there are some things I need it to do that I dont know how :P
Ive never messed about with Acess before so bare with me :P ok so this is what im looking for, any help would be greatly appreciated.
I have set up various tables for look up wizards for some of the menus on another table I designed for the raw data entry.
I need two tables, two forms - one table for very basic generic data such as
- Job Number
- Description
- Business Unit
- Owner
- Due Date
- Completion date
I then need to drill down into this to add more information, so its basically two tables on top of each other with co-dependencies.
So for example one of my team enter a new project at the very basic lvl, they have a job number, they know what business unit it is for and they know the date its due, as time goes by and work is done on the project I want them to be able to click on a button that takes them to another form where they can enter information that is linked to the original information.
So they search for job number 00000 and come up with the above data on the first form, I want them to then click on 'Project Information' button and be taken to another form that has a lot more stuff on there, like a description, complications, notes, links to documents etc etc. I tried to do this linking two tables together but it doesnt work as the two forms/tables are not co dependend and are irrespective of each other.
Does this make any sense ? and does anyone know how I might make this work ? Please let me know if I am not clear and I will try to explain as best I can
I would also like to change the form 'theme' aswell and have my companies logo transparent behind the data fields etc, anyone know how to do that as I only have the option to use the standard themes.
Any help would be greatly appreciated
Sleek
View 1 Replies
View Related
May 29, 2007
hi there,
Hiow can i make my access database to open the database window on pressing F11?
Thanks
Danny
View 14 Replies
View Related
