Inserting A Record With A Date/time Field
Feb 2, 2005
Hi,
I'm trying to insert a record into an Access database from some ASP Code.
I have:
...
strSQL = "INSERT INTO tblUser (UserID, Date, Comments) VALUES " & _
(" & nID & ", #" & Now() & "#, " & "''" & strComments & "');"
conn.Execute(strSQL)
...
I'd have thought this should work but it doesn't.I just get a "Syntax error in INSERT INTO statement".
My date field, called "Date" (just in case that's a problem!) in my database has no input mask defined, it's just a basic date/time field. I've tried replacing the '#' symbols with single quotes, and doesn't work either. I haven't had any luck finding a definitive example of how to do this, even though it's trivial surely. All the similar examples I've found talk about how to update a record set with a date, or how to set the system locale, etc.
Any ideas as to what I might be doing wrong?
View Replies
ADVERTISEMENT
Oct 14, 2004
The pest again!!
Please could you tell me how to insert the time into a form?
I have managed to get the date to work using:-
Private Sub Complete_Click() ((This is a yes/no field))
Me![DateComp].Value = Date
End Sub
Private Sub OrderNo_AfterUpdate()
Me![DateOpen] = Null
If Not IsNull(Me![OrderNo]) Then
Me![DateOpen].Value = Date
End If
End Sub
However if I include my [Time] field naught happens.
My format in the field is "Short Time"
Thanks again,
Brian.
Zimbabwe.
View 2 Replies
View Related
Mar 4, 2015
Here is what I am trying to do. I have a query with 2 fields. "Time In" & "Time Out". What I would like to happen is this. Whenever a character, let's say a "t", is entered into that field I would like the current time to populate that field. Right now we are actually typing in the time. I have the fields set up as DateTime fields currently.
View 10 Replies
View Related
Feb 11, 2008
I think my question is fairly simple - fingers crossed.
I want to create a field that, whenever a record is added, it auto-adds the date & time the record was created. I'd call it Book_Date_Added, or something like that. I tried snooping around the Default Value options but couldn't figure it out.
The table exists and it already has some records, so I'd need to know how to add this field rather than how to create it when I create a table (although that would be helpful too, if it's somehow different).
I'd appreciate any help, thanks a bunch.
View 3 Replies
View Related
Dec 28, 2005
I want to update an MS Access table date field with a blank date. How do I go about it? Right now, if the "dateAskFor_Funds" text field is blank, and I try to update the "AskFor_Funds_Date" field in the database, I get an error. That is why I don't do anything.
What expression should I use in the place of
intJunk = 1
in the code caption below.
Private Sub cmdUpdateApplication_Click()
Dim todaysDate
Dim intJunk As Integer
Dim rst As ADODB.Recordset
todaysDate = Date ' MyDate contains the current system date.
Set rst = New ADODB.Recordset
rst.Open "EA_Apps_List", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
…
…
…
If IsNull(Me!dateAskFor_Funds) Or Me!dateAskFor_Funds = "" Then
intJunk = 1
Else
rst!AskFor_Funds_Date = Format(Me!dateAskFor_Funds, "Short Date")
End If
…
…
…
Set rst = Nothing
End Sub
View 1 Replies
View Related
Mar 7, 2015
I have a form, has some fields, one of them is the current date, so when the user click (save )button , which make (add new record )to the only table I have the problem that .all fields are inserted in the table , except the current date !! it is a text box ( Now() )!!
View 1 Replies
View Related
Dec 14, 2014
I have a form for entry and some fields are computed or result of a query from another table. I have a function that looks up a value from another table like so
************************************************** ********
Public Function GetTargetType() As Variant
GetTargetType = DLookup("type", "tblFormulations", "[tblFormulations!formulation]=Forms![frmNmsConsumptionEntry]![formulation]")
End Function
************************************************** ********
Which works fine when I test in the immediate window.Then I have this form event. This however does not insert this value when I am adding records using my continuous form.
************************************************** ********
Private Sub Form_BeforeInsert(Cancel As Integer)
Me!target_group = GetTargetType()
'Forms!frmNmsConsumptionEntry!target_group = GetTargetType()
'[tblNmsConsumption.target_group] = GetTargetType()
End Sub
************************************************** ********
making sure I can insert this value once retrieved.
View 7 Replies
View Related
Aug 29, 2013
I have a date/time text field on a form with the General Date format and a combo box next to it that has sequencial times as the row source (IE. 12:45 AM, 1:00 AM, 1:15 AM, 1:30 AM, ETC.) When the user chooses a time in the combo box, I want the time portion of the text box to be updated with the chosen time in the combo. I have tried a few things but cant seem to get it right.
View 4 Replies
View Related
Mar 6, 2007
Hi,
Wonder if someone can help please. I'm quite new to Access so please bear with me.
I have a data field in my database consisting of both a date and time.
I then have a form containing two fields where the user can type a 'To' and 'From' date to extract the records that they are interested in. The query behind this uses the 'Between[Enter The Date] And [Enter The Date]' coding.
The problem is that because the field contains a time it doesn't return any records when I run the query.
Can anyone offer a bit of guidance on how I could ignore the time part of the field perhaps by adapting the above.
Many thanks
Chris
View 2 Replies
View Related
Mar 19, 2014
I have a column/field named [DateTaken] which contains test dates and times in the same cell. I am needing to find those with a test time less than 2:30 pm or <14:30pm.
data looks like this:
8/22/13 4:23 PM
1/29/14 12:21 PM
1/28/14 3:27 PM
8/26/13 4:27 PM
[code]....
this is what I have come up with to extract the time component of data set so that I can then later, sort it by the time in a query.
JustTime: TimeValue([YourField])
JustTime: ("hh:mm",([DateTaken])) and or ("hh:mm",[DateTaken])
I get either invalid operator or invalid syntax errors trying both of these.
View 5 Replies
View Related
Apr 26, 2013
I have a query based on a table which has a date field. the field both in the table and the query have the time also in the date value so when I try to query on a date I get nothing if I copy the date and time from the field I will get the result for that record if I just use the date I get nothing. I have tried the format which does display just date but if you click on the field the time is also there You must be able to query for a date only and get all the records.
View 11 Replies
View Related
Feb 18, 2014
In VBA, how to compare date variable with date/time field from table, as when defined the data type in table, date/time option is the only choice for date data type even though I don't want the time portion.
View 13 Replies
View Related
Dec 11, 2006
Hi,
I am trying to compare a value in my databse produced by the date() function, short date format, to one that is exactly 24 hours after the value recorded by the date() function. If the value in the database is 24 hours prior to the current date(), I need to flag a text box a diffrent color to alert the user. I am unsure on the If statement that I will need to produce this result.
Any help would be greatly appreciated!!!!
Thanks
Mikeco555
View 3 Replies
View Related
Dec 9, 2014
How can I convert a Date/Time field into a Date field?
View 4 Replies
View Related
Jun 24, 2005
just wanna know the steps that need to be taken to auto-insert the current date and time into separate date and time fields in a table.
thanks
View 6 Replies
View Related
Nov 11, 2007
I am trying to get the current time to display in the same record as an identity number.
I am using a barcode scanner to identify the ID number. Using a program called BC Wedge it will place the ID number into a field in a table of the database. I also want it to record the current time the ID number was scanned.
I tried setting the default value of the 2nd field to Now(), however this does record the current time, but in a new record. This basically gives me a list of ID numbers from the barcode scanner and a list of recorded times, however all the times are one record out.
Is there anyway to get the recorded time to be placed in the same record as the ID number?
As far as I am aware the BC Wedge software needs to have the table in table view, although I may be wrong about this.
Hope somebody can help me with this.
View 4 Replies
View Related
Jan 31, 2008
hi all,
I have created the database with multiple tables. I've created one main table to store all records from other tables. Then I have make several forms to enter record into several tables.
What I'd like to make is that when I enter new record to any one of those other tables, then it will be automitically add to the main table also. The record no of main table will be automatically increase by itself when the new record came in.
Can anyone help me with it?
thanks.
View 8 Replies
View Related
Feb 11, 2008
How can I create a field in a query, extracting the date portion of a date/time field? The format is mm/dd/yyyy hh:mm:ss. I need just the mm/dd/yyyy so I can prompt the user for a date range, without them having to key in the date mm/dd/yyyy 00:00:00 to mm/dd/yyyy 23:59:59. Please Help
View 3 Replies
View Related
Dec 15, 2005
Hi, on a database I created with probably over one hundred fields in it I have one text box called update. What I want is if ANY of the fields are altered it then updates the "update" field with date and time. At the moment I have started altering every field with creating an event proceedure in the "on change" entry which reads as below for a change to text5 box. (text141 is the update field)
Private Sub Text5_Change()
[Text141] = Now()
End Sub
Now, although this works; as you can imagine it's very time consuming going through every single field I have puting this proceedure in place. My question.....Is there a much simpler way of making this "update" field change if any part of the record gets altered ?????
Hope this isnt confusing.
I should add that each record has about 4 tabs(pages) too, so the overall record is spread over all this too. I need something that knows that anything is altered on the record and so update the "update" field
View 6 Replies
View Related
Jan 2, 2007
Hello everyone, I'm encounter a problem trying to write a code.
EX:
Date Months_to_credit Final_result
01/31/06 4 10/01/05
02/28/06 6 09/01/05
03/31/06 8 08/01/05
Does anyone have any ideas how to to substract Months_to_credit from Date and to return a date in the Final_result field?
View 1 Replies
View Related
Aug 17, 2006
Hello buddies :D, do you have any idea how to make this work?
To select data that falls within this criteria of date range between cboDate and cboDate2 (fields on my form). The date in [tblJobDetails]![timeIn] come in this format "08/17/06 10:24 AM", but the cboDate/cboDate2 (takes in date only e.g 08/17/06) what i am after is to evaluate specific hard coded time in addition to the date entered, i.e. even tho, i haven't entered time on the cboDate/cboDate2, I want specific time hard coded where e.g If i select a date range of 08/17/06 and 08/18/06 on my cboDate and cboDate2 it should really be evaluating: 08/17/06 8:00 AM to 08/18/06 8:00 AM.
This is the criteria i curentlly have on my query in design view tha works perfect in selecting date only.
([tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Or [tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Is Null) And ([tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Or [tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Is Null)
How can I incorporate 8:00am to 8:00am into my cboDate and cboDate2. What can i do to make this happen? Your kindness will be greatly appreciated http://www.naijaryders.com/forums/images/smilies/thankyou.gif
View 10 Replies
View Related
Sep 21, 2007
Hello All...
I'm very new to Access.. so please forgive my ignorance..
Here is the scenario..
I have a table with two fields named "item" & "description" that contains 10 records total.
I have another table with 100 records with the fields "invoice #","item", "price paid","date paid","time paid".
The item fields are linked between the two, and the 100 records contain different invoices for these ten items.
I want to pull the price paid off of the latest invoice, based on the time and date... What is the easiest way to do this?
Thanks!
View 1 Replies
View Related
Oct 9, 2006
I have two tables. PreOrder and Order Tables. When our customer actually order a product, I want all information of that product to move from PreOrder table and append it to Order table. Currently, my user has to run an append query to add the record from PreOrder and add it to Order table. After that, she has to run a delete query to delete the record from PreOrder table. Since our query is setup to run by date, and order number..My user has to type the same thing twice. Is there anyone out there know the easy way? Thanks in advance..
View 1 Replies
View Related
Jul 2, 2014
automatically adding modified date/time to a record as it changes..I have a work log database for a team of people to keep track of workflow (documents in/out, stage in the workflow, owner of the record, etc.). I have a number of records that I want to automatically update a corresponding date/time field when it is changed. For example, one part of the workflow tracks a document through the approval of 5 different people. So I have a five records that are yes/no flags for approval and five records that are date/time for when it was approved. Right now my team is manually entering the five date/time stamps, but I'd like to have it automatically update when the check the "yes" box and update that field in the record.
View 8 Replies
View Related
Aug 19, 2013
I have two forms both with separate tables
(1) Register and
(2) Payments.
One of the common denominators between them is the URN which is auto-populated as it is an auto number field. My issue is that when I want to add a new record to the payments table using the forms (I can get to the payments form via the register form), I want to be able to identify the record that I am currently viewing within the register and auto populate the URN field with the same number. This is what I have done so far,
Option Compare Database
Option Explicit
Private Sub AttachPaymentDetails()
Call PerformInsert("tblFinancialBudget", "frmFinancialBudget")
End Sub
[code]....
View 5 Replies
View Related
Jun 30, 2013
I have set up a form and only want to display the date and time that will appear automatically based on when the record was created. I definitely don't know how to do this.
The format that I'm looking for is: 07/24/13 11:45:44pm
From what I can see I would need two separate fields to accomplish this. I would prefer to only use one if possible. I would like to execute the value within the new record as it is entered. That date and time from that point should never change.
I will want to use the date and time in the future for quality control.
View 3 Replies
View Related
