Originally Posted by
ranman256
then the data is wrong. unless you have month and day swapped.
the query is sound.
I don't think the data is wrong. I created Table1 using the only 4 fields with the same details as the linked table and inserted a few rows of sample data.
Table1 Data basically looks like this.
My form looks like this
Table1 Design View
Table2 Design View
I tried this at first to ignore the textboxes and hardcode dates in to test but it did not work. No data was inserted into Table2.
Ignoring the face that I forgot to insert data into "yyyy" field.
Code:
Private Sub btnTest_Click()
On Error GoTo Err_btnTest_Click
Dim dbs As DAO.Database
Dim strSQL As String
' Const cstrNewTableName As String = "Table2"
Set dbs = CurrentDb
strSQL = "INSERT INTO Table2 ( ID, mm, dd, fullDate ) " & _
"SELECT Table1.ID, Table1.mm, Table1.dd, [mm] & '/' & [dd] & '/' & [yyyy] AS date1 " & _
"FROM Table1 " & _
"WHERE ((([mm] & '/' & [dd] & '/' & [yyyy]) Between #4/1/2015# And #4/1/2015#));"
Debug.Print strSQL
dbs.Execute strSQL, dbFailOnError
Exit_btnTest_Click:
Set dbs = Nothing
Exit Sub
Err_btnTest_Click:
MsgBox Err.Description
Resume Exit_btnTest_Click
End Sub
So I tried this instead
Code:
Private Sub btnTest_Click()
On Error GoTo Err_btnTest_Click
Dim dbs As DAO.Database
Dim strSQL As String
' Const cstrNewTableName As String = "Table2"
Set dbs = CurrentDb
strSQL = "INSERT INTO Table2 ( ID, mm, dd, fullDate ) " & _
"SELECT Table1.ID, Table1.mm, Table1.dd, [mm] & '/' & [dd] & '/' & [yyyy] AS date1 " & _
"FROM Table1 " & _
"WHERE ((DateValue([mm] & '/' & [dd] & '/' & [yyyy]) Between #4/1/2015# And #4/1/2015#));"
Debug.Print strSQL
dbs.Execute strSQL, dbFailOnError
Exit_btnTest_Click:
Set dbs = Nothing
Exit Sub
Err_btnTest_Click:
MsgBox Err.Description
Resume Exit_btnTest_Click
End Sub
Debug.Print shows
Code:
INSERT INTO Table2 ( ID, mm, dd, fullDate ) SELECT Table1.ID, Table1.mm, Table1.dd, [mm] & '/' & [dd] & '/' & [yyyy] AS date1 FROM Table1 WHERE ((DateValue([mm] & '/' & [dd] & '/' & [yyyy]) Between #4/1/2015# And #4/1/2015#));
And Table2 was populated with 1 row.
Once again, ignoring the face that I forgot to insert data into "yyyy" field.
Although date shown in "fullDate" is correct, the data in "dd", "mm" is wrong.
It should be "1" in "dd", "4" in "mm".
And from the data in Table1, it should be 2 rows, "ID" 1 & 4 instead of "ID" 3 which was inserted into Table2
Originally Posted by
ssanfu
Your
WHERE clause is wrong.
First, the part in RED would/must be concantated, not inside the quotes.
More to the point, you are
missing the
FIELD name for the criteria.
Looking at the result of the Debug.Print statement
Code:
WHERE (((DateValue(Format([mm],'00') & '/' & Format([dd],'00') & '/' & Format([yyyy],'0000'))) Between #04/01/2015# And #04/01/2015#)
what FIELD should the criteria be applied to???
Create a new SELECT query.
Add the fields from "Table1"
Now manually add the criteria to the date field: BETWEEN #04/01/2015# and #04/01/2015# <<= (April 1, 2015)
Execute the query. Is the data correct?
If yes. switch to SQL view. What does the WHERE clause look like?
I would recommend creating "Table2" one time and use an Append query rather than a make table query.
Your code would/should look something like:
Code:
Option Compare Database '<- should be at the top of EVERY module
Option Explicit '<- should be at the top of EVERY module
Private Sub btnTest_Click()
On Error GoTo Exit_btnTest_Click
Const cstrNewTableName As String = "Table2"
Dim dbs As DAO.Database
Dim strSQL As String
Set dbs = CurrentDb
' strSQL = "SELECT Table1.* INTO [" & cstrNewTableName & "] "
strSQL = "SELECT Table1.*"
strSQL = strSQL & " FROM Table1"
strSQL = strSQL & " WHERE Table1.MyDateField"
strSQL = strSQL & " Between " & Format(Me.[tbxFromDate], "\#mm\/dd\/yyyy\#")
strSQL = strSQL & " And " & Format(Me.[tbxFromDate], "\#mm\/dd\/yyyy\#")
strSQL = strSQL & " ORDER BY Table1.ID;"
Debug.Print strSQL
dbs.Execute strSQL, dbFailOnError
Exit_btnTest_Click:
Set dbs = Nothing
Exit Sub
Err_btnTest_Click:
MsgBox Err.Description
Resume Exit_btnTest_Click
End Sub
Change
MyDateField to your field name.
Thing is, I don't have 1 field that is a date field. It is 3 separate fields("mm","dd","yyyy").
I tried creating Table2 with the same fields plus 1 called fullDate then changing my sql to something like you showed but it still doesn't work.
Code:
Option Compare Database
Option Explicit
Private Sub btnTest_Click()
On Error GoTo Err_btnTest_Click
Dim dbs As DAO.Database
Dim strSQL As String
Set dbs = CurrentDb
strSQL = "INSERT INTO Table2 ( ID, mm, dd )"
strSQL = strSQL & " SELECT Table1.ID, Table1.mm, Table1.dd"
strSQL = strSQL & " FROM Table1"
strSQL = strSQL & " WHERE ([mm] & '/' & [dd] & '/' & [yyyy])"
strSQL = strSQL & " Between " & Format(Me.[tbxFromDate], "\#mm\/dd\/yyyy\#")
strSQL = strSQL & " And " & Format(Me.[tbxFromDate], "\#mm\/dd\/yyyy\#")
strSQL = strSQL & " ORDER BY Table1.ID;"
Debug.Print strSQL
dbs.Execute strSQL, dbFailOnError
Exit_btnTest_Click:
Set dbs = Nothing
Exit Sub
Err_btnTest_Click:
MsgBox Err.Description
Resume Exit_btnTest_Click
End Sub
No data was inserted into Table2
Debug.Print shows
Code:
INSERT INTO Table2 ( ID, mm, dd ) SELECT Table1.ID, Table1.mm, Table1.dd FROM Table1 WHERE ([mm] & '/' & [dd] & '/' & [yyyy]) Between #04/01/2015# And #04/01/2015# ORDER BY Table1.ID;
When I changed it to this, same thing happened as above, where 1 row is inserted but the wrong data is inserted.
Code:
Option Compare Database
Option Explicit
Private Sub btnTest_Click()
On Error GoTo Err_btnTest_Click
Dim dbs As DAO.Database
Dim strSQL As String
Set dbs = CurrentDb
strSQL = "INSERT INTO Table2 ( ID, mm, dd )"
strSQL = strSQL & " SELECT Table1.ID, Table1.mm, Table1.dd"
strSQL = strSQL & " FROM Table1"
strSQL = strSQL & " WHERE DateValue([mm] & '/' & [dd] & '/' & [yyyy])"
strSQL = strSQL & " Between " & Format(Me.[tbxFromDate], "\#mm\/dd\/yyyy\#")
strSQL = strSQL & " And " & Format(Me.[tbxFromDate], "\#mm\/dd\/yyyy\#")
strSQL = strSQL & " ORDER BY Table1.ID;"
Debug.Print strSQL
dbs.Execute strSQL, dbFailOnError
Exit_btnTest_Click:
Set dbs = Nothing
Exit Sub
Err_btnTest_Click:
MsgBox Err.Description
Resume Exit_btnTest_Click
End Sub
Debig.Print shows
Code:
INSERT INTO Table2 ( ID, mm, dd ) SELECT Table1.ID, Table1.mm, Table1.dd FROM Table1 WHERE DateValue([mm] & '/' & [dd] & '/' & [yyyy]) Between #04/01/2015# And #04/01/2015# ORDER BY Table1.ID;