Solved Query between two dates (1 Viewer)

Pop_Access

Member
Local time
Yesterday, 18:44
Joined
Aug 19, 2019
Messages
37
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

Well-known member
Local time
Yesterday, 20:44
Joined
Mar 13, 2021
Messages
1,323
the query would be written like this:
Code:
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

Super Moderator
Staff member
Local time
Today, 02:44
Joined
Sep 12, 2006
Messages
14,732
^^^ 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

Member
Local time
Yesterday, 18:44
Joined
Aug 19, 2019
Messages
37
the query would be written like this:
Code:
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

..forever waiting... waiting for jellybean!
Local time
Today, 10:44
Joined
May 7, 2009
Messages
15,030
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

Super Moderator
Staff member
Local time
Today, 02:44
Joined
Sep 12, 2006
Messages
14,732
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

Member
Local time
Yesterday, 18:44
Joined
Aug 19, 2019
Messages
37
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)))



1633006593669.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:44
Joined
May 7, 2009
Messages
15,030
Code:
.. 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#)
 

Users who are viewing this thread

Top Bottom