MyTechSupport.ca :: Your Computer Technical Resource Headquarters! MyTechSupport.ca :: Your Computer Technical Resource Headquarters!
HOME FORUMS RESOURCES & TOOLS ARTICLES ONLINE STORE ABOUT US
Computer Support Forums arrow Software Support arrow Database Problems arrow Topic: query throwing error 3075
April 23, 2019, 06:13:24 AM
 

Home Forum Rules Help Search Mobile Version Login Register

Welcome, Guest. Please login or register.
Did you miss your activation email?
April 23, 2019, 06:13:24 AM

Login with username, password and session length
 
News
Welcome to MyTechSupport.ca! - Registration is FREE, so why not join our friendly community today?
  0 Members and 1 Guest are viewing this topic.
Pages: [1] Go Down Print
Author Topic: query throwing error 3075  (Read 2710 times)
k1dfreeze
Full Member
***

Karma: +0/-0
Offline Offline

Gender: Male
Posts: 57


Bookmark and Share

View Profile
« on: April 23, 2007, 07:05:21 PM »

im working on a search form, and i had everything working until i added a new search criteria, a check box, and now when i run the query, im getting error 3075: missing opperator on my sql...im not sure why, i made that field the same way i made all my other ones. the only thing different is that i made a boolean instead of a string for the check box, and i think thats only thing different...also, it occurs when chkASAPS is checked or not checked...

here is a partial code for the form

Option Compare Database
Option Explicit

Private Sub cmdOK_Click()
'If anything should go wrong, this will excute
On Error GoTo cmdOK_Click_err
'These are the variables that we shall use
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strCaseAssignment As String 'Case Assignment
Dim strSubTypeCat As String 'SubTypeCat
Dim blnASAPS As Boolean 'ASAPS
Dim strSQL As String


Set db = CurrentDb

'Check if query exist, then create the query if it's not found,
'and assign the query to the variable.
If Not QueryExists("qrySearch") Then
Set qdf = db.CreateQueryDef("qrySearch")
Else
Set qdf = db.QueryDefs("qrySearch")
End If



'This code for SubTypeCat works
If IsNull(Me.cboSubTypeCat.Value) Or Me.cboSubTypeCat = "" Then
strSubTypeCat = " Like '*' "
Else
'After the second =, the ' needs to be there for string.
'Make sure it's closed out by " ' "
strSubTypeCat = "='" & Me.cboSubTypeCat.Value & "'"
End If

'This code for ASAPS doesn't work
If IsNull(Me.chkASAPS.Value) Then
blnASAPS = False
Else
'After the second =, the ' needs to be there for string.
'Make sure it's closed out by " ' "
blnASAPS = Me.chkASAPS.Value
End If

'SQL string to retreive search
strSQL = "SELECT CaseAssignment.ID, [ReviewerFN]+"", ""+[ReviewerLN] AS FullName, CaseAssignment.CaseYear," & _
"CaseAssignment.DateReceived, CaseAssignment.DateAssigned, CaseAssignment.DateCompleted, CaseAssignment.DesignationDate," & _
"CaseAssignment.DesignationStatus, County.County, CaseAssignment.MSSA, Name.Name, Type.Type, SubType.SubType," & _
"SubTypeCat.SubTypeCat, CaseAssignment.Status, CaseAssignment.HpsaID, CaseAssignment.FTE, CaseAssignment.Score," & _
"CaseAssignment.Comment, CaseAssignment.CaseID, CaseAssignment.ASAPS, CaseAssignment.ProvRatio, CaseAssignment.FacName " & _
"FROM Type INNER JOIN (SubTypeCat INNER JOIN (SubType INNER JOIN (Reviewer INNER JOIN (Name INNER JOIN" & _
"(County INNER JOIN CaseAssignment ON County.CountyID = CaseAssignment.CountyID) ON Name.NameID = CaseAssignment.NameID)" & _
"ON Reviewer.ReviewerID = CaseAssignment.ReviewerID) ON SubType.SubTypeID = CaseAssignment.SubTypeID)" & _
"ON SubTypeCat.SubTypeCatID = CaseAssignment.SubTypeCatID) ON Type.TypeID = CaseAssignment.TypeID " & _
"WHERE CaseAssignment.ID" & strCaseAssignment & _
"AND CaseAssignment.CaseYear BETWEEN " & strYear & _
"AND " & strEndYear & _
"AND [ReviewerFN]+"", ""+[ReviewerLN]" & strReviewer & _
"AND County.County" & strCounty & _
"AND CaseAssignment.MSSA" & strMSSA & _
"AND Name.Name" & strName & _
"AND CaseAssignment.Status" & strStatus & _
"AND Type.Type" & strType & _
"AND SubType.SubType" & strSubType & _
"AND SubTypeCat.SubTypeCat" & strSubTypeCat & _
"AND CaseAssignment.ASAPS" & blnASAPS & _
"ORDER BY CaseAssignment.ID;"

