Search form not working corerctly with dates

[mesba713]

Hallo Everyone,
I am really new to access and is overwhelmed with this probably simple query.
I have one table and a form based on this table.
In this form the user can select different criteria to filter the result.
But the Problem is with the fields [Forms]![Vergabeumfang]![500_Termin_von_Box] and [Forms]![Vergabeumfang]![500_Termin_Bis_Box]
its date from and date to . the user should be able to see the records within these dates. But if they are empty then all the records should be shown regardless if they are empty or filled with regards to the other criteria in the form like "((Vergabeumfang.V_Einkauf_Name)" but when i leave it empty the query shows only the records where Vergabeumfang.[500_Plan]) is empty and not all.



Any help would be highly appreciated

SELECT *
FROM Vergabeumfang
WHERE (((Vergabeumfang.V_Commodity_Nr) Like "*" & [Forms]![Vergabeumfang]![Commoduity_Nr_Box] & "*") AND ((Vergabeumfang.Einkaufsvorgang_Id) Like "*" & [Forms]![Vergabeumfang]![Vorgang ID box] & "*") AND ((Vergabeumfang.Produktlinien) Like "*" & [Forms]![Vergabeumfang]![Produktlinien Box] & "*") AND ((Vergabeumfang.Entscheidungsmethode) Like "*" & [Forms]![Vergabeumfang]![Entscheidungsmethode_Box] & "*") AND ((Vergabeumfang.Vergabestatus) Like "*" & [Forms]![Vergabeumfang]![Vergabestatus_Box] & "*") AND ((Vergabeumfang.V_Einkauf_Name) Like "*" & [Forms]![Vergabeumfang]![Einkauf_Name_Box] & "*") AND ((Vergabeumfang.V_QMT_Name) Like "*" & [Forms]![Vergabeumfang]![QMT_Name_Box] & "*") AND ((Vergabeumfang.V_Logistik_Name) Like "*" & [Forms]![Vergabeumfang]![Logistik_Name_Box] & "*") AND ((Vergabeumfang.V_Entwicklung_Name) Like "*" & [Forms]![Vergabeumfang]![Entwicklung_Name_Box] & "*") AND ((Vergabeumfang.Einkaufsvorgang) Like "*" & [Forms]![Vergabeumfang]![Einkaufvorgang_Box] & "*")





AND (IIF( [Forms]![Vergabeumfang]![500_Termin_bis_Box] Is Null and [Forms]![Vergabeumfang]![500_Termin_von_Box] Is Null , (Vergabeumfang.[500_Plan]) Is Null ,
(Vergabeumfang.[500_Plan]) Between [Forms]![Vergabeumfang]![500_Termin_von_Box] And [Forms]![Vergabeumfang]![500_Termin_bis_Box]
) )




);

[lfpm062010]

Have you try use "CDATE" or "#" to convert the date string on the form?

Like
- CDATE([Forms]![Vergabeumfang]![500_Termin_von_Box])
- CDATE([Forms]![Vergabeumfang]![500_Termin_Bis_Box])

[mesba713]

thanks for the advice it was a stupid naming problem in the sql it is solved now .

Have you try use "CDATE" or "#" to convert the date string on the form?

Like
- CDATE([Forms]![Vergabeumfang]![500_Termin_von_Box])

- CDATE([Forms]![Vergabeumfang]![500_Termin_Bis_Box])