Form Entries Leaving Entry Boxes Blank

[nickysquawkes]

I have a form that allows users to search the database for records based on there criteria. The form allows the user to search, through combo boxes, by variables. The first is MAT, the second is Relocation Area. The search works fine when the user selects options form the combo boxes. However sometimes it is neccesary to leave the location field blank and only search by MAT. When the location combo box is left blank no records are found. Is there anyway around this. The SQL code for the query is below:

SELECT [Extract Data].[ID], [Extract Data].AREA, [Extract Data].[MAT], [Extract Data].[Relocation Area 1], [Extract Data].[Relocation Area 2], [Extract Data].[Relocation Area 3], [Extract Data].[Grade Score]
FROM [Extract Data]
WHERE ((([Extract Data].[MAT])>forms![Data Search]!SearchMAT) And (([Extract Data].[Relocation Area 1])=Forms![Data Search]!SearchArea)) Or ((([Extract Data].[MAT])>Forms![Data Search]!SearchMAT) And (([Extract Data].[Relocation Area 2])=Forms![Data Search]!SearchArea)) Or ((([Extract Data].[MAT])>>Forms![Data Search]!SearchMAT) And (([Extract Data].[Relocation Area 3])=Forms![Data Search]!SearchArea));


Any help would be much appreciated
Nick

 

[cuttsy]

the way you have this set up I would say that the simplest way you can achive this is by writing 2 different queries one to search 'MAT' and another to search 'MAT' and location and then write an if statement in your VBA saying if the location field is blank then run one query else run the other one.

It's a nasty workaround but if you don't care then crack on.

 

[nickysquawkes]

Thanks cuttsy for the advice. Actually managed to solve it myself by adding an is null statement in the query criteria.

 

[cuttsy]

good cos my way was so nasty I needed a shower after I suggested it.