help with query criteria sintax

[divanalle15]

hello..
i have a parameter query that gets its parameters form a form, on that form i have 2 text boxes to provide 2 dates between whom i want the query results, but i want to be able to leave them blanck if at any time i dont need that information, i have this on the expression builder but the syntaxis is wrong

[mtr submittion date] =Between [Forms]![Form1]![Text15] And [Forms]![Form1]![Text17] or [Forms]![Form1]![Text15] And [Forms]![Form1]![Text17] is null

please help me telling me what is wrong with it

thanks

 

[SQL_Hell]

hi there

this is wrong: or [Forms]![Form1]![Text15]

replace with

([mtr submittion date] =Between [Forms]![Form1]![Text15] And [Forms]![Form1]![Text17]) or ([Forms]![Form1]![Text15] is null And [Forms]![Form1]![Text17] is null)

 

[divanalle15]

hello,
i copied that syntax on a new column in the query on the field row, is this correct?? or i have to put it on the criteria row in the field column ? because it keeps poping up a window that says that i have invalid syntax, and the word "between" is highlighted

thanks =)

 

[Rabbie]

You don't want the = before Between. That will give you a systax error. Also are Text15 and Text17 formated as TEXT or as DAte/time. If they are formatted as Text you will need to put # signs round the the dates.

ie

"([mtr submittion date] Between #" & [Forms]![Form1]![Text15] & "# And #" & [Forms]![Form1]![Text17] & "#) or ([Forms]![Form1]![Text15] is null And [Forms]![Form1]![Text17] is null)"

IF they are genuine date fields then you just need to remove the =

 

[divanalle15]

it worked!!!!!

thank you sooo much..!!!

 

[Rabbie]

it worked!!!!!

thank you sooo much..!!!

Glad to hear it.

 

[SQL_Hell]

You don't want the = before Between. That will give you a systax error. Also are Text15 and Text17 formated as TEXT or as DAte/time. If they are formatted as Text you will need to put # signs round the the dates.

ie

"([mtr submittion date] Between #" & [Forms]![Form1]![Text15] & "# And #" & [Forms]![Form1]![Text17] & "#) or ([Forms]![Form1]![Text15] is null And [Forms]![Form1]![Text17] is null)"

IF they are genuine date fields then you just need to remove the =

lol I didn't see the '=' before between, must get my eyes tested

 

[Rabbie]

lol I didn't see the '=' before between, must get my eyes tested

Thats my usual excuse at my age