Help to create table based on query date prompt (1 Viewer)

[lookforsmt]

Hi! All

I am trying to create a Temp Table based on date prompt (busStartDate & busEndDate) using something similar vba code

Currently the below code is to export data into excel, while i am looking to create Table in the same db
My table to be created is TempError from tbl_DispatchDetails and query name is qryDataToSend which has date prompt on it

Private Sub Command0_Click()
    Dim busStartDate As Date
    Dim busEndDate As Date

    Const xlsFileName = "C\Tempfile\MyExcelExport.xlsx"
    Const xlsTabName = "My_XLX_Tab_Name"
    Const tmpQueryName = "temp_error"
    Dim sql As String, qdf As QueryDef
    
    On Error Resume Next
    DoCmd.DeleteObject acQuery, tmpQueryName
    On Error GoTo 0
    sql = "SELECT * FROM qry_temp_error WHERE LogDate BETWEEN #" & busStartDate & "# AND #" & busEndDate & "#"
    Set qdf = CurrentDb.CreateQueryDef(tmpQueryName, sql)
    DoCmd.TransferSpreadsheet acExport, , tmpQueryName, xlsFileName, True
    DoCmd.DeleteObject acQuery, tmpQueryName
    MsgBox "Exported to: " & xlsFileName
End Sub

Request guidance on how to re-write this code for creating table
Thank you

 

[Ranman256]

To create a table, run a make table query.

 

[lookforsmt]

i am just attaching snapshot of the db,

Thanks Ranman, but how can i use make table with date prompt in vba.

 

[lookforsmt]

HI! anyone here who can help me to fix the code from post#1.
i can create new table from make-table query, but i need to create table based on date prompt through click on form

 

[Gasman]

Show the SQL for the make table query and indicate which field should be compared to the input date.

Easy to do via the QBE window?

 

[lookforsmt]

HI! Gasman,

the make table sql is

SELECT qry_dmd.RejectDate, qry_dmd.DispatchLocation, qry_dmd.CustomerAC, qry_dmd.RejectReason INTO tbl_tempError
FROM qry_dmd
WHERE (((qry_dmd.RejectDate)>=[StartDate] And (qry_dmd.RejectDate)<=[EndDate]));

RejectDate which has prompt date (StartDate and EndDate)

 

[Gasman]

So I would replace the code from the WHERE to the end, with the WHERE from your previous code, however I do not even see where you populate the busdates in that code?

Obviously change logdate for Rejectdate.

What you have there now should work, just with the two prompts for StartDate and EndDate. but it is still just a select query?. Once you get it working as you like, then change it to a MakeTable query and you will be prompted for the table name.

HTH

 

[lookforsmt]

yes, the logdate is replaced to Rejectdate. I have got the table name as tbl_tempError.
how do i put this code under vba. I am looking for date prompt on the click event for the user to enter date.

 

[Gasman]

Well you could run it with DoCmd.Execute (Sql) I believe, then you will be prompted for the dates?, when it is a maketable query? docmd.RunSQL will work for a Select. try it, that is how I learn.

I'd prefer a form, so you can check end is => start etc.

You have still not made it a make table query either?

Edit: In fact I go the commands wrong
https://www.databasejournal.com/fea...36_2/Executing-SQL-Statements-in-VBA-Code.htm

 

[Pat Hartman]

There is no reason to create a temp table in order to export data to Excel. Just use the query with the date criteria as the "source" in the TransferSpreadsheet method.

 

[Gasman]

There is no reason to create a temp table in order to export data to Excel. Just use the query with the date criteria as the "source" in the TransferSpreadsheet method.

Pat,
O/P is not exporting to Excel, merely showing code that they used to do so previously. O/P wants to create a table for some reason with certain records between two dates.
That is my understanding anyway.?

 

[Pat Hartman]

I meant to say there was no reason to create a temp querydef. But when I write "temp" , "table" always seems to come next. Thanks for the clarification.

 

[lookforsmt]

Good Morning all

O/P is not exporting to Excel, merely showing code that they used to do so previously. O/P wants to create a table for some reason with certain records between two dates.
That is my understanding anyway.?

Yes thats right

Gasman suggested to run vba with DoCmd.Execute (Sql) which will prompt for the dates. i am searching through net for similar code.
i am attaching my db for what i have done so far.

 

