append SQL no records

  • Thread starter Thread starter javablood
  • Start date Start date
J

javablood

I tried a make table SQL and now an append SQL in a form to combine data from
two tables into one. The end result is no data. However, if I just use a
stand alone query (or call it from the form) it works fine! I hope someone
can see the error of my ways! Here is the code:

Private Sub cmdbutrun_Click()
On Error GoTo Err_cmdbutrun_Click
Dim strSQL As String
Dim strSQLa As String

DoCmd.SetWarnings False
strSQL = "Delete * from tblSWTPZnew" ' delete data before append
' append new data
strSQLa = "INSERT INTO tblSWTPZnew ( STATION_ID, SAMPLE_DATE,
SAMPLE_TIME, GW_ELEV )" & _
"SELECT TagTable.TagName AS STATION_ID,
Format([FloatTable].[DateAndTime],""mm/dd/yy"") " & _
"AS SAMPLE_DATE, Format([FloatTable].[DateAndTime],""hh:nn:ss"") AS
SAMPLE_TIME, FloatTable.Val AS GW_ELEV " & _
"FROM FloatTable INNER JOIN TagTable ON FloatTable.TagIndex =
TagTable.TagIndex " & _
"WHERE (((Format([FloatTable].[DateAndTime],""mm/dd/yy"")) " & _
"Between [Forms]![frmRun]![lbodatbegin] And
[Forms]![frmRun]![lbodatend])) " & _
"ORDER BY TagTable.TagName,
Format([FloatTable].[DateAndTime],""mm/dd/yy"");"
DoCmd.RunSQL strSQLa
DoCmd.SetWarnings True

' check table for records
' -----------------------
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblSWTPZnew")
If rst.EOF = True And rst.BOF = True Then ' there are no records in
the recordset
ChngData = MsgBox("There are no data in the table!",
vbInformation, "No Records!")
rst.Close
Set rst = Nothing
Set dbs = Nothing
End If
' -----------------------

Exit_cmdbutrun_Click:
Exit Sub

Err_cmdbutrun_Click:
MsgBox Err.Description
Resume Exit_cmdbutrun_Click
End Sub

Thanks,
 
While I'm not sure if it's relevant to your problem, the fact that you're
formatting the date in your Where clause is going to cause problems:
 
Hi javablood,

try in this way
strSQLa = "INSERT INTO tblSWTPZnew ( STATION_ID, SAMPLE_DATE,
SAMPLE_TIME, GW_ELEV ) " & _ 'I've added a space after the parenthesis
"SELECT TagTable.TagName AS STATION_ID,
Format([FloatTable].[DateAndTime],"'mm/dd/yy'") " & _
"AS SAMPLE_DATE, Format([FloatTable].[DateAndTime],"'hh:nn:ss'") AS
SAMPLE_TIME, FloatTable.Val AS GW_ELEV " & _
"FROM FloatTable INNER JOIN TagTable ON FloatTable.TagIndex =
TagTable.TagIndex " & _
"WHERE (((Format([FloatTable].[DateAndTime],"'mm/dd/yy'")) " & _
"Between [Forms]![frmRun]![lbodatbegin] And
[Forms]![frmRun]![lbodatend])) " & _
"ORDER BY TagTable.TagName,
Format([FloatTable].[DateAndTime],"'mm/dd/yy'");"

I've substituted the double quotes with single quotes in the format statements

HTH Paolo
 
Not sure whether it's the cause of your problem, but the fact that you're
formatting the date in your Where clause is going to cause problems.

"WHERE (((Format([FloatTable].[DateAndTime],""mm/dd/yy"")) " & _
"Between [Forms]![frmRun]![lbodatbegin] And [Forms]![frmRun]![lbodatend]))
" & _

If you're trying to get only the date part from the date/time field, use the
DateValue function:

"WHERE (((DateValue([FloatTable].[DateAndTime])) " & _
"Between [Forms]![frmRun]![lbodatbegin] And [Forms]![frmRun]![lbodatend]))
" & _
 
Paolo,

Thanks for your reply. That fix did not work. I added the space and
changed the " to ' but for some reason Access added an extra space between
the " and ' in the first Format statement for mm/dd/yy and hence I got a
syntax error. But I followed Doug's advice and took the Format out of the
Where clause and got it to wrok. However, the DateValue did not work either.
See my reply to Doug for my ultimate fix.
--
javablood


Paolo said:
Hi javablood,

