Search by Date Range and Keyword (1 Viewer)

[argel]

Hello guys ! At the moment I have a search by keyword and date range with two buttons search,i.e., making the search individually. I'm having two problems. The first one is with the date format; with 2 digit on the day it works fine, however with one it makes a wrong search. I need that the date format be dd-mm-aaaa. How can I change that, since it's not an option in the format section? The second problem is that I can't cross the two searches (keyword and date range). Is it possible to make the search only with one button? Or, if with two, how can cross the search?


The code that I've build is :


--->KEYWORD :


Private Sub btnSearchMOB_Click()

Dim STR As String

STR = " SELECT LISTA_EQ.ID, LISTA_EQ.[No INV], LISTA_EQ.DESCRICAO, LISTA_EQ.MARCA, LISTA_EQ.MODELO, LISTA_EQ.[Num_SERIE / MATRICULA], LOC.MOBILIZAÇÃO, LOC.[CENTRO CUSTO LOCAL], LOC.[LOCALIZAÇÃO/UTILIZADOR], LOC.SITUAÇÃO, LOC.NEGLIGÊNCIA, LOC.[LOCAL DE REPARAÇÃO] " _
& " FROM LISTA_EQ INNER JOIN LOC ON LISTA_EQ.ID = LOC.ID " _
& " WHERE [No INV] LIKE '*" & Me.search_EQ_MOB_box & "*' " _
& " OR DESCRICAO LIKE '*" & Me.search_EQ_MOB_box & "*' " _
& " OR MARCA LIKE '*" & Me.search_EQ_MOB_box & "*' " _
& " OR MODELO LIKE '*" & Me.search_EQ_MOB_box & "*' " _
& " OR [Num_SERIE / MATRICULA] LIKE '*" & Me.search_EQ_MOB_box & "*' " _
& " OR ([LISTA_EQ.ID] = " & Me.search_EQ_MOB_box & ") " _
& "ORDER BY LISTA_EQ.[No INV], LOC.MOBILIZAÇÃO DESC"


Me.search_mob_list.Form.RecordSource = STR
Me.search_mob_list.Form.Requery

End Sub


===============================================


--->DATE RANGE :

Private Sub cmd_Search_Date_Click()

Dim STR, DATE_RANGE As String


DATE_RANGE = " ([LOC.MOBILIZAÇÃO] >= #" & Me.data_incio & "# And [MOBILIZAÇÃO] <= #" & Me.data_fim & "#) "

STR = " SELECT LISTA_EQ.ID, LISTA_EQ.[No INV], LISTA_EQ.DESCRICAO, LISTA_EQ.MARCA, LISTA_EQ.MODELO, LISTA_EQ.[Num_SERIE / MATRICULA], LOC.MOBILIZAÇÃO, LOC.[CENTRO CUSTO LOCAL], LOC.[LOCALIZAÇÃO/UTILIZADOR], LOC.SITUAÇÃO, LOC.NEGLIGÊNCIA, LOC.[LOCAL DE REPARAÇÃO] " _
& " FROM LISTA_EQ INNER JOIN LOC ON LISTA_EQ.ID = LOC.ID " _
& " WHERE (" & DATE_RANGE & ") " _
& "ORDER BY LOC.MOBILIZAÇÃO DESC"


Me.search_mob_list.Form.RecordSource = STR
Me.search_mob_list.Form.Requery


End Sub


===============================================

 

[June7]

Review http://allenbrowne.com/ser-36.html

 

[argel]

Review http://allenbrowne.com/ser-36.html

Thanks! To let you better understand what my problem is, I'll send my db file in attach!

 

[Gasman]

Debug.Print the date variables.

 

[argel]

Debug.Print the date variables.

Hi! Thanks for reply. I can't understand what you mean ...

 

[Gasman]

https://www.techonthenet.com/access/tutorials/vbadebug/debug02.php

No matter how you need to view the dates, they need to be in mm/dd/yyyy (or I believe recently yyyymmdd ?) format for SQL

Another poster was having problems with date format

https://www.access-programmers.co.uk/forums/showthread.php?t=303767

HTH

 

[argel]

https://www.techonthenet.com/access/tutorials/vbadebug/debug02.php

No matter how you need to view the dates, they need to be in mm/dd/yyyy (or I believe recently yyyymmdd ?) format for SQL

Another poster was having problems with date format

https://www.access-programmers.co.uk/forums/showthread.php?t=303767

HTH

Thank you for the help! Can you take a look to my db that I upload above? I've been trying put as you say but I can't... I'm new on this world.

 

[Gasman]

What version of Access are you using as I only have 2007 and it will not open most later version DBs

 

[argel]

What version of Access are you using as I only have 2007 and it will not open most later version DBs

I'm using the 2013 version. But on the format file it says 2007-2013

 

[Gasman]

So where is this code located?

 

[argel]

So where is this code located?

Sorry! I should told you! Is in the Form "SEARCH_MOB" . Where says "De" is "Date from" and "Até" is "Date To".

 

[Gasman]

Sorry, I'm confused (easily done I know)

It appears to work for me, whether I enter 7-jul-2018 or 7/7/2018 ?

BTW if you change the recordsource, no need to requery, your are just repeating the query?

 

[Gasman]

Re combining, I would build the SQL depending on what fields are populated.

Also your ORDER BY is different for each?

 

[argel]

Sorry, I'm confused (easily done I know)

It appears to work for me, whether I enter 7-jul-2018 or 7/7/2018 ?

BTW if you change the recordsource, no need to requery, your are just repeating the query?

I had already this format problem when I uploaded the table from the excel. " That type of date it's not allowed in my system (Portugal), don't know why. If you go to table,for example, "LISTA_EQ", on the field "MOBILIZAÇÃO" you'll see that the date format is " dd-mm-aaaa;@. I had to put it that way in order to upload the data to Access. When I was searching on the internet to make this searches all tutorials introduce that line of code. They say it's to continuously update the query.

 

[argel]

Re combining, I would build the SQL depending on what fields are populated.

Also your ORDER BY is different for each?

In this form I really need to the search by equipment or by date. My ORDER BY is now equal to every search. I miss it at the time I uploaded the file.

 

[Gasman]

AFAIAA a date is a date.?
I have just changed the format in that table to dd/mm/yyyy which is the format I would use here in the UK.

Your date search still appears to work.
As long as you upload a date and not text that looks like a date, you should be fine.

Requery is required in some instances, but changing the recordsource automatically initiates a requery. I discovered this when I started amending recordsources.

 

[Gasman]

In this form I really need to the search by equipment or by date.

Which to me it appears to do.:banghead:

If you just want to use one button, test which controls have values, perhaps issue a warning if both MOB and dates are completed.?

In the button, depending on what control is completed, issue the relevant SQL string.

 

[argel]

AFAIAA a date is a date.?
I have just changed the format in that table to dd/mm/yyyy which is the format I would use here in the UK.

Your date search still appears to work.
As long as you upload a date and not text that looks like a date, you should be fine.

Requery is required in some instances, but changing the recordsource automatically initiates a requery. I discovered this when I started amending recordsources.

Even if I upload with the date format it keep send error unless I change the format to dd-mm-yyyy, I really don't know why... :banghead: Can you send me the file that you change? In order to see if it works for me now. Thank you for the help and for being so patient !

 

[Gasman]

Of course.

File attached

Requery also commented out.

 

[argel]

Of course.

File attached

Requery also commented out.

When I open your file, the software automatically change the format to "dd-mm-yyyy;@" and the search still not match ... :banghead::banghead::banghead::banghead::banghead: