When Start and End date is empty no values are returned

[maria28gr]

Through ??_Control FORM I give values to ????????????? , hmer_voith and hmer2_voith.
If I don’t type any values to hmer_voith and hmer2_voith I take no results at all.
hmer_voith and hmer2 are DATE fields.

In VBA I have the following code for these fields:

If IsNull(Me!hmer_apo) Then
Me!hmer_voith = "*"
Else
Me!hmer_voith = Me!hmer_apo
End If

If IsNull(Me!hmer_eos) Then
Me!hmer2_voith = "*"
Else
Me!hmer2_voith = Me!hmer_eos
End If


The query I made with Access Query Builder is:

SELECT ???????????????????.AA, ???????????????????.?????????
FROM ?????????????????

 INNER JOIN ((??????????????????? LEFT JOIN ????????? ON ???????????????????.????????? = ?????????.???????) LEFT JOIN ???????????????????????? ON ???????????????????.????????????? = ????????????????????????.??) ON ?????????????????.????????????????? = ???????????????????.?????????

WHERE (((???????????????????.AA) Like [Forms]![??_Control]![??????]) AND ((???????????????????.?????????) Like [Forms]![??_Control]![?????????????])
 AND ((Format([Forms]![??_Control]![hmer2_voith],'yyyymmdd')) Is Null) 
AND ((Format([???????????????????].[?????????],'yyyymmdd'))>=
Format([Forms]![??_Control]![hmer_voith],'yyyymmdd'))) OR (((???????????????????.AA) Like [Forms]![??_Control]![??????])
 AND ((???????????????????.?????????) Like [Forms]![??_Control]![?????????????]) 
AND ((Format([???????????????????].[?????????],'yyyymmdd'))
<=Format([Forms]![??_Control]![hmer2_voith],'yyyymmdd') 
And (Format([???????????????????].[?????????],'yyyymmdd'))
>=Format([Forms]![??_Control]![hmer_voith],'yyyymmdd')))

ORDER BY ???????????????????.AA;

 

[PHV]

I wonder you don't have syntax error with such SQL code ;-)
hmer_voith and hmer2 are DATE fields
So, why are you doing this ?
Me!hmer_voith = "*"

(Format([Forms]![??_Control]![hmer2_voith],'yyyymmdd')) Is Null
Can't happen

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[maria28gr]

sorry about "????" shown in the question.

(Format([Forms]![AP_Control]![hmer2_voith],'yyyymmdd')) Is Null
Why can't this happen? If i don't type any value to the form hmer2_voith will be null, so i say that if that's null then take all the values (*) meeting the criteria from the table