Link to home
Start Free TrialLog in
Avatar of Hiro 714
Hiro 714

asked on

How to create a query to generate a record.

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.
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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

Avatar of Hiro 714
Hiro 714


Thank you! Would you edit my file? I would like to add a record to “test data” table
There are 100 users will select test a or b or c, then input result. I need a result and modified date.
Sorry, don't have time to update the file, did you look at what John gave you?
Again, as we can't read your mind: Add a record with what data, and are these data to be input by the user?