expression typed incorrectly or is too complicated

[ali37]

hi there, im a relative newbie to access and have been recently employed creating a basic database for a small company. i have been trying to create a 'search engine' which would search through all records of a table through the use of a sub form on a form, and unbound fields. What i want and have accomplished on a simpler version of this is the ability to enter into just one of the fields and the list of records in the subform reduces and matches to the entry in the field.

Now i say ive already accomplished this without using drop down boxes. However, ive begun to build a second search engine but this one comes up with expression typed incorrectly etc. i read that this could be due to the sql being too long, and one way around it is to convert it into VB. the sql for the said query is...

SELECT [Certificate details].[Certificate ID], [Certificate details].[Learner First Name], [Certificate details].[Learner Second Name], [Certificate details].[Course Name], [Certificate details].[Qualification type], [Certificate details].[Validity Length(yrs)], [Certificate details].[Date of Completion], [Certificate details].[Date of Expiry], [Certificate details].Instructor
FROM [Certificate details]
WHERE ((([Certificate ID]=[Forms]![Form1]![txtCertID] & "*" Or [Forms]![Form1]![txtCertID] Is Null)=True) AND (([Learner First Name] Like "*" & [Forms]![Form1]![txtFirstName] & "*" Or [Forms]![Form1]![txtFirstName] Is Null)=True) AND (([Learner Second Name] Like "*" & [Forms]![Form1]![txtSecondName] & "*" Or [Forms]![Form1]![txtSecondName] Is Null)=True) AND (([DOB]=[Forms]![Form1]![txtDOB] Or [Forms]![Form1]![txtDOB] Is Null)=True) AND (([Course Name] Like "*" & [Forms]![Form1]![txtCourseName] & "*" Or [Forms]![Form1]![txtCourseName] Is Null)=True) AND (([Qualification Type]=[Forms]![Form1]![CBOQualType] Or [Forms]![Form1]![CBOQualType] Is Null)=True) AND (([Validity Length(yrs)]=[Forms]![Form1]![txtQualLength] Or [Forms]![Form1]![txtValidLength] Is Null)=True) AND (([Date of Completion]=[Forms]![Form1]![txtDateComp] Or [Forms]![Form1]![txtDateComp] Is Null)=True) AND (([Date of Expiry]=[Forms]![Form1]![txtDateExp] Or [Forms]![Form1]![txtDateExp] Is Null)=True) AND (([Instructor]=[Forms]![Form1]![CBOInstructor] Or [Forms]![Form1]![CBOInstructor] Is Null)=True));

can someone help in converting this into visual basic if needs be, and where i would put this code, thanks

 

[Brianwarnock]

The = TRUE Suggests that you created this in the design grid, it is not needed.

There appears to be a LIKE missing for the first criteria as you have a wildcard in there.

Are you using Like because you want to be able to use partial string searches?

Brian

 

[ali37]

yes i used the design view within the query and typed in the instructions within the columns. the partial query is what i was going for with the fields first name, second name, company name and course name, but the missing like was a typo, that particular field shouldnt be partial. is there an issue with using the design view to do this in?

fixed sql

SELECT [Certificate details].[Certificate ID], [Certificate details].[Learner First Name], [Certificate details].[Learner Second Name], [Certificate details].[Course Name], [Certificate details].[Qualification type], [Certificate details].[Validity Length(yrs)], [Certificate details].[Date of Completion], [Certificate details].[Date of Expiry], [Certificate details].Instructor
FROM [Certificate details]
WHERE ((([Certificate ID]=[Forms]![Form1]![txtCertID] Or [Forms]![Form1]![txtCertID] Is Null)=True) AND (([Learner First Name] Like "*" & [Forms]![Form1]![txtFirstName] & "*" Or [Forms]![Form1]![txtFirstName] Is Null)=True) AND (([Learner Second Name] Like "*" & [Forms]![Form1]![txtSecondName] & "*" Or [Forms]![Form1]![txtSecondName] Is Null)=True) AND (([DOB]=[Forms]![Form1]![txtDOB] Or [Forms]![Form1]![txtDOB] Is Null)=True) AND (([Course Name] Like "*" & [Forms]![Form1]![txtCourseName] & "*" Or [Forms]![Form1]![txtCourseName] Is Null)=True) AND (([Qualification Type]=[Forms]![Form1]![CBOQualType] Or [Forms]![Form1]![CBOQualType] Is Null)=True) AND (([Validity Length(yrs)]=[Forms]![Form1]![txtQualLength] Or [Forms]![Form1]![txtValidLength] Is Null)=True) AND (([Date of Completion]=[Forms]![Form1]![txtDateComp] Or [Forms]![Form1]![txtDateComp] Is Null)=True) AND (([Date of Expiry]=[Forms]![Form1]![txtDateExp] Or [Forms]![Form1]![txtDateExp] Is Null)=True) AND (([Instructor]=[Forms]![Form1]![CBOInstructor] Or [Forms]![Form1]![CBOInstructor] Is Null)=True));