[Gasman]

Re read post #9, I got them the wrong way around. There is a link there as well.

 

[lookforsmt]

Hi! Gasman, i seen the link from post #9, tried to write the below code but i am getting error

Private Sub cmdCreate_Click()
    Dim qdf As DAO.QueryDef
    Set qdf = CurrentDb.QueryDefs("qry_Create_tbl")
    qdf.SQL = "SELECT qry_dmd.* INTO tbl_tempError " & _
              " FROM qry_dmd;"
    qdf.Execute
End Sub

Below Runtime error 3061.. Too few parameters. Expected 2

kindly note make-table is from union query which has date prompt.

 

[Gasman]

You are going to have to resolve the dates.?
I would have thought you could just use the dates in the make table and just have a WHERE your date BETWEEN startdate AND enddate?

OK, not as efficient, but it brings the dates to one place where you can enter them

In my DBs I have a constant for the date format
In a module

Option Compare Database
Option Explicit

Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.

in the sql

    strSQL = "SELECT qry_dmd.RejectDate, qry_dmd.DispatchLocation, qry_dmd.CustomerAC, qry_dmd.RejectReason INTO tbl_tempError FROM qry_dmd"
    strSQL = strSQL & " WHERE (((qry_dmd.RejectDate) BETWEEN " & strcJetDate(Me.busStartDate) & " AND " & strcJetDate(Me.busStartDate)

 

[lookforsmt]

Thanks Gasman, the date prompt is there in the Union All Query.

I would have thought you could just use the dates in the make table and just have a WHERE your date BETWEEN startdate AND enddate?

I tried to put the sql code but got an error, so left it as is.

i tried to come up with something which i have i done in the attached db.

below is the code

    Dim qdf As QueryDef
    Set qdf = CurrentDb.QueryDefs("qry_Create_tbl")
    qdf.Parameters("StartDate").Value = busStartDate               '   myTextFieldHavingBeginDate
    qdf.Parameters("EndDate").Value = busEndDate                   '   myTextFieldHavingEndDate
    qdf.Execute
End Sub

i am able to get the table created."tbl_tempError". Now i want to correct below:
a) the table created does not show until i close and open the db
b) how do i tag the code to the subform if i need to open some other tables.

 

[Gasman]

a) I do not know. Is there some sort of Refresh method for tables qdfs etc?
b) I would retrieve the relevant values, in this case it is just tablename and use a Replace() to replace "tbl_tempError" with the value of the tbl_name control.?

    Set qdf = CurrentDb.QueryDefs(YourQueryName)
    Debug.Print qdf.SQL
     qdf.sql = Replace("tbl_temp_error", YourTableNme)

You would use a similar process if you wanted a different query.?

I'm not sure how you got this to work as all the qry_create_table is is

SELECT qry_dmd.* INTO tbl_tempError
FROM qry_dmd;

I might even have placeholders to use all the time?

Edit: As for a) this link states RefreshDatabaseWindow

HTH

 

[lookforsmt]

Thanks Gasman

You would use a similar process if you wanted a different query.?

I wanted to make this dynamic, so i can use the code based on the table name in the subform mentioned which is selected as "Yes"
How can i do this.

You would use a similar process if you wanted a different query.?

As mentioned earlier, i have union all query from the two queries which has date prompt, which is used for make-table query. So the make-table query does not have date prompt.

I have add "RefreshDatabaseWindow" to the vba code line and it displays the table name in the navigation pane.

 

[Gasman]

Ok, you use the dates where you want them. I still feel that will limit you.
Right now you want a date between two dates. What happens when you want a field to be true?
So if it was me I'd have my criteria in my last query.?
However as always with programming, there are multiple ways to obtain the same result.

I've already shown you how to change the query and how to insert the tablename.

If you wanted to do it for all the records in the subform, then I would probably
1. use the subforms recordsetclone
2. check the field is yes
3. get the values from the recordset
4. populate that query qdf and queryname with those values
5. Run the query
Repeat steps 2 to 5 until you reach EOF of the recordsetclone

Whilst this is all doable, do you really envisage having to continually use this process? as it seems a lot of work, if you do not and just want to create a table once in a while? and do it manually.?