SQL Output - Characters found after end of SQL statement (1 Viewer)
- Thread starter Cark
- Start date
When I use SELECT DISTINCT ROW I got "Type mismatch error".
Code:
Private Sub Export_Click()
Dim ctl As Control
Dim sFilter As String
Dim sContName As String
sFilter = ""
sFilter = "WHERE TblTcAOG.[ATA2DIGIT] = 36"
'For Each ctl In Controls
' If ctl.Tag = "ATA" Then
' sContName = Right(ctl.Name, 2)
'Debug.Print sContName
'Once you have this working listing each control now interrogate the value
' If ctl.Value = True Then
' sFilter = sFilter & "," & sContName
' End If
' End If
'Next ctl
'sFilter = sFilter & ")"
'Debug.Print sFilter
Dim dbsCurrent As Database
Dim SQL_Name As QueryDef
Dim SQL_Output As String
Dim dStart As String
Dim dEnd As String
Set dbsCurrent = CurrentDb
Set SQL_Name = dbsCurrent.QueryDefs("qry_AOGreport_criteria")
dStart = "#" & Format([Forms]![FrmRptCriteriaAOG]![Beg_date_txt], "mm/dd/yyyy") & "#"
dEnd = "#" & Format([Forms]![FrmRptCriteriaAOG]![End_Date_txt], "mm/dd/yyyy") & "#"
SQL_Output = "SELECT DISTINCTROW TblTcAOG.[ID] " & _
"FROM tblactype INNER JOIN (TblAcrft INNER JOIN (TblTcAOG INNER JOIN TblAirports ON TblTcAOG.STN = TblAirports.STN) ON TblAcrft.Reg = TblTcAOG.REG) ON tblactype.actypeid = TblAcrft.ModelLink "
Set dbsCurrent = Nothing
Set SQL_Name = Nothing
End Sub
isladogs
MVP / VIP
- Local time
- Today, 10:50
- Joined
- Jan 14, 2017
- Messages
- 18,209
SELECT DISTINCTROW just sets Unique Records =Yes.
It will NOT cause datatype mismatch errors.
That error occurs when you try to join fields with different data types e.g. Number & Text. It can also occur when you incorrectly use quotes when filtering number fields as you've already found out.
So you need to recheck your datatypes.
It would appear you still have more than one issue as you are 'cycling' through these in turn depending on minor changes made
You haven't said whether the first query is now updateable.
If not the second query you were using definitely won't be either.
You seem to have changed your code in cmdExport procedure
You have three unused filter sFilter, dStart, dEnd. Also an unused query def.
Then you have SQLOutput that you do nothing with.
Even if you fix the errors, that procedure will do absolutely nothing
It will NOT cause datatype mismatch errors.
That error occurs when you try to join fields with different data types e.g. Number & Text. It can also occur when you incorrectly use quotes when filtering number fields as you've already found out.
So you need to recheck your datatypes.
It would appear you still have more than one issue as you are 'cycling' through these in turn depending on minor changes made
You haven't said whether the first query is now updateable.
If not the second query you were using definitely won't be either.
You seem to have changed your code in cmdExport procedure
You have three unused filter sFilter, dStart, dEnd. Also an unused query def.
Then you have SQLOutput that you do nothing with.
Even if you fix the errors, that procedure will do absolutely nothing
Ok so the mystery as to how this isn't working just got even more mysterious.
I had the opportunity to test the database using a colleague's computer to open the file from the shared network (where I have been opening it from all the time during this troubleshooting). We then did a number of exports using the exact same code and for some reason everything worked perfectly with no "Recordset not updateable" errors.
I then went back to my computer and started trying to open much much earlier versions of the Access database and they too were giving me "Recordset not updateable" errors despite me never having had issues with these versions before.
Could it be something to do with my version of Access being corrupted?
I had the opportunity to test the database using a colleague's computer to open the file from the shared network (where I have been opening it from all the time during this troubleshooting). We then did a number of exports using the exact same code and for some reason everything worked perfectly with no "Recordset not updateable" errors.
I then went back to my computer and started trying to open much much earlier versions of the Access database and they too were giving me "Recordset not updateable" errors despite me never having had issues with these versions before.
Could it be something to do with my version of Access being corrupted?
I can confirm the first query (the one I am calling qry_AOGreport_criteria) is updateable. The qry_AOGs_Export (the one which depends on qry_AOGreportcriteria) is not updateable.
When I compare this with the one for Delays, I have the following:
qry_report_criteria = updateable
qry_Delays_Export (which depends on qry_report_criteria) = doesn't appear to be updateable, but exports nicely into Excel.
How can I check to see where the difference lies?
When I compare this with the one for Delays, I have the following:
qry_report_criteria = updateable
qry_Delays_Export (which depends on qry_report_criteria) = doesn't appear to be updateable, but exports nicely into Excel.
How can I check to see where the difference lies?
The Export Button VBA always stops at:
But when I look at the query directly in Access, I am able to see the query has run correctly and brought up results.
Code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_AOGs_Export", "H:\TechDelaysBackupOfExports\qry_AOGs_Export.xlsx", TrueBut when I look at the query directly in Access, I am able to see the query has run correctly and brought up results.
- Local time
- Today, 04:50
- Joined
- Feb 28, 2001
- Messages
- 27,140
If it works from computer A and fails from computer B (reliably in both cases) then stop looking at your code and start looking at various settings including your references. Though you are looking at the same file across the network, references are part of the registry and therefore are machine-specific. Even the order of appearance can sometimes make a difference. You also need to look at the regional settings (though that is more of a long shot).
Further, I don't recall seeing the answer to this in the thread, though I might have missed it: Is there a front-end/back-end split in force here? If so compact and repair the front end. ALWAYS remember to make a copy of anything BEFORE you try to C&R because if something goes wrong, you can restore it and try again. The file that works from computer A but fails from computer B isn't acting like it is corrupted - but something is.
One last thing to consider, and this is another long shot. IF you have the distribution disk for Access or Office, try to run the installation. One of the options with Office is the "Repair" option, which verifies that all files are correct and replaces damaged files. If something has happened to damage a file, this would fix it on your machine that is failing.
Further, I don't recall seeing the answer to this in the thread, though I might have missed it: Is there a front-end/back-end split in force here? If so compact and repair the front end. ALWAYS remember to make a copy of anything BEFORE you try to C&R because if something goes wrong, you can restore it and try again. The file that works from computer A but fails from computer B isn't acting like it is corrupted - but something is.
One last thing to consider, and this is another long shot. IF you have the distribution disk for Access or Office, try to run the installation. One of the options with Office is the "Repair" option, which verifies that all files are correct and replaces damaged files. If something has happened to damage a file, this would fix it on your machine that is failing.
- Local time
- Today, 04:50
- Joined
- Feb 28, 2001
- Messages
- 27,140
One last thought, although it seems that you have owned up to the long IN clause that was part of the discussion in the early threads here...
SQL Code Injection is a known computer viral path of attack. It is POSSIBLE (not necessarily likely) that some other issue than you poor punctuation could also cause your errors. Does your machine have a good antivirus package and has it been updated recently? Has it passed a recent A/V scan?
I'm tossing out oddball stuff, I know, but this is a question you could answer easily. This would eliminate a lot of possibilities.
SQL Code Injection is a known computer viral path of attack. It is POSSIBLE (not necessarily likely) that some other issue than you poor punctuation could also cause your errors. Does your machine have a good antivirus package and has it been updated recently? Has it passed a recent A/V scan?
I'm tossing out oddball stuff, I know, but this is a question you could answer easily. This would eliminate a lot of possibilities.
Apologies for the delay in responding, I have been away from this project for the past week, but still been mulling things over in my mind.
My anti-virus appears to be set up in the exact same way as my colleague's so I don't think that ever had anything to do with it (not saying that in a sassy way, but purely my thoughts based on what I'm about to say next).
Seems as though I have solved the issue as I decided to test the export and the dataset to try and find which records had been causing the issue as the export button appeared to be "intermittently" working.
I exported my "Export" a few dates at a time and compared them against the records in my table. With there being 9,000+ rows I did 500 at a time until I hit the error message when exporting rows 8,000 - 8,500. I then traced it to row 8,386 where one of the ATA Chapters was set to REQUIRED. I then realised straight away that someone had accidentally left the field marked as the default value of "REQUIRED" and not the ATA Chapter. The user is meant to identify it says REQUIRED and then click the drop down to select the ATA Chapter such as "##-## SOME TEXT HERE" but with it saying "REQUIRED", it broke the calculated columns.
The error message Access was giving me was quite confusing, but everything appears to be working now that I have changed the value and I have changed the data validation logic for the input form.
My anti-virus appears to be set up in the exact same way as my colleague's so I don't think that ever had anything to do with it (not saying that in a sassy way, but purely my thoughts based on what I'm about to say next).
Seems as though I have solved the issue as I decided to test the export and the dataset to try and find which records had been causing the issue as the export button appeared to be "intermittently" working.
I exported my "Export" a few dates at a time and compared them against the records in my table. With there being 9,000+ rows I did 500 at a time until I hit the error message when exporting rows 8,000 - 8,500. I then traced it to row 8,386 where one of the ATA Chapters was set to REQUIRED. I then realised straight away that someone had accidentally left the field marked as the default value of "REQUIRED" and not the ATA Chapter. The user is meant to identify it says REQUIRED and then click the drop down to select the ATA Chapter such as "##-## SOME TEXT HERE" but with it saying "REQUIRED", it broke the calculated columns.
The error message Access was giving me was quite confusing, but everything appears to be working now that I have changed the value and I have changed the data validation logic for the input form.