Solved Query between two dates

[Pop_Access]

Hi All,

I want to create a query to display the records based on the date range that entered on a form, this form contained two parameters
1- Employee location , 2- date of employment (from date .... To date....)

I need to display the employees records who were employed within the date range on the form, but if i didn't fill the dates fields (from ... To..) I need to display all records even if (employment date) field is empty in the table.

sorry; I am not an programming expert!

Thank you.

 

[conception_native_0123]

the query would be written like this:

select * from table where (datefield =< forms!formname!textboxname or
forms!formname!textboxname is null) and (datefield2 >= forms!formname!textbox2name or
forms!formname!textbox2name is null)

 

[gemma-the-husky]

^^^ I am not sure about the above post.

because you are dealing with dates, your command may need to wrap the comparison date with ## characters
eg #01/01/2021#

You also may need to manage/disambiguate the date format because the command may try to use US dates.
#01/09/2021# will be Jan 9th, not 1st Sept.

Try hard coding your filter, and then work out how to replace the hard coded values with variables.

 

[Pop_Access]

the query would be written like this:

select * from table where (datefield =< forms!formname!textboxname or
forms!formname!textboxname is null) and (datefield2 >= forms!formname!textbox2name or
forms!formname!textbox2name is null)

Thank you, but I have one date field (employment date) only.

 

[arnelgp]

select * from your table where [employee location] =IIF(IsNull([Forms]![yourForm]![txtLocation]), [employee location],
([Forms]![yourForm]![txtLocation]) And
[employment date] Between Nz([Forms]![yourForm]![FromDate],#1/1/1800#) And Nz([Forms]![yourForm]![ToDate],#12/31/9999#)

 

[gemma-the-husky]

Thank you, but I have one date field (employment date) only.

It's not critical.

For each employee you have a start and finish date, or maybe just a start date.

So you want to select employees where the check date is >= employment start date, and either there is no employment end date or the check date is < employment end date.

So that's going to be the same or similar to the examples already given, but the query needs to have the correct syntax, which may need the dates to be presented within ## qualifiers.

 

[Pop_Access]

Really I want to thank all of you,

The following code is written in the query in field "Field":-

IIf([Forms]![FrmRepEmp]![Txtdatfrm] Is Null Or [Forms]![FrmRepEmp]![TxtdatTo] Is Null,True,IIf([master]![Date_of_Emplo] Is Null,Null,IIf([Master]![Date_of_Emplo] Between [Forms]![FrmRepEmp]![Txtdatfrm] And [Forms]![FrmRepEmp]![TxtdatTo],[Master]![Date_of_Emplo],Null)))

 

[arnelgp]

.. where Nz([master].[Date_of_Emplo], #1/1/1800#) 
Between 
Nz([Forms]![FrmRepEmp]![Txtdatfrm], #1/1/1800#) 
And 
Nz([Forms]![FrmRepEmp]![TxtdatTo], #12/31/9999#)