Solved Date field, INSERT INTO

[brucemc777]

Stick a fork in me, i'm done.

ALL day i have been trying to figure out how to make this work before pestering you folks. The actual SQL statement is much longer, but all of it works fine until i stick that last date field into it. I keep getting a syntax error and i don't know how many more combinations of # and " i can come up with, even though the same combination as i thought i learned works fine in another DATE/TIME field in the middle of same actual string. And before anyone asks (though i seriously doubt it would throw a syntax error) because i have seen a lot of squirrely things with VBA in working with Excel, i verified that the TimeStamp field was Date/TIME (Actually was DATE/TIME EXTENDED at first, but jic i switched it to plain DATE/TIME), and i verified dtNow was type 7...

DoCmd.RunSQL "INSERT INTO tblAddress (IDCustomer, TimeStamp) VALUES(" & lIDCustomer & ", #" & dtNow & "#);"

What am i doing wrong here?

Thank you so very much!!!

 

[theDBguy]

What's the value in dtNow? How are you populating it?

 

[brucemc777]

I first was assigning it Now(), then tried Date(), then tried #11/30/2024#. Kind of have run out of options!

 

[Gasman]

Put it all into a string variable, debug.print until you get it correct, then use that in the command.
If you still cannot see the error copy and paste the debug output back here.

 

[theDBguy]

I first was assigning it Now(), then tried Date(), then tried #11/30/2024#. Kind of have run out of options!

If you want to use Now(), try it this way.

INSERT INTO TableName (DateField) VALUES (Now())

 

[plog]

There's no space after VALUES and that opening parenthesis

 

[brucemc777]

Heck, because of how extensive and time consuming it would have been (the code is in a form module and the form takes about 3 other forms and a fair amount of input to get to the point of running the procedure), i've been running it over and over in the Immediate window, each time adding another field, then checking the table to verify functionality, then deleting that record, then adding a field, then running ...

Since it ran and added the record perfectly fine for the ten fields prior to this date field, i felt pretty good in my testing methodology, but you folks are the experts, i just make it up as i go along...

You've helped me before and been quite accurate; if you still want me to do so given this new information, just let me know and i will trust you have good reason! I'm sure i could easily design a regular module to run it anyways

 

[brucemc777]

There's no space after VALUES and that opening parenthesis

Perhaps (Well, certainly) not, but when i run

CurrentDb.Execute "INSERT INTO tblAddress (IDCustomer) VALUES(" & IDCustomer & ");"

that does what it is supposed to do without error!

And yes, i know that one does say "CurrentDb.Execute, but i did switch back and verified with DoCmd.RunSQL (I switched before to DoCmd for more clarity on errors).

 

[Gasman]

Using dtNow as it is, means it is going to be numeric, doesn't it?, so # not required?

 

[plog]

Ok, second guess--Timestamp is a reserved word. Using it as a name requires you escape it in code via brackets:

[Timestamp]

Learn about Access reserved words and symbols - Microsoft Support

support.microsoft.com

 

[brucemc777]

I tested it using VarType() It comes back as a 7...

I tried to provide the MS table to evidence a 7 is a date, but this system is suggesting my post was too spam like...(???)

 

[Gasman]

Show us the debug.print

 

[brucemc777]

Almost anything i write is being kicked back as resembling SPAM too much!

 

[theDBguy]

Almost anything i write is being kicked back as resembling SPAM too much!

Are you available for a screen-sharing session?

 

[Gasman]

A debug.print within code tags should not do that.

 

[brucemc777]

@ plog: I suspect progress. We've gone from a generic syntax error to

@ theDBguy: You are welcome to take full control, but i must warn you i am very hack in what i have been doing. The project is close to final testing, the actual full SQL got blown away about ten minutes ago when Access for unknown reason chose to restart, and though i am certain i can "quickly" rewrite the full statement, this is a matter where "quickly" will involve several minutes of my working through it. Then comes the fact that as mentioned, my testing was done through additive regenerations in the immediate window, which proved valid as i could see, but there's always a fly (or moth) just waiting to get in the way. I will get on it right away so i can supply Gasman that for which is requested, and i can also let you know at the time, and if you still do have the opportunity, you are welcome to go for it. Just don't access the plans of my fellow Martians to invade your planet...

@Gasman - On it, but as cited in paragraph above, it will take a little time-

 

[brucemc777]

Ummmmmmmmmmmmmmmmmm, hello everybody, and so much thank you; i can not thank all of you enough for coming to my aid.

In my last test putting TIMESTAMP in brackets, i saw where i screwed up the line of SQL elsewhere. Although i still have to recreate the entire line, the test segment finally has worked the way it should.

I so (sooooooooooooooooo) appreciate everyone's help.

Those Reserved Words are like walking through a minefield (albeit rather benign). This one took all day to navigate-

So many thanks!

 

[theDBguy]

Ummmmmmmmmmmmmmmmmm, hello everybody, and so much thank you; i can not thank all of you enough for coming to my aid.

In my last test putting TIMESTAMP in brackets, i saw where i screwed up the line of SQL elsewhere. Although i still have to recreate the entire line, the test segment finally has worked the way it should.

I so (sooooooooooooooooo) appreciate everyone's help.

Those Reserved Words are like walking through a minefield (albeit rather benign). This one took all day to navigate-

So many thanks!

Glad to hear you got it sorted out. Good luck with your project.

 

[Gasman]

Ummmmmmmmmmmmmmmmmm, hello everybody, and so much thank you; i can not thank all of you enough for coming to my aid.

In my last test putting TIMESTAMP in brackets, i saw where i screwed up the line of SQL elsewhere. Although i still have to recreate the entire line, the test segment finally has worked the way it should.

I so (sooooooooooooooooo) appreciate everyone's help.

Those Reserved Words are like walking through a minefield (albeit rather benign). This one took all day to navigate-

So many thanks!

THAT is why I give that advice I posted.
It will find stupid errors like that, then you just use the variable in the command and either comment or delete the debug.print.

 

[Gasman]

On it, but as cited in paragraph above, it will take a little time-

Not really, you just Dim a string variable and swap the command for strSQL =
Then add a line with DoCmd.RunSQL strSQL

Also it means you could do it all in one run, not incrementally like you have been doing.