Date error

[namliam]

Yes but the clone is not the form's recordset?
And a query is not the clone nore the form's recordset... it is the table itself...

 

[Groundrush]

Mailman,

No not aimed at you but the OP.

David

Would that be me, operator or operating system

 

[Brianwarnock]

Dim dtea As Date
dtea = Now()
strsql = ""
strsql = "INSERT INTO [tablex] "
MsgBox strsql
strsql = strsql & " SELECT " & 1 & " As NewID "
MsgBox strsql
strsql = strsql & " ," & dtea & " as SCDateRaised"
MsgBox strsql
strsql = strsql & " , SCDateOfReason = Now() "
MsgBox strsql
strsql = strsql & ";"

MsgBox strsql
Stop

When I run the above get

"INSERT INTO [tablex] SELECT 1 As NewID ,19/10/2009 15:08:37 as SCDateRaised , SCDateOfReason = Now() ;"

Does this mean the Now() is not being acted upon or am I being naivly ignorant.

BTW I also cannot see why the fields in the table cannot default to Now()

Brian

 

[Groundrush]

Yes but the clone is not the form's recordset?
And a query is not the clone nore the form's recordset... it is the table itself...

Yes I know that

I posted that code to point out that I was creating a new record in reponce to your comment

I agree with you though, sticking in the currentdate/time seems strange on these fields unless your creating a new record.(

Anyone have any suggestions on how to solve this?

Thanks

 

[namliam]

When I run the above get

"INSERT INTO [tablex] SELECT 1 As NewID ,19/10/2009 15:08:37 as SCDateRaised , SCDateOfReason = Now() ;"

Does this mean the Now() is not being acted upon or am I being naivly ignorant.

Brian, and how does SQL look if you make a query using NOW() in the designer??

It looks just exactly the same doesnt it?? Yes it does...

Now() is then executed upon execution of the SQL

As to solving it, the SQL isnt the problem!!! It doesnt need solving...

If the Recordset solution works, why do you need a query to do it?? The recordset does the same thing... ??? Why look for another solution?

 

[Groundrush]

If the Recordset solution works, why do you need a query to do it?? The recordset does the same thing... ??? Why look for another solution?

Because the record is from anther table related to the recordset on the main form

I am duplicating two records

the main record (recordset) then a linked record using the query.

the original code came from this site http://allenbrowne.com/ser-57.html

The difference is that the example gets the data from one table but mine duplicates data from two
And I don't have a subform

 

[Brianwarnock]

You're correct, I'll retire from this before making a bigger fool of myself, I'm confusing strsql syntax and DLookup type, I've been retired too long

Brian

 

[Groundrush]

You're correct, I'll retire from this before making a bigger fool of myself, I'm confusing strsql syntax and DLookup type, I've been retired too long

Brian

You have been retired too long & I should retire now

 

[namliam]

[rant]
OK one more time, the query is not the problem... THE QUERY IS NOT THE PROBLEM

Yes, the query is OK, it is GOOD, there isnt a problem with it!!

The problem is / has to be someplace else, without further looking into the DB itself I dont think I can see/say anything but please examine closely your column formats and types as well as your form(s) and your expectations...
Somewhere in the chain there is a problem... but.... IT IS NOT THE QUERY and it is not the NOW()

[/rant]

 

[Groundrush]

[rant]
OK one more time, the query is not the problem... THE QUERY IS NOT THE PROBLEM

Yes, the query is OK, it is GOOD, there isnt a problem with it!!

The problem is / has to be someplace else, without further looking into the DB itself I dont think I can see/say anything but please examine closely your column formats and types as well as your form(s) and your expectations...
Somewhere in the chain there is a problem... but.... IT IS NOT THE QUERY and it is not the NOW()

[/rant]

Namliam, I see you still think I didn't get it the first time round you mentioned that.

I know that there must be a problem elsewhere, that's why I have been looking at the tables, forms like you suggested a few threads back

I only responded to your replies trying to explain the situation better

Thanks for your help anyway
The code looks much tidier now & easier to follow

 

[Brianwarnock]

I think that Namliam is upset with me, I did admit that I was getting my wires crossed and that he was correct, but maybe I didn't grovel enough, tuff.

I still don't understand why you cannot put defaults in the table, no! no! ignore me, I'll read the result if its solved.

Brian

 

[Groundrush]

I think that Namliam is upset with me, I did admit that I was getting my wires crossed and that he was correct, but maybe I didn't grovel enough, tuff.

I still don't understand why you cannot put defaults in the table, no! no! ignore me, I'll read the result if its solved.

Brian

[Brian'sEyesOnly]I have used defaults in the table but after running the code via a cmd button from another form it only updates 00:00:00 instead of the current date & time. as Namian states it must be a problem elsewhere but I can't find it yet.
[/Brian'sEyesOnly]

 

[Brianwarnock]

LOL Its best to retain your sense of humour, if the format is Generaldate or dd/mm/yyyy HH:NN:SS how can it not have a date, even if only 30/12/1899 ??

Brian

 

[Brianwarnock]

just tried a little test, and it suggests that your fields are being set to 0, which for a General date field results in 00:00:00 or any field with a time element, if there is only a date ie shortdate then it returns 30/12/1899.

Brian

 

[Groundrush]

I've got it to work now

Rather than updating directly from my table I created a query instead & used the Now() function to replace the previous dates

Thanks for all the help