I have come across a weird event from the 1st July.
I have a form that inputs dates into the table.......nothing special.......
Form is totally unbound and the insertion to the table is done via DoCmd.RunSQL Insert every thing has been fine until the 1st of July at this point it started saving the date into the table in American format 07/02/2013 instead of UK 02/07/2013 There have been no changes to the database at all to give me a reason why this has happened.
The Table fields are set to Date Fields.
The SQL Insert was set to Date ( #" & DATE & "# )
The date displayed on the form is in the correct format to get around this I have set the insert of the date as a string ( '" & DATE & "' ) AND how can i reformat the dates that are incorrectly inserted to the correct format as they include the date and time 07/02/2012 11:11:47
There is about 1000 lines so manual change will be a nightmare.
I 'm downloading the excel data from the site and connecting it to access.
In excel the particular column (Time Taken) is in the format of "00:12:26".
After connecting it to access and appending it to the table, the format changed to "12:12:26", the first two digits changed to "12" and the remaining are as it is how it looks like in the excel. I need to change it to format what it looks like in the excel.
how shall i define my new Date or numbers.Now "yyyyXX" this is my date "y" is year 4 digits and "XX" is my problem 2 digits. I ll give an example,
(XX = 01....36) XX can be max 36 and min 01 of course. f. Exp. 201436 and next number shall be 201501.
the biggest problem is different of these number with basic math 201501-201436= 65 but for me it should be 01... The Question is; How can define these ??
I'm trying to pass some dates from an excel userform into access.
The date is chosen using the DTPicker tool ( basically a drop down calender). I have set the property of this to custom format dd/MM/yyyy, however dates get passed to the appropriate field in access in the American format.
In access the date fields are set to Short Date and the example shown for this format is in the UK format. I assign the date to a variable before passing that variable to the update SQL string:
Code: s1 = Nz(DTPicker1.Value, #1/1/2000#)
I have dimmed s1 as date and then added:
Code:
s1 = Format(Date, "dd/MM/yyyy")
My update string is:
Code: "SET [Stage 1] = " & "#" & s1 & "#" & " "
I suspect that the nozero function may be the issue but am at a bit of loss atm.
I've written a vba macro that takes data from Excel, processes it into an XML file & then imports into Access.All fields were fine apart from the date field, which appeared in the Access table as "00:00:00"...The date in the XML was appearing as 01/01/2014.
I have a database that contains a table with all tasks made in our team and the time it takes to perform the task. I have another table with month, year and value in which I put month after month the volumes that each task handles. So, in a query I multiply the time to perform the task and the volume; month after month I have different results. However "the time to perform the task" may vary depending on processes improvements that we made. If before I had 5 minutes to perform the task and now I have 2, I change it and then the results on the query are changed for all months. I do not know if there is a way to specify that that change must be applied from the month where it is updated to the following months and not retroactively.
Within my main table / form I have six status drop down boxes and would like to record with a date and time ever time they are changed and save this data in a second table.
If I cant do this with the six drop downs, can I do it if any change has happened to the recorded.
I am new to access 2010. I have a table called "Forecasts", and I wish to update the timestamp with todays date, whenever the user makes a change to the qty of goods forecasted.
I have been researching online for the solution to the correct format to datetime, but it doesnt seem to work.
Code: Dim todayDate As Date todayDate = Date Dim sqlString As String sqlString = "UPDATE [Forecasts] " & _
[Code] .....
The messagebox shows:
UPDATE [Forecast] SET Branch_Plant=123, Item_Number_Short='222', Description='AAA', UOM='EA', Estimated_Cost=123, Requesting_Business_Unit='AAA', End_Customer='CCC', Project ='Secret', Timestamp=#26/12/2013# WHERE ID =24
Then I hit a syntax error. Whats wrong with sqlString?
On my form have a status field with about 7 options to choose from a combo box, i have these values set in another table. What i would like is for my other field, which is a date, to automatically change whenever the status field is changed. So i know how long ago the status field was set. So basically if i change the status to complete i want the date to then set the date and time now.
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 table which has 2 fields 1) Project_Priority_Number and 2) Previous_Priority_Number. If there were 100 records these would be numbered 1-100 in the order that the user originally sets the priority (this number is in addition to the record ID number). I have created a form with code that moves the Project_Priority_Number to the Previous_Priority_Number and then shows the Project_Priority_Number as blank and displays all of the records.
The user can then set new priority numbers in the blank column. Say they choose to make the old priority number 4 the new number 3 and priority 27 now becomes say 2, etc. I want the user to press a button that re-numbers the remaining ones based on their old position + or - 1 (basically to fill the gaps but based on their previous positions). I understand how to renumber if one is deleted but I don't know how to be more specific and re-number based both on their previous priority number and whether something else is now set to replace that.
For Example (the following numbers need to re-order based on previous priority 4 becomming the new priority 1 and the previous number 8 becomming 3 - so the previous 1 becomes 2 and then everything after the new 3 moves forward 1):
PROJECT PRIORITY NUMBER PREVIOUS PRIORITY NUMBER 1 2 3 1 4 5 6 7 3 8 9 10
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.
On a form (default view: Single), I have a bunch of bound controls that display one record at a time from tblMain. I have a bunch of unbound controls as well (buttons).
If the user tabs through all the controls, reaches the last one (btnLast), and presses tab again, the focus is set to the first control (btnFirst) and the bound controls change to display the next record. I just want the focus to be set to the first control without the displayed record being changed.So I set up an "If" statement in Private Sub btnFirst_GotFocus()
Code:
If Screen.PreviousControl.Name = "btnLast" Then [code to change record back to previous record] End If
But btnFirst gets the focus when the form opens, so Screen.PreviousControl spits out an error.I've accommodated this like so:
Code:
On Error GoTo ErrorHandler If Screen.PreviousControl.Name = "btnLast" Then [code to change record back to previous record] End If Exit Sub ErrorHandler: Exit Sub
I have combo box call "supervisor" and check box call "supervisorchange" basically want to make supervisor change to true if combo box is change which I have made code you can see below it work's
Code: Private Sub Supervisor_Change() If Me.Supervisor = "" Then Me.SupervisorChange = False Else Me.SupervisorChange = True End If End Sub
right now my problem, prob easy fix for you access experts
If the supervisor combo box is empty and user inputs a supervisor I would like the supervisor change check box not to change to true
only if the user changes it after the first input I would like the supervisor change check box to become true
After a lot of reading and consolidating VBA codes for audit trail.How My Audit Trail Works..A module was made for a function named as "Changes" .Then inserted into before update event of a form where I will do the editing of the records.Then I made a table named as Audit.Inside this table I made all the fields I needed such as:
*AuditRecordId[autonumbered] *FormName[The name of the form for editing] *Index[The record ID of the record being edited] *ControlName[The Field being edited] *DateChanged[Date change was done] *TimeChanged[Time change was done] *PriorInfo[for the old value of data being changed] *NewInfo[For the new value of data changed] *CurrentUser[The user base on log in form that was set to Global into another module] *Reason[The reason for changing for future references]
And Here is the Function Code:
Code:
Option Compare Database Option Explicit Function Changes() Dim db As Database Dim rs As Recordset Dim strSQL As String Dim strCtl As String Dim strReason As String
[code]....
This audit trail function is valid only for one(1) form, due to the limitation of
Code: Screen.ActiveForm.Controls("SUP ID").Value
where "SUP ID" is the primary key of the record being updated/Change, so if there are Five(5) tables that needs audit trail, there will be also Five(5) forms, as well as Five(5) Function Changes namely; Changes(), Changes1(),Changes2(),etc... because all the table do have their own sets of primary Key.
Is there a shortcut, in such a way that the "rs!Index" will automatically return a value, equivalent to the Primary Key/Record Id of the record being updated/change, given that there are different updating forms for each table to be updated?
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 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 have a onclick tied to a label (for decoration purposes) that when clicked it launches VBA that essentially updates a form. All that part works except it will not recognize any changed value of the field I was last in?
Just to try to explain best as I can what happens.
- Form gets opened - I change field (quantity field) - I click the Label - It reverts to pre-existing value.
if I click off of the text field first then do the onclick - it recognizes just fine.
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?
