I need to be able to update all the date records in a table using an update query. Any ideas of what i have to put in the 'update to' box to get all the dates to change from dd/mm/2005 to be dd/mm/2006
cheers for any help
I have a table which contains dates in the format (mm/dd/yyyy) as well as other information, I am trying to get an ASP website to update the date with values chosen by the user. The problem is that when using the UPDATE command it returns an error, saying the syntax is wrong. Here is my SQL statement and the error message it produces, if anyone has got any ideas, please let me know -:
"UPDATE Users SET Name = '"& UpName &"', DOB = #"& AddMonth &"/"& AddDay &"/"& AddYear &"# WHERE ID = "& ID &" "
The error message I get in return is -:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression '#//#'.
Thanks in Advance
Andy
I was wondering if anyone knows any way to have the date/time field in my database automaticaly update itself to the current date when I change part of the data in that same line.
For instance, if I change the entry for last name, is there anyway to have the date field automaticaly change to the current date without me entering it manualy? Thanks for your time.
How do I set up a date field so that it changes to the current date whenever any other field in the record is changed?
View 6 Replies View RelatedI have a form named 'Race'. It uses table 'Tasks'. I want to click a command button and have the 'Completed Date' field updated to the current date.
Can anyone tell me how to code the command button?
Thanks!
Sunny
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
I want to update my date field in my database everytime when the record is changed. I do not want to do this by using a macro and I cannot make it word by using the before update function. In the before update I have tried the following without success (nothing happens):
Private Sub Modified_Date_BeforeUpdate(Cancel As Integer)
Me![Modified Date] = Now()
End Sub
I'm having a problem trying to update a date field from a button.
I have a form called Add_New_Domain which runs from a table called Domain_Names.
Within the form there is a field called Expiry_Date which tells me when the domain is set to expire. When I first create an entry I manually enter a date in here.
However, every year when the domain renews I would like to increase this date by exactly a year by clicking a button which is also on the form.
I am trying to do an auto time stamp in a data access page without having to go to new record. What I am doing is farming out parts of a record to different departments, and I want to time stamp each time each department performs their query, but I want to be able to display the data for the department, and store the timestamp in the original table.
Everything is stored under the table [Appointments] and different departments enter certain things. For instance, Reception gives the customer a ticket # which they enter into the database based on the query for the customer appointment. On the data-access page, I want them to be able to see their check in time. I am able to do this with an expression, but I can't store it in the field. Here's what I have:
Expr1:Now() (this is in the query)
In the data access page I display Expr1 with no problem. How do I get the field [Reception time stamp] to equal Expr1? When I try to do it in the query, it treats it as another expression and doesn't store it in the field (Reception time stamp = Expr1)
Is there a way to assign a field the value of an expression in queries or data access pages?
In other words, for the field I have named "Reception Time Stamp" is their anyway to plug in the Now() value when I do my query? Not search for Now() but have it show Now() when I use the query in a data access page without having to create new record. I know how to do it when you time stamp it with new record, but how can I do it with a query that pulls up an existing record that more info is being updated on it.
Table Demographics:
Account Number (Primary)
Name
Address
SSN
DOB
Table Appointments:
Appointment Number (Primary Autonumber)
Appointment Date
Appointment Time
Account Number (Relationship tied to Demographics)
Reception Ticket #
Reception Timestamp
Cashier Amount Collected
Cashier Timestamp
Relationship is One to Many (One Demographic to Many Appointments)
Everybody will be entering data on a data access page using internet explorer, which makes this more tricky, as it would be easy to do using forms. So here's how it works. Appointments enters customer information, appt date, appt time, and account number.
Reception pulls up by date and name(queries) the appointment, and then enters a ticket number (Like taking a number to be waited upon) After they enter this and write this ticket number, I want an auto-time stamp to be saved under the appointment table for reception time stamp.
Then, when their number gets called, the cashier pulls up the patient by ticket number(queries) and collects money from the customer. After they write the amount they collected, I need an auto-time stamp.
The reason I need to auto time stamp is so I can calculate time difference from the customer walking in the door and going out the door. The main reason this has proven difficult is because I am doing all data entry on data access pages, and everybody will be using internet explorer. Another reason is because almost all info is being saved under appointments, and each department only enters a couple of things towards completing the appointment record.
I am currently updating an old Db which rosters staff. One of the enhancements will allow the user to automatically allocate staff details to a Roster after a given date. i.e. Roster date 20th Feb 15, repeat for 3 days. 21st 22nd 23rd Feb 15. I have created a form with a medium date field and then a Combo box with drop down values of 1 thru 7 days. I want to link the combo box value to the Date box.
View 3 Replies View RelatedI have a form that has combo boxes and text fields (as well as sub forms). There is also a button linked to some code that says'
Private Sub cmdQuote_Click()
'Creates quote date and prints quote
Me.QuoteDate = Now()
Me.cbAgentID.Requery
DoCmd.OpenReport "Quote", acViewPreview, , "BookingID = " & Me.BookingID
End Sub
When the button is pressed the QuoteDate field (it is bound) should be be populated, but unfortunately it is not. I have played with refresh and requery but cannot derive a solution.
I have a form with a chart , 2 textboxes for start date and end date and a button to filter the data( filter the data by date range) How do i continue from that? The chart isnt updating.
View 5 Replies View RelatedI can't seem to get my date field to update to today when I add information to another field.
My table has a lookup field, date field, text field, text field, text field.
I have set the date field to default to today for each new record.
I work with this table as a subform.
I would like to have the date field update to the date I go back to the record and update the first text field.
I have tried using after update, and an if - then statement, but nothing seems to work.
I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.
I have tried
Code:
Between [StartDate:] And [EndDate:]
And
Code:
Between [StartDate:] & "00:00" And [EndDate:] & "23:59"
Neither of which work ....
I'm trying to get my "IncidentDate" field to autopopulate two other date fields to a few days from the "IncidentDate". The other two date fields are "ContainDueDate" and "RootDueDate". I'm trying to accomplish this on my "Test CAP Form"
I tried using the following in the BeforeUpdate of "ContainDueDate" and received a complier error: expected =
Code : DateAdd(d,2,[IncidentDate])
so I removed the parenthesis and nothing happened
Code : DateAdd d,2,[IncidentDate]
I even tried redoing it in the AfterUpdate of "IncidentDate" and nothing happened either
Code : DateAdd d,2,[ContainDueDate]
I'm not sure if I'm even using the right function to get what I want.
Can someone tell me how to get year to date totals, month to date totals, week to dates in a query? I need to get all three for three different fields.
I was not able to get the totals with the formulas given. I received the totals for each day instead. Are there any other suggestions? I am trying to different formulas, but they are not working either. I did try doing different queries with the formulas to see if that would work.
I am creating an online post, similar to a guestbook. When a user submits an entry, I am storing the date and time in a column titled "Timestamp", which is formatted 8/4/2005 9:16:58 ("General Date"). I am running the table through a query which is then posted on the web.
What I would like to do is this: I would like to use Timestamp to display the date that the entry was submitted, but not the time (basically, "Short Date"). Can I exclude the time using a query without changing the actual data stored in each record of the table? I understand that the time stored is completely different than the time displayed, even in the table. How can I do this?
I have a form with a combo box. If the supplier(s) name is not in the combo box, it asks do you want to add that supplier address? If yes, a popup form displays, where the name and address are entered. THis updates the table. But the problem is the combobox doesn't refresh so the user isn't aware that it has updated.
I've tried updating the form, but that results in the question being asked again, do you want to add that supplier address.
Here is my code
Dim intNewCategory As Integer, intTruncateName As Integer, strTitle As String, intMsgDialog As Integer
' Display message box asking if user wants to add a new category.
strTitle = "Shipper Database"
intMsgDialog = vbYesNo + vbQuestion + vbDefaultButton1
intNewCategory = MsgBox("Do you want to add this Company and Address to the List?", intMsgDialog, strTitle)
If intNewCategory = vbYes Then
' Remove new name from CategoryID combo box so
' control can be requeried when user returns to form.
'DoCmd.RunCommand acCmdUndo
' Display message box and adjust length of value entered in
' CategoryID combo box.
strTitle = "Name Too Long"
intMsgDialog = vbOKOnly + vbExclamation
If Len(NewData) > 50 Then
intTruncateName = MsgBox("Name of the Supplier can be no longer than " _
& "50 characters. The name you entered will be truncated.", _
intMsgDialog, strTitle)
NewData = Left(NewData, 50)
End If
Hi all
I have a curly problem
A sent a mate a DB a while ago and I want to update it for him... so I need to send him something that will automatically update the forms, queries etc... progamatically i.e without him having to do anything to it.
Ideally, I'd like to front end/back end the DB. But server infrastructure doesn't allow for this. 2 networks are operating and they don't talk cleanly. Essentially, a FE/BE DB will work on one of the networks but not the other. Therefore, the DB is one whole DB so that BE tables don't need refreshing.
The way I see it... I have 2 options. Or maybe there are other options??
1. Export forms, queries etc... to the old DB or
2. Import the data tables into the new one.
At this stage I prefer option 2.
I have some idea how do this this but would appreciate some advice. Ideally, I'd like the user to click an update button to import tables, delete the old DB and transfer the new DB into the appropriate folder (and maybe rename the DB if it needs it??)
Any help appreciated.
I want to make an update code without referring to the form
My update query reads in is sql as follows
Dim SQL As String
StrSQL = "UPDATE products SET products.new = [products].[grossprice]-[products].[grossprice]*28/100"
I want to rewrite it in the following way :
Dim Diff As String
Diff = [products].[grossprice] - [products].[grossprice]
SQL = "UPDATE products SET products.new = diff*28/100 "
However this effort fails since i have to refer to a form.Can i build a code without referring to a form?
Hi,
I have a database that has been in use for sometime but it needs some amendments.
I have burnt the db to cd and plan to make the updates and then take it back and import the tables from the one in use to the newly updated database.
I remember trying this before and it wouldn't let me import the tables as it said they had relationships.
Surely there is an easy way to do this? I don't have to delete the relationships, import the tables and then re-do the relationships?
Many thanks,
Housey
I have a database with details about employees in it.
There is a field with yearly salary in it and another with an ID number. Recently these slaaries have been increased by a non-standard amount. I have another table with the emplyee ID number and their new salary. I need to update the slaary figure in the first table with the salary figure in the second table. Is there anyway of doing this?
Any help would be most appreciated
Nick:)
Hi there people...i'm doing a databae project for A2 level coursework and have encountered a problem..please help me if you can.
I have a table of names. Also another table (actions) which contains several fields, but 2 of them are names, (1 being Proposer, and other being Actionee), are obtained from the names table.
However, when altering for instance the spelling of one of the names in the names table it does not update the spelling in the actions table. i have tried using the relationship-update option but this doesn't work because the names appear more than once in the actions table. if it were to appear just the once in the actions table then i wouldn't have any problem. but it is a requirement that the 2 names fields are in the actions table.
Any ideas of how to solve this?
can someone tell me how to make a form's data change when related data is changed in another form. E.g. If I change the price of a product in 'products' to £3.00, how do I make it automatically show that it is £3.00 in 'orders' just by typing in the product code?
View 1 Replies View Relatedhi everyone,
I have a "subformB" wich is inside a "MainForm". When "MainForm" loads up (and therefore subformB loads as well), "subformB" does not show the results it is supposed to show until I click on it. It doesn't update itself 'till I click on it. Can anyone give me a hand on this?
Thank you very much
I have used a make table query to create a table with text values. I need to convert these values to numerical data, for instance "Male" should become 1 and "Female" should become 0. I tried doing this in an update query, but had to use 2 separate queries. Don't know if a macro would work to update a table (there are no forms in this DB, it's all data fields). Same with code, I usually do this with forms. How can I update the raw data, which in some cases has multiple ifs, into the correct format? Thanks!
View 4 Replies View Related