Date error

[Groundrush]

I'm trying to update two date fields in the code below to pick up the current date & time.

For some reason it returns 00:00:00 instead of 19/10/2009 08:42:14

The fields in question are:
"SCDateRaised" I've tried SCDateRaised = Now()
and
"SCDateOfReason" also tried SCDateOfReason = Now()

If DCount("*", "qrySubbyFlash", "[TaskID] = " & Me.TaskID) > 0 Then
                strSql = "INSERT INTO [tblSubConOrders] ( SCLINKTask, SCOrderRaisedBy, SCDateRaised, SCSubContractor, SCFurtherInfo, SCCostOfOrder, SCReasonForIssue, SCDateOfReason, SCRPONo  ) " & _
                    "SELECT " & lngID & " As NewID, SCOrderRaisedBy,SCDateRaised = Now(), SCSubContractor, SCFurtherInfo, SCCostOfOrder, SCReasonForIssue, SCDateOfReason = Now(), SCRPONo  " & _
                    "FROM [tblSubConOrders] WHERE SCLINKTask = " & Me.TaskID & ";"

What am I doing wrong?

Thanks

 

[Brianwarnock]

I've never done this but I'm going to guess that the system will not pickup the Now() function because it is inside the " "

Brian

 

[Groundrush]

I've never done this but I'm going to guess that the system will not pickup the Now() function because it is inside the " "

Brian

There must be a way round this.

I'm duplicatiing records but I don't want the dates to be copied as I need the current date & time instead.

 

[namliam]

" For some reason it returns 00:00:00 "

00:00:00 is a time format, this leads me to believe that
1) Your field is setup as a short date
2) Your field has a format set on it to display only time (HH:NN:SS) or something simular

Also big SQL like yours is best reprecented more readable like so:

If DCount("*", "qrySubbyFlash", "[TaskID] = " & Me.TaskID) > 0 Then
    strSql = ""
    strSql = "INSERT INTO [tblSubConOrders] ( SCLINKTask, SCOrderRaisedBy, SCDateRaised, SCSubContractor, SCFurtherInfo, SCCostOfOrder, SCReasonForIssue, SCDateOfReason, SCRPONo  ) "
    strSql = strSql & " SELECT " & lngID & " As NewID "
    strSql = strSql & " , SCOrderRaisedBy "
    strSql = strSql & " , SCDateRaised = Now() "
    strSql = strSql & " , SCSubContractor "
    strSql = strSql & " , SCFurtherInfo "
    strSql = strSql & " , SCCostOfOrder "
    strSql = strSql & " , SCReasonForIssue "
    strSql = strSql & " , SCDateOfReason = Now() "
    strSql = strSql & " , SCRPONo "
    strSql = strSql & " FROM [tblSubConOrders] "
    strSql = strSql & " WHERE SCLINKTask = " & Me.TaskID & ";"

More readable to both you, future you and future generations (or total strangers on a forum)

 

[Brianwarnock]

Namliam's response suggests that I am wrong, but I can't buy his explanations either.
I would have expected to have to code either
something like
SCDateRaised = " & Now()

or having defined and set dtedateraised =Now() earlier

then & dtedateraised & " as SCDateRaised "

but like I said I've no experience of this.

Brian

 

[DCrake]

    strSql = strSql & " , SCDateOfReason = Now() "

can be better interpreted as

    strSql = strSql & " , SCDateOfReason = #" & Now() & "# "

David

 

[namliam]

Now() is access native you can use
strSql = strSql & " , SCDateOfReason = Now() "
or
strSql = strSql & " , SCDateOfReason = #" & Now() & "# "

Make sure to circumvent the "date issues" regarding date formatting/regional setting though... Might be better to use...
strSql = strSql & " , SCDateOfReason = #" & format( Now(), "mm/dd/yyyy HH:NN:SS") & "# "

Both are perfectly valid... The problem is not in the SQL 100% sure, the problem is in the field and/or format of the field/display/query etc... someplace else... 100% sure of this

 

[Groundrush]

Now() is access native you can use
strSql = strSql & " , SCDateOfReason = Now() "
or
strSql = strSql & " , SCDateOfReason = #" & Now() & "# "