try in this way
strSQLa = "INSERT INTO tblSWTPZnew ( STATION_ID, SAMPLE_DATE,
SAMPLE_TIME, GW_ELEV ) " & _ 'I've added a space after the parenthesis
"SELECT TagTable.TagName AS STATION_ID,
Format([FloatTable].[DateAndTime],"'mm/dd/yy'") " & _
"AS SAMPLE_DATE, Format([FloatTable].[DateAndTime],"'hh:nn:ss'") AS
SAMPLE_TIME, FloatTable.Val AS GW_ELEV " & _
"FROM FloatTable INNER JOIN TagTable ON FloatTable.TagIndex =
TagTable.TagIndex " & _
"WHERE (((Format([FloatTable].[DateAndTime],"'mm/dd/yy'")) " & _
"Between [Forms]![frmRun]![lbodatbegin] And
[Forms]![frmRun]![lbodatend])) " & _
"ORDER BY TagTable.TagName,
Format([FloatTable].[DateAndTime],"'mm/dd/yy'");"

I've substituted the double quotes with single quotes in the format statements

HTH Paolo

javablood said:
I tried a make table SQL and now an append SQL in a form to combine data from
two tables into one. The end result is no data. However, if I just use a
stand alone query (or call it from the form) it works fine! I hope someone
can see the error of my ways! Here is the code:

Private Sub cmdbutrun_Click()
On Error GoTo Err_cmdbutrun_Click
Dim strSQL As String
Dim strSQLa As String

DoCmd.SetWarnings False
strSQL = "Delete * from tblSWTPZnew" ' delete data before append
' append new data
strSQLa = "INSERT INTO tblSWTPZnew ( STATION_ID, SAMPLE_DATE,
SAMPLE_TIME, GW_ELEV )" & _
"SELECT TagTable.TagName AS STATION_ID,
Format([FloatTable].[DateAndTime],""mm/dd/yy"") " & _
"AS SAMPLE_DATE, Format([FloatTable].[DateAndTime],""hh:nn:ss"") AS
SAMPLE_TIME, FloatTable.Val AS GW_ELEV " & _
"FROM FloatTable INNER JOIN TagTable ON FloatTable.TagIndex =
TagTable.TagIndex " & _
"WHERE (((Format([FloatTable].[DateAndTime],""mm/dd/yy"")) " & _
"Between [Forms]![frmRun]![lbodatbegin] And
[Forms]![frmRun]![lbodatend])) " & _
"ORDER BY TagTable.TagName,
Format([FloatTable].[DateAndTime],""mm/dd/yy"");"
DoCmd.RunSQL strSQLa
DoCmd.SetWarnings True

' check table for records
' -----------------------
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblSWTPZnew")
If rst.EOF = True And rst.BOF = True Then ' there are no records in
the recordset
ChngData = MsgBox("There are no data in the table!",
vbInformation, "No Records!")
rst.Close
Set rst = Nothing
Set dbs = Nothing
End If
' -----------------------

Exit_cmdbutrun_Click:
Exit Sub

Err_cmdbutrun_Click:
MsgBox Err.Description
Resume Exit_cmdbutrun_Click
End Sub

Thanks,
Click to expand...
Click to expand...
 
Doug,

Thanks for your advice. I did get it to work when I took the Format out of
the Where clause. How come it works as a standalone query though with the
Format statements in the Where clause?

Also, the DateValue did not work as I still got the full Date/Time.
However, because I have to update the table to make sure my STATION_IDs match
up with the table to which these data will be appended to, I just formated
the Date and Time in an update query before appending.

Any insight on why the Format works in one case but not the other and the
problem with the DateVlaue is welcomed.
--
javablood


Douglas J. Steele said:
Not sure whether it's the cause of your problem, but the fact that you're
formatting the date in your Where clause is going to cause problems.

"WHERE (((Format([FloatTable].[DateAndTime],""mm/dd/yy"")) " & _
"Between [Forms]![frmRun]![lbodatbegin] And [Forms]![frmRun]![lbodatend]))
" & _

If you're trying to get only the date part from the date/time field, use the
DateValue function:

"WHERE (((DateValue([FloatTable].[DateAndTime])) " & _
"Between [Forms]![frmRun]![lbodatbegin] And [Forms]![frmRun]![lbodatend]))
" & _


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


javablood said:
I tried a make table SQL and now an append SQL in a form to combine data
from
two tables into one. The end result is no data. However, if I just use a
stand alone query (or call it from the form) it works fine! I hope
someone
can see the error of my ways! Here is the code:

Private Sub cmdbutrun_Click()
On Error GoTo Err_cmdbutrun_Click
Dim strSQL As String
Dim strSQLa As String

