Search by Date Range and Keyword (1 Viewer)

Gasman

Enthusiastic Amateur
Local time
Today, 16:50
Joined
Sep 21, 2011
Messages
14,044
Try this then

Put this code in a module
Code:
Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.

Then use Format(yourdate,strcJetDate) in the sql. That will force the dates to mm/dd/yyyy format.
 

argel

Registered User.
Local time
Today, 18:50
Joined
Feb 7, 2019
Messages
29
Try this then

Put this code in a module
Code:
Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.

Then use Format(yourdate,strcJetDate) in the sql. That will force the dates to mm/dd/yyyy format.

Where should I put that line of code? when you say "yourdate" is the text box or the format "dd-mm-yyyy;@" ?

Thank you !
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:50
Joined
Sep 21, 2011
Messages
14,044
Put the code at the top of a module.

Yourdate are your date controls from the form, start and end dates.

I don't even know what that @ is meant to do.:eek:
 

argel

Registered User.
Local time
Today, 18:50
Joined
Feb 7, 2019
Messages
29
Put the code at the top of a module.

Yourdate are your date controls from the form, start and end dates.

I don't even know what that @ is meant to do.:eek:


Can you write the code with more detail? Including the place to write the Format function. I'm not sure if I'm doing the right way. Sorry!
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:50
Joined
Sep 21, 2011
Messages
14,044
Try

Code:
DATE_RANGE = " ([LOC.MOBILIZAÇÃO] BETWEEN " & Format(Me.data_incio,"strcJetDate") & " And " & Format(Me.data_fim,"strcJetDate")
Use Debug.Print DATE_RANGE to check the syntax for the string.

HTH
 

argel

Registered User.
Local time
Today, 18:50
Joined
Feb 7, 2019
Messages
29
Try

Code:
DATE_RANGE = " ([LOC.MOBILIZAÇÃO] BETWEEN " & Format(Me.data_incio,"strcJetDate") & " And " & Format(Me.data_fim,"strcJetDate")
Use Debug.Print DATE_RANGE to check the syntax for the string.

HTH


Still not working ... Thanks a lot for the help anyway!
 

JHB

Have been here a while
Local time
Today, 17:50
Joined
Jun 17, 2012
Messages
7,732
Create a new query switch to SQL-view and paste the below into it!
Does that give the correct result?
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 (((LOC.MOBILIZAÇÃO)<=#2/15/2019#) AND ((LOC.[MOBILIZAÇÃO])>=#1/1/2019#))
ORDER BY LOC.MOBILIZAÇÃO DESC;
 

argel

Registered User.
Local time
Today, 18:50
Joined
Feb 7, 2019
Messages
29
Create a new query switch to SQL-view and paste the below into it!
Does that give the correct result?


Thank you for the answer! Before I saw it, I made a simple query with date range according to the data introduce on the form, just like yours, and solved the problem! Thank you very much!
 

JHB

Have been here a while
Local time
Today, 17:50
Joined
Jun 17, 2012
Messages
7,732
Good you got the problem solved, but to help others, what was the problem?
 

argel

Registered User.
Local time
Today, 18:50
Joined
Feb 7, 2019
Messages
29
Good you got the problem solved, but to help others, what was the problem?


I don't really know... I'm working in Mozambique and, despite of the OS be formatted to portuguese, because of the region the type of date is dd-mm-yyyy. If I made a search based on date range like I was doing, I can't get the right results. It always gave me error message. With the simple query (don't know why) the search works!
 

JHB

Have been here a while
Local time
Today, 17:50
Joined
Jun 17, 2012
Messages
7,732
..because of the region the type of date is dd-mm-yyyy.
...
The same as here - but even then when I search for dates the criteria must be in US-Format, (mm/dd/yy).
 

Users who are viewing this thread

Top Bottom