Search by Date Range and Keyword

[Gasman]

Try this then

Put this code in a module

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]

Try this then

Put this code in a module

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]

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.

 

[argel]

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.

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]

Try

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]

Try

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]

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]

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]

Good you got the problem solved, but to help others, what was the problem?

 

[argel]

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]

..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).