By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,639 Members | 2,276 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,639 IT Pros & Developers. It's quick & easy.

Date Error in VBA Query /Table not found Error

P: 20
I have a form where the arrival date is entered then all the info is exported to excel. However I can not seem to spot my error I keep getting query not found or tablename incorrect. Can someone please take a look at my code to see where I am going wrong.

Below is the Code THANKS

Private Sub cmdExport_Click()
On Error GoTo FinalStep

Dim querystring As String
Dim dbase As DAO.Database
Dim rsSchedules As DAO.Recordset
Dim tempi As Integer
Dim rptcnt As Integer
rptcnt = 0

'validations
If IsNull(txtStartDate.Value) Or Len(Trim(txtStartDate.Value)) = 0 Then

MsgBox " Invalid Report Date(s)", vbCritical, "Report Type"

ElseIf Not IsNull(txtStartDate.Value) Or Len(Trim(txtStartDate.Value)) = 0 Then

MsgBox "Exporting daily RON flight report for " & CStr(txtStartDate.Value)
Set dbase = CurrentDb()
Set rsSchedules = dbase.OpenRecordset(querystring)

'not sure why this query or tbl is not being recognized[/b]
querystring = "select * from tblflight1 where # Arrivaldate =#" & txtStartDate.Value & "#;)"
'I also tried this code
querystring = "select * from tblflight1 where tblflight1.Arrivaldate Between Forms!frmexportadhocron.txtStartDate.Value"


DoCmd.RunSQL querystring

If Not (rsSchedules.EOF = True) Then
CreateDailyReport rsSchedules
rptcnt = 1
End If
rsSchedules.Close

dbase.Close
If (rptcnt = 0) Then
MsgBox "No Reports Found for Excel Export", vbExclamation, "Export"
Else
MsgBox "Export to excel file(s) completed", vbExclamation, "Export Complete"
End If
Set rsSchedules = Nothing
Set dbase = Nothing
End If

Exit Sub
FinalStep:
MsgBox Err.Description, vbCritical, "Error"
End Sub


I think that my problem lies somewhere here

'not sure why this query or tbl is not being recognized[/b]
querystring = "select * from tblflight1 where # Arrivaldate =#" & txtStartDate.Value & "#;)"
'I also tried this code
querystring = "select * from tblflight1 where tblflight1.Arrivaldate Between Forms!frmexportadhocron.txtStartDate.Value"
May 15 '07 #1
Share this Question
Share on Google+
1 Reply


JConsulting
Expert 100+
P: 603
I have a form where the arrival date is entered then all the info is exported to excel. However I can not seem to spot my error I keep getting query not found or tablename incorrect. Can someone please take a look at my code to see where I am going wrong.

Below is the Code THANKS

Private Sub cmdExport_Click()
On Error GoTo FinalStep

Dim querystring As String
Dim dbase As DAO.Database
Dim rsSchedules As DAO.Recordset
Dim tempi As Integer
Dim rptcnt As Integer
rptcnt = 0

'validations
If IsNull(txtStartDate.Value) Or Len(Trim(txtStartDate.Value)) = 0 Then

MsgBox " Invalid Report Date(s)", vbCritical, "Report Type"

ElseIf Not IsNull(txtStartDate.Value) Or Len(Trim(txtStartDate.Value)) = 0 Then

MsgBox "Exporting daily RON flight report for " & CStr(txtStartDate.Value)
Set dbase = CurrentDb()
Set rsSchedules = dbase.OpenRecordset(querystring)

'not sure why this query or tbl is not being recognized[/b]
querystring = "select * from tblflight1 where # Arrivaldate =#" & txtStartDate.Value & "#;)"
'I also tried this code
querystring = "select * from tblflight1 where tblflight1.Arrivaldate Between Forms!frmexportadhocron.txtStartDate.Value"


DoCmd.RunSQL querystring

If Not (rsSchedules.EOF = True) Then
CreateDailyReport rsSchedules
rptcnt = 1
End If
rsSchedules.Close

dbase.Close
If (rptcnt = 0) Then
MsgBox "No Reports Found for Excel Export", vbExclamation, "Export"
Else
MsgBox "Export to excel file(s) completed", vbExclamation, "Export Complete"
End If
Set rsSchedules = Nothing
Set dbase = Nothing
End If

Exit Sub
FinalStep:
MsgBox Err.Description, vbCritical, "Error"
End Sub


I think that my problem lies somewhere here

'not sure why this query or tbl is not being recognized[/b]
querystring = "select * from tblflight1 where # Arrivaldate =#" & txtStartDate.Value & "#;)"
'I also tried this code
querystring = "select * from tblflight1 where tblflight1.Arrivaldate Between Forms!frmexportadhocron.txtStartDate.Value"
give this a go
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdExport_Click()
  2. On Error GoTo FinalStep
  3.  
  4. Dim querystring As String
  5. Dim dbase As DAO.DataBase
  6. Dim rsSchedules As DAO.Recordset
  7. Dim tempi As Integer
  8. Dim rptcnt As Integer
  9. rptcnt = 0
  10.  
  11. 'validations
  12. If Nz(Me.txtStartDate, 0) = 0 Or Len(Trim(Me.txtStartDate)) = 0 Then
  13.  
  14. MsgBox " Invalid Report Date(s)", vbCritical, "Report Type"
  15. End If 'End of validation
  16.     MsgBox "Exporting daily RON flight report for " & CStr(txtStartDate.Value)
  17.     Set rsSchedules = CurrentDb.OpenRecordset(querystring)
  18.     'not sure why this query or tbl is not being recognized[/b]
  19.     querystring = "select * from tblflight1 where Arrivaldate = #" & Me.txtStartDate & "#;"
  20.     'I also tried this code
  21.     'querystring = "select * from tblflight1 where tblflight1.Arrivaldate Between Forms!frmexportadhocron.txtStartDate.Value"
  22.     DoCmd.RunSQL querystring
  23.     If Not (rsSchedules.EOF = True) Then
  24.         CreateDailyReport rsSchedules
  25.         rptcnt = 1
  26.     End If
  27.     rsSchedules.Close
  28.     If (rptcnt = 0) Then
  29.         MsgBox "No Reports Found for Excel Export", vbExclamation, "Export"
  30.     Else
  31.         MsgBox "Export to excel file(s) completed", vbExclamation, "Export Complete"
  32.     End If
  33.     Set rsSchedules = Nothing
  34.     Set dbase = Nothing
  35. Exit Sub
  36. FinalStep:
  37. MsgBox err.Description, vbCritical, "Error"
  38. End Sub
  39.  
J
May 15 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.