Issue With Date In Access
Mar 4, 2008
Hi,
I have a problem when updating the date in the table using sql in Access 03. When the current date is 2nd of Feb, the problem seems to arise. I don't have any problems if the day is greater than 12. It seems that when the day is less than 12, it gets confused as to whether it's a month or a day.
Here is my code
(Note: i set the date of my PC to the 8th of Feb 2008)
currDate = Format(Now, "dd/mm/yyyy")
strSQL = "UPDATE [Project Register] SET FirstCompletedFlag = '" & Me.CK_FirstMilestone.Value & "', FirstBudget = " & Me.TB_FirstBudget & ", FirstCompletedDate = #" & currDate & "#"
After the record was successfully updated, i went to check on the table. FirstCompletedDate has the value of 2/08/2008 (this is not what i wanted). It should be 8/02/2008, not the other way around.
Your help is greatly appreciated.
Thank you in advance
View Replies
ADVERTISEMENT
Sep 21, 2006
Hi all,
I have a database from which I build a query to populate a table with data, and one of the fields is a user selected date from a calender control. The query looks something like this:
SELECT
SampleSite.SiteID,
Format(#06-09-2006 11:42:19#,"dd/mm/yyyy") AS D_Date,
"" AS UserID,
SampleSite.SampleSite AS SourceID,
0 AS Depth,
0 AS Temp,
0 AS pH,
0 AS ElectricalConductivity,
0 AS DissolvedOxygen, SampleSite.SiteID+"."+SampleSite.SampleSite+"."+SampleSite.Type AS SampleRef,
SampleSite.Suite AS Suite,
"" AS Comments
FROM SampleSite
Unforunately, for the date shown above, when inserted into the table it comes out as 09/06/2006, and subsequent queries searching against date do not find this record. I understand that access only uses the US date format mm/dd/yyyy, so the question is how do i stop this from happening? I noticed that if i put a date of 13/09/2006, the date is inserted correctly, but dates with a day value of less than 12 get 'switched'.
Arrgghhhh!!
Dan
View 4 Replies
View Related
Aug 21, 2004
God morning everybody
I am a beginner in MS Access and need for some help. I am working in a project with c# and MA Access. I want to retrieve some attribute from a table based on condition with date comparison. The date of format (Monthe dd,yyyy) e.g (March 20,2004). I have tried to use (TableDate>TO_DATE(myDate,‘Month dd,yyyy’) but fails. How can I implement this???
Thanx alot
View 2 Replies
View Related
Jun 12, 2005
Hi and thanks for taking the time to read :)
now this is my first post and i hope i dont post in the wrong section my apologies if i do.
Now for the problem...
i have a vb6 project that manages a MS Access 2002 DB. Now one of the problems i have just come up with is the date. My application will be used by australians and our short date format is "DD/MM/YYYY".
But when i whip up a query and make the date criteria "DD/MM/YY" the access program then changes the format to "MM/DD/YYYY" automatically in the sql view of the query.And even if the record is formated or completed as "DD/MM/YYYY" it wont accept it and it errors me :( with garbage data.
Now i have done the following to try preventing this from happening:
1. Formated the access db to show short date format like this "DD/MM/YYYY" in the properties of the attribute.
2. Ive check my systems to see whether the "Regional & Language Option" are ok and there seems to be no issue there.
3. Reinforce the query by using variable to set the date.
but still no good ive also tried googling but i dont know how to ask google for something like this, so i came here to ask you fine people.
Any help with this is greatly appreciated
ps. if any of this does not make sense ill detail it more in the next post. but i really need help :)
thanks in advance
View 2 Replies
View Related
Sep 12, 2005
Windows XP
Office 2003 SP1 with Access 97 SR2
When opening a certain Access database a user is recieving this error:
Function isn't available in expressions in query expression '(((PRODUCTION.DATECREATE)=Date())'.
This only happens on a specific computer. I've tried uninstalling and reinstalling Office, Access, I've even tried just installing Office 97. I also went through and reinstalled all the ActiveX controls that have to do with the date (MSCAL.ocx gives us trouble sometimes).
I'm not that familiar with the programming for the database, it was done quite some time before I got here, but I know enough to understand what is happening. I think the problem lies with the Date() function (this still happens as admin on her machine so it isn't a permissions error) but I have no idea how to fix it.
I remember reading something not long ago about a similar issue that had to do with differing versions of library files for VBA. I was going to try and replace the library file containing that function on her machine with one from mine, until I found that it apparently isn't stored in a file.
Any help would be greatly appreciated as I'm at my wits' end with this thing.
- Pat
View 2 Replies
View Related
Jun 20, 2005
Hi,
I am trying to run a query that when you click on it, it shows any due dates for inspections test to appear for the month your in. E.g. I want to know all the inspections tests that are due in June. I have the following code but it doesn’t seem to work, it will show some of the tests due but not all of them.
Here’s the SQL:
SELECT [tblemployee Details].Department, [tblemployee Details].[Payroll No], [tblemployee Details].[First Name], [tblemployee Details].ADateDue, [tblemployee Details].LDateDue, [tblemployee Details].CDateDue, [tblemployee Details].CCDateDue, [tblemployee Details].SDateDue, [tblemployee Details].HDateDue, [tblemployee Details].MDateDue, [tblemployee Details].RDateDue
FROM [tblemployee Details]
WHERE (((Month([tblemployee Details].[LDate Due]))=Month(Now()))) OR (((Month([tblemployee Details].[LDate Due])=Month(Now()))=True))
ORDER BY [tblemployee Details].Department;
Thanks
Michelle
View 1 Replies
View Related
Jan 23, 2006
Hello, im new to this forum, and i need some help fast.
I have a database which is a list of all of member with some information and an expiry date for there membership. What i would like to be able to do is run a query and for it to search a table and give me the names of the people whos member ship has run out or is going ot run out within a month of the query being run.
I have tried several things already but access isn't realy my thing, i do Visual basic .net .
If any one has any ideas about how i could sort this would you please be able to respond, thanks in advance!
View 5 Replies
View Related
Jan 26, 2005
Hello,
Im trying to list the entries between two dates, so I made a form with selection boxes for the dates, months and years and just piece them togheter.
startdate = request.form("smm") & "/" & request.form("sdd") & "/" & request.form("syy")
I then use the following query to get the data:
StrSql="Select * From calls where datestamp >= " & startdate & " and datestamp <= " & enddate & ";"
But it seems it doesnt treat the startdate variable as a date? (se example below where i compared the date to diplay how it compared in the script)
1/25/2005 is bigger then 1/25/2005
1/25/2005 is bigger then 1/25/2005
1/25/2005 is bigger then 1/25/2005
Any input appretiated...
View 2 Replies
View Related
May 23, 2007
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
View 3 Replies
View Related
Sep 7, 2007
Hi guys, im really unfamiliar with MS access so could use a bit of guidance.
I have an SQL server back end and MS access front end, im trying to run a simple date query to look at records with todays date only.
In sql it would be something like: -
SELECT Contract, convert(char(10), DispatchDate,103) as [Dispatch Date]
FROM BinLocations
where convert(char(10), DispatchDate,103) = (select convert(char(10), getdate(), 103))
I know i can use date() within MSaccess, but not sure i would convert the datetime field within sql to display this in msaccess.
Anyone have the ms equivilant to my sql query?
Thanks in advance
Note: datatype for this field within sql is date/time
View 1 Replies
View Related
Jan 1, 2008
Hi all and a happy new year,
I am getting more and more infuriated with dates and access. I thought I had it all working, and now I get to 01/01/2008 and it all falls apart. Anyway,
I have a lump of ASP that finds various dates and queries them against an Access DB. I have used response.write to see what is being sent to the database, and they are in UK format DD/MM/YYYY as they should be. The field in my DB stores the dates as DD/MM/YYYY. Now for the bit that is driving me mad.
Somewhere in access, US format is being used. For instance, I have a date value that finds tomorrows date. In this case its 02/01/2008 for the 2nd of January 2008. When I enter the SQL in access though, no data is brought back. If I query on date equalling 01/02/2008 then data is brought back!
So, my program is outputting UK format, the dates are stored in UK format, my regional settings and everything else on my computer are UK format. Access is trying to find US format though-why?
Its driving me mad!
View 4 Replies
View Related
Sep 10, 2015
I am building a database and in it I need to do date calculations. Without too much detail I input a date based on the built in calender from the date/time field and based on that date I need to have the following data returned into other individual fields: 6 months from that date, the days until that date, 2 months from the date input, 4 months from the date input, 5 months from the date input, 60 days from the end date, 45 days from the end date, 30 days from the end date, and 15 days from the end date.
Example: I input "Sep 10, 2015" and I would get "Mar 10, 2016", "181 Days", "Nov 10, 2015", "Jan 10, 2016", "Feb 10, 2016", "Jan 10, 2016", "Jan 25, 2016", "Feb 9, 2016", "Feb 24, 2016".
I have read through websites regarding date calculations within Access but I cannot find the specifics for the application that I need.
View 5 Replies
View Related
Jan 5, 2006
Hi,
I have an access database which has a date field. I've entered all of the dates in valid UK format (dd/mm/yyyy). An example of the table looks like this:
Id| SeminarName | SeminarDate
1 | Introduction | 18/02/2006
2 | Other stuff | 19/02/2006
When I do:
SELECT * FROM tblSeminar WHERE SeminarDate=#18/02/2006#;
It doesn't return any records. If I remove the WHERE SeminarDate... it works fine, likewise if I use:
SELECT * FROM tblSeminar WHERE SeminarDate=#02/18/2006#;
So something is causing the date not to match.
I'm using <%Session.lcid = 2057%> at the top of all of my pages, and am hoping to keep all formatting in UK dd/mm/yyyy format.
Can anyone help here?? Thanks very much...
View 5 Replies
View Related
Mar 13, 2006
Hi,
I am having trouble with the format of dates comming from access, if I view a table in access with a field of Date/Time the date is formatted correctly as dd/mm/yyyy, but when I retreve the date from my ASP page the format changed to mm/dd/yyyy. I can successfully change the format using a date format function but is there a way of getting access to send the date in the correct format in the first place or know why it's doing this. I am using access 2003 and the following connection string
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:webSites estWebdataBasefcd_db_080306. mdb"
Thanks for any help and guidance you can give
Joolz
View 1 Replies
View Related
Apr 4, 2007
Hi all,
I'm new to access and have been creating a table to store data entered by machine operators about product quality checks in my factory. I can create the fields with the relevant data ok and make a form as the interface for the operators to enter the data but i am having trouble with the date and time.
What i need is for the date and time to be entered into the data base for each entry at the time of entry, but i need this to happen as an autodate type function as i dont want the operators to have the ability to fudge the dates or times.
I hope I have explained this well enough.
Any help would be appreciated.
Cheers,
Steve
View 7 Replies
View Related
Aug 15, 2006
I have a query that reports on records according to date. The criterea line of the date field has [Enter Date]. I have confirmed that there are dates from 8/1/06 through 8/10/06 in the table, but when I enter the date 8/10/06 the query returns no records. But it does return records for all other dates (8/1/06 through 8/9/06 inclusive).
So then I tried to make the date a range:
Between [Enter Start Date] And [Enter End Date].
I entered 8/9/06 and 8/11/06 in these fields and there in the report were the records for 8/10/06. Then I went back to [Enter Date] as criterea and entered 8/10/06 and no records are returned.
View 3 Replies
View Related
May 2, 2008
I have converted an old database to Access 2007 and find I need to edit my queries that use the date format function.
eg change from Format([date],'yyyy') to Year(date)
I can change each query manually but is there a quicker way to do this?
View 2 Replies
View Related
Aug 4, 2005
For my own database which i created in Access i have something called a mutation date. so when i changed something i typed the date of that day.
Now i want that to go automaticly. How do i do that???
It would be great if someone could help me with that. Once there's something changed in the record i need to make the date of that record changed.
Hope u can Help me
SilverBlood
What doesn't kill u makes u stronger
View 6 Replies
View Related
Feb 9, 2005
I have a question regarding time/date issue. I am creating a database that will track trucks coming and going. I'll have several date/time fields. Appointment, Arrival, Departure, etc. My question is. On the Appointment field, Sometimes the trucks won't have an appointment. I would like to put some type of data here in this field. I have tried to set it up so if the truck doesn't have an appointment the user would put 00:00, but it reverts to 12:00 AM. I have checked the Data Type of the field in the table and it is just set to Date/Time with no formatting. What is going on with this thing? I tried changing the format to just short time and it reverts all my data in the table to 00:00.
Can ya help me out?
Thanks in Advance.
TaznLeo
View 5 Replies
View Related
Jun 14, 2005
I hope someone can help me here because I'm pulling my hair out... Anyway... Basically I'm trying to save and retrieve a UK date (dd/mm/yyy) to an Access database using asp. The database resides on a server located in the US
Things I've tried so far:
-- Specifiying Session.LCID both in Global.asa and individual ASP pages
-- Using Custom formatting to format date before entering into database (using SPLIT function)
-- Using custom formatting to format date AFTER retreiveing from database
The problem is that the date seems to change how it's stored in the database. If I entered "16/02/1982", it would be saved in teh database as '02/16/1982".. but if I entered "03/04/2005" it would save it as this.. So fomatting after retreiveing the date from the DB just messes everything up!
Does anyone have any ideas how to solve this other than moving to a UK server?
View 2 Replies
View Related
May 15, 2013
I've used this switch query:
DueDate: [Date] + Switch([Type] = "", 182, [Type] = "", 182, [Type] = "", 182, [Type] = "", 182, [Type] = "", 182, [Type] = "", 182, [Type] = "", 91, [Type] = "", 91, [Type] = "", 91)
To try and calculate a due date based on the reponse [Type]. The logic is "If the [Type] is "MEETING TYPE", then add 182 days onto the [Date]," And so on.
I've used this switch example from another thread on here as an alternative to IIF.
View 2 Replies
View Related
Mar 14, 2013
I am having a problem with the date function in access 2010. The default format is Date/Time "General" but the sample on the right side just shows a number (17). The field goes blank on any date I enter. I have to manually format the date field with mm/dd/yy to show the dates in the date field. I don't always have to manually format the date. The long and medium dates are the only two from the drop down list that show dates properly.
View 3 Replies
View Related
Aug 21, 2012
I have in a cell in Excel: MM/DD/YYYY...I want to add it to my Access database as: YYYY/MM/DD...This is what I have so far in Excel VBA. It is giving me a date error. I have tried both as MM/DD/YYYY still did not work.
Code:
n=1
accDateSub="01/01/2011"
Dim conn As New ADODB.Connection
conn.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:pathfile1.accdb;"
thisSQL = "INSERT INTO [Table1] VALUES (" & n & ", #" & Format(accDateSub, "yyyy/mm/dd") & "#);"
conn.Execute CommandText:=thisSQL
View 7 Replies
View Related
Jul 13, 2005
Lads,
Here's a little problem that is driving me nuts. I know what is wrong and yet I can't fix it!
I have an Access D/B and a mail merge word doc which gets its data from a table in the database. Now the date is in the correct format in the table i.e. uk/european format. The 5 of january 2005 would be 05/01/2005.
However when this gets into the word doc it changes to USA format i.e. 01/05/2005.
Not very desireable! how do I fix it?
Thanks in advance,
Liam
View 4 Replies
View Related
Aug 13, 2005
How can I create a database which automaically inputs the date and time when a value is placed it another collumn.
e.g 3 collumns= name, date + time
When the name is inputted the present date and time are assigned to the name.
If the name is changed then the date and time are updated to the present.
Many Thanks.
J
View 2 Replies
View Related
Mar 8, 2007
I have developed an Access database which keeps track of items we have received. What I want to do is enter a date in a field and then by clicking a button that date and the name of the item gets enetered into my Outlook calendar so that it will pop up a reminder on that date.
I have no idea if/how this can be done. Any ideas or examples very much appreciated.
View 2 Replies
View Related
