Resulting Date Format In Datasheet Is Inconsistent
Jun 1, 2007
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.
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
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?
I have a subform on a Customer Data form for Phone Numbers (in order to normalize the data). Is there a way to display a subform in a format other than datasheet view? I only want to have space for 3 phone numbers, so a datasheet isn't necessary - I would rather the subform blend into my form instead.
I have read this: http://support.microsoft.com/?kbid=216675 , but it is not what I am looking for, (or I can't get it to work) I have searched the forum - with no luck, formatting subform posts all seem to do with conditional formatting.
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 have a combo box that looks at a table with an SQL statement. In the form, the combo box shows the headers of the resulting table. Is there any way that the user can click on any header (let's say 'LastName' or 'Date' ) so the drop down list would be sorted by that header that the user clicks on??
I tried the search and while I found some things related to my issue, I couldn't really come to a conclusion on my issue.
I have all the databases on the network set to compact on close. This is resulting in a new copy of the compacted database with the generic "db1.mdb" file name every time the db is compacted. It also does not compact the correct one.
So, basically...it is copying the db, compacting it, but not deleting the old one and renaming the new one.
If I copy the database to my hard drive, it compacts, deletes, and renames sucessfully.
The only thing I can think of at this point is there's some issue with the server. But this is happening on multiple servers.
I am assisting with the design of a db, where depending on two criteria, one of two letters needs to be printed. If one check box is positive, a letter is to be generated. If it is empty then a second checkbox will be checked, resulting in a different letter.
I am trying to run this code but getting a run time error 94 - null value - when it shouldn't be.I am thinking I have written the Dlookup incorrectly?
Code: Private Sub txtProductName_Click() Dim iProdType As Integer Dim ProductID As Integer iProdType = DLookup("ProductTypeID", "tblProduct", "ProductID" = Forms![frmBooking]![cboProductID].[Value]) Note ProductID in an integer
How can I diagnose this or what could the solution be?
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
'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
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
I am looking to create a query that adds a new field to the resulting table. The field should be 'Yes/No' and for every entry the default should be 'Yes'. The query looks something like this:
Code:
Select .*, [here the new field] From MyTable1 Union Select .*, [here the new field] Form MyTable2
The Union statement isn't really relevant, just for completion's sake.
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.
I 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].
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.
I have a checkbox that determines whether or not to display certain form controls. How can I also hide the resulting white space that comes from hiding the form controls?
Can I put all of the controls in some sort of container and hide the container? The form objects are all displayed in order, so it shouldn't cause issue.
Can I create a subform for the objects and hide it? This would make the main and subforms based off of the same table.
Occam's Razor would be the preferred philosophy in this case. Nothing too fancy is needed.
I am trying to create an INSERT statement from a form to put unbound fields in a table. The challenge that I am a getting is that I am getting a
Run-time error '3075' Syntax error in date in query expression '#'
What is really perplexing and perhaps something that may guide in identifying the culprit is that I have an identifcal form that uses the identical code and it works.
Here is the code below:
Dim strSQL As String Dim strCriteria As String strSQL = "" strSQL = strSQL & " INSERT INTO [tblTicket]"
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.
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?
I have a form in datasheet view, with a date column. When I filter for last week it gives me all the entries for that week, but when I try and filter on a single day it only gives me 1 entry even though I know there are several entries for that day. Why would it be doing this? It also is the same when I go directly to the table and try to filter. I know the underlying data that was imported is a full date and time stamp, which I'm displaying as just the date portion, but surely it should show me all that are on a particular date, not just 1?
Just checked to see whether it would give me "yesterday", and while it seem to be able to when I checked 2 days ago, now it's not even giving me those.
I'm an Access newbie, self-taught. I can make a basic form to input data.
What I'm trying to create is a tool for assessing aquisition of student skills. I want to be able to check that a student knows the name of a letter or the sound of a letter - but have the check recorded as a date in the datasheet.
Is this possible? And, can you explain to me how to do this in language I can understand? I'm not a programmer you see
I have a subform that is in Datasheet view. If I open this subform on it's own, the Date Picker works for the Date field. However, if I open the main form with the subform on it, the date picker doesn't work? Nothing happens when I select a date on the calender.
