Insert SQL - Data queried by textbox on form (1 Viewer)

Malcolm17

Member
Local time
Today, 13:16
Joined
Jun 11, 2018
Messages
107
I am looking to copy data to a temp table to compile a report. Users will select the date range and the criteria to use.

I am looking for the user to select a criteria via a textbox in a form and to press print so the report is created. (Eg: if they put ToDoList in to the textbox then it will use the ToDoList data from the table for the report.)

I cannot work out how to get my SQL command to append the data from the table rather then the value from the textbox. My SQL line is:

DoCmd.RunSQL "INSERT INTO CustomRepData (Text1) SELECT Forms![Configuration]![Text1Field] FROM Diary WHERE TheDate = forms.Configuration.TextStartDate"

Text1Field is the name of the textbox on the form Configuration.
 

June7

AWF VIP
Local time
Today, 05:16
Joined
Mar 9, 2014
Messages
5,425
Concatenate:

DoCmd.RunSQL "INSERT INTO CustomRepData (Text1) SELECT [" & Forms!Configuration!Text1Field & "] FROM Diary WHERE TheDate = #" & Forms.Configuration.TextStartDate & "#"

or maybe if code is behind form Configuration:

DoCmd.RunSQL "INSERT INTO CustomRepData (Text1) SELECT [" & Me.Text1Field & "] FROM Diary WHERE TheDate = #" & Me.TextStartDate & "#"

I prefer CurrentDb.Execute instead of DoCmd.RunSQL. The former won't trigger warning messages.
 

Malcolm17

Member
Local time
Today, 13:16
Joined
Jun 11, 2018
Messages
107
Thank you, that worked a treat!!
 

Malcolm17

Member
Local time
Today, 13:16
Joined
Jun 11, 2018
Messages
107
I wondered if you or anyone else may be able to help me with this one too:

I am looking to add in here to copy the data from another two text boxes, so it would be:

Greater than or Equal to TextStartDate
and Less than or Equal to TextEndDate

DoCmd.RunSQL "INSERT INTO CustomRepData (Text1) SELECT [" & Forms!Configuration!Text1Field & "] FROM Diary WHERE TheDate = #" & Forms.Configuration.TextStartDate & "#"

All of the text boxes are on the same form - Configuration.
 

June7

AWF VIP
Local time
Today, 05:16
Joined
Mar 9, 2014
Messages
5,425
You want a date range as filter criteria?

" … TheDate BETWEEN #" & Me.TextStartDate & "# AND #" & Me.TextEndDate & "#"
 

Malcolm17

Member
Local time
Today, 13:16
Joined
Jun 11, 2018
Messages
107
Thank you!! Just one more question.

Do you know if I can use multiple table sources in my SQL statement, IE: ... FROM Diary, Revenue WHERE ...

Or can you think of how I can get data from multiple tables in to my one temp table?

I am looking to create a temp table like the following:
(Name would be from Diary Table, Date and Sales would be from Revenue Table)

Date Name Sales
1 Dec John 5.00
2 Dec Sam 2.15
3 Dec Jim 8.20
 

June7

AWF VIP
Local time
Today, 05:16
Joined
Mar 9, 2014
Messages
5,425
Certainly. Build a query that pulls in whatever tables and/or queries you want. Use query builder.

Why would you need to save records to a temp table instead of just using query?
 

Malcolm17

Member
Local time
Today, 13:16
Joined
Jun 11, 2018
Messages
107
I'm trying to build a form which allows users to select up to 4 fields to build a custom report, so it would be dates down the side and up to 4 fields along the top.

TheDate is a field in each table like the primary key, all headings (Table Field Names) are in a dropdown box for users to select and have the table name populated in to another text box, hence the need for a temp table. My thinking was to add the data required for the report to a temp table, this would allow the user to select which field of data from a table that they want and run the report.
 

Users who are viewing this thread

Top Bottom