Search & Filter Data between two date fields.! (1 Viewer)

[adhishvellore]

Hi all,

I have a form in access where i need to select a record between two different dates. For example i have a "valid_from" and "valid_to" field. I have an unbound text box with short date format and calendar control inserted for users to select a date. This is named "drpdate".

I have a bunch of other filters also in the same form. Now my issue is that i have not been able to figure out how to put in a SQL statement which would give me the data which is between teh "Valid_from" and "valid_to" fields based on the date selected in the unbound text box.

If the selected date does not meet the criteria, then it needs to be give the results from another table (which i have already done).

Any help would be really appreciated (if you could write down the exact code too please.!!)

Thanks in advance.!

Cheers
Adhish

 

[pr2-eugin]

Hello adhishvellore, I think what you want to do would be something along the lines of..

SELECT someField FROM someTable
WHERE validFrom >= Forms!yourForm!unBoundDate AND validTo <= Forms!yourForm!unBoundDate;

However, what do you mean by..

If the selected date does not meet the criteria, then it needs to be give the results from another table (which i have already done).

Do you mean to say, other criteria that you have already built?

 

[adhishvellore]

Hi Paul,

thanks for the reply. Yes, i do have the other criteria (just taht the same also needs the date filters.

I tried the below given code, but it is giving me some runtime errors. i am not so good in these codings and had Joern from this same forum help me construct these codes.
I have attached my database here, if that could help you.

Many thanks once again.

Cheers
Adhish

 

[adhishvellore]

It's done now...anyways thanks for helping
i was having problems with the date format where i have to # instead of "

Cheers
Adhish

 

[pr2-eugin]

Hello Adhish, Sorry.. I had changed your code, but I forgot to reply.. Got carried away with lunch..

sqlStr = "SELECT Carrier, Country, Drop_Location, Depot_Code, Depot_Name, " _
  & "Cust_Code, Cust_Name, Contract_ID, [Cont_Size/Type], Amount, Currency, Valid_From, Valid_To, Sales_Contact, Approver, Comments " _
  & "FROM PIO_Contracts " _
  & "WHERE ((Valid_From >= #" & Format(Me.drpdate, "mm/dd/yyyy") & "#) AND (Valid_To <= #" & Format(Me.drpdate, "mm/dd/yyyy") & "#)) AND (Country = '" & Me.cmbcountry & "' AND Drop_Location = '" & Me.cmblocation & "' AND Cust_Name = '" & Me.cmbcustname & "' AND ([Cont_Size/Type] = """ & Me.cmbcontsizetype & """))"

So sorry.. But I am glad you have it sorted..

 

[adhishvellore]

Thanks Paul. I did not use the "format" control though in my sql But it is better to put it in there, so i have taken your's