Make sure to circumvent the "date issues" regarding date formatting/regional setting though... Might be better to use...
strSql = strSql & " , SCDateOfReason = #" & format( Now(), "mm/dd/yyyy HH:NN:SS") & "# "

Both are perfectly valid... The problem is not in the SQL 100% sure, the problem is in the field and/or format of the field/display/query etc... someplace else... 100% sure of this

OK I have taken your points on board & altered my code to

             If DCount("*", "qrySubbyFlash", "[TaskID] = " & Me.TaskID) > 0 Then
    strSql = ""
    strSql = "INSERT INTO [tblSubConOrders] ( SCLINKTask, SCOrderRaisedBy, SCDateRaised, SCSubContractor, SCFurtherInfo, SCCostOfOrder, SCReasonForIssue, SCDateOfReason, SCRPONo  ) "
    strSql = strSql & " SELECT " & lngID & " As NewID "
    strSql = strSql & " , SCOrderRaisedBy "
   'strSql = strSql & " , SCDateRaised = Now() "
   'strSql = strSql & " , SCDateRaised = #" & Now() & "# "
    strSql = strSql & " , SCDateRaised = #" & Format(Now(), "mm/dd/yyyy HH:NN:SS") & "# "
    strSql = strSql & " , SCSubContractor "
    strSql = strSql & " , SCFurtherInfo "
    strSql = strSql & " , SCCostOfOrder "
    strSql = strSql & " , SCReasonForIssue "
   'strSql = strSql & " , SCDateOfReason = Now() "
   'strSql = strSql & " , SCDateOfReason = #" & Now() & "# "
    strSql = strSql & " , SCDateOfReason = #" & Format(Now(), "mm/dd/yyyy HH:NN:SS") & "# "
    strSql = strSql & " , SCRPONo "
    strSql = strSql & " FROM [tblSubConOrders] "
    strSql = strSql & " WHERE SCLINKTask = " & Me.TaskID & ";"

But it still returns 00:00:00

The data type for the two fields are set as general date & is not formatted in anyway (just left blank)


What is the difference between HH:NN:SS and HH:MM:SS?

 

[DCrake]

If you do a Debug.Print StrSql after the final construction what does it return?

David

 

[namliam]

MM = months
NN = Minutes

Only thing is when you use HH:MM:SS access has been made smart enough to "understand" you mean NN, not MM. Though this is cause for MANY errors!

strSql = strSql & " , SCDateOfReason = #" & Format(Now(), "mm/dd/yyyy HH:NN:SS") & "# "

CANNOT go wrong, impossible! There has to be SOME formatting some limitting some something going on somewhere The SQL IS NOT the problem

There is either some Formatting issue or your form is bound and you dont realize it is where the date's you want added directly into the form, while the SQL is putting it into the table, never showing up on the form.

Let me repeat
The SQL IS NOT the problem

 

[Groundrush]

MM = months
NN = Minutes

Only thing is when you use HH:MM:SS access has been made smart enough to "understand" you mean NN, not MM. Though this is cause for MANY errors!

strSql = strSql & " , SCDateOfReason = #" & Format(Now(), "mm/dd/yyyy HH:NN:SS") & "# "

CANNOT go wrong, impossible! There has to be SOME formatting some limitting some something going on somewhere The SQL IS NOT the problem

There is either some Formatting issue or your form is bound and you dont realize it is where the date's you want added directly into the form, while the SQL is putting it into the table, never showing up on the form.

Let me repeat
The SQL IS NOT the problem

I understand

I will recheck my forms etc

thanks

 

[Groundrush]

There is no formatting on my table or forms and the default value on those fields on the form are set as =Now()
(must point out that there are two different forms here that may confuse things)

There is a form that creates a record and the date fields are set as = Now()

The second form only runs the code to copy data from another table that is linked to the record


I have no problems when a record is created via the form because the date & time fields work OK it's only when I run the code to duplicate a previous record that I get 00:00:00


Another weird thing is that if I try to set it as date only using = Date ()
it returns 29/12/1899 instead of 19-10-2009

 

[Brianwarnock]

Thats a negative date ? -1 I think.

Brian

 

[DCrake]

It has just hit me that you are appending records via this sql and one of the fields is a date/time stamp when the field hit the table.

