How to create a query to generate a record.

HIROYUKI TAMURA
HIROYUKI TAMURA used Ask the Experts™
on
I have a query trying to create a record, but it doesn't work.
another question is, how to set a modified date field.
Please take a look on sample file.
Database2.accdb
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software & Systems Engineer
Commented:
Take a look at my attachment
Database2--1-.accdb
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You need to tell which record you wish to create - in which table and with what data, and if this data are to be input by the user.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Well a simple insert query would look like the following when run from the SQL view in Access
INSERT INTO yourTableName (Field2integer, Field3Text, Field4Date)
Values (1, "test", #11/21/2019#)

Open in new window

Note that I did not include Field1, which in my case is always an Autonumber and gets created automatically.

if you want to do this from code, you have to make a couple of minor modifications, replacing the quotes with single quotes and wrapping the entire string in quotes, like this:
Dim strSQL as string
strSQL = "INSERT INTO yourTableName (Field2integer, Field3Text, Field4Date) " _
       & "Values (1, 'test', #11/21/2019#)"
Currentdb.Execute strsql, dbfailonerror

Open in new window

You can also insert NULLs into fields which accept them.
INSERT INTO yourTableName (Field2integer, Field3Text, Field4Date, Field5AllowsNulls)
Values (1, "test", #11/21/2019#, NULL)

Open in new window

HTH
Dale
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
Thank you! Would you edit my file? I would like to add a record to “test data” table

Author

Commented:
There are 100 users will select test a or b or c, then input result. I need a result and modified date.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Sorry, don't have time to update the file, did you look at what John gave you?
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Again, as we can't read your mind: Add a record with what data, and are these data to be input by the user?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial