Search Between Date in form

**ijaz8883** · 05-08-2022, 11:09 PM

Hi Experts
I am facing a problem in a search form. The Search is working perfect but there is a problem Its searching the date as mm/dd/yyyy as today date "05/09/2022" however I want to search as dd/mm/yyyy. My Computer Date and Database Date is also set as dd/mm/yyyy as today date "09/05/2022"

Const conJetDate = "dd/MM/yyyy"
varWhere = Null
it is also entered in codes

I am using the code:
Private Sub txtdatefrom_AfterUpdate()
Me.RecordSource = "SELECT * FROM Complete_Criminal " & BuildFilter
Me.Requery
End Sub

If Me.txtdatefrom > "" Then
varWhere = varWhere & "([decision_date] >= " & Format(Me.txtdatefrom, conJetDate) & ") AND "
End If

Its working as mm/dd/yyyy
But when I search as:

If Me.txtdatefrom > "" Then
varWhere = varWhere & "[decision_date] like '*" & Me.txtdatefrom & "*' AND "
End If
It works perfect as I desired but its search the exact date but I want to search date from and date to, How can I add grater than less than with like as like => , like =<.
whenever I tried to write the dual statements its give errors.
Let me know what to do?

Click image for larger version.

Name: Structure.jpg
Views: 44
Size: 25.7 KB
ID: 47767

**June7** · 05-08-2022, 11:55 PM

Wildcards don't work well with number and date/time data, they are intended for text.

"[DecisionDate] >= #" & Me.txtDateFrom & "# AND [DecisionDate]<=#" & Me.txtDateTo & "#"

Or

"[DecisionDate] BETWEEN #" & Me.txtDateFrom & "# AND #" & Me.txtDateTo & "#"

**Welshgasman** · 05-09-2022, 12:19 AM

No need to requery either, if you just set recordsource.

**moke123** · 05-09-2022, 06:54 AM

Also note that dates must be delimited with octothorpes #

**ijaz8883** · 05-09-2022, 09:40 PM

Originally Posted by June7

Wildcards don't work well with number and date/time data, they are intended for text.

[DecisionDate] >= #" & Me.txtDateFrom & "# AND [DecisionDate]<=#" & Me.txtDateTo & "#"

Or

[DecisionDate] BETWEEN #" & Me.txtDateFrom & "# AND #" & Me.txtDateTo & "#"

Sorry to say dear I dont understand where to put this code because I am biginner.
I am attaching my DB Sample and lets check that when I search date in like command its work perfect but when I search dates with other => commands its not showing the dates as dd/mm/yyyy.
Let me know where to put code that its work perfect as I desired.
Note: I have also other multiple search fields like text search with these date fiels that are working but I have deleted so that you feel relex to work with the above said problem.

Record Check.zip

**ijaz8883** · 05-09-2022, 09:43 PM

Thanks I shall take care for the next. I just have copied the code from other DB already available on net.

**ijaz8883** · 05-09-2022, 09:44 PM

Originally Posted by moke123

Also note that dates must be delimited with octothorpes #

octothorpes # not working in my db lets check I have attached my DB Dample

**June7** · 05-09-2022, 10:01 PM

I provided you two options for date criteria to use in your code to replace the failing string you posted.

However, your dates are international structure so review http://allenbrowne.com/ser-36.html and http://allenbrowne.com/ser-62code.html

**ijaz8883** · 05-10-2022, 01:38 AM

Originally Posted by June7

I provided you two options for date criteria to use in your code to replace the failing string you posted.

However, your dates are international structure so review http://allenbrowne.com/ser-36.html and http://allenbrowne.com/ser-62code.html

I have tried this one but its not working. If you have time please check my DB and make necessary correction. Thanks

**moke123** · 05-10-2022, 09:37 AM

From Allen Browne:

In VBA code, delimit dates with the "#" symbol. Regardless or your regional settings, Access expects these literal dates to be in the American format, e.g. #12/31/1999#.

you are using

Code:

Const conJetDate = "\#dd\/mm\/yyyy\#"

Allen's code is

Code:

Const conJetDate = "\#mm\/dd\/yyyy\#"

I used the below which appeared to work fine

Code:

Me.RecordSource = "SELECT * FROM tblCriminal where [decision_date] between #" & Me.txtdatefrom & "#  and # " & Me.txtDateTo & "#"

you may want to use a filter.
You can format a field in your query to display the date as dd/mm/yyyy

Code:

Dim strFilter As String
strFilter = "[decision_date] between #" & Me.txtdatefrom & "#  and # " & Me.txtDateTo & "#"
Me.Filter = strFilter
Me.FilterOn = True

**ijaz8883** · 05-10-2022, 11:09 PM

Originally Posted by moke123

From Allen Browne:

you are using

Code:

Const conJetDate = "\#dd\/mm\/yyyy\#"

Allen's code is

Code:

Const conJetDate = "\#mm\/dd\/yyyy\#"

I used the below which appeared to work fine

Code:

Me.RecordSource = "SELECT * FROM tblCriminal where [decision_date] between #" & Me.txtdatefrom & "#  and # " & Me.txtDateTo & "#"

you may want to use a filter.
You can format a field in your query to display the date as dd/mm/yyyy

Code:

Dim strFilter As String
strFilter = "[decision_date] between #" & Me.txtdatefrom & "#  and # " & Me.txtDateTo & "#"
Me.Filter = strFilter
Me.FilterOn = True

Sorry to Say its not worked

.
However, you suddenly give me another way to think and I got it

by coding manually. I don't know I do good or bad but I have done my work. may be in future its make problem, if so I shall consult you again in this forum

.
I just Delete the Code: Const conJetDate = "\#dd\/mm\/yyyy\#" and maunally entered in command line "varWhere = varWhere & "([decision_date] >= " & Format(Me.txtdatefrom, dd - mm - yyyy) & ") AND " " and its (done its (dd - mm - yyyy) worked for me as I desired.
However thanks to all for giving me new thaught

. Glade to see you all.

This is the best forum to discuss Access problems.

**Welshgasman** · 05-11-2022, 02:20 AM

No, that is not going to work.

**moke123** · 05-11-2022, 05:40 AM

Access stores dates as the number of days since 12/31/1899. So todays date is stored as 44692.
Jet/Ace uses the U.S. date format regardless of what format your computer is set to in your regional settings.
Therefore to get accurate results you need to convert your international dates to a format that jet/ace uses.

Code:

Const conJetDate = "\#mm\/dd\/yyyy\#"
Format(Me.txtdatefrom, conJetDate)

Added bonus to the above is that it delimits your dates with octothorpes so access knows it is a date datatype.

**Minty** · 05-11-2022, 12:26 PM

To avoid any confusion, if you format your dates #yyyy-mm-dd# access always get it's conversions right, and if you are debugging it's much easier to see the exact date being passed around, without wondering what format is being used or displayed.

This works everywhere no matter what your regional settings are.

**ijaz8883** · 05-15-2022, 10:19 PM

Originally Posted by Minty

To avoid any confusion, if you format your dates #yyyy-mm-dd# access always get it's conversions right, and if you are debugging it's much easier to see the exact date being passed around, without wondering what format is being used or displayed.

This works everywhere no matter what your regional settings are.

Sorry but my requirement was alongwith Regional Setting so I have to do this because its familiar to us. Thanks

Search Between Date in form

Thread Tools

Search Between Date in form

Similar Threads

Date Search fields in Form - Need help pls!!!

Search form with date

Form to search for data by date range

Search Date Range by month in a Combo Box within a Form?

Search form with a date range

Posting Permissions