I have a table with several date fields. All the fields are formatted to medium date, but the data is being saved in to different ways. Some records are yy-mmm-dd and some are dd-mmm-yy. Each field has at least a few inconsistent dates but not always in the same record. I can't figure out how to make them consistent again. Has anyone else ever had this problem?
:confused:
I am at a loss as to why my dates in my table datasheet are not consistent in the Date/Time format. In the table and specifically the Date/Time field it is formatted as the selection "Short Date". I am located in the U.S. using MS Access 2003. The database I'm using was a free download from the MS website called "Accounting Ledger" and it is for Access 2003.
http://office.microsoft.com/en-us/templates/TC010175341033.aspx?CategoryID=CT101426031033
While I have dabbled in databases some I am really at a loss as to why I am seeing two >>different<< Date/Time formats in the *same* table.
Below is the date range I've entered from January 2007 to May 2007 and below has been copied and pasted directly from the datasheet. Trying to do a date sort in any fashion is out of the question until I resolve this.
I am consistent in my date input... April 6, 2007 is entered as 4/6/07, January 18, 2007 is entered as 1/18/07, February 21, 2007 is entered as 2/21/07 and so on.
However, some dates are showing as mm/dd/yyyy format while others are showing as dd/mm/yyyy format.
Note: if I choose a general date or long date format Access reads these as July, September and October dates in some cases.
Here is January (as copied and pasted from the datasheet)
01/03/2007 (these first 7 show a mm/dd/yyyy format)
01/03/2007
01/02/2007
01/10/2007
01/09/2007
01/05/2007
01/11/2007
18/01/2007 (these last 6 show a dd/mm/yyyy format)
24/01/2007
27/01/2007
27/01/2007
02/01/2007
31/01/2007
Here is February's
05/02/2007
02/07/2007
26/02/2007
02/09/2007
21/02/2007
28/02/2007
15/02/2007
04/02/2007
And March's... (the only month that appears correct...)
14/03/2007
17/03/2007
21/03/2007
14/03/2007
19/03/2007
22/03/2007
14/03/2007
15/03/2007
April's....
04/11/2007
04/05/2007
04/10/2007
04/10/2007
04/11/2007
04/11/2007
04/11/2007
04/11/2007
13/04/2007
04/12/2007
04/12/2007
13/04/2007
13/04/2007
13/04/2007
04/04/2007
20/04/2007
04/06/2007
23/04/2007
24/04/2007
17/04/2007
17/04/2007
17/04/2007
27/04/2007
04/10/2007
04/10/2007
13/04/2007
24/04/2007
May's...
05/06/2007
05/01/2007
05/03/2007
05/08/2007
05/09/2007
05/09/2007
05/10/2007
05/11/2007
14/05/2007
14/05/2007
14/05/2007
15/05/2007
15/05/2007
16/05/2007
16/05/2007
17/05/2007
25/05/2007
Any help would sure be appreciated!
Thanks in advance.
Rod
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?
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.
I dont get any errors with this code. Let me explain what im doing:
T1 = temparary table (not really temparary but the values just change so often it might as well be)
this table has field names: partname, order, maxorder, etc. stuff like that
pretty much its a table that has records for each part name.
T2 = main database
this table holds the order for all parts for a particular time and day
T1 is the one that people are familiar with, where as T2 's set up is something that they would not be, so for clarity we use T1 for displaying etc.
now my problem is, i try to transfer data from one table to another but it works sometimes and doesnt work others, i dont get any errors and i have option explicit on as well. if someone could look at this code and tell me if there are any problems that you see, the quicker the response the better.
technical specs:
Access 2000
Windows 2000
Code:
Private Sub insertRecMainDB(ByVal typeOfOrder As String)
'This sub will insert a new record of type O (Ordered) and put in all values from the table Order
'Also it will make a duplicate record of type R (Received) and put in same values so when later
'if there are no changes (meaning all product was received) no entry has to occur, however if you
'are missing a part then you can enter how many on the other form.
Dim yesOrNo As String
Dim tempSQL As String
Dim rec As New ADODB.Recordset
Dim orderAmountRec As New ADODB.Recordset
Dim partNameRec As New ADODB.Recordset
Dim numOfRecords As Integer
Dim cnt As Integer
'Checks the type of order and either checks or does not check it in the DB
If typeOfOrder = "O" Then
yesOrNo = "yes"
Else
yesOrNo = "no"
End If
'Create the SQL statements to update MainDB
tempSQL = "INSERT INTO [MainDB] ([OrderNumber],[DateTime],[OrderedReceived],[TypeOfOrder]) VALUES (" + CStr(txtOrderNumber.Value) + ",'" + CStr(lblDateTime.Caption) + "'," + yesOrNo + ",'" + typeOfOrder + "')"
CurrentDb.Execute (tempSQL)
'Counts the number of parts we have to order, saves it in numOfRecords
rec.Open ("SELECT* FROM [Order]"), conn, adOpenStatic, adLockReadOnly
numOfRecords = rec.RecordCount
rec.Close
'Loop to transfer data from the table Order to the table MainDB
For cnt = 1 To numOfRecords
'Gets the name of each part
tempSQL = "SELECT [Description] FROM [Order] WHERE [PartNumber]=" + CStr(cnt) + ";"
partNameRec.Open (tempSQL), conn, adOpenStatic, adLockReadOnly
'Gets the amount ordered of that part
tempSQL = "SELECT [MaterialOrdered] FROM [Order] WHERE [PartNumber]=" + CStr(cnt) + ";"
orderAmountRec.Open (tempSQL), conn, adOpenStatic, adLockReadOnly
'Updates that part name with the ordered amount
tempSQL = "UPDATE [MainDB] SET [" + CStr(partNameRec.Fields(0)) + "]=" + CStr(orderAmountRec.Fields(0)) + " WHERE [OrderNumber]=" + CStr(txtOrderNumber.Value) + " AND [DateTime]='" + CStr(lblDateTime.Caption) + "' AND [TypeOfOrder]='" + typeOfOrder + "';"
CurrentDb.Execute (tempSQL)
partNameRec.Close
orderAmountRec.Close
Next
End Sub
I have a query and the works fine
The query is
Code:SELECT RawData.OutlineNumber, RawData.OutlineDescription, RawData.DeliverableDesc, IIf(subQuery1.TotalProgress=0,"",IIf(subQuery2.TotalDaysForDeliverable=0,"",FormatPercent((RawData.PercentComplete/subQuery1.TotalProgress)*(RawData.Finish-RawData.Start)/(subQuery2.TotalDaysForDeliverable)))) AS WeightedContributionFROM RawData, [SELECT DeliverableDesc, SUM(PercentComplete) AS TotalProgress FROM RawData GROUP BY DeliverableDesc]. AS subQuery1, [SELECT DeliverableDesc, SUM(RawData.Finish-RawData.Start) AS TotalDaysForDeliverable FROM RawData GROUP BY DeliverableDesc]. AS subQuery2WHERE ((RawData.DeliverableDesc)=(subQuery1.DeliverableD esc) And (RawData.DeliverableDesc)=(subQuery2.DeliverableDe sc) AND RawData.ReleasePeriod=ReleaseMonth);
I am now try to set that query as the record source in a sub form using VBA but it throws a syntax error, run time error ‘3075’
Code:strRowSource = "SELECT RawData.OutlineNumber, RawData.OutlineDescription, RawData.DeliverableDesc, IIf(subQuery1.TotalProgress=0,"",IIf(subQuery2.TotalDaysForDeliverable=0,"",FormatPercent((RawData.PercentComplete/subQuery1.TotalProgress)*(RawData.Finish-RawData.Start)/(subQuery2.TotalDaysForDeliverable)))) AS WeightedContribution " & _ "FROM RawData, [SELECT DeliverableDesc, SUM(PercentComplete) AS TotalProgress FROM RawData GROUP BY DeliverableDesc]. AS subQuery1, [SELECT DeliverableDesc, SUM(RawData.Finish-RawData.Start) AS TotalDaysForDeliverable FROM RawData GROUP BY DeliverableDesc]. AS subQuery2 " & _ "WHERE ((RawData.DeliverableDesc)=(subQuery1.DeliverableD esc) And (RawData.DeliverableDesc)=(subQuery2.DeliverableDe sc) And RawData.ReleasePeriod = '" & Me.Combo10.value & "' )" Me.DeliverableStatus_Contribution_Of_Tasks_subform .Form.RecordSource = strRowSource
How is it that the query works when I run it but when I try to apply it to a sub form it gives a syntax error, any ideas how to correct this
Using access 2010; i have a form that includes a date field. Is there a way to force the user to only choose a month end date? When the user clicks the date from the popup, they may use 9/1/2013 when the mgr. want them to use only 8/31/2013. I am thinking validation field to put a msg but want to be able to force it not the option.
View 2 Replies View RelatedI am making a race results database that is supposed to sum an entrant's two times together to obtain a total combined time.
Let's say that the two times I have are '41.43' (41 seconds and 43 milliseconds, there is no colon) and '1:48.17' (1 minute and 48 seconds and 17 milliseconds), and both are currently of the short text data type in my table. How would I go about obtaining the sum of these two times? I have already tried using queries to convert the strings to seconds with no success.
I have a date and time stamp in a Date/Time field of General Date format (3/1/2006 7:52:25 AM).
I wish to select query on the table's Date/Time field by date portion only (3/1/2006) and not include the time portion (7:52:25 AM) of the field.
Using this expression in the query's criteria - "Between [Enter Start Date: (MM/DD/YY Format)] And [End Date: (MM/DD/YY]" will not return the date ranges as desired without also typing in the full time string.
How can the date integer be parsed out and the query properly expression ed on the criteria field without using VB?
I work for a bank and have build a few little DB's for differant groups but my current project is a work flow tool for a department. I have it 90% completed and i still have to build in the reporting side. Part of the reporting is caclulating times and this is where i am having problem.
I have some coding built but i am getting inconsistant results, as an example if i am trying to calculate the total amount of time between [start Time], [End Time] then minus any [Stop Time].
Code:
Me.Total_Time = Format(Int(Workhours(DateAdd("n", Nz([Total_Stop_Time]), [Processor_Date_Stamp]), [Date_Sent_to_RM])), "00") & ":" & Format(Int((Workhours(DateAdd("n", Nz([Total_Stop_Time]), [Processor_Date_Stamp]), [Date_Sent_to_RM]) * 3600 - (Int(Workhours(DateAdd("n", Nz([Total_Stop_Time]), [Processor_Date_Stamp]), [Date_Sent_to_RM])) * 3600)) / 60), "00") & ":" & Format(((Workhours(DateAdd("n", Nz([Total_Stop_Time]), [Processor_Date_Stamp]), [Date_Sent_to_RM]) * 3600 Mod 60)), "00")
I have this on a command button, i have this same code for a few other calculations and in some cases it seems to be working i have just changed my start and stop fields along with what stop time to take out in each case. I have attached some parts of my DB, one of the modules, my main table and the form where i am running the coding.
On my report, I want rows with expired dates to stand out by having their borders thicker. Something like:
Code:
If Me.txtDate < Date() Then
Me.txtDate.BorderWidth = 6
End If
When I do that, it ends up formatting every txtDate field in the report if one of them passes the If criteria instead of only ones that are expired.
I have split the database, with the back-end residing on the server. Only 1 other person is working in Access right now; she's verifying the data. Today she's working directly in the back-end, could this be the problem. If it is, I'm going to have to create a front-end for her quickly.
My problem is this...I'm working on queries and forms so that the scientists who will ultimately be using this application, won't be able to go in and inadvertently change something in the tables set-up. When I'm in the back-end main data table, it says I have 2723 records.
When I create a front-end query to query all the records in that table, it says I have 2160 records.
The input form that I created with most of the same fields as the query (created BEFORE I created the query...I'll have it pull from the query now instead of directly from the table) also says we only have 2160 records.
Using Access 2010
I have a continuous subform on my main form where one text box is a hyperlink that opens a form. All is well, except....
When the mouse hovers over the text box of the top record, the cursor does not change to the hyperlink pointed finger thing (it's I-beam), even though the text in the textbox displays in hyperlink format.
If I hover over the hyperlink/text boxes of records 2 thorough x, the hand-cursor appears.
More info:
1. The hyperlink functions normally (i.e., the appropriate form opens to the appropriate record)
2. When I then return to the main form, the hand-cursor magically appears when hovering the first record.
3. I have 2 other continuous subforms on the same main form that behave the same way.
I have a simple select query on a SQL table from Access. The query is:
SELECT tbl_Orders.OrderID, tbl_Orders.Approved
FROM tbl_Orders
WHERE (((tbl_Orders.Approved)=0) AND ((tbl_Orders.Completed)<>0))
ORDER BY tbl_Orders.OrderID;
The strange thing is that sometimes it pulls 34 results, and sometimes 38. From what I can tell, it should be pulling all 38.
What can I do to make sure it gets all the records?
The date format from one of my access tables is as follows:
11051977
9101978
and I need to amend to following formats within my select query:
11/05/1977
09/10/1978
I have tried using formula:
Date of Birth: DateValue(Mid([DOB],7,2) & "/" & Mid([DOB],5,2) & "/" & Mid([DOB],1,4))
that results in an #Error messages
Can anyone help?
Thanks
Hello
Can someone help format the date correctly? I'm currently linking a flat file in access and then creating a table afterwards. The date comes in the following format below:
i.e. 30102006 - txt type
I wanted the date to look like - mm/dd/yy. Thanks for the help.
I have a date field in my table and my computer date is set as a custom long date, if I enter 24/2/07 in my date field when I press enter and move to the next field it is displayed as Saturday 24 February 2007 but when I select the date field again it reverts back to 24/2/07. I include the date field in a merge with a word document and would like the date displayed as Saturday 24 February 2007, but the merge is displayed as 24/2/07. Can anyone help with how to merge the date as Saturday 24 February 2007?
View 4 Replies View RelatedHello,
I have two pc's, both with Windows XP SP2 and Office 2003 SP2 (dutch) and the same input locales. I made a program in Access (connected to a MS SQL Server) where dates are stored. Now I want to built a report with a daterange. Because of the dateformat in SQL I made a function to convert the date because they enter it in Dutch format. The function is:
Function DatumSQL(datDatum As Date) As String
DatumSQL = "#" & Format(datDatum, "mm") & "-" & Format(datDatum, "dd") & "-" & Format(datDatum, "yyyy") & "#"
End Function
This works great on computer 1. But not on computer 2. On the second computer the date is converted again. When I don't use the function it works fine on computer 2 but not anymore on computer 1.
I searched for differences and found that on computer 1 the files in the map C:Program FilesCommon filesSystemado are from 3-8-2004 and on computer 2 they are from 2-3-2006.
How can this happen? And what is the solution?
I hope someone can help me.
Thanks in advance.
Benno
I have a date field with a format of 'Short - dd/mm/yyyy'. All of the data stored in that field is in that format.
I have a listbox that displays all those dates. I select a date, then click on a button to filter the form by the date selected. Here is my code that fires on the cmd_Click event:
Forms!frmPondSampling.Filter = "RPID = " & Me.cboPondID & " AND Date = #" & Me.lstDate & "#"
Forms!frmPondSampling.FilterOn = True
This works for dates where the dd is more than 12, but it wo't work when the dd is less than 12. I'm assuming it is getting the dd and mm mixed up, but all my data is correct and matches the field type.
I am trying to find a date format and superised that MS doean't offer this feature. The format I am trying to set setup is this: 01/01/2006. Right now, all I can do is to set it up by the befault setting of 1/1/2006 and is not what I am looking for. Is there a way that this format can be modified?
View 11 Replies View RelatedI am trying to run a query that someone already established/saved. It asks for beginning date and end date. I have tried a few different formats but I cannot get the query to run. I have tried the date as 1998,01,01 for beginning and 2006,03,14 for end. i have tried also 01-01-98 and 01-01-1998 etc. I keep getting the same error that the date is too complex or something. Can anyone help me?
View 7 Replies View RelatedUsing MS Access I have the following SQL for a selection query:
SELECT qryOfferteAfdruk.pkOfferte, qryOfferteAfdruk.Nummer, qryOfferteAfdruk.Datum, qryOfferteAfdruk.Bedrijfsnaam FROM qryOfferteAfdruk WHERE [qryOfferteAfdruk.Datum] Between #08-01-2008# And #12-01-2008# ORDER BY qryOfferteAfdruk.pkOfferte DESC
The problem is that the 'between' dates should have dd-mm-yyyy format, but once the query is executed the format is mm-dd-yyyy. I do not understand why, because in the control panel of Windows I selected dd-mm-yyyy and in MS Access I selected the standard date format, being dd-mm-yyyy.:confused:
Hello,
This is problably a very simple question; however, I can't find the way to do it.
I have a field in a form that its default value is today's date: Default value: Date().
As you know, it always appears as dd/mm/yyyy. Is there a way to change it and instead to appear as mm/dd/yyyy ? I am sending this field to some reports and an error is appearing as in the database dates are stored as mm/dd/yyyy
Thank you!
I using a auto date in a table and want it to display differently in a form.
How do i do it?
I have control sources set up in a report as follows:
=DateAdd("d",0", Forms!frmDates!txtEnd
=DateAdd("d",1", Forms!frmDates!txtEnd
=DateAdd("d",2", Forms!frmDates!txtEnd
=DateAdd("d",3", Forms!frmDates!txtEnd
=DateAdd("d",4", Forms!frmDates!txtEnd
...etc...
They are coming up with a mmddyyyy format. I'd like to only show the dd part. Is there a way to do this in the report, or do I need to go back into my SQL query to do it?...
I have a form where i am inputting dates.
The short date format is MM/DD/YYYY, but i can't find a way to put it DD/MM/YYYY
Can you please help?