Why not go to the date/time field in question and set the default value to Now() and remove the field from your sql. This will cut out the problem you have now.

David

 

[Groundrush]

It has just hit me that you are appending records via this sql and one of the fields is a date/time stamp when the field hit the table.

Why not go to the date/time field in question and set the default value to Now() and remove the field from your sql. This will cut out the problem you have now.

David

I can't because the code is being run from another form that does not have those fields. The form is actually based on another table but is linked to the table that I want to duplicate.

The full code duplicates the record displayed on the main form & then checks to see if there are any related records (the piece of code we are working on)
if there are then it duplicates the linked records expect for the dates.

I did originally copy the date values across but they would pre date the new main record.

 

[namliam]

There is either some Formatting issue or your form is bound and you dont realize it is where the date's you want added directly into the form, while the SQL is putting it into the table, never showing up on the form.

From your wording I am guessing above is the problem, your form is creating the dup, where you dont have date/now in.
While the SQL Does.... Now your expecting the Now/Date to show up which doesnt cause it isnt there...

 

[DCrake]

INSERT INTO ....
    strSql = strSql & " , SCDateRaised = #" & Format(Now(), "mm/dd/yyyy HH:NN:SS") & "# "
     strSql = strSql & " , SCDateOfReason = #" & Format(Now(), "mm/dd/yyyy HH:NN:SS") & "# "

I appreciate what you are saying, but your sql is inserting the current date and time in the newly appended record, is it not? So no matter what the where condition is, where it is called from, etc, whenever you use this code the curernt date/time is appended. As the field names suggests "Date Raised" and "Date of Reason" are akin to new records. If you were using an Select ... Update To sql then I would agree with you. So I don't agree with with your agrument. Unless of course I am missing the obvious.

David

 

[namliam]

So I don't agree with with your agrument. Unless of course I am missing the obvious.

Is that refering to me? I agree with you though, sticking in the currentdate/time seems strange on these fields unless your creating a new record.

But this is business logic of the OP which I have learned not to go debating to much... Business and logic rarely go hand in hand

 

[DCrake]

Mailman,

No not aimed at you but the OP.

David

 

[Groundrush]

Is that refering to me? I agree with you though, sticking in the currentdate/time seems strange on these fields unless your creating a new record.

But this is business logic of the OP which I have learned not to go debating to much... Business and logic rarely go hand in hand

INSERT INTO ....
    strSql = strSql & " , SCDateRaised = #" & Format(Now(), "mm/dd/yyyy HH:NN:SS") & "# "
     strSql = strSql & " , SCDateOfReason = #" & Format(Now(), "mm/dd/yyyy HH:NN:SS") & "# "

I appreciate what you are saying, but your sql is inserting the current date and time in the newly appended record, is it not? So no matter what the where condition is, where it is called from, etc, whenever you use this code the curernt date/time is appended. As the field names suggests "Date Raised" and "Date of Reason" are akin to new records. If you were using an Select ... Update To sql then I would agree with you. So I don't agree with with your agrument. Unless of course I am missing the obvious.

David

Yes i am creating a new record based on another, just the dates are different.


I have no problems when I duplicate the main record using the code below

It also has a date field that is set to Now()

'Duplicate the main record: add to form's clone.
        With Me.RecordsetClone
            .AddNew
                !CallerID = Me.cboCaller
                !BuildingID = Me.cboBuilding
                !ContractNo = Me.cboContract
                !Category = Me.cboTypeOfWork
                !Trade = Me.cboTrade
                !JobLocation = Me.cboLocation
                [COLOR="Magenta"]!DateReported = Now()[/COLOR]
                !CallerID = Me.cboCaller
                !CostCode = Me.cboCostCode
                !JobDetails = Me.txtJobDetails
                !FurtherDetails = Me.memoFurtherDetails
                !TaskPriority = Me.cboPriority
                !TaskStatus = "1"
                !LOC = Me.cboLevelOfCompletion
                !EstimatedCost = Me.txtEstimatedCost
                !FinancialYear = Me.FinancialYear
                !AuditTrail = "Duplicate Record created from Job No " & Me.JobNo & " on " & Now & " by " & fOSUserName() & ";"