DoCmd.SetWarnings False
strSQL = "Delete * from tblSWTPZnew" ' delete data before append
' append new data
strSQLa = "INSERT INTO tblSWTPZnew ( STATION_ID, SAMPLE_DATE,
SAMPLE_TIME, GW_ELEV )" & _
"SELECT TagTable.TagName AS STATION_ID,
Format([FloatTable].[DateAndTime],""mm/dd/yy"") " & _
"AS SAMPLE_DATE, Format([FloatTable].[DateAndTime],""hh:nn:ss"") AS
SAMPLE_TIME, FloatTable.Val AS GW_ELEV " & _
"FROM FloatTable INNER JOIN TagTable ON FloatTable.TagIndex =
TagTable.TagIndex " & _
"WHERE (((Format([FloatTable].[DateAndTime],""mm/dd/yy"")) " & _
"Between [Forms]![frmRun]![lbodatbegin] And
[Forms]![frmRun]![lbodatend])) " & _
"ORDER BY TagTable.TagName,
Format([FloatTable].[DateAndTime],""mm/dd/yy"");"
DoCmd.RunSQL strSQLa
DoCmd.SetWarnings True

' check table for records
' -----------------------
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblSWTPZnew")
If rst.EOF = True And rst.BOF = True Then ' there are no records in
the recordset
ChngData = MsgBox("There are no data in the table!",
vbInformation, "No Records!")
rst.Close
Set rst = Nothing
Set dbs = Nothing
End If
' -----------------------

Exit_cmdbutrun_Click:
Exit Sub

Err_cmdbutrun_Click:
MsgBox Err.Description
Resume Exit_cmdbutrun_Click
End Sub

Thanks,
Click to expand...
Click to expand...
 
Did you have the same values in the lbodatbegin and lbodatend controls on
forms frmRun in both cases?

The problem with using the Format function is that it converts the value to
text and text comparisons aren't the same as date comparisons. (The only way
you can guarantee that the text comparison will be the same is if you've
formatted the date in yyyy-mm-dd format)

Note that using DateValue will NOT change what's in the field in you're
displaying the value of the DateAndTime field just because you've used the
function in the Where clause.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


javablood said:
Doug,

Thanks for your advice. I did get it to work when I took the Format out
of
the Where clause. How come it works as a standalone query though with the
Format statements in the Where clause?

Also, the DateValue did not work as I still got the full Date/Time.
However, because I have to update the table to make sure my STATION_IDs
match
up with the table to which these data will be appended to, I just formated
the Date and Time in an update query before appending.

Any insight on why the Format works in one case but not the other and the
problem with the DateVlaue is welcomed.
--
javablood


Douglas J. Steele said:
Not sure whether it's the cause of your problem, but the fact that you're
formatting the date in your Where clause is going to cause problems.

"WHERE (((Format([FloatTable].[DateAndTime],""mm/dd/yy"")) " & _
"Between [Forms]![frmRun]![lbodatbegin] And
[Forms]![frmRun]![lbodatend]))
" & _

If you're trying to get only the date part from the date/time field, use
the
DateValue function:

"WHERE (((DateValue([FloatTable].[DateAndTime])) " & _
"Between [Forms]![frmRun]![lbodatbegin] And
[Forms]![frmRun]![lbodatend]))
" & _


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


javablood said:
I tried a make table SQL and now an append SQL in a form to combine data
from
two tables into one. The end result is no data. However, if I just
use a
stand alone query (or call it from the form) it works fine! I hope
someone
can see the error of my ways! Here is the code:

Private Sub cmdbutrun_Click()
On Error GoTo Err_cmdbutrun_Click
Dim strSQL As String
Dim strSQLa As String

DoCmd.SetWarnings False
strSQL = "Delete * from tblSWTPZnew" ' delete data before append
' append new data
strSQLa = "INSERT INTO tblSWTPZnew ( STATION_ID, SAMPLE_DATE,
SAMPLE_TIME, GW_ELEV )" & _
"SELECT TagTable.TagName AS STATION_ID,
Format([FloatTable].[DateAndTime],""mm/dd/yy"") " & _
"AS SAMPLE_DATE, Format([FloatTable].[DateAndTime],""hh:nn:ss"") AS
SAMPLE_TIME, FloatTable.Val AS GW_ELEV " & _
"FROM FloatTable INNER JOIN TagTable ON FloatTable.TagIndex =
TagTable.TagIndex " & _
"WHERE (((Format([FloatTable].[DateAndTime],""mm/dd/yy"")) " & _
"Between [Forms]![frmRun]![lbodatbegin] And
[Forms]![frmRun]![lbodatend])) " & _
"ORDER BY TagTable.TagName,
Format([FloatTable].[DateAndTime],""mm/dd/yy"");"
DoCmd.RunSQL strSQLa
DoCmd.SetWarnings True