' Pass the SQL string to the query
qdf.SQL = strSQL

here is an image of the error im getting...


im sure this is a quick and easy fix, but i can't see it, could it be due to the way the values are stored and called? ie. stored as .value = 0 and when called it reads .value = false
thanks in advance
Logged

 
k1dfreeze
Full Member
***

Karma: +0/-0
Offline Offline

Gender: Male
Posts: 57


Bookmark and Share

View Profile
« Reply #1 on: April 23, 2007, 09:43:46 PM »

***SOLVED***

i knew it was something stupid...i had the ASAPS checkbox as a boolean, so i made it a string and tweaked the sql select statement a bit and now it works lol...

If IsNull(Me.chkASAPS) Or Me.chkASAPS.Value = 0 Then
strASAPS = " No "
Else
strASAPS = " Yes "
End If


strSQL = "SELECT CaseAssignment.ID, [ReviewerFN]+"", ""+[ReviewerLN] AS FullName, CaseAssignment.CaseYear, CaseAssignment.DateReceived, CaseAssignment.DateAssigned, CaseAssignment.DateCompleted, CaseAssignment.DesignationDate, CaseAssignment.DesignationStatus, County.County, CaseAssignment.MSSA, Name.Name, Type.Type, SubType.SubType, SubTypeCat.SubTypeCat, CaseAssignment.Status, CaseAssignment.HpsaID, CaseAssignment.FTE, CaseAssignment.Score, CaseAssignment.Comment, CaseAssignment.CaseID, CaseAssignment.ASAPS, CaseAssignment.ProvRatio, CaseAssignment.FacName " & _
"FROM Type INNER JOIN (SubTypeCat INNER JOIN (SubType INNER JOIN (Reviewer INNER JOIN (Name INNER JOIN (County INNER JOIN CaseAssignment ON County.CountyID = CaseAssignment.CountyID) ON Name.NameID = CaseAssignment.NameID) ON Reviewer.ReviewerID = CaseAssignment.ReviewerID) ON SubType.SubTypeID = CaseAssignment.SubTypeID) ON SubTypeCat.SubTypeCatID = CaseAssignment.SubTypeCatID) ON Type.TypeID = CaseAssignment.TypeID " & _
"WHERE CaseAssignment.ID" & strCaseAssignment & _
"AND CaseAssignment.CaseYear BETWEEN " & strYear & _
"AND " & strEndYear & _
"AND [ReviewerFN]+"", ""+[ReviewerLN]" & strReviewer & _
"AND County.County" & strCounty & _
"AND CaseAssignment.MSSA" & strMSSA & _
"AND Name.Name" & strName & _
"AND CaseAssignment.Status" & strStatus & _
"AND Type.Type" & strType & _
"AND SubType.SubType" & strSubType & _
"AND SubTypeCat.SubTypeCat" & strSubTypeCat & _
"AND CaseAssignment.ASAPS =" & strASAPS & _
"ORDER BY CaseAssignment.ID;"
Logged

 
Pages: [1] Go Up Print 
 
Jump to:  

Powered by MySQL Powered by PHP

Powered by SMF 1.1.21 | SMF © 2015, Simple Machines

Valid XHTML 1.0! Valid CSS!

Disclaimer
This site is NOT responsible for any damage that the information on this site may cause to your system. Everything you try, whether inspired by the response given from this site or not, is entirely at your own risk. All product names and company names used herein are for identification purpose only and may be trademarks or registered trademarks of their respective owners. We are in no way affiliated or representing any of the companies on this site unless specified.
Back to Top
Stop Spam Harvesters, Join Project Honey Pot Fight Back Against Spammers! Get Firefox! Get Thunderbird! View Sylvain Amyots profile on LinkedIn
Back to Top
Google visited last this page January 04, 2019, 01:42:24 PM