' check table for records
' -----------------------
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblSWTPZnew")
If rst.EOF = True And rst.BOF = True Then ' there are no records
in
the recordset
ChngData = MsgBox("There are no data in the table!",
vbInformation, "No Records!")
rst.Close
Set rst = Nothing
Set dbs = Nothing
End If
' -----------------------

Exit_cmdbutrun_Click:
Exit Sub

Err_cmdbutrun_Click:
MsgBox Err.Description
Resume Exit_cmdbutrun_Click
End Sub

Thanks,
Click to expand...
Click to expand...
Click to expand...
 
--
javablood


Douglas J. Steele said:
Did you have the same values in the lbodatbegin and lbodatend controls on
forms frmRun in both cases?
Click to expand...

Yes I did.
The problem with using the Format function is that it converts the value to
text and text comparisons aren't the same as date comparisons. (The only way
you can guarantee that the text comparison will be the same is if you've
formatted the date in yyyy-mm-dd format)
Click to expand...

Then why wouldn't I get an error because the field is a Date/Time type?
Note that using DateValue will NOT change what's in the field in you're
displaying the value of the DateAndTime field just because you've used the
function in the Where clause
okay.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


javablood said:
Doug,

Thanks for your advice. I did get it to work when I took the Format out
of
the Where clause. How come it works as a standalone query though with the
Format statements in the Where clause?

Also, the DateValue did not work as I still got the full Date/Time.
However, because I have to update the table to make sure my STATION_IDs
match
up with the table to which these data will be appended to, I just formated
the Date and Time in an update query before appending.

Any insight on why the Format works in one case but not the other and the
problem with the DateVlaue is welcomed.
--
javablood


Douglas J. Steele said:
Not sure whether it's the cause of your problem, but the fact that you're
formatting the date in your Where clause is going to cause problems.

"WHERE (((Format([FloatTable].[DateAndTime],""mm/dd/yy"")) " & _
"Between [Forms]![frmRun]![lbodatbegin] And
[Forms]![frmRun]![lbodatend]))
" & _

If you're trying to get only the date part from the date/time field, use
the
DateValue function:

"WHERE (((DateValue([FloatTable].[DateAndTime])) " & _
"Between [Forms]![frmRun]![lbodatbegin] And
[Forms]![frmRun]![lbodatend]))
" & _


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I tried a make table SQL and now an append SQL in a form to combine data
from
two tables into one. The end result is no data. However, if I just
use a
stand alone query (or call it from the form) it works fine! I hope
someone
can see the error of my ways! Here is the code:

Private Sub cmdbutrun_Click()
On Error GoTo Err_cmdbutrun_Click
Dim strSQL As String
Dim strSQLa As String

DoCmd.SetWarnings False
strSQL = "Delete * from tblSWTPZnew" ' delete data before append
' append new data
strSQLa = "INSERT INTO tblSWTPZnew ( STATION_ID, SAMPLE_DATE,
SAMPLE_TIME, GW_ELEV )" & _
"SELECT TagTable.TagName AS STATION_ID,
Format([FloatTable].[DateAndTime],""mm/dd/yy"") " & _
"AS SAMPLE_DATE, Format([FloatTable].[DateAndTime],""hh:nn:ss"") AS
SAMPLE_TIME, FloatTable.Val AS GW_ELEV " & _
"FROM FloatTable INNER JOIN TagTable ON FloatTable.TagIndex =
TagTable.TagIndex " & _
"WHERE (((Format([FloatTable].[DateAndTime],""mm/dd/yy"")) " & _
"Between [Forms]![frmRun]![lbodatbegin] And
[Forms]![frmRun]![lbodatend])) " & _
"ORDER BY TagTable.TagName,
Format([FloatTable].[DateAndTime],""mm/dd/yy"");"
DoCmd.RunSQL strSQLa
DoCmd.SetWarnings True

' check table for records
' -----------------------
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblSWTPZnew")
If rst.EOF = True And rst.BOF = True Then ' there are no records
in
the recordset
ChngData = MsgBox("There are no data in the table!",
vbInformation, "No Records!")
rst.Close
Set rst = Nothing
Set dbs = Nothing
End If
' -----------------------

Exit_cmdbutrun_Click:
Exit Sub

Err_cmdbutrun_Click:
MsgBox Err.Description
Resume Exit_cmdbutrun_Click
End Sub

Thanks,
Click to expand...
Click to expand...
Click to expand...
 
javablood said:
Then why wouldn't I get an error because the field is a Date/Time type?
Click to expand...

You're comparing a text field to two date fields. That means either the text
field is going to have to be coerced into a date, or the date fields are
going to have to be coerced into text fields. I'm not positive, but I
believe the latter is what will occur.
 
Back
Top